Create Tables from Query Results Using CTAS - AnalyticDB for MySQL

CREATE TABLE AS SELECT (CTAS) creates a new table and populates it with the results of a query. CTAS shares the same syntax as CREATE TABLE—including automatic index creation for every column—and adds a SELECT clause to define the table's initial data.

Syntax

CREATE TABLE [IF NOT EXISTS] <table_name> [table_definition]
[IGNORE|REPLACE] [AS] <query_statement>

Note

CTAS and CREATE TABLE share the same default behaviors. Regardless of which statement creates a table, an index is automatically created for each column.

Parameters

Parameter	Required	Description
`table_name`	Yes	Name of the new table. Must be 1–127 characters and can contain letters, digits, and underscores (`_`). Must start with a letter or underscore. Use `db_name.table_name` to distinguish tables with the same name across different databases.
`IF NOT EXISTS`	No	Skips table creation if `table_name` already exists.
`IGNORE`	No	Before writing a record, checks whether a record with the same primary key already exists. If a match is found, the new record is discarded.
`REPLACE`	No	Before writing a record, checks whether a record with the same primary key already exists. If a match is found, the new record overwrites the existing one.

Examples

All examples create a new_customer table from the following source table:

CREATE TABLE customer (
  customer_id   BIGINT    NOT NULL COMMENT 'Customer ID',
  customer_name VARCHAR   NOT NULL COMMENT 'Customer name',
  phone_num     BIGINT    NOT NULL COMMENT 'Phone number',
  city_name     VARCHAR   NOT NULL COMMENT 'City',
  sex           INT       NOT NULL COMMENT 'Gender',
  id_number     VARCHAR   NOT NULL COMMENT 'ID card number',
  home_address  VARCHAR   NOT NULL COMMENT 'Home address',
  office_address VARCHAR  NOT NULL COMMENT 'Office address',
  age           INT       NOT NULL COMMENT 'Age',
  login_time    TIMESTAMP NOT NULL COMMENT 'Logon time',
  PRIMARY KEY (login_time, customer_id, phone_num)
)
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT 'Customer information table';

Copy all columns

Create new_customer with the same columns and data types as customer:

CREATE TABLE new_customer
AS
SELECT * FROM customer;

Copy specific columns

Create new_customer with only customer_id and customer_name:

CREATE TABLE new_customer
AS
SELECT customer_id, customer_name
FROM customer;

Define a primary key and distribution key

Copy customer_id and login_time, and explicitly define the primary key and distribution key on the new table:

CREATE TABLE new_customer (
  PRIMARY KEY (customer_id, login_time)
)
DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id, login_time
FROM customer;

Override a column data type

Copy customer_id and login_time, define the primary key and distribution key, and change login_time from TIMESTAMP to DATE:

CREATE TABLE new_customer (
  login_time DATE,
  PRIMARY KEY (customer_id, login_time)
)
DISTRIBUTED BY HASH (customer_id)
AS
SELECT customer_id, login_time
FROM customer;

Define an inverted index

Copy customer_id, customer_name, and login_time, and define customer_id as an inverted index column:

CREATE TABLE new_customer (
  INDEX a_idx (customer_id)
)
AS
SELECT customer_id, customer_name, login_time
FROM customer;