不同数据库的时间、换行格式问题

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 ]]
  do

    rn_begain=$min_rn
    min_rn=`expr $min_rn + dt数量`
    rn_end=`expr $min_rn - 1`
    echo "rn_begain:"$rn_begain
    echo "rn_end:"$rn_end

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