分享知识，分享快乐

0%

StartRocks补数据

发表于

StartRocks补数据

创建catalog

1
2
3
4
5
6
7
8
9
CREATE EXTERNAL CATALOG hive_catalog0 
PROPERTIES(
  "type"="hive", 
  "hive.metastore.uris"="thrift://172.20.85.29:9083,thrift://172.20.85.29:9083"
);

SHOW RESOURCES;

show databases from hive_catalog0

创建parquet文件格式的临时表

1
2
3
4
create table PAY_TRADECENTER.T_TC_BASE_P 
 STORED AS parquet  
 as 
SELECT  *  from  PAY_TRADECENTER.T_TC_BASE where pk_day >= '2022-07-16' and  pk_day < '2022-08-17'

导入数据

1
2
3
4
5
6
7
8
SET query_timeout = 259200;
set exec_mem_limit=21474836480;
SET enable_insert_strict = false; 


INSERT  into PAY_TRADECENTER.`T_TC_BASE` (CREATED_AT ,ID, TRADE_NO, OUT_TRADE_NO, PRODUCT_TYPE, SUB_PRODUCT_TYPE, PRODUCT_CHANNEL, TRADE_CHANNEL, REQUEST_DATE, REQUEST_SYSTEM, TRADE_AMT, SERVICE_FEE, CCY, MERCHANT_NO, MERCHANT_TRADE_NO, ORIG_TRADE_NO, TRADE_FINISH_DATE, MEMO, EXTEND, SUBJECT, TRADE_STATUS, TRADE_FAIL_STATUS, TRADE_TYPE, REFUND_AMT, RESULT_CODE, RESULT_DESC, TRACE_LOG_ID, SETTLE_ACCOUNT_NO, SETTLE_ACCOUNT_TYPE, FEE_ACCOUNT_NO, FEE_ACCOUNT_TYPE, SUB_MERCHANT_ID, AGENT_NO, STORE_ID, EXTEND1, EXTEND2, EXTEND3, CREATED_BY, UPDATED_AT, UPDATED_BY, USER_ID, EXTEND4, EXTEND5, EXTEND6, TOTAL_ORDER_AMT, UNION_INFO)
SELECT CREATED_AT ,ID, TRADE_NO, OUT_TRADE_NO, PRODUCT_TYPE, SUB_PRODUCT_TYPE, PRODUCT_CHANNEL, TRADE_CHANNEL, REQUEST_DATE, REQUEST_SYSTEM, TRADE_AMT, SERVICE_FEE, CCY, MERCHANT_NO, MERCHANT_TRADE_NO, ORIG_TRADE_NO, TRADE_FINISH_DATE, MEMO, EXTEND, SUBJECT, TRADE_STATUS, TRADE_FAIL_STATUS, TRADE_TYPE, REFUND_AMT, RESULT_CODE, RESULT_DESC, TRACE_LOG_ID, SETTLE_ACCOUNT_NO, SETTLE_ACCOUNT_TYPE, FEE_ACCOUNT_NO, FEE_ACCOUNT_TYPE, SUB_MERCHANT_ID, AGENT_NO, STORE_ID, EXTEND1, EXTEND2, EXTEND3, CREATED_BY, UPDATED_AT, UPDATED_BY, USER_ID, EXTEND4, EXTEND5, EXTEND6, TOTAL_ORDER_AMT, UNION_INFO
FROM  hive_catalog0.PAY_TRADECENTER.T_TC_BASE_P

删除临时表

1
drop table PAY_TRADECENTER.T_TC_BASE_P

PAY_AGGREGATE T_ORDER_EXTERNAL CREATED_AT
PAY_BENEFIT T_BIZ_CMD CREATED_AT
PAY_BENEFIT T_ORDER_BASE CREATED_AT
PAY_BENEFIT T_ORDER_BASE_BENEFIT CREATED_AT
PAY_GATEWAY T_ACQ_AGGREGATE CREATED_AT
PAY_GATEWAY T_ACQ_AGGREGATE_DYNAMIC CREATED_AT
PAY_GATEWAY T_ACQ_NOTIFY CREATED_AT
PAY_GATEWAY T_ACQ_REFUND CREATED_AT
PAY_GATEWAY T_RISK_INFO CREATED_AT
PAY_SETTLE T_SETTLE_ORDER_ACQUIRING TRADE_FINISH_DATE
PAY_SETTLE T_SETTLE_ORDER_RE·FUND TRADE_FINISH_DATE
PAY_TRADECENTER T_BIZ_CMD CREATED_AT
PAY_TRADECENTER T_TC_ACCOUNT_TRANS CREATED_AT
PAY_TRADECENTER T_TC_AGGREGATE_TRANS CREATED_AT
PAY_TRADECENTER T_TC_BASE CREATED_AT
PAY_TRADECENTER T_TC_CHANNEL_MSG CREATED_AT
PAY_TRADECENTER T_TC_CHANNEL_TRANS CREATED_AT
PAY_VERIFY T_VERIFY_BANK_TRANS VERIFY_DATE
PAY_VERIFY T_VERIFY_CHANNEL_COST VERIFY_DATE
PAY_VERIFY T_VERIFY_CHANNEL_TRANS VERIFY_DATE
PAY_VERIFY T_VERIFY_RESULT VERIFY_DATE

– PAY_GATEWAY数据库
PAY_GATEWAY.T_ACQ_AGGREGATE_DYNAMIC 保留一个月
PAY_GATEWAY.T_ACQ_AGGREGATE 保留一个月
PAY_GATEWAY.T_ACQ_NOTIFY 保留一个月
PAY_GATEWAY.T_ACQ_REFUND 保留一个月
PAY_GATEWAY.T_RISK_INFO 保留一个月
PAY_GATEWAY.T_BIZ_CMD 暂不归档

– PAY_TRADECENTER数据库
PAY_TRADECENTER.T_BIZ_CMD 保留一个月
PAY_TRADECENTER.T_TC_ACCOUNT_TRANS 保留一个月
PAY_TRADECENTER.T_TC_AGGREGATE_TRANS 保留一个月
PAY_TRADECENTER.T_TC_BASE 保留一个月
PAY_TRADECENTER.T_TC_CHANNEL_MSG 保留一个月
PAY_TRADECENTER.T_TC_CHANNEL_TRANS 保留一个月

– PAY_AGGREGATE
PAY_AGGREGATE.T_ORDER_EXTERNAL 保留四个月

– PAY_BENEFIT
PAY_BENEFIT.T_ORDER_BASE 保留四个月
PAY_BENEFIT.T_BIZ_CMD 保留四个月
PAY_BENEFIT.T_ORDER_BASE_BENEFIT 保留四个月

---- PAY_VERIFY
PAY_VERIFY.T_VERIFY_RESULT 保留四个月
PAY_VERIFY.T_VERIFY_CHANNEL_TRANS 保留四个月
PAY_VERIFY.T_VERIFY_CHANNEL_COST 保留四个月
PAY_VERIFY.T_VERIFY_BANK_TRANS 保留四个月

– PAY_SETTLE
PAY_SETTLE.T_SETTLE_ORDER_ACQUIRING 保留四个月
PAY_SETTLE.T_SETTLE_ORDER_WITHDRAW 保留四个月
PAY_SETTLE.T_SETTLE_ORDER_REFUND 保留四个月
PAY_SETTLE.T_SETTLE_ORDER_CANCEL 保留四个月