我试图在Hbase表上创建Hive表。详细信息如下所示:
HBase表具有类似的数据
Connected to: Phoenix (version 4.7)
Driver: PhoenixEmbeddedDriver (version 4.7)
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
Building list of tables and columns for tab-completion (set fastconnect to true to skip)...
1341/1341 (100%) Done
Done
sqlline version 1.1.8
0: jdbc:phoenix:maxiqtesting1.lti.com:2181:/h>
select * from HBASE_TEST_6JULY_1792409;
+---------+---------+---------+
| FIELD0 | FIELD1 | FIELD2 |
+---------+---------+---------+
| 1 | qq | 23 |
| 2 | ee | 12 |
| 3 | dd | 123 |
+---------+---------+---------+
3 rows selected (0.139 seconds)
0: jdbc:phoenix:maxiqtesting1.lti.com:2181:/h>
创建配置单元表命令:
CREATE EXTERNAL TABLE HBASE_TEST_6JULY(FIELD0 int,FIELD1 string, FIELD2 int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,0:FIELD1,0:FIELD2","hbase.table.default.storage.type" = "binary", 'serialization.format'='1')
TBLPROPERTIES("hbase.table.name" = "HBASE_TEST_6JULY_1792409");
Hive表上的 SELECT命令给出的结果为:
hive> select * from HBASE_TEST_6JULY;
OK
-2147483647 qq -2147483625
-2147483646 ee -2147483636
-2147483645 dd -2147483525
Time taken: 0.963 seconds, Fetched: 3 row(s)
整数列值显示不正确。如果我在 hive 中将所有列都设为String，那么我在HBase 中对应的整数列将为null
谁能帮助我，并提供通过在HBase上公开Hive表来读取具有正确值的数字/非字符串列的解决方案？
最佳答案
要从hbase读取非字符串列，您的ddl语句应读取
CREATE EXTERNAL TABLE HBASE_TEST_6JULY(
FIELD0 int,
FIELD1 string,
FIELD2 int
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key#b,0:FIELD1,0:FIELD2#b", 'serialization.format'='1')
TBLPROPERTIES("hbase.table.name" = "HBASE_TEST_6JULY_1792409");
请注意，在int列的列映射限定符中使用“#b”。
这为我解决了。
See the jira issue thread on this
关于hadoop - HBase表上的Hive表显示整数列为NULL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45030794/