HIVE 建表访问 HBASE 笔记

在HIVE建表，同时往HBASE里创建此表
DROP TABLE works as usual, but ALTER TABLE is not yet supported for non-native tables.
所以，如果要修改HIVE中非本地表的结构，那么只能 drop table 然后重新创建外部表了
下面这个建表脚本会创建一个HIVE表，同时也会创建一个 HBASE 表
建表脚本：
-------------------------------------------------------------------
CREATE TABLE hbase_table_1(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val")
TBLPROPERTIES ("hbase.table.name" = "xyz");
-------------------------------------------------------------------

set hbase.client.scanner.caching=10000;

可以把建表脚本存到文件中，然后用hive命令去生成表
-------------------------------------------------------------------
cat create_hbase_table.sql | hive > create.log
-------------------------------------------------------------------

########################################################################
# 一些DEMO
########################################################################
创建二进制存储的列，在列处使用关键字 #b，#s 是 string的意思
-------------------------------------------------------------------
CREATE TABLE hbase_table_1 (key int, value string, foobar double)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key#b,cf:val,cf:foo#b"
);
-------------------------------------------------------------------
CREATE TABLE hbase_table_1 (key int, value string, foobar double)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,cf:val#s,cf:foo",
"hbase.table.default.storage.type" = "binary"
);
-------------------------------------------------------------------

创建一个外部表，用来访问HBASE数据
If you want to give Hive access to an existing HBase table, use CREATE EXTERNAL TABLE:
-------------------------------------------------------------------
CREATE EXTERNAL TABLE hbase_table_2(key int, value string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf1:val")
TBLPROPERTIES("hbase.table.name" = "some_existing_table");
-------------------------------------------------------------------

创建一个表，第一列为 MAP 型，MAP 型对应HBASE里一个列族，
列族对应的 KEY-VALUE 分别是 MAP 的 KEY-VALUE，这个厉害哦
Note that the key of the MAP must have datatype string, since it is used for naming the HBase column, so the following table definition will fail: "map<int,int>"
-------------------------------------------------------------------
CREATE TABLE hbase_table_1(value map<string,int>, row_key int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = "cf:,:key"
);
-------------------------------------------------------------------