目录

Hive

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
bin/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
5
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');

date_format

转换为时间格式的字符串

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);