From 6607fb1f220cd1dff6450463da8e5fe5edd433f8 Mon Sep 17 00:00:00 2001 From: Nikita Vasilev Date: Fri, 26 Sep 2025 12:19:38 +0300 Subject: CTAS Docs en (#25489) --- .../yql/reference/syntax/create_table/as_select.md | 73 ++++++++++++++++++++++ .../yql/reference/syntax/create_table/index.md | 3 + .../yql/reference/syntax/create_table/toc_i.yaml | 3 +- 3 files changed, 78 insertions(+), 1 deletion(-) create mode 100644 ydb/docs/en/core/yql/reference/syntax/create_table/as_select.md diff --git a/ydb/docs/en/core/yql/reference/syntax/create_table/as_select.md b/ydb/docs/en/core/yql/reference/syntax/create_table/as_select.md new file mode 100644 index 00000000000..09154379635 --- /dev/null +++ b/ydb/docs/en/core/yql/reference/syntax/create_table/as_select.md @@ -0,0 +1,73 @@ +# Creating a table filled with query results + +{% include [not_allow_for_oltp](../../../../_includes/not_allow_for_oltp_note.md) %} + +`CREATE TABLE AS` creates a new table {% if concept_table %}[table]({{ concept_table }}){% else %}table{% endif %} filled with data from query results. + +```yql +CREATE TABLE table_name ( + PRIMARY KEY ( column, ... ) +) +WITH ( key = value, ... ) +AS SELECT ... +``` + +Names and types of columns will correspond to the `SELECT` results. +[Non-optional](../../types/optional.md) columns will also have the `NOT NULL` constraint. + + +When creating a table using `CREATE TABLE AS`, it is not possible to specify column names (column names of the created table will be derived from the query result), [secondary indexes](secondary_index.md), [vector indexes](vector_index.md), or [column groups](family.md). All of those can be changed after the table has been created using [`ALTER TABLE`](../alter_table/index.md). [Additional parameters](with.md) are also supported. + + + +## Considerations + +{% note warning %} + +Rows are overwritten, similar to using [`REPLACE INTO`](../replace_into.md ), but the order in which rows are written is unpredictable. + +If `SELECT` returns two or more rows with the same primary key value, after the `CREATE TABLE AS` is executed, there will only be one row with that primary key value in the created table. Which record from the `SELECT` was written to the table is undetermined. + +{% endnote %} + + +* `CREATE TABLE AS` is supported only for [implicit transaction control mode](../../../../ concepts/transactions.md#implicit). When the table appears at the specified path it's already filled. + +* `CREATE TABLE AS` can only be a sigle [DML](https://en.wikipedia.org/wiki/Data_manipulation_language)/[DDL](https://en.wikipedia.org/wiki/Data_definition_language) statement in a query. It's possible to use [PRAGMA](../pragma.md), [DECLARE](../declare.md) or [named expressions](../expressions.md#named-nodes) in the same query. + +* `CREATE TABLE AS` doesn't cause lock conflicts with other transactions. It doesn't use locks. Reads use a consistent snapshot. Moving or splitting [tablets](../../../../concepts/glossary.md#tablet) doesn't cause errors. + +* `CREATE TABLE AS` allows using [column-oriented tables](../../../../concepts/glossary.md#column-oriented-table) and [row-oriented tables](../../../../concepts/glossary.md#row-oriented-table) in the same query. + +* `CREATE TABLE AS` creates a temporary table and moves it to the specified location after filling that table. If there was an error during the `CREATE TABLE AS` execution, it's possible that the temporary table will not be deleted immediately, but it will remain for some short period of time. + +## Examples + +{% list tabs %} + +- Creating a row-oriented table with a single row + + ```yql + CREATE TABLE my_table ( + PRIMARY KEY (key) + ) AS SELECT + 1 AS key, + "test" AS value; + ``` + +- Creating a column-oriented table from the query results + + ```yql + CREATE TABLE my_table ( + PRIMARY KEY (key1, key2) + ) WITH ( + STORE=COLUMN + ) AS SELECT + key AS key1, + Unwrap(other_key) AS key2, + value, + String::Contains(value, "test") AS has_test + FROM other_table; + ``` + +{% endlist %} diff --git a/ydb/docs/en/core/yql/reference/syntax/create_table/index.md b/ydb/docs/en/core/yql/reference/syntax/create_table/index.md index 21a099ea8f1..965707b0688 100644 --- a/ydb/docs/en/core/yql/reference/syntax/create_table/index.md +++ b/ydb/docs/en/core/yql/reference/syntax/create_table/index.md @@ -39,6 +39,7 @@ The invocation of `CREATE TABLE` creates {% if concept_table %}a [table]({{ conc ) {% if feature_map_tables %} WITH ( key = value, ... ) + [AS SELECT ...] {% endif %} {% if oss == true and backend_name == "YDB" %} @@ -248,10 +249,12 @@ When creating row-oriented tables, it is possible to specify: * [A vector index](vector_index.md). * [Column groups](family.md). * [Additional parameters](with.md). +* [Creating a table filled with query results](as_select.md). When creating column-oriented tables, it is possible to specify: * [Column groups](family.md). * [Additional parameters](with.md). +* [Creating a table filled with query results](as_select.md). {% endif %} diff --git a/ydb/docs/en/core/yql/reference/syntax/create_table/toc_i.yaml b/ydb/docs/en/core/yql/reference/syntax/create_table/toc_i.yaml index 8bc05bb9765..a34d39fa51e 100644 --- a/ydb/docs/en/core/yql/reference/syntax/create_table/toc_i.yaml +++ b/ydb/docs/en/core/yql/reference/syntax/create_table/toc_i.yaml @@ -4,4 +4,5 @@ items: - { name: VECTOR INDEX, href: vector_index.md } - { name: FAMILY, href: family.md } - { name: TEMPORARY, href: temporary.md } -- { name: WITH, href: with.md } \ No newline at end of file +- { name: WITH, href: with.md } +- { name: AS SELECT, href: as_select.md } \ No newline at end of file -- cgit v1.3