建表语句如下:-- 归档库
create database if not exists archive ;
use archive ;
CREATE TABLE ods_1_2_game_center_user_balance_record_archive
(
partition_created_at datetime NOT NULL COMMENT "创建时间:分区键",
record_id bigint NOT NULL COMMENT "主键ID",
merchant_id bigint NULL COMMENT "商户ID",
platform_id bigint NULL COMMENT "平台ID",
created_at bigint NULL COMMENT "创建时间",
merchant_user_id varchar(64) NULL COMMENT "商户用户id",
user_id bigint NULL COMMENT "用户ID",
game_id int NULL COMMENT "游戏id",
transaction_type int NULL COMMENT "交易类型：1=投注 玩家扣款,2=结算派奖 玩家入款,3=押金 - 玩家扣款,4=返还押金 玩家入款,5=取消投注 玩家入款,6=结算撤单 玩家扣款,7=重新派奖 玩家入款-扣款[之前派奖的钱扣除，加新派奖的钱],8=调整金额 玩家[入款]-[扣款],9=转入游戏 玩家扣款,10=从游戏转出 玩家入款",
amount decimal(20, 6) NULL COMMENT "变动金额（正数表示增加，负数表示减少）",
balance_before decimal(20, 6) NULL COMMENT "变动前余额",
balance_after decimal(20, 6) NULL COMMENT "变动后余额",
original_platform_id int NULL COMMENT "原游戏厂商id",
original_game_id int NULL COMMENT "原游戏id （用户点击）",
is_diversion int NULL COMMENT "是否切流 1 是 2否",
currency_code varchar(10) NULL COMMENT "币种代码",
related_order_id varchar(128) NULL COMMENT "关联业务记录ID",
platform_order_id varchar(128) NULL COMMENT "平台订单ID（第三方平台的订单号）",
merchant_order_id varchar(128) NULL COMMENT "下游商户订单ID",
transaction_id varchar(128) NULL COMMENT "中台交易流水号",
remark varchar(1024) NULL COMMENT "备注信息",
client_ip varchar(64) NULL COMMENT "客户端IP",
device_id varchar(64) NULL COMMENT "设备id",
device_os varchar(32) NULL COMMENT "设备os",
ext_data text NULL COMMENT "扩展数据（JSON格式）",
updated_at int NULL COMMENT "更新时间戳",
deleted_at int NULL COMMENT "删除时间戳（软删除）",
op varchar(4) NULL COMMENT "数据变更类型 r:历史数据 c:新增, u:更新, d:删除",
ts_binlog_millisecond bigint NULL COMMENT "数据变更时间戳，毫秒",
ts_collect_nano bigint NULL COMMENT "数据采集时间，纳秒，sequence序列键更新",
server_zone_code varchar(32) NULL COMMENT "租户数据库时区code，例如 GMT+08",
app_zone_code varchar(32) NULL COMMENT "产品时区Code，默认2位或4位数字 例如 GMT+08",
flink_time datetime(3) NULL COMMENT "flink执行sink的数据时间 yyyy-MM-dd HH:mm:ss.SSS",
doris_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT "Doris数据落表时间",
data_id varchar(255) NULL DEFAULT "" COMMENT "数据ID(业务表主键字段和值) 例如 id::123&created_at::1758100895",
category_code varchar(96) NULL COMMENT "游戏分类",
trade_time bigint NULL COMMENT "交易时间（毫秒）"
) ENGINE=OLAP
UNIQUE KEY(partition_created_at, record_id, merchant_id, platform_id, created_at)
COMMENT '用户余额变动记录表（账变记录表）'
auto partition by range (date_trunc(partition_created_at, 'month')) ()
DISTRIBUTED BY HASH(record_id) BUCKETS 5
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"bloom_filter_columns" = "merchant_user_id, platform_order_id, merchant_order_id, user_id",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-120",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "5",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "2",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.start_day_of_month" = "1",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"compression" = "ZSTD",
"enable_unique_key_merge_on_write" = "false",
"light_schema_change" = "true",
"function_column.sequence_col" = "ts_collect_nano",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "true",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
建resource和存储策略
-- ods_1_2_game_center_user_balance_record_archive
CREATE RESOURCE "cos_bigdata_prod_ods_1_2_game_center_user_balance_record_archive"
PROPERTIES
(
"type" = "s3",
"s3.endpoint" = "cos.ap-singapore.myqcloud.com",
"s3.region" = "ap-singapore",
"s3.bucket" = "doris-archive-prod-1353452678",
"s3.root.path" = "/archive/doris/prod/ods/ods_1_2_game_center_user_balance_record_archive",
"s3.access_key" = "IKIDvmV3OrKi1Pp272oTzhIn3EKXimw9IPND",
"s3.secret_key" = "31ysqtqeVIySn0ATp1CKKB2ukRhx56tf",
"s3.connection.maximum" = "50",
"s3.connection.request.timeout" = "3000",
"s3.connection.timeout" = "1000"
);
CREATE STORAGE POLICY doris_policy_prod_ods_1_2_game_center_user_balance_record_archive
PROPERTIES(
"storage_resource" = "cos_bigdata_prod_ods_1_2_game_center_user_balance_record_archive",
"cooldown_ttl" = "60"
);
ALTER TABLE ods_1_2_game_center_user_balance_record_archive set ("storage_policy" = "doris_policy_prod_ods_1_2_game_center_user_balance_record_archive");
