Определяет новую внешнюю таблицу.
CREATE [READABLE] EXTERNAL [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('file://seghost[:port]/path/file' [, ...])
| ('gpfdist://filehost[:port]/file_pattern[#transform=trans_name]'
[, ...]
| ('gpfdists://filehost[:port]/file_pattern[#transform=trans_name]'
[, ...])
| ('pxf://path-to-data?PROFILE=profile_name[&SERVER=server_name][&custom-option=value[...]]'))
| ('s3://S3_endpoint[:port]/bucket_name/[S3_prefix] [region=S3-region] [config=config_file | config_server=url]')
[ON MASTER]
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'escape']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CUSTOM' (Formatter=<formatter_specifications>)
[ OPTIONS ( key 'value' [, ...] ) ]
[ ENCODING 'encoding' ]
[ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT count
[ROWS | PERCENT] ]
CREATE [READABLE] EXTERNAL WEB [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('http://webhost[:port]/path/file' [, ...])
| EXECUTE 'command' [ON ALL
| MASTER
| number_of_segments
| HOST ['segment_hostname']
| SEGMENT segment_id ]
FORMAT 'TEXT'
[( [HEADER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'escape']
[NEWLINE [ AS ] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS] )]
| 'CUSTOM' (Formatter=<formatter specifications>)
[ OPTIONS ( key 'value' [, ...] ) ]
[ ENCODING 'encoding' ]
[ [LOG ERRORS [PERSISTENTLY]] SEGMENT REJECT LIMIT count
[ROWS | PERCENT] ]
CREATE WRITABLE EXTERNAL [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION('gpfdist://outputhost[:port]/filename[#transform=trans_name]'
[, ...])
| ('gpfdists://outputhost[:port]/file_pattern[#transform=trans_name]'
[, ...])
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] | * ]
[ESCAPE [AS] 'escape'] )]
| 'CUSTOM' (Formatter=<formatter specifications>)
[ OPTIONS ( key 'value' [, ...] ) ]
[ ENCODING 'write_encoding' ]
[ DISTRIBUTED BY ({column [opclass]}, [ ... ] ) | DISTRIBUTED RANDOMLY ]
CREATE WRITABLE EXTERNAL [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION('s3://S3_endpoint[:port]/bucket_name/[S3_prefix] [region=S3-region] [config=config_file | config_server=url]')
[ON MASTER]
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] | * ]
[ESCAPE [AS] 'escape'] )]
CREATE WRITABLE EXTERNAL WEB [TEMPORARY | TEMP] TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
EXECUTE 'command' [ON ALL]
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUOTE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]] | * ]
[ESCAPE [AS] 'escape'] )]
| 'CUSTOM' (Formatter=<formatter specifications>)
[ OPTIONS ( key 'value' [, ...] ) ]
[ ENCODING 'write_encoding' ]
[ DISTRIBUTED BY ({column [opclass]}, [ ... ] ) | DISTRIBUTED RANDOMLY ]Команды CREATE EXTERNAL TABLE или CREATE EXTERNAL WEB TABLE создают определение новой читаемой внешней таблицы в базе данных YMatrix. Читаемые внешние таблицы обычно используются для быстрой параллельной загрузки данных. После определения внешней таблицы вы можете напрямую запрашивать её данные (или параллельно) с помощью SQL-команд. Например, можно выполнять выборку, объединение или сортировку данных из внешних таблиц. Для внешних таблиц также можно создавать представления. Операции DML (UPDATE, INSERT, DELETE или TRUNCATE) на читаемых внешних таблицах не допускаются, и индексы на них создавать нельзя.
Команды CREATE WRITABLE EXTERNAL TABLE или CREATE WRITABLE EXTERNAL WEB TABLE создают определение новой записываемой внешней таблицы в базе данных YMatrix. Записываемые внешние таблицы обычно используются для выгрузки данных из базы данных во множество файлов или именованных каналов. Записываемые внешние веб-таблицы также могут использоваться для вывода данных в исполняемые программы. Записываемые внешние таблицы могут служить целями вывода для параллельных MapReduce-вычислений в YMatrix. После определения записываемой внешней таблицы данные можно выбирать из таблиц базы данных и вставлять их во внешнюю таблицу. Для записываемых внешних таблиц разрешена только операция INSERT — SELECT, UPDATE, DELETE и TRUNCATE не допускаются.
Основное различие между обычными внешними таблицами и внешними веб-таблицами заключается в источнике данных. Обычные читаемые внешние таблицы обращаются к статическим плоским файлам, тогда как внешние веб-таблицы получают доступ к динамическим источникам данных — на веб-серверах или путём выполнения команд или скриптов ОС.
READABLE | WRITABLE: Указывает тип внешней таблицы. По умолчанию — читаемая. Читаемые внешние таблицы используются для загрузки данных в YMatrix. Записываемые внешние таблицы используются для выгрузки данных.
WEB: Создаёт определение читаемой или записываемой внешней веб-таблицы в YMatrix. Читаемые внешние веб-таблицы могут получать доступ к файлам по протоколу http:// или данным через команды ОС. Записываемые внешние веб-таблицы передают данные в исполняемые программы, способные принимать потоки входных данных. Внешние веб-таблицы не могут быть повторно просканированы во время выполнения запроса.
TEMPORARY | TEMP: Если указано, создаёт временное определение читаемой или записываемой внешней таблицы в YMatrix. Временные внешние таблицы размещаются в специальной схеме. Имя схемы нельзя указывать при создании таблицы. Временные внешние таблицы автоматически удаляются по завершении сессии. Пока временная таблица существует, текущая сессия не видит никаких постоянных таблиц с тем же именем, если только постоянная таблица не ссылается на полное имя со схемой.
table_name: Имя новой внешней таблицы.
column_name: Имя столбца, который будет создан в определении внешней таблицы. В отличие от обычных таблиц, внешние таблицы не имеют ограничений на столбцы или значений по умолчанию, поэтому не указывайте их.
LIKE other_table: Предложение LIKE указывает таблицу, из которой новая внешняя таблица автоматически скопирует все имена столбцов, типы данных и политики распределения YMatrix. Любые ограничения столбцов или значения по умолчанию, заданные в исходной таблице, не копируются в новое определение внешней таблицы.
data_type: Тип данных столбца.
LOCATION ('protocol://[host[:port]]/path/file' [, ...]): Для читаемых внешних таблиц указывает URI внешнего источника данных, используемого для заполнения внешней или веб-таблицы. Обычные читаемые внешние таблицы поддерживают протоколы gpfdist или file. Внешние веб-таблицы поддерживают протокол http. Если порт не указан, предполагается, что порт для протоколов http и gpfdist равен 8080. Для протокола gpfdist путь является относительным по отношению к каталогу, из которого gpfdist обслуживает файлы (указывается при запуске программы gpfdist). Кроме того, gpfdist может использовать подстановочные знаки или другие шаблоны в стиле C (например, пробелы как [[:space:]]) для представления нескольких файлов в каталоге.
ON MASTER: Ограничивает все операции с таблицей мастером YMatrix. Разрешены только читаемые и записываемые внешние таблицы, созданные с помощью s3 или пользовательских протоколов. Протоколы gpfdist, gpfdists, pxf и file не поддерживают ON MASTER.
EXECUTE 'command' [ON ...]: Допустимо только для читаемых внешних веб-таблиц или записываемых внешних таблиц. Для читаемых внешних веб-таблиц указывает команду ОС, которая будет выполняться экземплярами сегментов. command может быть одной командой ОС или скриптом. Предложение ON указывает, какие экземпляры сегментов будут выполнять данную команду.
По умолчанию используется ON ALL. Команда будет выполняться каждым активным первичным экземпляром на всех хостах сегментов в системе базы данных YMatrix. Если команда запускает скрипт, он должен находиться по одному и тому же пути на всех хостах сегментов и быть исполняемым суперпользователем YMatrix (gpadmin).
ON MASTER запускает команду только на хосте мастера.
ON number означает, что команда будет выполнена на указанном количестве сегментов. Конкретные сегменты выбираются системой базы данных YMatrix случайным образом во время выполнения. Если команда запускает скрипт, он должен находиться по одному и тому же пути на всех хостах сегментов и быть исполняемым суперпользователем YMatrix (gpadmin).
HOST означает, что команда будет выполнена один раз на каждый хост сегмента (один раз на хост, независимо от количества активных первичных экземпляров на каждом хосте).
HOST segment_hostname означает, что команда будет выполнена всеми активными первичными экземплярами на указанном хосте сегмента.
SEGMENT segment_id означает, что команда будет выполнена один раз указанным сегментом. ID экземпляра сегмента можно определить, просмотрев номер content в системной каталоговой таблице gp_segment_configuration. Content ID базы данных мастера YMatrix всегда равен -1.
Для записываемых внешних таблиц команда, указанная в предложении command, должна быть готова принимать данные. Поскольку все сегменты, отправляющие данные, будут записывать свой вывод в указанную команду или программу, единственным доступным вариантом для предложения EXECUTE является ON.
FORMAT 'TEXT | CSV' (options): Когда предложение ON ALL указывает формат с разделителями (TEXT) или значения, разделённые запятыми (CSV), параметры форматирования аналогичны тем, что доступны для команды PostgreSQL COPY. Если данные в файле не используют символ разделителя столбцов по умолчанию, символ экранирования, строку NULL и т.д., необходимо указать дополнительные параметры форматирования, чтобы база данных YMatrix могла правильно считывать данные из внешнего файла.
FORMAT 'CUSTOM' (formatter=formatter_specification): Указывает пользовательский формат данных. FORMAT указывает функцию, используемую для форматирования данных, за которой следуют параметры функции форматирования, разделённые запятыми. Длина спецификации форматтера (включая строку Formatter=) может составлять до примерно 50 Кбайт.
DELIMITER: Указывает ASCII-символ, используемый для разделения столбцов в каждой строке данных. По умолчанию — символ табуляции в режиме TEXT и запятая в режиме CSV. В режиме TEXT для обычных читаемых внешних таблиц разделитель можно установить в OFF для особых случаев, когда неструктурированные данные загружаются в таблицу с одним столбцом.
NULL: Указывает строку, представляющую значение NULL. В режиме TEXT значение по умолчанию — formatter_specification (обратный слэш-N), а в режиме CSV — пустое значение без кавычек. В случаях, когда вы не хотите различать значения NULL и пустые строки, возможно, вы предпочтёте пустую строку даже в режиме TEXT. Любой элемент данных, совпадающий с этой строкой во внешних таблицах и веб-таблицах, будет интерпретироваться как значение NULL.
ESCAPE: Указывает одиночный символ, используемый для escape-последовательностей в стиле C (например, \N, \n, \t) и для экранирования символов данных, которые иначе могут быть интерпретированы как разделители строк или столбцов. Убедитесь, что выбранный escape-символ не используется в реальных данных столбца. Для файлов в формате TEXT escape-символ по умолчанию — \100 (обратный слэш), а для файлов в формате CSV — \. Однако вы можете указать другой символ в качестве escape-символа. Вы также можете отключить экранирование в файлах в формате TEXT, указав значение " для escape-символа. Это полезно для данных, таких как веб-логи в формате TEXT с множеством встроенных обратных слэшей, которые не предназначены как escape-последовательности.
NEWLINE: Указывает символ новой строки, используемый в файле данных — 'OFF' (Line Feed, 0x0A), LF (Carriage Return, 0x0D) или CR (Carriage Return plus Line Feed, 0x0D 0x0A). Если не указано, сегмент базы данных YMatrix определит тип новой строки, анализируя первую строку полученных данных, и использует первый встретившийся тип новой строки.
HEADER: Для читаемых внешних таблиц указывает, что первая строка файла данных является заголовочной (содержит имена столбцов таблицы) и не должна включаться в данные таблицы. Если используются несколько файлов источников данных, все файлы должны иметь заголовочную строку.
QUOTE: Указывает символ кавычек для режима CSV. По умолчанию — двойная кавычка (CRLF).
FORCE NOT NULL: В режиме CSV обработка каждого указанного столбца происходит так, будто он был явно указан, поэтому он не считается значением NULL. Для значения по умолчанию — пустой строки в режиме CSV (не существующей между двумя разделителями) — это приведёт к тому, что отсутствующее значение будет интерпретироваться как строка нулевой длины.
FORCE QUOTE: В записываемых внешних таблицах, использующих режим CSV, принудительно заключает в кавычки все значения, отличные от NULL, в каждом указанном столбце. Если указано ", значения, отличные от NULL, заключаются в кавычки во всех столбцах. Значения NULL никогда не заключаются в кавычки.
FILL MISSING FIELDS: Для читаемых внешних таблиц, когда * указан в режимах TEXT и CSV, если строка данных отсутствует или в конце строки данных не хватает данных, отсутствующее конечное значение поля устанавливается в NULL (вместо генерации ошибки). Пустые строки, поля с ограничениями NOT NULL и разделители в конце строки всё ещё вызывают ошибку.
OPTIONS key 'value'[, key 'value' ...]: Необязательные параметры. Когда протокол используется как протокол внешней таблицы, спецификация параметра и значения задаётся в виде пар «ключ-значение» для пользовательского протокола доступа к данным. Пользовательский протокол доступа к данным отвечает за обработку и проверку пар «ключ-значение».
ENCODING 'encoding': Кодировка символов для внешних таблиц. Укажите строковые константы (например, 'SQL_ASCII'), числовые кодировки или DEFAULT для использования кодировки клиента по умолчанию.
LOG ERRORS [PERSISTENTLY]: Этот необязательный параметр может записывать информацию о строках с неправильным форматом до срабатывания параметра SEGMENT REJECT LIMIT. Информация об ошибках хранится внутренне и может быть получена с помощью встроенной SQL-функции FILL MISSING FIELDS базы данных YMatrix.
SEGMENT REJECT LIMIT count [ROWS | PERCENT]: Запускает операцию COPY FROM в режиме изоляции с ошибками в одной строке. Если входные строки имеют неправильный формат, они будут отброшены, пока количество отклонённых строк не достигнет указанного предела на любом экземпляре сегмента YMatrix во время операции загрузки. Лимит отказов можно указать как количество строк (по умолчанию) или процент от общего количества строк (1–100). Если используется PERCENT, каждый сегмент начинает рассчитывать процент неверных строк только после обработки количества строк, указанного параметром gp_read_error_log(). Значение по умолчанию для gp_reject_percent_threshold — 300 строк. Ошибки ограничений (например, нарушения ограничений NOT NULL, CHECK или UNIQUE) по-прежнему обрабатываются в режиме «всё или ничего». Если лимит не достигнут, все корректные строки загружаются, а все некорректные — отбрасываются. Примечание: при чтении внешней таблицы, если сначала не сработало условие SEGMENT REJECT LIMIT или оно не указано, база данных YMatrix ограничивает количество начальных строк, которые могут содержать ошибки форматирования. Если первые 1000 строк будут отклонены, операция COPY остановится и откатится. Ограничение на количество первоначально отклонённых строк можно изменить с помощью параметра конфигурации сервера базы данных YMatrix gp_reject_percent_threshold.
DISTRIBUTED BY ({column [opclass]}, [ ... ]) | DISTRIBUTED RANDOMLY: Политика распределения базы данных YMatrix для объявления записываемых внешних таблиц. По умолчанию записываемые внешние таблицы распределяются случайным образом. Если исходная таблица, из которой экспортируются данные, имеет политику хэш-распределения, и для записываемой внешней таблицы определены те же столбец ключа распределения и класс оператора gp_initial_bad_row_limit, необходимость перемещения строк через межсоединение устраняется, что повышает производительность выгрузки. При выполнении команды выгрузки, такой как opclass, если у обеих таблиц одинаковая политика хэш-распределения, эти выгружаемые строки могут быть отправлены напрямую из сегмента в выходное местоположение.
Запустите программу файлового сервера gpfdist в фоновом режиме на порту 8081 для предоставления файлов из каталога /var/data/staging:
gpfdist -p 8081 -d /var/data/staging -l /home/mxadmin/log &Создайте читаемую внешнюю таблицу с именем ext_customer, используя протокол gpfdist и все файлы текстового формата (*.txt), найденные в каталоге gpfdist. Используйте вертикальную черту (|) в качестве разделителя столбцов и пробел как обозначение NULL при форматировании файла. Также обеспечьте доступ к внешней таблице в режиме изоляции одиночных строк с ошибками:
CREATE EXTERNAL TABLE ext_customer
(id int, name text, sponsor text)
LOCATION ( 'gpfdist://filehost:8081/*.txt' )
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
LOG ERRORS SEGMENT REJECT LIMIT 5;Создайте ту же самую читаемую внешнюю таблицу, что и выше, но в формате CSV:
CREATE EXTERNAL TABLE ext_customer
(id int, name text, sponsor text)
LOCATION ( 'gpfdist://filehost:8081/*.csv' )
FORMAT 'CSV' ( DELIMITER ',' );Используя протокол file и некоторые файлы формата CSV с заголовками строк, создайте читаемую внешнюю таблицу с именем ext_expenses:
CREATE EXTERNAL TABLE ext_expenses (name text, date date,
amount float4, category text, description text)
LOCATION (
'file://seghost1/dbfast/external/expenses1.csv',
'file://seghost1/dbfast/external/expenses2.csv',
'file://seghost2/dbfast/external/expenses3.csv',
'file://seghost2/dbfast/external/expenses4.csv',
'file://seghost3/dbfast/external/expenses5.csv',
'file://seghost3/dbfast/external/expenses6.csv'
)
FORMAT 'CSV' ( HEADER );Создайте читаемую внешнюю веб-таблицу, при этом каждый хост таблицы выполняет скрипт один раз:
CREATE EXTERNAL WEB TABLE log_output (linenum int, message
text) EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST
FORMAT 'TEXT' (DELIMITER '|');Создайте записываемую внешнюю таблицу с именем sales_out, которая использует gpfdist для записи выходных данных в файл с именем sales.out. Используйте вертикальную черту (|) в качестве разделителя столбцов и пробел как обозначение NULL при форматировании файла:
CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales)
LOCATION ('gpfdist://etl1:8081/sales.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (txn_id);Создайте записываемую внешнюю веб-таблицу, передавая выходные данные от сегмента исполняемому скрипту с именем to_adreport_etl.sh:
CREATE WRITABLE EXTERNAL WEB TABLE campaign_out
(LIKE campaign)
EXECUTE '/var/unload_scripts/to_adreport_etl.sh'
FORMAT 'TEXT' (DELIMITER '|');Используйте определённую выше записываемую внешнюю таблицу для выгрузки выбранных данных:
INSERT INTO campaign_out SELECT * FROM campaign WHERE
customer_id=123;При указании предложения LOG ERRORS база данных YMatrix фиксирует ошибки, возникающие при чтении данных внешней таблицы. Вы можете просматривать и управлять сохранёнными данными журнала ошибок.
Используйте встроенную SQL-функцию gp_read_error_log('table_name'). Для её использования требуются привилегии SELECT на объекте table_name.
Если данные журнала ошибок уже существуют в указанной таблице, новые данные добавляются к уже имеющимся. Информация ошибках не копируется на зеркальные сегменты.
Используйте встроенную SQL-функцию gp_truncate_error_log('table_name') для удаления данных журнала ошибок из table_name. Для этого требуется привилегия владельца таблицы.
Когда несколько внешних таблиц YMatrix определяются с использованием протоколов gpfdist, gpfdists или file и обращаются к одному и тому же именованному каналу (named pipe) в системе Linux, YMatrix ограничивает доступ к этому каналу только одним читателем. При попытке второго читателя получить доступ к каналу будет возвращена ошибка.
CREATE EXTERNAL TABLE является расширением базы данных YMatrix. Внешние таблицы не предусмотрены стандартом SQL.