CREATE TABLE AS SELECT

Description

CREATE TABLE AS SELECT (CTAS)ステートメントを使用すると、テーブルをクエリし、クエリ結果に基づいて新しいテーブルを作成し、そしてクエリ結果を新しいテーブルに挿入することができます。

SUBMIT TASKを使用して非同期のCTASタスクを送信することもできます。

Syntax

テーブルをクエリし、クエリ結果に基づいて新しいテーブルを作成し、そしてクエリ結果を新しいテーブルに挿入する。ただし、同期的に実行します。

CREATE TABLE [IF NOT EXISTS] [database.]table_name
[(column_name [, column_name2, ...]]
[key_desc]
[COMMENT "table comment"]
[partition_desc]
[distribution_desc]
[PROPERTIES ("key"="value", ...)]
AS SELECT query
[ ... ]

テーブルを非同期でクエリし、クエリ結果に基づいて新しいテーブルを作成し、そしてクエリ結果を新しいテーブルに挿入する。

SUBMIT [/*+ SET_VAR(key=value) */] TASK [[database.]<task_name>]AS
CREATE TABLE [IF NOT EXISTS] [database.]table_name
[(column_name [, column_name2, ...]]
[key_desc]
[COMMENT "table comment"]
[partition_desc]
[distribution_desc]
[PROPERTIES ("key"="value", ...)]
AS SELECT query
[ ... ]

Parameters

Parameter	Required	Description
column_name	Yes	新しいテーブルの列名です。列のデータ型を指定する必要はありません。StarRocksは列のために適切なデータ型を自動的に指定します。FLOATとDOUBLEデータはDECIMAL(38,9)データに変換されます。CHAR、VARCHAR、およびSTRINGデータはVARCHAR(65533)データに変換されます。
key_desc	No	構文は`key_type ( <col_name1> [, <col_name2> , ...])`です。パラメータ： `key_type`：新しいテーブルのキーのタイプ。有効な値：`DUPLICATE KEY`および`PRIMARY KEY`。デフォルト値：`DUPLICATE KEY`。 `col_name`：キーを形成する列。
COMMENT	No	新しいテーブルのコメントです。
partition_desc	No	新しいテーブルのパーティショニング方法です。このパラメータを指定しない場合、新しいテーブルはデフォルトでパーティションを持たない状態になります。パーティショニングの詳細については、CREATE TABLEを参照してください。
distribution_desc	No	新しいテーブルのバケット方法です。このパラメータを指定しない場合、バケット列はコストベースの最適化器(CBO)が収集した基数が最も高い列にデフォルトで設定されます。バケット数のデフォルト値は10です。CBOが基数の情報を収集しない場合、バケット列は新しいテーブルの最初の列にデフォルトで設定されます。バケットに関する詳細については、CREATE TABLEを参照してください。
Properties	No	新しいテーブルのプロパティです。
AS SELECT query	Yes	クエリの結果です。`... AS SELECT query`で列を指定することもできます。たとえば、`... AS SELECT a, b, c FROM table_a;`という指定ができます。この例では、`a`、`b`、`c`はクエリされるテーブルの列名を示しています。新しいテーブルの列名を指定しない場合、新しいテーブルの列名も`a`、`b`、`c`が使用されます。`... AS SELECT query`で式を指定することもできます。たとえば、`... AS SELECT a+1 AS x, b+2 AS y, cc AS z FROM table_a;`という指定ができます。この例では、`a+1`、`b+2`、`cc`はクエリされるテーブルの列名を示し、`x`、`y`、`z`は新しいテーブルの列名を示します。注意：新しいテーブルの列数は、SELECTステートメントで指定された列数と同じである必要があります。わかりやすい列名を使用することをお勧めします。

Usage notes

CTASステートメントでは、次の条件を満たす新しいテーブルの作成のみがサポートされます：
- ENGINEがOLAPであること。
- テーブルはデフォルトでDuplicate Keyテーブルです。キーをPrimary Keyテーブルとして指定することもできます。
- ソートキーは最初の3つの列で、これら3つの列のデータ型のストレージスペースが36バイトを超えないようにしてください。
CTASステートメントでは、新しく作成されたテーブルに対してインデックスを設定することはサポートされていません。
CTASステートメントが実行できない場合（例：FEの再起動などの理由で）、次のいずれかの問題が発生する可能性があります：
- 新しいテーブルは作成されますが、データは含まれません。
- 新しいテーブルの作成に失敗します。
新しいテーブルが作成された後、複数の方法（例：INSERT INTOなど）を使用して新しいテーブルにデータを挿入する場合、最初にINSERT操作を完了した方法がデータを新しいテーブルに挿入します。
新しいテーブルが作成された後は、このテーブルに対して手動でユーザーに権限を付与する必要があります。
テーブルを非同期でクエリし、クエリ結果に基づいて新しいテーブルを作成し、そしてクエリの結果を新しいテーブルに挿入する場合、タスクの名前を指定しませんでしたら、StarRocksは自動的にタスクの名前を生成します。

Examples

Example 1: テーブル order をクエリし、クエリ結果に基づいて新しいテーブル order_new を作成し、そしてクエリ結果を新しいテーブルに挿入する。

CREATE TABLE order_new
AS SELECT * FROM order;

Example 2: テーブル order の k1、k2、k3 列をクエリし、クエリ結果に基づいて新しいテーブル order_new を作成し、そしてクエリ結果を新しいテーブルに挿入する。さらに、新しいテーブルの列名を a、b、c に設定する。

CREATE TABLE order_new (a, b, c)
AS SELECT k1, k2, k3 FROM order;

または

CREATE TABLE order_new
AS SELECT k1 AS a, k2 AS b, k3 AS c FROM order;

Example 3: テーブル employee の salary 列で最大値をクエリし、クエリ結果に基づいて新しいテーブル employee_new を作成し、そしてクエリ結果を新しいテーブルに挿入する。さらに、新しいテーブルの列名を salary_max に設定する。

CREATE TABLE employee_new
AS SELECT MAX(salary) AS salary_max FROM employee;

データが挿入された後、新しいテーブルをクエリします。

SELECT * FROM employee_new;

+------------+
| salary_max |
+------------+
|   10000    |
+------------+

Example 4: Primary Keyテーブルを作成するためにCTASを使用します。ただし、Primary Keyテーブルのデータ行数はクエリ結果のデータ行数よりも少ない場合があります。これは、Primary Keyテーブルが、同じプライマリキーを持つ複数の行のうち、最新のデータ行のみを保持するためです。

CREATE TABLE employee_new
PRIMARY KEY(order_id)
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

Example 5: lineorder、customer、supplier、partの4つのテーブルをクエリし、クエリ結果に基づいて新しいテーブル lineorder_flat を作成し、そしてクエリ結果を新しいテーブルに挿入する。さらに、新しいテーブルのパーティショニング方法とバケット方法を指定します。

CREATE TABLE lineorder_flat
PARTITION BY RANGE(`LO_ORDERDATE`)
(
    START ("1993-01-01") END ("1999-01-01") EVERY (INTERVAL 1 YEAR)
)
DISTRIBUTED BY HASH(`LO_ORDERKEY`) AS SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

Example 6: テーブル order_detail を非同期でクエリし、クエリ結果に基づいて新しいテーブル order_statistics を作成し、そしてクエリ結果を新しいテーブルに挿入する。

SUBMIT TASK AS CREATE TABLE order_statistics AS SELECT COUNT(*) as count FROM order_detail;

+-------------------------------------------+-----------+
| TaskName                                  | Status    |
+-------------------------------------------+-----------+
| ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2 | SUBMITTED |
+-------------------------------------------+-----------+

タスクの情報を確認します。

SELECT * FROM INFORMATION_SCHEMA.tasks;

-- タスクの情報

TASK_NAME: ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2
CREATE_TIME: 2022-06-14 14:07:06
SCHEDULE: MANUAL
DATABASE: default_cluster:test
DEFINITION: CREATE TABLE order_statistics AS SELECT COUNT(*) as cnt FROM order_detail
EXPIRE_TIME: 2022-06-17 14:07:06

TaskRunの状態を確認します。

SELECT * FROM INFORMATION_SCHEMA.task_runs;

-- TaskRunの状態

QUERY_ID: 37bd2b63-eba8-11ec-8d41-bab8ee315bf2
TASK_NAME: ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2
CREATE_TIME: 2022-06-14 14:07:06
FINISH_TIME: 2022-06-14 14:07:07
STATE: SUCCESS
DATABASE: 
DEFINITION: CREATE TABLE order_statistics AS SELECT COUNT(*) as cnt FROM order_detail
EXPIRE_TIME: 2022-06-17 14:07:06
ERROR_CODE: 0
ERROR_MESSAGE: NULL

TaskRunの状態がSUCCESSである場合、新しいテーブルをクエリします。

SELECT * FROM order_statistics;

CREATE TABLE AS SELECT

Description​

Syntax​

Parameters​

Usage notes​

Examples​

Description

Syntax

Parameters

Usage notes

Examples