CREATE READABLE EXTERNAL TABLE

Содержание раздела
  1. Синтаксис
    1. Ключевое слово IF NOT EXISTS
    2. Ключевое слово PRIMARY KEY (column_name[, ... ])
    3. Ключевое слово DISTRIBUTED BY (column_name[, ... ])
    4. Ключевое слово LOCATION 'core:datasource_name://path_to_table | source_URI'
    5. Ключевое слово FORMAT 'AVRO'
    6. Ключевое слово OPTIONS ('option=value[; ... ]')
  2. Варианты ответа
  3. Ограничения
  4. Примеры
    1. Создание таблицы для чтения из standalone-таблицы
    2. Создание таблицы для загрузки из топика Kafka

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

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

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

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

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

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

Синтаксис 

CREATE READABLE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]ext_table_name (
  column_name data_type [NULL | NOT NULL],
  [ ... ,]
  [PRIMARY KEY (column_name[, ... ])]
) 
[DISTRIBUTED BY (column_name[, ... ])]
LOCATION 'core:datasource_name://path_to_table | source_URI'
[FORMAT 'AVRO']
[OPTIONS ('option=value[; ... ]')]

Параметры:

db_name

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

ext_table_name

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

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

column_name

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

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

data_type

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

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

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

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

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

Ключевое слово опционально, если связанная standalone-таблица уже существует или будет расположена в датасорсе типа ADB или ADP.

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

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

Ключевое слово опционально, если связанная standalone-таблица уже существует или будет расположена в датасорсе типа ADB или ADP.

Ключевое слово LOCATION 'core:datasource_name://path_to_table | source_URI'

Задает путь к standalone-таблице (core:datasource_name://path_to_table) или топику Kafka (source_URI), где:

  • datasource_name — имя датасорса, в котором размещена или будет размещена standalone-таблица, связанная с внешней таблицей. Имя должно быть указано в соответствии с конфигурацией;
  • path_to_table — путь к standalone-таблице, состоящий из имени схемы (если применимо для СУБД) и имени таблицы.
    Примеры пути к таблице agreements в разных СУБД:
    • ADQM: dtm__marketing.agreements,
    • ADP, ADB: marketing.agreements,
    • ADG: dtm__marketing__agreements;
  • source_URI — путь к топику Kafka.

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

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

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

Задает список дополнительных параметров (option) и их значений (value).

Возможные параметры и их значения:

  • параметры создания standalone-таблицы (предназначены для readable-таблицы, используемой для чтения и выгрузки данных из standalone-таблицы):
    • auto.create.table.enable — признак автоматического создания standalone-таблицы при создании внешней readable-таблицы. Возможные значения:
      • false (по умолчанию) — не создавать standalone-таблицу,
      • true — создать standalone-таблицу.
  • параметры загрузки данных из топика Kafka, переопределяющие конфигурацию (предназначены для readable-таблицы, используемой для загрузки данных из топика Kafka):
    • kafka.consumer.group.name — имя консьюмер-группы, загружающей данные;
    • kafka.poll.duration.ms — максимальное время одного прохода вычитки из топика в миллисекундах. Коннектор делает нужное количество таких проходов, чтобы вычитать все новые для консьюмер-группы данные до максимального офсета, который был записан в топике на момент получения коннектором команды на загрузку данных;
    • kafka.poll.buffer.size — максимальный размер буфера прочитанных, но еще не загруженных в таблицу сообщений. Когда буфер достигает заданного максимального размера, коннектор приостанавливает вычитку из топика и ожидает освобождения буфера;
    • db.records.buffer.size — максимальное количество строк, загружаемых в таблицу за один проход.

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

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

  • пустой объект 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;
')