LM计算引擎 CTAS是什么?

CTAS（Create Table As Select）操作详解与注意事项

CTAS（Create Table As Select）是一种强大的SQL语句，允许您根据查询结果集创建新表并填充数据。此过程不仅简化了建表和数据复制的步骤，还提供了灵活性以适应不同的业务需求。以下是关于CTAS操作的详细说明及关键点：

CTAS操作流程概述

1. Step 1: 执行CREATE TABLE命令，建立新表结构，这一步骤与其他实时写入表的创建过程一致。
2. Step 2: 完成新表的上线操作，确保表可以接收数据。
3. Step 3: 通过INSERT from SELECT操作将查询结果插入新表中。
4. Step 4: 对新表执行Merge Baseline操作，对已插入的实时数据进行整理合并。

注意事项

• 原子性问题: AnalyticDB当前不保证CTAS整个流程的原子性。建表和表上线是原子操作，失败则回滚；但INSERT from SELECT可能部分成功，需注意数据完整性。
• 业务保障策略: 在ETL流程中使用CTAS时，为确保业务完整性，可采取以下策略：
  • 先向目标表插入一条标记数据。
  • 执行实时表的FLUSH操作，确保数据落盘。
  • 查询验证标记数据是否成功入库，若未成功，则考虑删除表并重新尝试CTAS操作。

CTAS语法示例

• 完全定义列:

  CREATE TABLE IF NOT EXISTS weitao.table1 (
  user_id long COMMENT 'user ID',
  seller_id long COMMENT 'seller ID',
  follow_id long COMMENT 'user_id follower',
  score double COMMENT 'user_id score',
  interest_flag multivalue COMMENT 'multiple value column' delimiter ',',
  primary Key (user_id, seller_id)
  )
  PARTITION BY HASH KEY(user_id) PARTITION NUM 128
  CLUSTERED BY (follow_id, score)
  TABLEGROUP weitao_group12
  AS SELECT * FROM db1.table1 WHERE col1 = 3;
  

  此示例中，明确指定了目标表的所有列定义、注释、主键、分区方式等信息，并基于一个筛选条件从源表中选择数据填充新表。

• 不定义列:

  CREATE TABLE bar (PRIMARY KEY (n)) AS SELECT n FROM foo;
  

  当不直接定义目标表的列结构时，CTAS会自动采用SELECT子句返回的列名和类型，但要求显式指定主键。

结论

CTAS是快速创建表并填充数据的有效手段，适用于多种场景，包括但不限于数据迁移、报表准备或临时数据分析。在使用过程中，务必关注数据完整性和操作的非原子性风险，合理设计ETL流程以确保业务连续性和数据准确性。