共计 5554 个字符，预计需要花费 14 分钟才能阅读完成。
Doris数据库执行建表语句，报错提示如下：
ERROR 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@62] get db connection error com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema. at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:57) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] at com.baidu.bce.edap.jobs.JobManager.lambda$startJob$1(JobManager.java:133) [com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_211] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_211] at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_211] Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[na:na] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[na:na] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[na:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483) ~[na:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441) ~[na:na] at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[na:na] at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[na:na] at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:46) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] ... 4 common frames omitted 20 INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@342] job purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915 is finished with FAILED status on RemoteAgent at 1684285152819 21 INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@253] start to close db connection 22 INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@255] close sql statement 23 INFO 2023-05-17 08:59:12.819 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@260] close db connection 24 ERROR 2023-05-17 08:59:12.825 [JobSubmitExecutor-223:purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915@139] startJob jobId:[purchase_costs_analysis.script.1684219093_773eff88-24e3-428f-99a2-3025c2d0f915] error com.baidu.bce.edap.jobs.JobException: com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema. at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:64) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] at com.baidu.bce.edap.jobs.JobManager.lambda$startJob$1(JobManager.java:133) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_211] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_211] at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_211] Caused by: com.baidu.bce.edap.jobs.JobException: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema. at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:57) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] ... 4 common frames omitted Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[na:na] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[na:na] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[na:na] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[na:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483) ~[na:na] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2441) ~[na:na] at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[na:na] at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[na:na] at com.baidu.bce.edap.jobs.AgentJDBCJob.run(AgentJDBCJob.java:46) ~[com.baidu.bce.edap.job-core-1.0.0.4-20230412.123416-11.jar:na] ... 4 common frames omitted
日志关键信息：
Caused by: java.sql.SQLException: errCode = 2, detailMessage = Key columns should be a ordered prefix of the schema.
问题原因：
注意⚠️：AGGREGATE KEY (….) 括号里面的维度列顺序要严格按照，上文建表字段的顺序保持一致
原始SQL(按照列创建顺序比对下来，我的SQL在指定AGGREGATE KEY列时，字段丢失，顺序错乱都会出现这个错误)：
CREATE TABLE `purchase_costs_analysis` ( `secondary_company` ...., `tertiary_company` ....., `project` varchar(30) ....., `purchase_method` ....., `material_category` ....., `purchase_category` ....., `purchase_model` ....., `subcontract_type` ....., `subcontract_model` ....., `factory_direct_purchase` ....., `created_time` ....., `labor_professional_subcontract_total_standard_cost` decimal(22, 8) SUM ....., `labor_professional_subcontract_total_purchase_amount` decimal(22, 8) SUM ....., `material_equipment_total_standard_cost` decimal(22, 8) SUM ....., `material_equipment_subcontract_total_purchase_amount` decimal(22, 8) SUM ..... ) ENGINE = OLAP AGGREGATE KEY(`secondary_company`,`tertiary_company`,`project`,`purchase_method`,`material_category`,`purchase_model`,`subcontract_type`,`subcontract_model`,`factory_direct_purchase`,`created_time`) DISTRIBUTED BY HASH(`project`);
正文完
