【Doris】[1105] [HY000]: errCode = 2, detailMessage = Key columns should be a ordered prefix of the sc

图腾还未停下

已于 2022-10-11 09:31:14 修改

阅读量1.2w

点赞数 1

分类专栏： Doris 文章标签：数据库大数据 odps

于 2022-10-10 15:29:44 首次发布

本文链接：https://blog.csdn.net/weixin_42270217/article/details/127245338

版权

Doris 专栏收录该内容

1 篇文章

订阅专栏

使用Doris执行以下建表语句

CREATE TABLE ods.ods_log_data
(
    `action` INT COMMENT 'action',
     `mac` VARCHAR(255) COMMENT 'mac',
    `coll_id` VARCHAR(255) COMMENT '专辑id',
    `coll_name` VARCHAR(255) COMMENT '专辑名称',
    `video_id` VARCHAR(255) COMMENT '视频ID',
    `video_name` VARCHAR(255) COMMENT '视频ID',
    `channel_id` INT COMMENT '频道ID',
    `content_source` INT COMMENT '内容源',
    `play_dur` BIGINT COMMENT '播放时长',
    `play_type` INT COMMENT '播放类型',
    `video_type` INT COMMENT '视频类型',
    `ls` INT COMMENT '日志序号',
    `time` DATETIME COMMENT '时间'
)
DUPLICATE KEY(`time`,`action`,`mac`)
COMMENT "日志数据"
PARTITION BY RANGE(`time`) ()
DISTRIBUTED BY HASH(`mac`)
PROPERTIES (
     "dynamic_partition.enable" = "true",
     "dynamic_partition.time_unit" = "MONTH",
     "dynamic_partition.end" = "3",
     "dynamic_partition.prefix" = "p",
     "dynamic_partition.buckets" = "16"
)

报错了

[1105] [HY000]: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.

这是因为建表语句中的列顺序与DUPLICATE KEY指定的排序列顺序不一致导致的
在这里插入图片描述
调整一下建表语句中的列顺序问题解决

CREATE TABLE ods.ods_log_data
(
    `time` DATETIME COMMENT '时间',
    `action` INT COMMENT 'action',
     `mac` VARCHAR(255) COMMENT 'mac',
    `coll_id` VARCHAR(255) COMMENT '专辑id',
    `coll_name` VARCHAR(255) COMMENT '专辑名称',
    `video_id` VARCHAR(255) COMMENT '视频ID',
    `video_name` VARCHAR(255) COMMENT '视频ID',
    `channel_id` INT COMMENT '频道ID',
    `content_source` INT COMMENT '内容源',
    `play_dur` BIGINT COMMENT '播放时长',
    `play_type` INT COMMENT '播放类型',
    `video_type` INT COMMENT '视频类型',
    `ls` INT COMMENT '日志序号'
)
DUPLICATE KEY(`time`,`action`,`mac`)
COMMENT "日志数据"
PARTITION BY RANGE(`time`) ()
DISTRIBUTED BY HASH(`mac`)
PROPERTIES (
     "dynamic_partition.enable" = "true",
     "dynamic_partition.time_unit" = "MONTH",
     "dynamic_partition.end" = "3",
     "dynamic_partition.prefix" = "p",
     "dynamic_partition.buckets" = "16"
)