Hbase 三：数据传输至hive

表空间及表名如下：

查看hbase中的族及字段

一：方法一

Hive上建外部表，链到Hbase ；特点：Hive drop表后，Hbase表不变

在hive中建表如下：
设置参数：

-- set runtime parameters
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.optimize.sort.dynamic.partition=true;
SET hive.auto.convert.join=false;

创建表空间

-- create database if not exists
CREATE DATABASE IF NOT EXISTS events;
-- the current database
SET hivevar:db=events;

创建表
(先创建外表作为映射，在删除，建立内表进行查询)

-- check if train table exists
DROP TABLE IF EXISTS ${db}.hb_train;

-- create train table
CREATE EXTERNAL TABLE ${db}.hb_train(row_key STRING, user STRING, event STRING, invited STRING, time_stamp STRING, interested STRING,not_interested STRING)
    STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key, eu:user, eu:event, eu:invited, eu:time_stamp, eu:interested,eu:not_interested')
    TBLPROPERTIES ('hbase.table.name' = 'events_db:train');
    
-- check if train table exists
DROP TABLE IF EXISTS ${db}.train;

-- create train table
CREATE TABLE ${db}.train
STORED AS ORC AS
    SELECT * FROM ${db}.hb_train;
    
-- check if train table exists
DROP TABLE IF EXISTS ${db}.hb_train;

在hive中查询

0: jdbc:hive2://192.168.101.130:10000> select * from train limit 3;

二：方法二

Hive 建内部表，链到hbase ；特点：Hive drop表后，Hbase 表同步删除

建表方式

--key是hbase的rowkey, 各个字段是hbase中的quailiter
CREATE external TABLE hb_train(key String, user STRING, event STRING, invited STRING, time_stamp STRING, interested STRING,not_interested STRING)  -- 创建hive的表 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  -- 使用的类
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,eu:user, eu:event, eu:invited, eu:time_stamp, eu:interested,eu:not_interested") -- 字段映射关系
TBLPROPERTIES ("hbase.table.name" = "events_db:train"); --映射的表

查看方式同上所示