一:Hive是什么
Hive是面向大数据的数据仓库,是一种将SQL转换为mapreduce的工具。
二:hive表
内表、外表、分区、桶表、location、压缩这些都是表的属性,每个之间没有什么关系(内表外表不可以同时存在)。也就是说一个表既可以是内表,也可是分区表桶表,也可以规定存放路径还可压缩
内表
内表其实就是将拷贝到Hive的目录下,表和数据关联,表删除数据删除。
create table if not exists table_test
(
aa string
)
row format delimited fields terminated by '\001'
location '/hive/table/table_test';
以上的语句就是建一个内表,一行是一条记录,字段之间按照\001分割,数据最终存放的位置是/hive/table/table_test。
装载数据:
insert into table_test
select aa from table_aa;
或
load data inpath '/hive/date/table_test' overwrite into table table_test;(overwrite是覆盖数据,可去掉)
外表
内表相对来说是不安全的:因为表删除后数据就丢了。所以还有外表,将数据和表分开(外表drop后重新,什么都没有变会将数据load两份)。
create external table if not exists table_test
(
aa string
)
row format delimited fields terminated by '\001'
location '/hive/table/table_test';
以上的语句就是建一个外表,一行是一条记录,字段之间按照\001分割,数据最终存放的位置是/hive/table/table_test。
装载数据:
load data inpath '/hive/date/table_test' overwrite into table table_test;(overwrite是覆盖数据,可去掉)
分区表
分区表其实就是将数据按照一个字段放进去不同的文件夹,减少数据扫描。(分区表是文件夹名字就是字段名字,分文件夹)
create table if not exists table_test
(
aa string
) partitioned by (date string,hour string)
row format delimited fields terminated by '\001'
location '/hive/table/table_test';
以上的语句就是建一个内表同时又是分区表,一行是一条记录,字段之间按照\001分割,数据最终存放的位置是/hive/table/table_test。
装载数据:
insert into table_test partition (date=20190908,hour)
select aa,hour from table_aa;
或
load data inpath '/hive/date/table_test' overwrite into table table_test partition (date=20190908,hour=12);(overwrite是覆盖数据,可去掉)
桶表
桶表其实就是将数据按照一个字段放进去不同文件,减少数据扫描。(将date取hash,对结果对4取余确定文件,个人理解哈哈)
create table if not exists table_test
(
aa string
) clustered by(date) into 4 buckets
row format delimited fields terminated by '\001'
location '/hive/table/table_test';
以上的语句就是建一个内表同时又是桶表,一行是一条记录,字段之间按照\001分割,数据最终存放的位置是/hive/table/table_test。
装载数据:
insert into table_test
select aa,date from table_aa;
或
load data inpath '/hive/date/table_test' overwrite into table table_test;(该操作是不会将结果分文件的,其实没有起到桶表的作用)
表压缩
表压缩的好处不言而喻,节约空间减少传输,以下是最优的选择。
create table if not exists table_test
(
aa string
) partitioned by (date string,hour string)
row format delimited fields terminated by '\001'
stored as orc
location '/hive/table/table_test'
tblproperties ('orc.compress'='SNAPPY');
//stored as后可跟文件存储格式(TEXTFILE、SEQUENCEFILE、RCFILE、PARQUET)
// ('orc.compress'='')后可跟压缩方式(ZLIB、SNAPPY、NONE) 选项都是支持的
装载数据:
insert into table_test
select aa,date from table_aa;
或
load data inpath '/hive/date/table_test' overwrite into table table_test;(该操作是不会将结果分文件的,其实没有起到桶表的作用)
三:hive高级函数及特性
1.json_tuple函数
例子:select aa from table lateral view json_tuple(jsonstr,'aa','bb','cc') tmp1 as aa,bb,cc;
原值:
jsonstr
{aa:1,bb:2,cc:3}
结果值:
aa bb cc
1 2 3
将表中jsonstr按照json类型解析,将其中aa,bb,cc的值提取出来,生成的列叫做aa,bb,cc。没有值的数据会默认填空。非常适合处理脏json数据。
2.explode函数
例子:select explode(split(regexp_replace(regexp_replace(jsonstr,'\\[\\{',''),'}]',''),'},\\{')) as sale_info from table ;
原值:
jsonstr
[{aa:1,bb:2,cc:3},{aa:4,bb:5,cc:6},{aa:7,bb:8,cc:9}]
结果值:
jsonstr
aa:1,bb:2,cc:3
aa:4,bb:5,cc:6
aa:7,bb:8,cc:9
将表中jsonstr替换掉[{和}]按照},{将数据分为数组,然后展开
3.hive表常用操作
ALTER TABLE TABLE RENAME TO AA; 将表的名称改变
ALTER TABLE TABLE ADD COLUMNS (BB STRING COMMENT); //增加列
修改速度很快(原因是修改了元数据)
以后插入数据的时候会多需要一列的数据。
查询历史数据的BB列为NULL值。
真正看历史文件是没有BB列的。
ALTER TABLE TABLE REPLACE COLUMNS (BB STRING COMMENT); //删除列,基本理论和增加一致
ALTER TABLE TABLE DROP PARTITION (STAT_YEAR_MONTH='20190908');删除分区
TRUNCATE TABLE TABLE;清空表