CREATE TABLE AS SELECT

説明

CREATE TABLE AS SELECT (CTAS) ステートメントを使用して、同期または非同期でテーブルをクエリし、クエリ結果に基づいて新しいテーブルを作成し、その後クエリ結果を新しいテーブルに挿入できます。

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

構文

• テーブルを同期的にクエリし、クエリ結果に基づいて新しいテーブルを作成し、その後クエリ結果を新しいテーブルに挿入します。

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [database.]table_name
  [column_name1 [, column_name2, ...]]
  [index_definition1 [, index_definition2, ...]]
  [key_desc]
  [COMMENT "table comment"]
  [partition_desc]
  [distribution_desc]
  [ORDER BY (column_name1 [, column_name2, ...])]
  [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_name1 [, column_name2, ...]]
  [index_definition1 [, index_definition2, ...]]
  [key_desc]
  [COMMENT "table comment"]
  [partition_desc]
  [distribution_desc]
  [ORDER BY (column_name1 [, column_name2, ...])]
  [PROPERTIES ("key"="value", ...)] AS SELECT query
  [ ... ]

パラメータ

パラメータ	必須	説明
TEMPORARY	いいえ	一時テーブルを作成します。v3.3.1 から、StarRocks は Default Catalog での一時テーブルの作成をサポートしています。詳細は Temporary Table を参照してください。現在、StarRocks は SUBMIT TASK を使用して非同期タスクでの一時テーブルの作成をサポートしていません。
column_name	いいえ	新しいテーブルの列名です。列のデータ型を指定する必要はありません。StarRocks は自動的に適切なデータ型を指定します。StarRocks は FLOAT および DOUBLE データを DECIMAL(38,9) データに変換します。また、CHAR、VARCHAR、および STRING データを VARCHAR(65533) データに変換します。
index_definition	いいえ	v3.1.8 以降、新しいテーブルにビットマップインデックスを作成できます。構文は INDEX index_name (col_name[, col_name, ...]) [USING BITMAP] COMMENT 'xxxxxx' です。パラメータの説明と使用上の注意については Bitmap indexes を参照してください。
key_desc	いいえ	構文は key_type ( <col_name1> [, <col_name2> , ...]) です。 パラメータ: key_type: 新しいテーブルのキータイプ。有効な値: DUPLICATE KEY および PRIMARY KEY。デフォルト値: DUPLICATE KEY。 col_name: キーを形成する列。
COMMENT	いいえ	新しいテーブルのコメント。
partition_desc	いいえ	新しいテーブルのパーティション化の手法。デフォルトでは、このパラメータを指定しない場合、新しいテーブルにはパーティションがありません。パーティション化の詳細については CREATE TABLE を参照してください。
distribution_desc	いいえ	新しいテーブルのバケット化の手法。このパラメータを指定しない場合、バケット列はコストベースオプティマイザ (CBO) によって収集された最も高いカーディナリティを持つ列にデフォルト設定されます。バケット数はデフォルトで 10 です。CBO がカーディナリティに関する情報を収集しない場合、バケット列は新しいテーブルの最初の列にデフォルト設定されます。バケット化の詳細については CREATE TABLE を参照してください。
ORDER BY	いいえ	v3.1.8 以降、新しいテーブルが主キーテーブルである場合、ソートキーを指定できます。ソートキーは任意の列の組み合わせにすることができます。主キーテーブルは、テーブル作成時に PRIMARY KEY (xxx) が指定されたテーブルです。
Properties	いいえ	新しいテーブルのプロパティ。
AS SELECT query	はい	クエリ結果。... 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, c*c AS z FROM table_a; のように指定します。この例では、a+1、b+2、c*c はクエリされたテーブルの列名を示し、x、y、z は新しいテーブルの列名を示します。注意: 新しいテーブルの列数は、SELECT ステートメントで指定された列数と同じである必要があります。識別しやすい列名を使用することをお勧めします。

使用上の注意

• CTAS ステートメントは、次の要件を満たす新しいテーブルのみを作成できます:

  • ENGINE は OLAP です。

  • テーブルはデフォルトで重複キーテーブルです。key_desc で主キーテーブルとして指定することもできます。

  • ソートキーは最初の 3 列であり、これらの 3 列のデータ型のストレージスペースは 36 バイトを超えません。

• CTAS 文が FE の再起動などの理由で実行に失敗した場合、次のいずれかの問題が発生する可能性があります:

  • 新しいテーブルが正常に作成されますが、データが含まれていません。

  • 新しいテーブルの作成に失敗します。

• 新しいテーブルが作成された後、複数の方法（INSERT INTO など）を使用して新しいテーブルにデータを挿入する場合、INSERT 操作を最初に完了した方法がそのデータを新しいテーブルに挿入します。

• 新しいテーブルが作成された後、このテーブルに対する権限を手動でユーザーに付与する必要があります。

• テーブルを非同期でクエリし、クエリ結果に基づいて新しいテーブルを作成する際にタスクの名前を指定しない場合、StarRocks はタスクの名前を自動的に生成します。

例

例 1: テーブル order を同期的にクエリし、クエリ結果に基づいて新しいテーブル order_new を作成し、その後クエリ結果を新しいテーブルに挿入します。

CREATE TABLE order_new
AS SELECT * FROM order;

例 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;

例 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    |
+------------+

例 4: テーブル customers の customer_id および first_name 列を同期的にクエリし、クエリ結果に基づいて新しいテーブル customers_new を作成し、その後クエリ結果を新しいテーブルに挿入します。さらに、新しいテーブルの列名を customer_id_new および first_name_new に設定します。また、新しいテーブルの customer_id_new 列にビットマップインデックスを作成します。

CREATE TABLE customers_new 
(   customer_id_new,
    first_name_new,
    INDEX idx_bitmap_customer_id (customer_id_new) USING BITMAP
) 
AS SELECT customer_id,first_name FROM customers;

例 5: テーブル customers を同期的にクエリし、クエリ結果に基づいて新しいテーブル customers_new を作成し、その後クエリ結果を新しいテーブルに挿入します。さらに、新しいテーブルを主キーテーブルとして指定し、そのソートキーを first_name および last_name に指定します。

CREATE TABLE customers_pk
PRIMARY KEY (customer_id)
ORDER BY (first_name,last_name)
AS SELECT  * FROM customers;

例 6: CTAS を使用して主キーテーブルを作成します。主キーテーブルのデータ行数がクエリ結果の行数より少ない場合があることに注意してください。これは、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;

例 7: 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;

例 8: テーブル 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;