how to use presto to query hive data

Question

I just installed presto and when I use the presto-cli to query hive data, I get the following error:

$ ./presto --server node6:8080 --catalog hive --schema default
presto:default> show tables;
Query 20131113_150006_00002_u8uyp failed: Table hive.information_schema.tables does not exist

The config.properties is:

coordinator=true
datasources=jmx,hive
http-server.http.port=8080
presto-metastore.db.type=h2
presto-metastore.db.filename=/root/h2
task.max-memory=1GB
discovery-server.enabled=true
discovery.uri=`http://node6:8080`

And the hive.properties is:

connector.name=hive-cdh4
hive.metastore.uri=thrift://node6:9083

The hadoop distribution I used is CDH 4.4. I believe it's properly installed and hive can process queries successfully on its own.

Can anyone help me work it out? Any ideas will be appreciated.

Answer 1

As recommended by the Getting Started, I created a controller (jmx only) and a separate worker (jmx,hive), each on separate machines.

What finally solved this for me was to specify the worker's hostname and http-server.http.port as the --server argument to presto. When specifying the controller, it didn't work.

This all makes sense, but I am still wondering what will happen when I have two Presto-Hive workers...

Answer 2

Add more line to etc/catalog/hive.properties "hive.config.resources=/etc/hadoop/conf/core-site.xml,/etc/hadoop/conf/hdfs-site.xml" ofcourse check values of path before do it.

presto-metastore.db.filename= <- is this the value for Hive Warehouse Directory ? => this presto's metastore,not hive.

Answer 3

I just figured out what was wrong in my case: you also have to add following line to $HIVE_HOME/conf/hive-env.sh for informing hive to open thrift port(same as mentioned under hive.metastore.uris property in hive-site.xml file). This port is used by hive client to connect to Metastore through RPC.

export METASTORE_PORT=9084

in the hive-env.sh file in the conf folder. This should sync your hive with presto.