-- version doris-2.0.3
-- show create table ...
CREATE TABLE device_origin_data (
partition_date date NULL,
time_stamp bigint(20) NULL,
device_id varchar(255) NULL,
user_id varchar(255) NULL,
device_type text NULL,
device_metrics json NULL,
event_hour datetime NULL,
event_time datetime NULL
) ENGINE=OLAP
UNIQUE KEY(partition_date, time_stamp, device_id, user_id)
COMMENT 'OLAP'
PARTITION BY RANGE(partition_date)
(
PARTITION p_202402 VALUES [('2024-02-01'), ('2024-03-01')),
PARTITION p_202403 VALUES [('2024-03-01'), ('2024-04-01')),
PARTITION p_202404 VALUES [('2024-04-01'), ('2024-05-01')),
PARTITION p_202405 VALUES [('2024-05-01'), ('2024-06-01')),
PARTITION p_202406 VALUES [('2024-06-01'), ('2024-07-01')),
PARTITION p_202407 VALUES [('2024-07-01'), ('2024-08-01')),
PARTITION p_202408 VALUES [('2024-08-01'), ('2024-09-01')))
DISTRIBUTED BY HASH(device_id, user_id) BUCKETS 100
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Europe/London",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "12",
"dynamic_partition.prefix" = "p_",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "100",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"dynamic_partition.storage_medium" = "SSD",
"dynamic_partition.start_day_of_month" = "1",
"storage_format" = "V2",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
-- 导出时的语句
EXPORT TABLE DeviceHubData.device_origin_data
partition (p_202405)
TO 's3://dw-cluster-ap-northeast-1/doris/export/DeviceHubData/device_origin_data/partition=p_202405/'
PROPERTIES (
'columns' = 'device_id,user_id,time_stamp,device_type,device_metrics',
'column_separator' = '\x07',
'format' = 'csv',
'max_file_size' = '64MB',
'parallelism' = '2'
)
with S3 (
'AWS_ENDPOINT' = 's3.ap-northeast-1.amazonaws.com',
'AWS_REGION' = 'ap-northeast-1',
'AWS_ACCESS_KEY' = '',
'AWS_SECRET_KEY' = ''
);
