自动分区的分区表突然无法写入数据,数据写入sql如下
INSERT INTO szmssq_dwd.dwd_fact_slfb_pro_app_content
select
b.create_time as order_create_time,
a.id as handle_id,
a.create_time as handle_date,
a.state as handle_link_desc,
a.pro_wo_id as order_id,
a.app_org_id as handle_dept,
a.oper_type as handle_type,
null as next_finish_time,
a.assign_org_id as next_handle_dept,
a.oper_name as handle_name,
CONCAT(CAST(a.state AS CHAR), '_', CAST(a.oper_type AS CHAR)) as handle_cd,
a.back_reason as back_reason,
a.disagree_back_cause as rx_disagree_back_cause,
a.org_back_case as rx_back_case,
a.back_content_type as back_content_type,
a.back_content as back_content,
a.specific_unit_code as specific_unit_code,
a.app_user_no as app_user_no,
a.treatment_type as treatment_type,
a.lz_scope as lz_scope,
a.lz_comment as lz_comment,
a.promise_end_date as promise_end_date,
a.content as handle_content,
a._update_time as _update_time,
a.reply_details as reply_details
FROM ods_slfb_pro_app_content a
JOIN dwd_fact_slfb_pro_wo_form_full b ON a.pro_wo_id = b.order_id
WHERE a._update_time >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00');
报错如下
2024-08-13T17:05:02.835 ERROR: errCode = 2, detailMessage = Unknown column 'order_create_time' in 'a'
单独执行select片段能正常查出来数据,怀疑是数据写入的问题,show create table查看了一下目标表,发现了一个问题自动分区的字段本来是order_create_time
,不知道啥时候变成了
AUTO PARTITION BY RANGE (date_trunc(<slot 7>, 'month'))
<slot 7>不知道是哪里来的,重新建表后写入正常。
show create table的
完整见表语句如下:
CREATE TABLE `dwd_fact_slfb_pro_app_content` (
`order_create_time` DATETIME NOT NULL ,
`handle_id` VARCHAR(136) NULL ,
`handle_date` DATETIME NULL ,
`handle_link_desc` BIGINT NULL,
`order_id` VARCHAR(136) NULL ,
`handle_dept` VARCHAR(136) NULL ,
`handle_type` BIGINT NULL ',
`next_finish_time` VARCHAR(900) NULL ,
`next_handle_dept` VARCHAR(136) NULL ,
`handle_name` VARCHAR(300) NULL ,
`handle_cd` VARCHAR(30) NULL',
`back_reason` VARCHAR(512) NULL,
`rx_disagree_back_cause` VARCHAR(900) NULL,
`rx_back_case` VARCHAR(900) NULL ',
`back_content_type` VARCHAR(300) NULL ,
`back_content` TEXT NULL ,
`specific_unit_code` VARCHAR(150) NULL ,
`app_user_no` VARCHAR(132) NULL ,
`treatment_type` VARCHAR(360) NULL ,
`lz_scope` VARCHAR(390) NULL,
`lz_comment` TEXT NULL ,
`promise_end_date` VARCHAR(60) NULL ,
`handle_content` TEXT NULL ,
`_update_time` DATETIME NULL ,
`reply_details` TEXT NULL
) ENGINE=OLAP
UNIQUE KEY(`order_create_time`, `handle_id`)
COMMENT ''
AUTO PARTITION BY RANGE (date_trunc(<slot 7>, 'month'))
(PARTITION p20220101000000 VALUES [('2022-01-01 00:00:00'), ('2022-02-01 00:00:00')),
PARTITION p20220201000000 VALUES [('2022-02-01 00:00:00'), ('2022-03-01 00:00:00')),
PARTITION p20220301000000 VALUES [('2022-03-01 00:00:00'), ('2022-04-01 00:00:00')),
PARTITION p20220401000000 VALUES [('2022-04-01 00:00:00'), ('2022-05-01 00:00:00')),
PARTITION p20220501000000 VALUES [('2022-05-01 00:00:00'), ('2022-06-01 00:00:00')),
PARTITION p20220601000000 VALUES [('2022-06-01 00:00:00'), ('2022-07-01 00:00:00')),
PARTITION p20220701000000 VALUES [('2022-07-01 00:00:00'), ('2022-08-01 00:00:00')),
PARTITION p20220801000000 VALUES [('2022-08-01 00:00:00'), ('2022-09-01 00:00:00')),
PARTITION p20220901000000 VALUES [('2022-09-01 00:00:00'), ('2022-10-01 00:00:00')),
PARTITION p20221001000000 VALUES [('2022-10-01 00:00:00'), ('2022-11-01 00:00:00')),
PARTITION p20221101000000 VALUES [('2022-11-01 00:00:00'), ('2022-12-01 00:00:00')),
PARTITION p20221201000000 VALUES [('2022-12-01 00:00:00'), ('2023-01-01 00:00:00')),
PARTITION p20230101000000 VALUES [('2023-01-01 00:00:00'), ('2023-02-01 00:00:00')),
PARTITION p20230201000000 VALUES [('2023-02-01 00:00:00'), ('2023-03-01 00:00:00')),
PARTITION p20230301000000 VALUES [('2023-03-01 00:00:00'), ('2023-04-01 00:00:00')),
PARTITION p20230401000000 VALUES [('2023-04-01 00:00:00'), ('2023-05-01 00:00:00')),
PARTITION p20230501000000 VALUES [('2023-05-01 00:00:00'), ('2023-06-01 00:00:00')),
PARTITION p20230601000000 VALUES [('2023-06-01 00:00:00'), ('2023-07-01 00:00:00')),
PARTITION p20230701000000 VALUES [('2023-07-01 00:00:00'), ('2023-08-01 00:00:00')),
PARTITION p20230801000000 VALUES [('2023-08-01 00:00:00'), ('2023-09-01 00:00:00')),
PARTITION p20230901000000 VALUES [('2023-09-01 00:00:00'), ('2023-10-01 00:00:00')),
PARTITION p20231001000000 VALUES [('2023-10-01 00:00:00'), ('2023-11-01 00:00:00')),
PARTITION p20231101000000 VALUES [('2023-11-01 00:00:00'), ('2023-12-01 00:00:00')),
PARTITION p20231201000000 VALUES [('2023-12-01 00:00:00'), ('2024-01-01 00:00:00')),
PARTITION p20240101000000 VALUES [('2024-01-01 00:00:00'), ('2024-02-01 00:00:00')),
PARTITION p20240201000000 VALUES [('2024-02-01 00:00:00'), ('2024-03-01 00:00:00')),
PARTITION p20240301000000 VALUES [('2024-03-01 00:00:00'), ('2024-04-01 00:00:00')),
PARTITION p20240401000000 VALUES [('2024-04-01 00:00:00'), ('2024-05-01 00:00:00')),
PARTITION p20240501000000 VALUES [('2024-05-01 00:00:00'), ('2024-06-01 00:00:00')),
PARTITION p20240601000000 VALUES [('2024-06-01 00:00:00'), ('2024-07-01 00:00:00')),
PARTITION p20240701000000 VALUES [('2024-07-01 00:00:00'), ('2024-08-01 00:00:00')),
PARTITION p20240801000000 VALUES [('2024-08-01 00:00:00'), ('2024-09-01 00:00:00')))
DISTRIBUTED BY HASH(`handle_id`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
); |