CREATE READABLE EXTERNAL TABLE

Содержание раздела
  1. Синтаксис
    1. Ключевое слово IF NOT EXISTS
    2. Ключевое слово PRIMARY KEY
    3. Ключевое слово DISTRIBUTED BY
    4. Ключевое слово LOCATION
    5. Ключевое слово FORMAT
    6. Ключевое слово OPTIONS
  2. Варианты ответа
  3. Ограничения
  4. Примеры
    1. Создание таблицы для чтения из standalone-таблицы
    2. Создание таблицы для загрузки из топика Kafka

Поддерживается в версиях:  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.

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

Запросом можно создать внешнюю readable-таблицу для чтения и выгрузки данных из standalone-таблицы или для загрузки данных из топика Kafka. Назначение таблицы определяется ключевым словом LOCATION, которое задает путь к standalone-таблице или топику соответственно.

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

Изменение внешней таблицы недоступно. Чтобы заменить таблицу, удалите ее и создайте новую.

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

Синтаксис 

CREATE READABLE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]ext_table_name (
  column_name_1 datatype_1 [NULL | NOT NULL],
  column_name_2 datatype_2 [NULL | NOT NULL],
  column_name_3 datatype_3 [NULL | NOT NULL],
  [PRIMARY KEY (column_list_1)]
) 
[DISTRIBUTED BY (column_list_2)]
LOCATION 'core:datasource_alias://path_to_table | source_URI'
[FORMAT 'AVRO']
[OPTIONS ('option_list')]

Параметры:

db_name

Имя логической базы данных, в которой создается внешняя таблица. Опционально, если выбрана логическая БД, используемая по умолчанию.

ext_table_name

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

Чтобы быстро различать разные типы внешних таблиц между собой, рекомендуется давать им имена, указывающие на тип таблицы, например agreements_ext_read или agreements_ext_read_adp. Типы writable- и readable-таблиц можно проверить в системном представлении tables.

column_name_N

Имя столбца таблицы. Должно удовлетворять условиям, перечисленным в секции Ограничения.

Если таблицу планируется использовать для загрузки данных из Kafka в логическую таблицу, в качестве последнего столбца укажите столбец sys_op с типом BIGINT. Этот столбец нужен в служебных целях; без него данные не загрузятся.

datatype_N

Тип данных столбца column_name_N. Возможные значения см. в разделе Логические типы данных.

column_list_1

Список столбцов, входящих в первичный ключ таблицы. Опционален, если связанная standalone-таблица будет расположена в СУБД ADB или ADP (эти СУБД поддерживают таблицы без первичного ключа), или уже существует.

column_list_2

Список столбцов, входящих в ключ шардирования таблицы. Опционален, если связанная standalone-таблица будет расположена в СУБД ADB или ADP (эти СУБД поддерживают таблицы без ключа шардирования), или уже существует.

datasource_alias

Имя датасорса, в котором уже размещена или нужно разместить standalone-таблицу, связанную с внешней таблицей.

path_to_table

Путь к связанной standalone-таблице. Обязательный параметр, если внешняя readable-таблица предназначена для чтения и выгрузки данных из standalone-таблицы.

Путь состоит из имени схемы (если применимо для СУБД) и имени таблицы, указанных через точку. Примеры:

  • dtm__marketing.agreements — ADQM,
  • marketing.agreements — ADP, ADB,
  • dtm__marketing__agreements — ADG.
source_URI

Путь к топику Kafka. Обязательный параметр, если внешняя readable-таблица предназначена для загрузки данных из топика Kafka.

option_list

Список дополнительных параметров и их значений в формате option1=value1;option2=value2....

