Presto can't fetch content in HIVE table

3
votes

My environment: hadoop 1.0.4 hive 0.12 hbase 0.94.14 presto 0.56

All packages are installed on pseudo machine. The services are not running on localhost but on the host name with a static IP.

presto conf:

coordinator=false
datasources=jmx,hive
http-server.http.port=8081
presto-metastore.db.type=h2
presto-metastore.db.filename=/root
task.max-memory=1GB
discovery.uri=http://<HOSTNAME>:8081

In presto cli I can get the table in hive successfully:

presto:default> show tables;
       Table
-------------------
ht1
k_business_d_
k_os_business_d_
...
tt1_
(11 rows)

Query 20140114_072809_00002_5zhjn, FINISHED, 1 node
Splits: 2 total, 2 done (100.00%)
0:11 [11 rows, 291B] [0 rows/s, 26B/s]

but when I try to query data from any table the result always be empty: (no error information)

presto:default> select * from k_business_d_;
 key | business | business_name | collect_time | numofalarm | numofhost | test
-----+----------+---------------+--------------+------------+-----------+------
(0 rows)

Query 20140114_072839_00003_5zhjn, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0:02 [0 rows, 0B] [0 rows/s, 0B/s]

If I executed the same sql in HIVE, the result show there are 1 row in the table.

hive> select * from k_business_d_;
OK
9223370648089975807|2   2       测试机       2014-01-04 00:00:00     NULL    1.0         NULL
Time taken: 2.574 seconds, Fetched: 1 row(s)

Why presto can't fetch from HIVE tables?

1
hivepresto
What file format does your table use? Please show the output of "DESCRIBE FORMATTED table" in Hive. – David Phillips
Thanks for your help. it's an external table on HBASE – user3189447
hive> DESCRIBE FORMATTED k_business_d_; OK # col_name data_type comment key string from deserializer business int from deserializer business_name string from deserializer collect_time string from deserializer numofalarm double from deserializer numofhost double from deserializer test double from deserializer – user3189447
# Detailed Table Information Database: default Owner: root CreateTime: Thu Jan 09 16:04:37 CST 2014 LastAccessTime: UNKNOWN Protect Mode: None Retention: 0 Location: hdfs://allinone:9000/user/hive/warehouse/k_business_d_ Table Type: EXTERNAL_TABLE Table Parameters: EXTERNAL TRUE hbase.table.name K_BUSINESS_D storage_handler org.apache.hadoop.hive.hbase.HBaseStorageHandler transient_lastDdlTime 1389254677 – user3189447
# Storage Information SerDe Library: org.apache.hadoop.hive.hbase.HBaseSerDe InputFormat: org.apache.hadoop.hive.hbase.HiveHBaseTableInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HivePassThroughOutputFormat – user3189447

1 Answers

4
votes

It looks like this is an external table that uses HBase via org.apache.hadoop.hive.hbase.HBaseStorageHandler. This is not supported yet, but one mailing list post indicates it might be possible if you copy the appropriate jars to the Hive plugin directory: https://groups.google.com/d/msg/presto-users/U7vx8PhnZAA/9edzcK76tD8J