Hive简介及常用 *** 作

CREATE DATAbase [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

# 默认存储路径是hdfs上的/user/hive/warehouse/*.db
create database db;
# 指定hdfs存储路径/data/warehouse/db/
create database db location '/data/warehouse/db/';

hive (liker)> desc database db;
OK
db_name	comment	location	owner_name	owner_type	parameters
db		hdfs://hadoop001:9820/data/warehouse/db	liker	USER

hive (liker)> desc database extended db;
OK
db_name	comment	location	owner_name	owner_type	parameters
db		hdfs://hadoop001:9820/data/warehouse/db	liker	USER	{crtdate=2021-11-11}

# 切换到db数据库
hive (liker)> use db;
OK
Time taken: 0.013 seconds
hive (db)> 

CREATE EXTERNAL TABLE dms_table (
    `id` STRING COMMENT 'ID',
    `wnumber` STRING COMMENT 'SN',
    `itemcode` STRING COMMENT '整车物料编码',
    `itemname` STRING COMMENT '整车物料描述',
    `series_code` STRING COMMENT '产品系列编码'
) COMMENT '电摩主部件'
PARTITIonED BY (`dt` STRING COMMENT '时间分区',`rgn` STRING COMMENT '大区') 
ROW FORMAT DELIMITED FIELDS TERMINATED by '01' 
STORED AS parquet 
LOCATION '/data/hive/warehouse/db/dws/dms_table' TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

hive的数据是存储在hdfs上的，内部表和外部表的差别就是，当删除表的时候，hdfs的文件会不会被删除。
由于删除内部表的时候，数据也会被删除。为了数据安全通常正式建表，都是创建外部表，并且指定外部表存储路径。
内部表通常用于测试使用，用完删了即可，数据也会被删除，不会占用集群存储空间。例如，在数据校验时，作为对比的表，就可以创建内部表，当校验结束后，把表删除即可。

# 创建内部表
create table table_name ...
# 创建外部表
create external table table_name ...
# 内部表和外部表转换
alter table table_name set tblproperties('EXTERNAL'='TRUE');
# 通过desc查看
desc formatted table_name;
......
	EXTERNAL            	TRUE   
......

分区的作用是把数据分开存储，在hdfs上将一个表的不同分区的数据分开存储。优点就是在查询的时候，可以通过指定分区来减少查询时底层需要加载的数据量。
例如增量存储的数据中，可以把数据按照天来存储。

0B dt=20211101
0B dt=20211102
0B dt=20211103

# 创建按天分区
create external table partition_table(
...)
partitioned by (day string,rgn string)

create table test_partition (id int, name string)
partitioned by(dt string);
insert overwrite table test_partition partition(dt='20211101') values(3,'hehe');
insert overwrite table test_partition partition(dt='20211102') values(3,'hehe');

show partitions test_partition;
dt=20211101
dt=20211102

# 添加分区
alter table table_name if not exists add partition (dt='20211103')
# 添加后，查看分区
show partitions test_partition;
dt=20211101
dt=20211102
dt=20211103

# 删除分区
alter table table_name drop partition (dt='20211101')
# 删除后查看分区
show partitions test_partition;
dt=20211102
dt=20211103

# 查询分区的数据
# 当只需要某一天或某几天的数据时，可以在where后添加分区的过滤条件
 select * from test_partition where day='20210101'
# or
 select * from test_partition where day='20210101' or day='20210102'

• insert into：向指定表增量添加数据。通常不会使用
• insert overwrite：向指定表添加数据，覆盖之前的数据，通常结合分区使用。

# insert into 示例
create table test_tab (id int, name string);
insert into test_tab values(1,'kwang');
insert into test_tab values(2,'fwang');
select * from test_tab;
1       kwang
2       fwang

# insert overwrite 示例
insert overwrite table test_tab values(4,'zz');
select * from test_tab;
4        hehe

• 向分区表添加数据，向分区表添加数据，必须指定分区，否则报错

create table test_partition (id int, name string)
partitioned by(dt string);
insert into test_partition partition(dt='20211101') values(1,'kwang');
insert into test_partition partition(dt='20211101') values(2,'fwang');
select * from test_partition ;
1       kwang
2       fwang

# insert overwrite
insert overwrite table test_partition partition(dt='20211101') values(3,'hehe');
select * from test_partition ;
3    hehe

以上介绍的分区 *** 作都是在添加的时候指定分区，如果在向某个表添加数据时，需要根据表中的具体数据来添加到指定分区。

• 添加测试数据

create table test_data (id int, name string,day_date string);
insert into table test_data values(1,'mapreduce','20211101'),(2,'hadoop','20211102'),(4,'hive','20211102'),(3,'spark','20211103');
select * from test_data;
1    mapreduce    20211101
2    hadoop       20211102
4    hive         20211102
3    spark        20211103

• 添加数据到分区表

# 创建分区表
create table test_partition (id int, name string)
partitioned by(dt string);
# 在hive中动态分区默认是关闭的，使用前需要手动打开，并且关闭严格模式。需要设置如下两个参数
SET hive.exec.dynamici.partition = TRUE;
SET hive.exec.dynamic.partition.mode = nonstrict;
insert overwrite table test_partition partition(dt)
select 
id,
name,
day_date as dt
from test_data;
# 验证分区的数据
select * from test_data where dt='20211101';
1    mapreduce    20211101
select * from test_data where dt='20211102';
2    hadoop       20211102
4    hive         20211102
select * from test_data where dt='20211103';
3    spark        20211103

# 避免使用distinct的方式
select distinct name,age,tel from user
# 使用group by
select name,age,tel from user group by name,age,tel
# 同样的count(distinct) 改为group by
select distinct name,age,tel from user
select name,age,tel,count(1) cnt from user group by name,age,tel

• nvl：接受两个参数，如果第一个为null，取第二个值

select nvl(null,1)
1
select nvl(1,2,)
1

• coalesce: 返回第一个不为null的值

select coalesce(null,'1',3)
1

# 添加测试数据
create table test_array_data(id int,name string,hoby string);
insert into test_array_data values(1,'hive','hiveserver2,beenline,hadoop'),(2,'hdfs','namenode,hadoop,datanode'),(3,'spark','hadoop,rdd,sql,dataframe,dataset')
drop table test_arr;
select id,name,split(hoby,',') hoby from test_array_data;
create table test_arr(id int,name string,hoby array);
insert into table test_arr select id,name,split(hoby,',') hoby from test_array_data;
select * from test_arr;
1 hive  ["hiveserver2","beenline","hadoop"]
2 hdfs  ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"]

select id,name,split(hoby,',') hoby from test_array_data;
# explode
select id,name,h from test_arr lateral view explode(hoby) test_arr as h 
1 hive  hiveserver2
1 hive  beenline
1 hive  hadoop
2 hdfs  namenode
2 hdfs  hadoop
2 hdfs  datanode
3 spark hadoop
3 spark rdd
3 spark sql
3 spark dataframe
3 spark dataset
# size
select id,name,size(hoby) len from test_arr;
id  name len
1 hive  3
2 hdfs  3
3 spark 5
# array_contains
select id,name,array_contains(hoby,'beenline') from test_arr;1 hive  true
2 hdfs  false
3 spark false
# 根据下标获取
select hoby[1] from test_arr;
beenline
hadoop
rdd
# concat_ws 
select id,name,concat_ws('-',hoby) from test_arr;
1 hive  hiveserver2-beenline-hadoop-hadoop
2 hdfs  namenode-hadoop-datanode
3 spark hadoop-rdd-sql-dataframe-dataset

-- 测试数据 
insert overwrite table test_array_data values(1,'hive','hiveserver2,beenline,hadoop,hadoop'),(2,'hdfs','namenode,hadoop,datanode'),(3,'spark','hadoop,rdd,sql,dataframe,dataset')
insert overwrite table test_arr select id,name,split(hoby,',') hoby from test_array_data;
# 查询数据
with temp as (
select id,name,h from test_arr lateral view explode(hoby) test_arr as h
)select id,name,collect_list(h) h_list,collect_set(h) h_set from temp group by id,name

# collect_list & ,collect_set
id  name  h_list  h_set
1 hive  ["hiveserver2","beenline","hadoop","hadoop"]  ["hiveserver2","beenline","hadoop"]
2 hdfs  ["namenode","hadoop","datanode"]  ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"]  ["hadoop","rdd","sql","dataframe","dataset"]
# split 
select id,name,split(hoby,',') hoby from test_array_data;
1 hive  ["hiveserver2","beenline","hadoop","hadoop"]
2 hdfs  ["namenode","hadoop","datanode"]
3 spark ["hadoop","rdd","sql","dataframe","dataset"]

• date_add: 用来为给定的日期增加指定的天数

# 格式 string date_add(string startdate, int days)
# 增加2天
select date_add('2021-10-10',2) # 2021-10-12
# 减去1天
select date_add('2021-10-10',-1) # 2021-10-09

• datediff：计算两个日期相差的天数 计算时会忽略时分秒

# 格式 int datediff(string enddate, string startdate) 
# 计算2021-10-10 和2021-10-14相差几天
select datediff('2021-10-10','2021-10-14') # -4
select datediff('2021-10-14 00:00:00','2021-10-10 23:59:59') # 4

• date_format：指定格式转换格式

# 将yyyy-MM-dd HH:mm:ss 转换为yyyy/MM/dd HH-mm-dd
select date_format('2021-10-10 11:12:11','yyyy/MM/dd HH-mm-ss')
# 2021/10/10 11-12-11

• current_date：获取当前日期，年月日

# 获取当前日期
select current_date(); #2021-11-17

• last_day：获取指定时间所在月的最后一天

# 获取当前时间所在月的最后一天
 select last_day(current_date()) # 2021-11-30

• from_unixstamp：根据时间戳获取指定格式的时间

# 从时间戳
 select from_unixtime(unix_timestamp('2021-11-17 10:11:12'),'yyyy/MM/dd HH-mm-ss') # 2021/11/17 10-11-12

• unix_timestamp

# 获取指定时间的时间戳
select unix_timestamp('2021-11-17 10:10-10','yyyy-MM-dd HH:mm-ss') ; # 1637143810

• weekofyear

select weekofyear('2021-10-10') # 40