- 筛选时间:当个月份,默认当前月到昨天;
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');