CREATE ROUTINE LOAD ccc_scaling_output_load_101 ON ccc_scaling_output
WITH APPEND
COLUMNS TERMINATED BY ",",
COLUMNS(DataID,DataType,BranchCode,CameraSerial,Serial,ROiId,MetricId,PeopleTypeId,CombineObjectTypeId,OutputTopicName,InValue,OutValue,Timestamp,Create_at,UtcDateTime,UploadedLocalDateTime,UploadedUTCDateTime,Source,PeopleId,DeviceUploadedLocalDateTime,DeviceUploadedUTCDateTime,IsPatched,LastUpdateUTCDatetime,LastUpdateUTCDatetime=utc_timestamp()),
PRECEDING FILTER (`UploadedUTCDateTime` > utc_timestamp() - INTERVAL 60 DAY)
PROPERTIES
(
"desired_concurrent_number" = "30",
"max_error_number" = "10000000",
"max_batch_interval" = "20",
"max_batch_rows" = "20000000",
"max_batch_size" = "2009715200",
"format" = "json",
"jsonpaths" = '["$.DataID", "$.DataType", "$.BranchCode", "$.CameraSerial", "$.Serial", "$.RoiId", "$.MetricId", "$.PeopleTypeId","$.CombineObjectTypeId", "$.OutputTopicName", "$.InValue", "$.OutValue", "$.Timestamp", "$.Create_at", "$.UtcDateTime", "$.UploadedLocalDateTime", "$.UploadedUTCDateTime", "$.Source", "$.PeopleId","$.DeviceUploadedLocalDateTime","$.DeviceUploadedUTCDateTime","$.IsPatched"]',
"strip_outer_array" = "false",
"num_as_string" = "false",
"fuzzy_parse" = "false",
"strict_mode" = "false",
"timezone" = "Etc/UTC",
"exec_mem_limit" = "2147483648"
)
FROM KAFKA
(
"kafka_topic" = "ccc-scaling-output",
"property.kafka_default_offsets" = "OFFSET_BEGINNING",
"property.kafka_offsets" = "OFFSET_BEGINNING",
"property.isolation.level" = "read_committed"
);


CREATE TABLE `ccc_scaling_output` (
  `UploadedLocalDateTime` datetime NULL,
  `Serial` varchar(255) NULL,
  `Create_at` datetime NULL,
  `PeopleId` int NULL,
  `DataID` varchar(255) NULL,
  `DataType` varchar(255) NULL,
  `BranchCode` varchar(255) NULL,
  `CameraSerial` varchar(255) NULL,
  `ROiId` int NULL,
  `MetricId` int NULL,
  `PeopleTypeId` int NULL,
  `CombineObjectTypeId` int NULL,
  `OutputTopicName` varchar(255) NULL,
  `InValue` int NULL,
  `OutValue` int NULL,
  `Timestamp` bigint NULL,
  `UtcDateTime` datetime NULL,
  `UploadedUTCDateTime` datetime NULL,
  `Source` varchar(255) NULL,
  `DeviceUploadedLocalDateTime` datetime NULL,
  `DeviceUploadedUTCDateTime` datetime NULL,
  `IsPatched` int NULL,
  `LastUpdateUTCDatetime` datetime NULL
) ENGINE=OLAP
DUPLICATE KEY(`UploadedLocalDateTime`, `Serial`, `Create_at`, `PeopleId`)
COMMENT 'OLAP'
PARTITION BY RANGE(`UploadedLocalDateTime`)
(PARTITION p20260422 VALUES [('2026-04-22 00:00:00'), ('2026-04-23 00:00:00')),
PARTITION p20260423 VALUES [('2026-04-23 00:00:00'), ('2026-04-24 00:00:00')),
PARTITION p20260424 VALUES [('2026-04-24 00:00:00'), ('2026-04-25 00:00:00')),
PARTITION p20260425 VALUES [('2026-04-25 00:00:00'), ('2026-04-26 00:00:00')))
DISTRIBUTED BY HASH(`UploadedLocalDateTime`, `Serial`) BUCKETS 4
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Etc/UTC",
"dynamic_partition.start" = "-2",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "4",
"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" = "HDD",
"storage_medium" = "hdd",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);