Перейти к основному содержимому

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НетСоздает временную таблицу. Начиная с версии 1.5.0, Selena поддерживает создание временных таблиц в Default Catalog. Для получения дополнительной информации см. Временная таблица. В настоящее время Selena не поддерживает создание временных таблиц с асинхронными задачами с использованием SUBMIT TASK.
column_nameНетИмя столбца в новой таблице. Вам не нужно указывать тип данных для столбца. Selena автоматически указывает подходящий тип данных для столбца. Selena преобразует данные FLOAT и DOUBLE в данные DECIMAL(38,9). Selena также преобразует данные CHAR, VARCHAR и STRING в данные VARCHAR(65533).
index_definitionНетНачиная с версии 1.5.0, для новой таблицы может быть создан bitmap-индекс. Синтаксис: INDEX index_name (col_name[, col_name, ...]) [USING BITMAP] COMMENT 'xxxxxx'. Для получения дополнительной информации об описании параметров и примечаниях по использованию см. Bitmap-индексы.
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НетНачиная с версии 1.5.0, для новой таблицы может быть указан ключ сортировки, если новая таблица является таблицей Primary 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.

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

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

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

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

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

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

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

Примеры​

Пример 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-индекс для столбца 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 и указать ее ключ сортировки как 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 хранит только самую последнюю строку данных среди группы строк, имеющих одинаковый первичный ключ.

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

Проверить информацию о задаче.

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;