Hive查询

• 筛选时间：当个月份，默认当前月到昨天；

where month(event_time) = month(current_date()) and event_time < current_date()

• 当前月份第一天

select trunc(current_date(),'MM')

• 仅删除表中数据，保留表结构

truncate table table_name;

• 插入数据

insert into table countly_log_event_dwd partition(data_date) values('test','test','test','2022-02-01');

insert overwrite table countly_log_event_dwd partition(data_date)
select * from csv1;

• from_utc_timestamp(argument1,argument2)

argument1
1.为13位时间戳 例：1648512000000
2.为字符串形式的带具体时间的日期 例：'1970-01-01 00:00:00'
argument2
字符串形式的世界时区英文缩写 例：'UTC'  'GMT'
北京时区表示方法：'GMT+8'  'PRC'

• unix_timestamp() 获取当前十位时间戳

select unix_timestamp();       --1648538759

• unix_timestamp(string date) 输入的时间戳格式必须为’yyyy-MM-dd HH:mm:ss’,如不符合则返回null

select unix_timestamp('2022-03-29 08:00:00');       >>1648540800
select unix_timestamp('2022-03-29');       >>NULL

• unix_timestamp(string date,string format) 将指定时间字符串格式字符串转化成unix时间戳

select unix_timestamp('2022-03-29','yyyy-MM-dd');       >>1648512000
select unix_timestamp('2022-03-29 08:00:00','yyyy-MM-dd HH:mm:ss');       >>1648540800

• from_unixtime(bigint unixtime,string format) 将时间戳秒数转化为UTC时间，并用字符串表示，可通过format规定的时间格式，指定输出的时间格式

select from_unixtime(1648540800,'yyyy-MM-dd HH:mm:ss');       >>2022-03-29 08:00:00
不写format参数，默认为'yyyy-MM-dd HH:mm:ss'
select from_unixtime(1648540800);       >>2022-03-29 08:00:00
select from_unixtime(1648540800,'yyyy-MM-dd');       >>2022-03-29

• 获取当前时间

select from_unixtime(unix_timestamp(),'yyyy-MM-dd');       >>2022-03-29

• hive提供了json的解析函数：get_json_object

对于jsonArray（json数组），如person表的xjson字段有数据：

[{“name”:“王二狗”,“sex”:“男”,“age”:“25”},{“name”:“李狗嗨”,“sex”:“男”,“age”:“47”}]

取出第一个json对象，那么hive sql为：

SELECT get_json_object(xjson,“$.[0]”) FROM person;

结果是：

{“name”:“王二狗”,“sex”:“男”,“age”:“25”}

取出第一个json的age字段的值：

SELECT get_json_object(xjson,“$.[0].age”) FROM person;

结果

25

• Hive 修复分区 msck repair table

MSCK REPAIR TABLE命令主要是用来：
解决通过hdfs dfs -put或者hdfs api写入hive分区表的数据在hive中无法被查询到的问题。

我们知道hive有个服务叫metastore，
这个服务主要是存储一些元数据信息，
比如数据库名，表名或者表的分区等等信息。
如果不是通过hive的insert等插入语句，
很多分区信息在metastore中是没有的，
如果插入分区数据量很多的话，
你用 ALTER TABLE table_name ADD PARTITION 一个个分区添加十分麻烦。
这时候MSCK REPAIR TABLE就派上用场了。
只需要运行MSCK REPAIR TABLE命令，
hive就会去检测这个表在hdfs上的文件，
把没有写入metastore的分区信息写入metastore。

msck repair table countly_oa_log_ods;
msck repair table countly_oa_log_ods_new;

• show partitions table_name

查看表的分区

• 按条件删除对应分区内数据文件

alter table oa_statistics.countly_oa_log_ods drop if exists partition(data_date <= '$seven_day_ago')

Hive 有两种方法删除指定parition的数据：truncate partition, drop partition
truncate 只删除数据文件，保存在mysql中的metadata不会被删除。
drop partition 只删除数据文件且删除在mysql中的metadata。

• decimal

decimal(18,0)
18是定点精度，0是小数位数。
decimal(a,b)
a指定指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。

select 
cast(get_json_object(message,'\\$.reqts') as decimal(30,0))*1000 as server_time,
message
from oa_statistics.countly_oa_log_ods

• 创建临时表

create temporary table tblname as
select * from ods_tblname

• hive动态增加partitions不能超过100的问题，全量动态生成partitions超过100会出现异常

set hive.exec.max.dynamic.partitions.pernode=2048;
set hive.exec.max.dynamic.partitions=2048;

设置动态partitions为100000，该语句插入几年按天的partitions超过1000个，成功执行语句。

• nvl

NVL( string1, replace_with)
如果第一个参数的值为空值，则返回第二个参数的值，否则返回第一个参数的值。

• add partition()

alter table jskp_invoice_callback_info_tmp add partition(dt='$daily');

• 在hive表指定位置添加字段

alter table city_info add columns(version string comment ‘开发周期版本’); ----添加在最后

alter table city_info change column version version string after city_name; ----移动到指定位置city_name字段的后面

• load data

load data inpath ‘/warehouse/tablespace/managed/hive/nuoyanht.db/core_userask_ods’ into table nuoyanht.core_userask_ods;

• insert overwrite

insert overwrite table dbname.tblname(dt=‘${daily}’)
select
a
,b
,c
from dbname.tblname1;

insert overwrite table dbname.tblname(dt)
select
a
,b
,c
,dt
from dbname.tblname1;

• with as insert

with
r1 as(),
r2 as(),
r3 as()
insert overwrite table dbname.tblname(dt)
select … from r1,r2,r3;

• 判断数组中是否包含某个值

array_contains(数组,值) 返回布尔类型

case when array_contains(split(r7.invoice_line,','),'p') = true or array_contains(split(r7.invoice_line,','),'e') = true or array_contains(split(r7.invoice_line,','),'b') = true then '有电票票种' else '无电票票种' end       invoice_line

• 手动删除分区 添加分区

alter table statistics.dzfp_invoice_mid_json drop if exists partition(dt='2022-05-31');

alter table statistics.dzfp_invoice_mid_json drop if exists partition(dt='2022-06-01');

alter table statistics.dzfp_invoice_mid_json drop if exists partition(dt='2022-06-02');


ALTER TABLE statistics.dzfp_invoice_mid_json ADD PARTITION (dt='2022-05-31');

ALTER TABLE statistics.dzfp_invoice_mid_json ADD PARTITION (dt='2022-06-01');

ALTER TABLE statistics.dzfp_invoice_mid_json ADD PARTITION (dt='2022-06-02');

• 删除多字段分区

alter table tmp_tinf_sharecurrents drop if exists partition(c_tano = '00',pt_date = '2023-05-09');