Hbase表映射Hive表三种方法

https://blog.csdn.net/qq_39680564/article/details/89948253
https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration

Hive3.1.1：https://blog.csdn.net/qq_39680564/article/details/89714184
Hbase2.1.0：https://blog.csdn.net/qq_39680564/article/details/89515459
hadoop3.0.3：https://blog.csdn.net/qq_39680564/article/details/89513162

推荐hive的可视化连接工具dbeaver：
https://blog.csdn.net/qq_39680564/article/details/89945195

sql1


CREATE EXTERNAL TABLE hiveFromHbase(
rowkey string,
f1 map<STRING,STRING>,
f2 map<STRING,STRING>,
f3 map<STRING,STRING>
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f1:,f2:,f3:")
TBLPROPERTIES ("hbase.table.name" = "hbase_test");

CREATE EXTERNAL TABLE userdb.us_population
(hkey string,
POPULATION string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES
("hbase.columns.mapping" = ":key,CF:POPULATION")
TBLPROPERTIES ("hbase.table.name" = "ns1:us_population");


vim ~/.bashrc 
source ~/.bashrc
sqlline.py

/opt/phoenix-4.9.0-cdh5.9.1
# phoenix
export S1_HOME=/opt/phoenix-4.9.0-cdh5.9.1
export PATH=$PATH:$S1_HOME/bin


# scala
export SCALA_HOME=/opt/scala-2.11.8
export PATH=$PATH:$SCALA_HOME/bin



-- phoenix
CREATE TABLE IF NOT EXISTS "ns1"."us_population2" (
      state CHAR(2) NOT NULL,
      city VARCHAR NOT NULL,
      cf.population VARCHAR
      CONSTRAINT my_pk PRIMARY KEY (state, city)) column_encoded_bytes=0;

upsert into "ns1"."us_population2" values('NY','NewYork','8143197');
upsert into "ns1"."us_population2" values('NY','NewYork2','8143177');
upsert into "ns1"."us_population2" values('NY','NewYork3','8143187');

-- hive
CREATE EXTERNAL TABLE userdb.us_population
(hkey string,
POPULATION string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES
("hbase.columns.mapping" = ":key,CF:POPULATION")
TBLPROPERTIES ("hbase.table.name" = "ns1:us_population2");

phoenix创建映射表和创建索引、删除索引、重建索引

https://blog.csdn.net/qq_35207086/article/details/121251457
https://blog.csdn.net/weixin_39681724/article/details/111662633
https://blog.csdn.net/yuan1164345228/article/details/115707220
https://blog.csdn.net/m0_48379126/article/details/121374232
https://blog.csdn.net/weixin_45967421/article/details/109266040
https://blog.csdn.net/haoheiao/article/details/126583757


1、新建phoenix中的表
CREATE TABLE CAP.FMS_REF_AAA
(MSISDN VARCHAR,
PFINFO.AAA VARCHAR,
PFINFO.BBB VARCHAR,
CONSTRAINT PK_FMS_REF_ENDORSEMENT PRIMARY KEY (MSISDN)
) COLUMN_ENCODED_BYTES=‘NONE’;

PFINFO为列簇名（可自行定义）
COLUMN_ENCODED_BYTES=‘NONE’ 设置不进行编码，不加HIVE中不能显示数据。

2、在HIVE中新建外部表
CREATE EXTERNAL TABLE CAP.FMS_REF_AAA
(MSISDN string,
AAA string,
BBB string
)
STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’
WITH SERDEPROPERTIES
(“hbase.columns.mapping” = “:key,PFINFO:AAA,PFINFO:BBB”)
TBLPROPERTIES (“hbase.table.name” = “CAP:FMS_REF_AAA”);


CREATE TABLE IF NOT EXISTS "ns1"."us_population" (
      state CHAR(2) NOT NULL,
      city VARCHAR NOT NULL,
      info.population BIGINT
      CONSTRAINT my_pk PRIMARY KEY (state, city)) column_encoded_bytes=0;

CREATE TABLE IF NOT EXISTS "ns1"."us_population" (
      info.state CHAR(2) NOT NULL,
      info.city VARCHAR NOT NULL,
      info.population BIGINT
      CONSTRAINT my_pk PRIMARY KEY (state, city)) column_encoded_bytes=0;

CREATE TABLE IF NOT EXISTS "ns1"."us_population" (
      cf.state CHAR(2) NOT NULL,
      cf.city VARCHAR NOT NULL,
      cf.population BIGINT
      CONSTRAINT my_pk PRIMARY KEY (state, city)) column_encoded_bytes=0;

upsert into "ns1"."us_population" values('NY','NewYork',8143197);
upsert into "ns1"."us_population" values('CA','Los Angeles',3844829);
upsert into "ns1"."us_population" values('IL','Chicago',2842518);

要禁用跨所有新表的列映射，需要将phoenix.default.column.encode .bytes.attrib设置为0。还可以全局地保留它，并通过在create table语句中设置COLUMN_ENCODED_BYTES = 0属性来选择性地禁用它。
类型前添加：unsigned_ 表示无符号，但仅对正数有效，如果hbase中的数据为负数则无效（unsigned_*类型）
column_encoded_bytes=0 表示与Hbase中字段相同
注意：column_encoded_bytes=0不加可能导致查询数据不显示


scan 'ns1:us_population'
scan 'ns1:us_population',{FORMATTER => 'toString'}
scan 'ns1:us_population',{FORMATTER => 'toString'}
scan 'ns1:us_population',{COLUMNS => 'CF:POPULATION:toString'}
scan 'ns1:us_population',{COLUMNS => 'CF:POPULATION:toInt'}
scan 'ns1:us_population',{COLUMNS => 'CF:POPULATION:toLong'}
scan 'ns1:us_population',{COLUMNS => 'CF:POPULATION:toBigDecimal'}
scan 'ns1:us_population',{COLUMNS => 'CF:POPULATION:toDouble'}


org.apache.hadoop.hbase.util.Bytes
scan 'test', {formatter_class => 'org.apache.hadoop.hbase.util.bytes', formatter => 'tostring', column=>'f:c4'}
scan 'test', {formatter_class => 'org.apache.hadoop.hbase.util.bytes', formatter => 'tostring'}
scan 'test', {formatter => 'tostring',limit=>1,column=>'f:c4'}
scan 'test',{COLUMNS => 'cf:name:toString'}
scan 'test',{FORMATTER => 'toString'}
scan 'ns1:us_population',{FILTER=>"SingleColumnValueFilter('CF','POPULATION',=,'binary:3844829')"}
scan 'ns1:us_population',{COLUMN=>'CF:POPULATION',FILTER=>"SingleColumnValueFilter('CF','POPULATION',=,'binary:3844829')"}


还有一个0:_0 这个列是没有值的，这个是Phoenix处于性能方面考虑增加的一个列，不用管这个列
还有一个CF:_0 这个列是没有值的，这个是Phoenix处于性能方面考虑增加的一个列，不用管这个列

scan 'hbase:meta',{FILTER=>"SingleColumnValueFilter('info','server',=,'binary:chds-2:60020')"}
# 查询hbase :meta表中列簇为info.列名为server的所有数据。

scan 'student', {COLUMN=>'stuinfo:age',FILTER=>"SingleColumnValueFilter('stuinfo','age',=,' binary:19')"}    
# 查询stuinfo列族age列中值等于19的所有键值对



scan 'myTable', { COLUMNS => 'cf:abc', FILTER => "ValueFilter(=, 'substring:myvalue')"}

scan 'tableName', {COLUMNS=>['CF:qualifier1', 'CF:qualifier2'], LIMIT=>10,
FILTER=>SingleColumnValueFilter.new(Bytes.toBytes('CF'),
Bytes.toBytes('qualifier1'), CompareFilter::CompareOp.valueOf('EQUAL'),
Bytes.toBytes('value'))}
https://sites.google.com/site/nathanlexwww/tools/utf8-convert

https://www.jianshu.com/p/0a0e0b258b64
https://cloud.tencent.com/developer/article/2070995
https://blog.csdn.net/gulugulu_gulu/article/details/105558176
https://blog.csdn.net/qq_34676998/article/details/128329651
https://blog.csdn.net/ASN_forever/article/details/106248874
https://blog.csdn.net/hammring/article/details/111057789

参考

https://blog.csdn.net/w1992wishes/article/details/103049552
https://blog.csdn.net/panxiaojun/article/details/103086718
https://blog.csdn.net/weixin_41228362/article/details/83501755
https://blog.csdn.net/Mogeko1/article/details/127647544
https://blog.csdn.net/hongchenshijie/article/details/103814228
https://blog.csdn.net/Xiayebuliang/article/details/129406185

posted @ 2023-10-16 17:32 三里清风18 阅读(321) 评论(0) 收藏举报

刷新页面返回顶部

cw2blog

开心一刻

Hbase表映射Hive表三种方法

Hbase表映射Hive表三种方法

sql1

phoenix创建映射表和创建索引、删除索引、重建索引

参考

公告