Перейти к основному содержимому
Версия: 2.0.x

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НетСоздает временную таблицу. Начиная с v1.5.2, Selena поддерживает создание временных таблиц в Default Catalog. Дополнительную информацию см. в разделе Временная таблица. В настоящее время Selena не поддерживает создание временных таблиц с помощью асинхронных задач через SUBMIT TASK.
column_nameНетИмя столбца в новой таблице. Вам не нужно указывать тип данных для столбца. Selena автоматически назначает подходящий тип данных для столбца. Selena преобразует данные FLOAT и DOUBLE в данные DECIMAL(38,9). Selena также преобразует данные CHAR, VARCHAR и STRING в данные VARCHAR(65533).
index_definitionНетНачиная с v1.5.2, для новой таблицы можно создать bitmap index. Синтаксис: 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НетМетод бакетирования новой таблицы. Если вы не указываете этот параметр, столбец bucket по умолчанию устанавливается как столбец с наибольшей кардинальностью, собранной оптимизатором на основе стоимости (CBO). Количество bucket по умолчанию равно 10. Если CBO не собирает информацию о кардинальности, столбец bucket по умолчанию устанавливается как первый столбец в новой таблице. Дополнительную информацию о бакетировании см. в разделе CREATE TABLE.
ORDER BYНетНачиная с v1.5.2, для новой таблицы можно указать sort key, если новая таблица является Primary Key таблицей. Sort key может быть комбинацией любых столбцов. Primary Key таблица — это таблица, для которой при создании таблицы указан 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.

    • По умолчанию таблица является Duplicate Key таблицей. Вы также можете указать её как Primary Key таблицу в key_desc.

    • Sort keys — это первые три столбца, и объем хранения типов данных этих трех столбцов не превышает 36 байт.

  • Если выполнение выражения CTAS завершится неудачей по таким причинам, как перезапуск FE, может возникнуть одна из следующих проблем:

    • Новая таблица успешно создана, но не содержит данных.

    • Новая таблица не создана.

  • После создания новой таблицы, если для вставки данных в новую таблицу используется несколько методов (таких как INSERT INTO), метод, который первым завершит операцию INSERT, вставит свои данные в новую таблицу.

  • После создания новой таблицы вам необходимо вручную предоставить разрешения на эту таблицу пользователям.

  • Если вы не указываете имя для Task при асинхронном запросе таблицы и создании новой таблицы на основе результата запроса, Selena автоматически генерирует имя для Task.

Примеры​

Пример 1: Синхронный запрос таблицы order и создание новой таблицы order_new на основе результата запроса, а затем вставка результата запроса в новую таблицу.

CREATE TABLE order_new
AS SELECT * FROM order;

Пример 2: Синхронный запрос столбцов k1, k2 и k3 в таблице order и создание новой таблицы 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: Синхронный запрос наибольшего значения столбца salary в таблице employee и создание новой таблицы 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: Синхронный запрос столбцов customer_id и first_name в таблице customers и создание новой таблицы customers_new на основе результата запроса, а затем вставка результата запроса в новую таблицу. Кроме того, установить имена столбцов новой таблицы как customer_id_new и first_name_new. Также построить bitmap index для столбца 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 на основе результата запроса, а затем вставка результата запроса в новую таблицу. Кроме того, указать новую таблицу как Primary Key таблицу и указать её sort key как 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 таблицы. Обратите внимание, что количество строк данных в Primary Key таблице может быть меньше, чем в результате запроса. Это происходит потому, что 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;

Пример 7: Синхронный запрос четырех таблиц, включая lineorder, customer, supplier и part, и создание новой таблицы 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 |
+-------------------------------------------+-----------+

Проверьте информацию о Task.

SELECT * FROM INFORMATION_SCHEMA.tasks;

-- Информация о Task

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;