CREATE TABLE

Содержание раздела

Как работает запрос
Синтаксис
Варианты ответа
Ограничения
Примеры

Поддерживается в версиях: 7.2 / 7.1 / 7.0 / 6.12 / 6.11 / 6.10 / 6.9 / 6.8 / 6.7 / 6.6 / 6.5 / 6.4 / 6.3 / 6.2 / 6.1 / 6.0 / 5.8 / 5.7 / 5.6 / 5.5 / 5.4 / 5.3 / 5.2 / 5.1 / 5.0.

Запрос создает логическую таблицу или прокси-таблицу в логической базе данных.

Если в датасорсе есть физическая таблица с таким же именем, как у создаваемой прокси-таблицы, она будет удалена при создании прокси-таблицы.

Как работает запрос

Запрос попадает в очередь операций и обрабатывается в порядке его поступления в очередь. Каждое создание таблицы записывается в журнал, который можно посмотреть с помощью запроса GET_CHANGES.

При успешном выполнении запроса система создает таблицу в логической базе данных, соответствующие ей физические таблицы в датасорсах хранилища данных, а также tslog таблицы в сервисной базе данных.

При ошибке исполнения корректного запроса система блокирует все последующие DDL-запросы в логической БД. О снятии такой блокировки см. в разделе Снятие блокировки DDL-запросов.

Синтаксис

Обычная логическая таблица (таблица, не участвующая в партиционировании данных):

CREATE TABLE [ IF NOT EXISTS ] [db_name.]table_name (
  column_name data_type [ NULL | NOT NULL ],
  [ ... , ]
  PRIMARY KEY (column_name[, ... ] )
) 
[ DISTRIBUTED BY (column_name[, ... ]) ]
[ DATASOURCE_TYPE ('destination_datasource'[, ... ]) ]
[ RETENTION ('source_datasource', retention_period[, 'archive_datasource']) ]
[ ... ]
[ LOGICAL_ONLY ]

Партиционированная таблица:

CREATE TABLE [ IF NOT EXISTS ] [db_name.]table_name (
  column_name data_type [ NULL | NOT NULL ],
  [ ... , ]
PRIMARY KEY (column_name[, ... ])
)
[ DISTRIBUTED BY (column_name[, ... ]) ]
[ DATASOURCE_TYPE ('destination_datasource') ]
PARTITION BY (column_name[, ... ] )
[ LOGICAL_ONLY ]

Партиция:

CREATE TABLE [ IF NOT EXISTS ] [db_name.]table_name
PARTITION OF [db_name.]partitioned_table_name
[ DISTRIBUTED BY (column_name[, ... ]) ]
[ DATASOURCE_TYPE ('destination_datasource'[, ... ]) ]
[ RETENTION ('source_datasource', retention_period[, 'archive_datasource']) ]
[ ... ]
FOR VALUES FROM (left_end_of_range) TO (right_end_of_range)
[ ... ]
[ LOGICAL_ONLY ]

Прокси-таблица (альтернативно можно создать запросом CREATE PROXY TABLE):

