hive实战使用文档（一）之hive on hbase知多少

罗少说

已于 2023-06-28 07:28:41 修改

阅读量720

点赞数 1

分类专栏： # hive 文章标签： hive hadoop 数据仓库

于 2023-06-28 07:19:12 首次发布

本文链接：https://blog.csdn.net/luo981695830/article/details/131428769

版权

hive 专栏收录该内容

40 篇文章 20 订阅

订阅专栏

hive对库表的常用命令

查看数据库：

show database;

切换数据库:

use database_name;

查看所有的表：

show tables;

查询表结构:

desc table_name;

创建数据库:

create database database_name;

删除数据库

drop database if exists database_name;
drop database database_name;

创建表

create table if not exists table_name(
    field1 type1,
    field2 type2,
    field3 type3,
    ……
)comment "学生信息表"
row format delimited
fields terminated by "\t"

删除表

drop table table_name

创建视图

create view view_name as
select * from table_name where field1 > condition

创建外部表

create external table if not exists `db_name`.`table_name`(
        `col_name1` type comment "",
        `col_name2` type comment ""
)

partitioned by(data string)
row format delimited fields terminated by ",";

建表语句标准化

create view if not exists `db_name`.`table_name`(
        `col_name1` type comment "",
        `col_name2` type comment ""
)

create table student(
    name string,
    age int
);

查看 show create table student

CREATE TABLE `student`(
  `name` string, 
  `age` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://yunqidt:9000/yunqi/hive/warehouse/ods.db/student'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='true', 
  'numFiles'='1', 
  'numRows'='1', 
  'rawDataSize'='5', 
  'totalSize'='6', 
  'transient_lastDdlTime'='1591778652')

hive on hbase建表方式及说明

方式一：hive内部映射hbase

建表方式两种(已测):
1、创建内部表

创建时，指定hbase表名不能在hbase中提前创建，或已存在也不行

create table `dws`.`student`(
`id` string,
`name` string,
`age` int
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key,
info:name,
info:age"
)
TBLPROPERTIES("[hbase.table.name](http://hbase.table.name/)" = "dws:student");

create table `dws`.`tmp_dws_rkfb_info_jtgxxx_z`(
`id` string,
`xb` string,
`xm` string,
`zjlx` string,
`zjhm` string,
`ryzt` string,
`dxxm` string,
`dxzjlx` string,
`dxzjhm` string,
`dxzt` string,
`jtgx` string,
`data_begin_date` date,
`data_end_date` date
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key,
info:name,
info:age"
)
TBLPROPERTIES("[hbase.table.name](http://hbase.table.name/)" = "dws:tmp_dws_rkfb_info_jtgxxx_z");

全量插入数据

insert into table tmp_dws_rkfb_info_jtgxxx_z select * from dws_rkfb_info_jtgxxx_z;

分区插入数据

insert into table tmp_dws_rkfb_info_jtgxxx_z select * from dws_rkfb_info_jtgxxx_z where 分区名称="指定分区";

2、创建外部表
提前在hbase中创建表

create 'classes','user'

创建外部Hive表

create external table student(id int, name string, age int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,
user:name,
user:age"
)
TBLPROPERTIES("[hbase.table.name](http://hbase.table.name/)" = "student");

hive外部表数据存在hbase中
hive内部表数据存在hbase中

注意事项(已测)：

当hive表与hbase建立映射关系，数据都存在hbase中，与hive表是内部表还是外部表无关
当删除hbase中关联表数据时，查询hive表数据则会报错，提示hbase表不存在
当删除hive中关联表时
如果hive表是外部表，则hbase相关表和数据还在
如果hive表是内部表，则hbase相关表和数据一并删除
如果hive外部表误删，重新与hbase表建立映射关系即可

说明:
1、适用于数据量不是特别大的场景
2、映射方式
2.1 创建hive内部表
2.2 stored by 指定数据的存储方式
2.3 SERDEPROPERTIES：表示字段映射，对应hive中的表字段的顺序，
需要注意的是 :key指的是Hbase中的rowdy，hive表中要有一个key字段与之对应，
否则会报错的。
2.4 TBLPROPERTIES:表示表名映射，指定需要映射的Hbase表名
3、映射规则
3.1 hbase中的空cell在hive中会补null
3.2 hive和hbase中不匹配的字段会补null
3.3 Bytes类型的数据，建hive表示加#b
3.4 hive内部表的数据，由hive自己管理，因此删除hive表，则对应的Hbase表也会被删除
4、hive与hbase建立映射关系

方式二:
hfile文件导入,对文件格式有要求

说明:
适用于数据量较大的场景
步骤：
1、启动hive，添加需要的jar包

add jar /lib/hive-hbase-handler-2.3.3.jar;
add jar /lib/hbase-protocol-1.1.1.jar;
add jar /lib/hbase-common-1.1.1.jar;
add jar /lib/hbase-client-1.1.1.jar;
add jar /lib/hbase-server-1.1.1.jar;

2、创建hive表

此处建表时需要注意，输出格式一定要为：HiveHFileOutputFormat。
/user/hive-hbase/info是生成的hfile在HDFS上的路径，其中info为Hbase的family。
可以向hive表中插入数据，然后查看HDFS的该目录下是否有文件
hdfs dfs -ls /user/hive-hbase/info

create table student{
name string comment '名字',
age int comment'年龄'
}
STORED AS
INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.hbase.HiveHFileOutputFormat'
TBLPROPERTIES ('hfile.family.path' = '/user/hive-hbase/info');

此处建表时需要注意，输出格式一定要为：HiveHFileOutputFormat。
/user/hive-hbase/info是生成的hfile在HDFS上的路径，其中info为Hbase的family。
可以向hive表中插入数据，然后查看HDFS的该目录下是否有文件

hdfs dfs -ls /user/hive-hbase/info

hive常用函数

1、over()开窗范围：rows是行数的意思，后面跟的是函数的范围，between是行数在什么范围，unbounded是行数的起点,这里可以将unbounded替换成1，那就是相邻上一个月的意思，preceding是前面的意思，current row是当前行的意思
2、cast(数据类型1 as 数据类型2)表示将数据类型1强转成数据类型2
3、decimal(10,2)是整数位长度为10，小数位长度为2，如果没有小数位，默认补0
4、row_number：添加序号，无论字段值是否相同
5、dense_rank()：基于over开窗函数的排序函数，如果值相同，则排序的序号相同，紧接的序号不跳过。举例123,1123,1223这样6、6、date_sub(日期，数值)，用日期-数值，即当前日期的前n天，返回值是日期字符串类型
7、ntile：把有序的数据集合平均分配到指定的数据量个桶中，将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
8、split(字符串，分割符)：使用分割符切割字符串，返回一个数组
9、lateral view explode(数组)：将数组字段拆分成多行
10、concat_ws(连接符,字符串，字符串)：连接多个字符串
11、collect_list(分组键)：将分组中的某列聚合成一个数组，数组中元素与分组后的数据保持一致
12、map：将多个排列好的k,v,k,v...变成一个map结构，这是初始化map结构的方式，取数据是map[key]
13、row_number()：row_number是基于over()开窗函数的一个不重复的序号，如上结果所示，即便结果相同，也会顺延,序号自增
14、substring(字符串，起始位置，步长)：根据起始位置和步长切割字符串
15、nvl(value1，value2)：如果value1是null，则返回value2，如果不是，则返回value1
16、lag(字段，n，默认值)：基于over开窗函数，根据排序规则取当前行前第n个数，如果不指定n，则默认取前一个，如果取不到，返回默认值，如果不指定默认值，取不到则返回Null