Querying hive schema with HAWQ and PXF

Question

I know it's possible to query data managed by Hive with HAWQ, and HCatalog, I have installed Hortonworks ambari that installs all the tools needed. To query the hive i schema followed pivotal instructions where it says to enter PostgreSQL and do the command SET pxf_service_address TO "localhost:51200", but I always get the error unrecognized configuration parameter "pxf_service_address". Does anyone have this problem before?

Answer 1

Your version() output indicates you are connecting to a PostgreSQL instance and this is NOT HAWQ.

The default port for both PostgreSQL and HAWQ is 5432 so on a single node, one has to be changed. I think it is a bug that Hortonworks has decided to change the HAWQ port instead of the PostgreSQL port. So if you want to give some feedback to Hortonworks, tell them to change the PostreSQL port to something other than 5432.

Find hawq_master_address_port in your /usr/local/hawq/etc/hawq-site.xml file. The value will be something other than 5432. You should also look at the hawq_master_address_host value and make sure you are on the Master host. So ssh to your master host to connect via psql.

Now, armed with your HAWQ port and logged in as gpadmin on the Master host, edit your .bashrc file. Add:

export PGPORT=<hawq_master_address_port>

Substitute hawq_master_address_port with the value from hawq-site.xml.

Now source your .bashrc file and then run:

psql template1

I'm also guessing you don't have a gpadmin database so create it.

psql -d template1 -c "create database gpadmin;"

Now you can just run "psql" and connect to HAWQ.