Hive相关的用例参考。
[spark 2.3 sql api 参考]
客户端
HiveServer2 Client Ref
1
2
|
.config("spark.sql.sources.partitionOverwriteMode", "dynamic").config("hive.exec.dynamic.partition",
"true").config("hive.exec.dynamic.partition.mode", "nonstrict") // 增加对分区操作权限
|
登录
使用HIVE自带的beeline进行进行远程登录。
启动beeline：
连接数据库：
1
|
beeline>!connect jdbc:hive2://192.168.1.72:10000
|
连接成功后：
1
2
3
4
5
|
Connecting to jdbc:hive2://192.168.1.72:10000
Connected to: Apache Hive (version 3.1.0.3.1.0.0-78)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://192.168.1.72:10000>
|
命令参考：connect <url> <username> <password> [driver]
查询表
1
|
0: jdbc:hive2://192.168.1.72:10000>show tables;
|
注意： hive在3.0之后，默认不和spark公用一个数据库。连hive的数据库，需要改为spark的地址和端口。
表管理
新增字段
1
|
ALTER TABLE table_name ADD COLUMNS (test_field STRING);
|
删除字段
hive 无法针对字段删除。但是可以使用replace进行覆盖。
删除列的唯一方法是使用replace命令。
可以说，我有一个表emp，其中包含id，name和dept列。 我想删除表emp的id列。 因此，在replace columns子句中提供要成为表一部分的所有那些列。 下面的命令将从emp表中删除id列。
1
|
ALTER TABLE emp REPLACE COLUMNS( name string, dept string);
|
复制表
1
|
create table table_name as select * from beacon_web_kuaishou_effect_report
|
分区管理
修改分区
1
2
|
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
|
Spark Hive 常用查询参考
插入覆写
1
|
insert overwrite table xxx select * from yyy;
|
查看分区最大及最小值
| 字段 |
说明 |
| min_date |
最小分区 |
| max_date |
最大分区 |
| total |
分区总数 |
| date_interval |
分区时间间隔 |
1
2
3
|
val tableName = "ds1505_tbl_sms_log"
spark.sql(s"show partitions ${tableName}").withColumn("date", to_date(col("partition").substr(8,8),"yyyyMMdd")).agg(min("date").as("min_date"), max("date").as("max_date"),
count("date").as("total")).withColumn("date_interval", datediff(col("max_date"), col("min_date"))).show(false)
|
JSON操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
-- 获取指定json字段
select get_json_object('{"a":"b"}', '$.a');
-- 获取数组内容
select get_json_object('{"a":["a"]}', '$.a[0]');
-- 获取数组指定内容
select get_json_object('["0","1"]', '$[1]');
-- 获取多个字段
SELECT json_tuple('{"a":1, "b":2}', 'a', 'b');
-- 获取指定内容
select get_json_object(ADCREATIVE_ELEMENTS, '$.description_list[0]') from TO_WECHATMP_MIDDLE_DI where pt_day='20200415' limit 10
-- 获取多个字段，输出格式为元组
select json_tuple(ADCREATIVE_ELEMENTS, 'description_list', 'title', 'image_list') from TO_WECHATMP_MIDDLE_DI where pt_day='20200415' limit 10
|
日期时间
timestamp
timestamp(expr)
将expr转换为时间格式（timestamp）。
1
2
3
4
5
6
7
8
|
select timestamp(0);
-- 1970-01-01 08:00:00
select timestamp(1587004676);
-- 2020-04-16 10:37:56
select timestamp('2020-02-02 11:00:00');
-- 2020-02-02 11:00:00
select timestamp('2020-02-02');
-- 2020-02-02 00:00:00
|
to_date
to_date(date_str [，fmt])
将date_str表达式与fmt表达式解析为一个日期。
默认情况下，如果省略了fmt，它将遵循强制转换规则到日期。
例子：
1
2
3
4
|
SELECT to_date('2009-07-30 04:17:52');
-- 2009-07-30
SELECT to_date('2016-12-31', 'yyyy-MM-dd');
-- 2016-12-31
|
from_unixtime
以指定的格式返回 unixtime。
例子：
1
2
3
4
|
-- 1970-01-01 08:00:00
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
-- 2020-04-16 10:37:56
select from_unixtime(1587004676, 'yyyy-MM-dd HH:mm:ss');
|
转换为时间格式的字符串。
1
|
select date_format(now(), 'yyyy-MM-dd');
|
trunc
返回使用指定格式截取时间的一部分。返回格式为date。
可截取范围["year","yyyy","yy","mon","month","mm"]
1
2
3
4
|
-- 截取月份，返回 2019-07-01
trunc('2019-07-02', 'MONTH');
-- 截取年份，返回 2019-01-01
trunc('2019-07-01','YEAR');
|
date_sub
获取以周一为起点的起点日期
1
2
|
select date_sub(CREATE_TIME, pmod(datediff(CREATE_TIME,'1900-01-08'),7)) as
ST_TM,
|
weekofyear
获取该时间是第几个自然周。
1
|
select weekofyear(time);
|