CREATE TABLE [ IF NOT EXISTS ] [db_name.]table_name (
  column_name data_type [ NULL | NOT NULL ],
  [ ... , ]
[ PRIMARY KEY (column_name[, ... ]) ]
)
[ DISTRIBUTED BY (column_name[, ... ]) ]
[ DATASOURCE_TYPE ('destination_datasource'[, ... ]) ]
OPTIONS ('
  auto.version.records.enable=false;
  [set.entity.ttl.ms=ttl_value;]
  ')
[ LOGICAL_ONLY ]

Параметры:

db_name: Имя логической базы данных, в которой создается таблица. Опционально, если выбрана логическая БД, используемая по умолчанию.
table_name: Имя создаваемой логической таблицы или прокси-таблицы, уникальное среди логических сущностей логической БД.
column_name: Имя столбца таблицы.
data_type: Тип данных столбца column_name. Возможные значения см. в разделе Логические типы данных.
partitioned_table_name: Имя партиционированной таблицы, к которой относится создаваемая партиция.

Ключевое слово `IF NOT EXISTS`

Включает проверку наличия логической таблицы до попытки создания. Если ключевое слово указано в запросе, успешный ответ возвращается при наличии или успешном создании логической таблицы, иначе — только при успешном создании логической таблицы.

Ключевое слово `PRIMARY KEY (column_name[, ... ])`

Задает список столбцов, входящих в первичный ключ таблицы. Обязательно для сущностей:

обычных логических таблиц,
партиционированных таблиц,
прокси-таблиц, размещенных в датасорсах типа ADQM и ADG.

Ключевое слово `DISTRIBUTED BY (column_name[, ... ])`

Задает список столбцов, входящих в ключ шардирования таблицы. Обязательно для датасорсов типа ADQM и ADG, опционально — для ADP и ADB.

Если указано, все столбцы ключа шардирования должны присутствовать в первичном ключе.

Ключевое слово `PARTITION BY (column_name[, ... ])`

Задает список столбцов, входящих в ключ партиционирования таблицы, и используется только для партиционированных таблиц.

Все указываемые столбцы должны присутствовать в первичном ключе. В состав ключа партиционирования не могут входить столбцы типа DOUBLE и FLOAT.

Ключевое слово `DATASOURCE_TYPE ('destination_datasource'[, ... ])`

Задает имена датасорсов, в которых будут размещаться данные обычной логической таблицы, партиции или прокси-таблицы. Для партиционированной таблицы задает имя датасорса-буфера, куда будут загружаться и вставляться данные перед их распределением по партициям. Имена датасорсов должны соответствовать конфигурации.

Ключевое слово обязательно:

для партиционированных таблиц — если хранилище данных содержит более одного датасорса;
для партиций — если хранилище данных содержит датасорсы типа ADB, ADQM и (или) ADG.

Если ключевое слово не указано:

данные обычных логических таблиц и партиций размещаются во всех датасорсах хранилища;
данные прокси-таблицы размещаются в случайном или псевдослучайном датасорсе хранилища;
для партиционированной таблицы используется единственный датасорс хранилища (если он такой один) или выдается ошибка (иначе).

Ключевое слово `RETENTION ('source_datasource', retention_period[, 'archive_datasource'])`

Задает retention-правило для логической таблицы, где:

source_datasource — имя датасорса-источника, из которого перемещаются или удаляются неактуальные версии данных;
retention_period — срок хранения неактуальных версий данных (в секундах) в датасорсе-источнике. При значении 0 в датасорсе-источнике хранятся только актуальные версии данных, а все неактуальные версии данных перемещаются в хранилище холодных данных или удаляются при каждой проверке retention-правила;
archive_datasource — имя датасорса-приемника, куда перемещаются неактуальные версии данных по истечении срока их хранения. Если значение не указано, неактуальные версии данных по истечении срока хранения удаляются, а не перемещаются в хранилище холодных данных.

Для каждого датасорса, где размещены данные логической таблицы, можно задать свое retention-правило. Подробнее о retention-правилах см. в разделе Retention-правило.

Если ключевое слово не указано, неактуальные версии данных бессрочно хранятся вместе с актуальными версиями данных.

Ключевое слово `FOR VALUES FROM (left_end_of_range) TO (right_end_of_range)`

Задает диапазон значений ключа партиционирования и используется только для партиций, где:

left_end_of_range — левая граница диапазона партиционирования (граница включается в диапазон);
right_end_of_range — правая граница диапазона партиционирования (граница включается в диапазон).

Диапазоны должны быть указаны так:

левая граница диапазона меньше или равна его правой границе;
диапазоны партиции не пересекаются;
диапазоны партиций, относящихся к одной партиционированной таблице, не пересекаются и включают все возможные значения ключа партиционирования.

Значения диапазона партиционирования могут иметь любой из следующих типов (описание типов см. в разделе Логические типы данных):

BOOLEAN,
VARCHAR[(n)],
LINK,
CHAR[(n)],
UUID,
BIGINT,
INTEGER,
DATE,
TIME,
TIMESTAMP.

Ключевое слово `OPTIONS ('option=value[; ... ]')`

Задает дополнительные параметры (option) и их значения (value). Доступные параметры:

auto.version.records.enable — признак версионирования данных в таблице. Возможные значения:
- false — таблица без версионирования данных (прокси-таблица),
- true (по умолчанию) — таблица с версионированием данных (логическая таблица);
set.entity.ttl.ms — срок жизни временной прокси-таблицы в миллисекундах. Если не задан, таблица считается постоянной.

Ключевое слово `LOGICAL_ONLY`

Позволяет создать таблицу только на логическом уровне (в логической схеме данных), не обновляя физическую схему в хранилище данных.

Данные логической таблицы становятся недоступны при ее удалении и пересоздании с ключевым словом LOGICAL_ONLY. Вернуть доступ можно только к данным актуальной версии, выполнив операцию записи в пересозданной таблице.

Если ключевое слово не указано, таблица создается на логическом и физическом уровнях.

Варианты ответа

В ответе возвращается:

пустой объект ResultSet при успешном выполнении запроса;
исключение при неуспешном выполнении запроса.

Ограничения

Ограничения выполнения

Выполнение запроса к логической таблице недоступно, если она участвует в незавершенной операции записи.
Выполнение запроса недоступно, если установлен запрет на изменение сущностей.

Ограничения имен

Имена таблицы и ее столбцов должны соответствовать требованиям, описанным в разделе Соглашения об именах.
Имена столбцов должны быть уникальны в рамках таблицы.

Ограничения ключей

Первичный ключ должен включать все столбцы ключа шардирования и все столбцы ключа партиционирования.

Ограничения ключевых слов

Ключевое слово RETENTION доступно только для СУБД ADB и ADP.
Выражение с ключевым словом RETENTION не может содержать датасорс-источник и датасорс-приемник разных типов.

Ограничения партиционирования

Создание партиционированных таблиц и партиций доступно только в СУБД ADP.
Вложенное партиционирование недоступно: партиция не может быть иметь свои партиции.
Диапазоны партиционирования:
- Диапазоны партиции должны быть заданы от меньшего значения к большему без пересечения диапазонов между собой.
- Диапазоны партиций, относящихся к одной партиционированной таблице, должны включать все возможные значения ключа партиционирования без пересечения диапазонов между собой.
Партиционированная таблица и все ее партиции должны находиться в одной логической БД и иметь одинаковую структуру.
Для партиционированной таблицы недоступны retention-правила.

Другие ограничения

Информационная схема обновляется асинхронно, поэтому созданная таблица может не сразу появиться в информационной схеме.
HTTP-запросы с одинаковым значением queryId и одинаковым списком датасорсов в DATASOURCE_TYPE/конфигурации создают прокси-таблицы в одном датасорсе, только если он активен при исполнении всех таких запросов.

Примеры

Логическая таблица с размещением данных во всех датасорсах

CREATE TABLE marketing.sales (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)

Логическая таблица с составным первичным ключом

CREATE TABLE marketing.stores (
  id BIGINT NOT NULL,
  category VARCHAR(256) NOT NULL,
  region VARCHAR(256) NOT NULL,
  address VARCHAR(256) NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id, region)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp', 'adb', 'adqm')

Логическая таблица с размещением данных в двух датасорсах

CREATE TABLE marketing.clients (
  id BIGINT NOT NULL,
  first_name VARCHAR(256) NOT NULL,
  last_name VARCHAR(256) NOT NULL,
  patronymic_name VARCHAR(256),
  birth_date DATE,
  PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp','adqm')

Логическая таблица, созданная только на логическом уровне

CREATE TABLE marketing.sales1 (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256) NOT NULL,
  product_units BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
LOGICAL_ONLY

Логические таблицы с retention-правилами

Таблица с периодическим остужением и удалением данных из двух датасорсов:

-- создание логической таблицы clients_all_adp с размещением в датасорсах adp1 и adp2,
-- откуда, в зависимости от датасорса, 
-- неактуальные версии данных старше месяца будут перемещаться в хранилище холодных данных (adp1 -> adp_archive) или удаляться (adp2)
CREATE TABLE marketing.clients_all_adp (
id BIGINT NOT NULL,
first_name VARCHAR(256) NOT NULL,
last_name VARCHAR(256) NOT NULL,
patronymic_name VARCHAR(256),
birth_date DATE,
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp1', 'adp2')
RETENTION ('adp1', 2678400, 'adp_archive')
RETENTION ('adp2', 2678400)

Таблица с периодическим остужением данных одного датасорса:

-- создание логической таблицы clients_adp1 с размещением данных в датасорсе adp1, 
-- откуда неактуальные версии данных старше недели будут перемещаться в хранилище холодных данных
CREATE TABLE marketing.clients_adp1 (
id BIGINT NOT NULL,
first_name VARCHAR(256) NOT NULL,
last_name VARCHAR(256) NOT NULL,
patronymic_name VARCHAR(256),
birth_date DATE,
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp1')
RETENTION ('adp1', 604800, 'adp_archive')

Таблица с периодическим удалением данных из одного датасорса:

-- создание логической таблицы clients_adp2 с размещением данных в датасорсе adp2, 
-- откуда неактуальные версии данных старше недели будут удаляться
CREATE TABLE marketing.clients_adp2 (
id BIGINT NOT NULL,
first_name VARCHAR(256) NOT NULL,
last_name VARCHAR(256) NOT NULL,
patronymic_name VARCHAR(256),
birth_date DATE,
PRIMARY KEY (id)
) DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp2')
RETENTION ('adp2', 604800)

Партиционированные таблицы и партиции с распределением данных по регионам

Таблицы с партиционированием данных по кодам регионов:

-- таблица с данными магазинов, партиционированными по регионам
CREATE TABLE marketing.stores_partitioned_by_regions (
  id BIGINT NOT NULL,
  category VARCHAR(256),
  region_code INTEGER NOT NULL,
  address VARCHAR(256) NOT NULL,
  rent_agreement_id BIGINT NOT NULL,
  description VARCHAR(256),
  PRIMARY KEY (id, region_code)
)
DATASOURCE_TYPE ('adp_main')
PARTITION BY (region_code);

-- таблица с данными арендных договоров магазинов, партиционированными по регионам
CREATE TABLE marketing.rent_agreements_partitioned_by_regions (
  id BIGINT NOT NULL,
  store_id BIGINT NOT NULL,
  number VARCHAR NOT NULL,
  signature_date DATE,
  effective_date DATE,
  closing_date DATE,
  region_code INTEGER NOT NULL,
  description VARCHAR,
  PRIMARY KEY(id, region_code)
)
DATASOURCE_TYPE ('adp')
PARTITION BY (region_code);

Партиции для распределения данных магазинов по регионам:

-- партиция для записей о магазинах Москвы
CREATE TABLE marketing.stores_moscow 
PARTITION OF marketing.stores_partitioned_by_regions
DATASOURCE_TYPE ('adp_moscow')
RETENTION ('adp_moscow', 2678400, 'adp_archive')
FOR VALUES FROM (77) TO (77)
FOR VALUES FROM (177) TO (177)
FOR VALUES FROM (97) TO (97)
FOR VALUES FROM (197) TO (197)
FOR VALUES FROM (99) TO (99)
FOR VALUES FROM (199) TO (199)
FOR VALUES FROM (777) TO (777);

-- партиция для записей о магазинах Санкт-Петербурга
CREATE TABLE marketing.stores_spb 
PARTITION OF marketing.stores_partitioned_by_regions
DATASOURCE_TYPE ('adp_spb')
RETENTION ('adp_spb', 2678400, 'adp_archive')
FOR VALUES FROM (78) TO (78)
FOR VALUES FROM (98) TO (98)
FOR VALUES FROM (178) TO (178);

-- партиция для записей о магазинах Новосибирска
CREATE TABLE marketing.stores_novosibirsk
PARTITION OF marketing.stores_partitioned_by_regions
DATASOURCE_TYPE ('adp_novosibirsk')
RETENTION ('adp_novosibirsk', 2678400, 'adp_archive')
FOR VALUES FROM (54) TO (54)
FOR VALUES FROM (154) TO (154);

... -- партиции для других регионов

Партиции для распределения данных договоров аренды по регионам:

-- партиция для записей об арендных договорах в Москве
CREATE TABLE marketing.rent_agreements_moscow 
PARTITION OF marketing.rent_agreements_partitioned_by_regions
DATASOURCE_TYPE ('adp_moscow')
RETENTION ('adp_moscow', 2678400, 'adp_archive')
FOR VALUES FROM (77) TO (77)
FOR VALUES FROM (177) TO (177)
FOR VALUES FROM (97) TO (97)
FOR VALUES FROM (197) TO (197)
FOR VALUES FROM (99) TO (99)
FOR VALUES FROM (199) TO (199)
FOR VALUES FROM (777) TO (777);

... -- партиции для других регионов

Партиционированная таблица и партиции с распределением данных по периодам времени

Таблица с партиционированием данных по дате и времени платежной транзакции:

CREATE TABLE marketing.sales_partitioned (
  id BIGINT NOT NULL,
  transaction_date TIMESTAMP NOT NULL,
  product_code VARCHAR(256),
  product_units BIGINT,
  store_id BIGINT,
  description VARCHAR(256),
  PRIMARY KEY (id, transaction_date)
)
DATASOURCE_TYPE ('adp_main')
PARTITION BY (transaction_date)

Партиции для распределения данных транзакций по месяцам:

-- партиция для записей о транзакциях за январь 2023 года
CREATE TABLE marketing.sales_jan_2023 
PARTITION OF marketing.sales_partitioned
DATASOURCE_TYPE ('adp1')
FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-01-31 23:59:59');

-- партиция для записей о транзакциях за февраль 2023 года
CREATE TABLE marketing.sales_feb_2023 
PARTITION OF marketing.sales_partitioned
DATASOURCE_TYPE ('adp2')
FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-02-28 23:59:59');

... -- партиции для записей за другие месяцы

Постоянная прокси-таблица в указанном датасорсе

CREATE TABLE IF NOT EXISTS marketing.payments_proxy_in_specified_ds (
  id BIGINT NOT NULL,
  agreement_id BIGINT,
  type_code VARCHAR(16),
  amount DOUBLE,
  currency_code VARCHAR(3),
  description VARCHAR,
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp')
OPTIONS ('auto.version.records.enable=false;')

Временная прокси-таблица в случайном датасорсе из указанных

Временная прокси-таблица с первичным ключом и ключом шардирования и размещением в случайном активном датасорсе из указанных в DATASOURCE_TYPE (см. полный HTTP-запрос ниже):

CREATE TABLE IF NOT EXISTS marketing.payments_proxy_temp_in_any_listed_ds (
  id BIGINT NOT NULL,
  agreement_id BIGINT,
  type_code VARCHAR(16),
  amount DOUBLE,
  currency_code VARCHAR(3),
  description VARCHAR,
  PRIMARY KEY (id)
)
DISTRIBUTED BY (id)
DATASOURCE_TYPE ('adp', 'adp2')
OPTIONS ('
  auto.version.records.enable=false;
  set.entity.ttl.ms=120000;
  ')

HTTP-запрос для создания прокси-таблицы payments_proxy_temp_in_any_listed_ds

В примере ниже показан HTTP-запрос через конечную точку /query. Альтернативно запрос можно отправить через конечную точку /ddl или по JDBC.

curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/query' \
  -H 'x-request-id: 18ab9a21-fe66-4cdb-8bfd-e43cad2e8d96' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "CREATE TABLE IF NOT EXISTS marketing.payments_proxy_temp_in_any_listed_ds (id BIGINT NOT NULL, agreement_id BIGINT, type_code VARCHAR(16), amount DOUBLE, currency_code VARCHAR(3), description VARCHAR, PRIMARY KEY (id)) DISTRIBUTED BY (id) DATASOURCE_TYPE ('adp', 'adp2') OPTIONS ('auto.version.records.enable=false; set.entity.ttl.ms=120000;')"
}'

Временная прокси-таблица в псевдослучайном датасорсе из сконфигурированных

Временная прокси-таблица без первичного ключа и ключа шардирования с размещением в псевдослучайном активном датасорсе из сконфигурированных (без DATASOURCE_TYPE, но с queryId; см. полный HTTP-запрос ниже):

CREATE TABLE IF NOT EXISTS marketing.payments_proxy_temp_in_seed_configured_ds (
  id BIGINT NOT NULL,
  agreement_id BIGINT,
  type_code VARCHAR(16),
  amount DOUBLE,
  currency_code VARCHAR(3),
  description VARCHAR
)
OPTIONS ('
  auto.version.records.enable=false;
  set.entity.ttl.ms=120000;
  ')

HTTP-запрос для создания прокси-таблицы payments_proxy_temp_in_seed_configured_ds

В примере ниже показан HTTP-запрос через конечную точку /ddl. Альтернативно запрос можно отправить через конечную точку /query.

curl -X 'POST' \
  'http://localhost:9090/api/v1/datamarts/ddl' \
  -H 'x-request-id: a9a8300e-32fc-4b02-a10b-6d7d553985ad' \
  -H 'Content-Type: application/json' \
  -d '{
  "query": "CREATE TABLE IF NOT EXISTS marketing.payments_proxy_temp_in_seed_configured_ds (id BIGINT NOT NULL, agreement_id BIGINT, type_code VARCHAR(16), amount DOUBLE, currency_code VARCHAR(3), description VARCHAR) OPTIONS ('auto.version.records.enable=false; set.entity.ttl.ms=120000;')",
  "queryId": "28258592"
}'