phoenix语法:
-
时间:TO_CHAR(CONVERT_TZ(String,'UTC','Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') as etl_time
-
换行:regexp_replace(String,'\n','') as sku_brand_name
sybase语法:
-
时间:convert(CHAR(8), current_time(), 108) as etl_time
-
换行:str_replace(str_replace(str_replace(String,char(13),''),char(10),'||'),'^A','') as GUEST -- ^A hive的orc分割符号
-
将datetime类型的日期转换为yyyy-mm-dd格式
-
STR_REPLACE(CONVERT(VARCHAR, GETDATE(), 111) ,'/','-')
- select datepart(yy,getdate()) --year
select datepart(mm,getdate()) --month
select datepart(dd,getdate()) --day
select datepart(hh,getdate()) --hour
select datepart(mi,getdate()) --min
select datepart(ss,getdate()) --sec
oracler语法:
-
时间:to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as etl_time
hana语法:
- 格式:\"/BIC/SUBJECT\" as a
- 时间:current_timestamp as etl_time
- 虚拟表:select * from dummy;
hive语法:
-
etlCycle=`date +%Y-%m-%d`
etlCycleYesterday=`date -d yesterday +%Y-%m-%d`
etlCycleYesterdayStr=`date -d yesterday +%Y%m%d`
etlCycleTomorrow=`date -d tomorrow +%Y-%m-%d`
dateSuffix=`date +%Y%m%d`
dateSuffixYesterday=`date -d yesterday +%Y%m%d` -
from_unixtime(CAST(SUBSTR(get_json_object(event_data,'$.time'), 1, 10) AS BIGINT), "%Y-%m-%d %H:%M:%S") as r_time
-
max_update_time=`beeline -u "${BEELINE_CONN_URL}" --hiveconf hive.server2.logging.operation.level=NONE -n hive --showHeader=false --silent=true -e "
select max(update_time) from table.table_name
where dt= '22220202'
"`
maxupdatetime=`echo $max_update_time | tr -cd "[0-9]"` -
row_number() over(partition by order_id order by departure_date) as rn, -- 直排 rank() over(partition by order_id order by departure_date) as rk, -- 并列的,下一个数字会跳过 dense_rank() over(partition by order_id order by departure_date) as d_rk --并列的,下一个数字不会跳过
分区循环插入:
-
max_rn=`echo $query_max_rn | tr -cd "[0-9]"`
-
while [[ $min_rn -le $max_rn ]]
dorn_begain=$min_rn
min_rn=`expr $min_rn + dt数量`
rn_end=`expr $min_rn - 1`
echo "rn_begain:"$rn_begain
echo "rn_end:"$rn_endbeeline -u "${BEELINE_CONN_URL}" -n hive -e "
insert overwrite table partition (partition)
select date_format(from_utc_timestamp(current_timestamp(), 'PRC'), 'yyyy-MM-dd HH:mm:ss') as etl_time
from table p0
where p0.rn between ${rn_begain} and ${rn_end};
"
if [ $? -eq 0 ]; then
echo "step process suss, rn from $rn_begain to $rn_end."
else
echo "step process fail."
exit 1;
fi
done
date_format(from_utc_timestamp(current_timestamp(), 'PRC'), 'yyyy-MM-dd HH:mm:ss')
ck: PARTITION BY CAST(formatDateTime(toDateTime(update_time) - toIntervalHour(1), '%Y%m%d%H'), 'int') ORDER BY (batch_id, update_time)
TTL create_time + toIntervalDay(7);