1、判断分区是否存在并创建
ALTER TABLE app.union_open_gateway_comm_log ADD if not exists partition(dt='2019-10-11')
2、 显示表接口
SHOW CREATE TABLE app.union_open_gateway_comm_log;
3、表的分区明细
dfs -ls hdfs://ns1001/user/mart_mobile/app.db/union_open_gateway_comm_log/dt=2019-10-13
4、删除指定文件
dfs -rm hdfs://ns1001/user/mart_mobile/app.db/union_open_gateway_comm_log/dt=2019-10-11/union_open_gateway_rsync_log20190226_t.sh.version
5、显示分区
show PARTITIONs app_union_open_gateway_retcode_log
6、覆盖数据(清空原有数据,并新增查询数据)
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]]
示例:指定分区内覆盖数据(app_union_open_gateway_retcode_log orc表,app_union_open_gateway_retcode_log_tmp textfile表)
INSERT OVERWRITE TABLE app.app_union_open_gateway_retcode_log partition(dt='$DT',hour='$HR') select request_id,api_name,request_time,request_param,union_id,ret_code,ret_msg from app.app_union_open_gateway_retcode_log_tmp where dt='$DT' and hour='$HR'
7、 删除分区
alter table test_partition2 drop partition(year=2018,month=12);
此方法只是把hdfs文件从hive表卸载,彻底删除用
$hadoop_exe fs -rm -f $t1/dt=$DT/hour=$HR/*.log,tl=table location
8、脚本中执行hive语句:
hive -e "alter table app.app_union_open_gateway_retcode_log drop partition(dt='$DT',hour='$HR')"
9、 if语句
select if(get_json_object('{"materialId":"https://wq.jd.com/pingou_api/GetAutoTuan?sku_id=67951438776&from=cps","positionId":1821631962,"subUnionId":"hs_857157_32302306_1000_10000"}', '$.subUnionId1') is null,'null','temp1')=if(map('k0','abc','k1','01,02,03','k2','456')['subUnionId'] is null,'null','temp2')