Параметры создания standalone-таблицы (используются, если таблица предназначена для чтения и выгрузки данных из standandalone-таблицы):

  • auto.create.table.enable — признак автоматического создания связанной standalone-таблицы при создании внешней readable-таблицы. Возможные значения:
    • false (по умолчанию) — не создавать standalone-таблицу,
    • true — создать standalone-таблицу.

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

  • kafka.consumer.group.name — имя консьюмер-группы, загружающей данные;
  • kafka.poll.duration.ms — максимальное время одного прохода вычитки из топика в миллисекундах. Коннектор делает нужное количество таких проходов, чтобы вычитать все новые для консьюмер-группы данные до максимального офсета, который был записан в топике на момент получения коннектором команды на загрузку данных;
  • kafka.poll.buffer.size — максимальный размер буфера прочитанных, но еще не загруженных в таблицу сообщений. Когда буфер достигает заданного максимального размера, коннектор приостанавливает вычитку из топика и ожидает освобождения буфера;
  • db.records.buffer.size — максимальное количество строк, загружаемых в таблицу за один проход.

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

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

Ключевое слово PRIMARY KEY

Ключевое слово PRIMARY KEY задает список столбцов, входящих в первичный ключ таблицы. Ключевое слово опционально, если связанная standalone-таблица будет расположена в СУБД ADB или ADP (эти СУБД поддерживают таблицы без первичного ключа), или уже существует.

См. также параметр column_list_1.

Ключевое слово DISTRIBUTED BY

Ключевое слово DISTRIBUTED BY задает список столбцов, входящих в ключ шардирования таблицы. Ключевое слово опционально, если связанная standalone-таблица будет расположена в СУБД ADB или ADP (эти СУБД поддерживают таблицы без ключа шардирования), или уже существует.

См. также параметр column_list_2.

Ключевое слово LOCATION

Ключевое слово LOCATION задает путь к standalone-таблице или топику Kafka. См. также параметр path_to_table.

Ключевое слово FORMAT

Ключевое слово FORMAT задает формат загрузки данных из топика Kafka. Должно быть указано, если создаваемая таблица нужна для загрузки данных из топика, а не для чтения и выгрузки данных из standalone-таблицы. Возможное значение — 'AVRO'.

Ключевое слово OPTIONS

Ключевое слово OPTIONS задает список дополнительных параметров и их значений. См. также параметр option_list.

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

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

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

Ограничения

  • Имена таблицы и ее столбцов должны соответствовать требованиям, описанным в разделе Соглашения об именах.
  • Имена и порядок столбцов должны совпадать во внешней таблице и связанной standalone-таблице.
  • В readable-таблице, предназначенной для загрузки данных из Kafka в логическую таблицу, последним должен быть указан столбец sys_op с типом BIGINT. Столбец используется в служебных целях; без него данные не загрузятся.
  • Информационная схема обновляется асинхронно, поэтому созданная таблица может не сразу появиться в информационной схеме.

Примеры

Создание таблицы для чтения из standalone-таблицы

Создание readable-таблицы с ключами и параметрами:

CREATE READABLE EXTERNAL TABLE marketing.agreements_ext_read_adp (
  id BIGINT NOT NULL,
  client_id BIGINT NOT NULL,
  number VARCHAR NOT NULL,
  signature_date DATE,
  effective_date DATE,
  closing_date DATE,
  description VARCHAR,
  PRIMARY KEY(id)
)
DISTRIBUTED BY (id)
LOCATION 'core:adp://marketing.agreements'
OPTIONS ('auto.create.table.enable=true')

Создание readable-таблицы без ключей и параметров:

CREATE READABLE EXTERNAL TABLE marketing.payments_ext_read_adqm (
  id BIGINT NOT NULL,
  agreement_id BIGINT,
  code VARCHAR(16),
  amount DOUBLE,
  currency_code VARCHAR(3),
  description VARCHAR
)
LOCATION 'core:adqm://dtm__marketing.payments'

Создание таблицы для загрузки из топика Kafka 

CREATE READABLE EXTERNAL TABLE marketing.sales_ext_upload_through_ret (
  id BIGINT,
  transaction_date TIMESTAMP,
  product_code VARCHAR(256),
  product_units BIGINT,
  store_id BIGINT,
  description VARCHAR(256),
  sys_op INTEGER,
  PRIMARY KEY(id)
)
DISTRIBUTED BY (id)
LOCATION  'kafka://$kafka/sales_upload_through_ret'
FORMAT 'AVRO'
OPTIONS ('
    kafka.consumer.group.name=consumername1;
    kafka.poll.duration.ms=200;
    kafka.poll.buffer.size=2000;
    db.records.buffer.size=2000;
')