查看数据库
show create database hive_test;
查看表
show create table page_view;
表结构
desc formatted page_view;
加载数据到表
load data local inpath '/home/test/hive-2.3.7/data/page_view.txt' into table page_view;
根据中间表创建分区
(静态)
insert into page_view_partition partition(dt='2020-04',country='china') select * from page_view;
(动态)
insert into page_view_partition partition (dt,country) select *,substr(viewTime,0,7) dt, 'china' as country from page_view;
动态分区永久生效
hive-site里面
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
行转列+关联
select pageid,adid from pageAds lateral view explode(adid_list) t as adid;
自定义函数操作
添加jar
add jar /opt/module/apache-hive-2.3.7-bin/hive-1.0.1-SNAPSHOT.jar;
创建临时函数
create temporary function get_level as 'com.bw.hive.udf.LevelUDF';
删除临时函数
drop temporary function get_level;
调用函数
select *,getClassLevel(score) as level from sockinfo;