Открыть страницу поиска
Привет, Я DocuDroid!
Открыть страницу ИИ Помощника
Оценка ИИ поиска
Спасибо за оценку нашего ИИ поиска!
Мы будем признательны, если вы поделитесь своими впечатлениями, чтобы мы могли улучшить наш ИИ поиск для вас и других читателей.
GitHub

Использование коннекторов PXF object store для чтения и записи текстовых данных между Greengage DB и S3

Антон Монаков

С помощью коннекторов PXF object store можно читать и записывать данные, хранящиеся в объектных хранилищах Azure Blob Storage, Azure Data Lake, Google Cloud Storage, Amazon S3, MinIO и других, совместимых с S3.

В этой статье описывается настройка и использование коннектора PXF object store для чтения и записи текстовых данных в объектных хранилищах с использованием внешних таблиц, а также приводятся практические примеры.

Создание внешней таблицы с использованием протокола PXF

Чтобы создать внешнюю таблицу Greengage DB для чтения и записи текстовых данных, хранящихся в объектных хранилищах, используется следующий синтаксис:

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
    LOCATION ('pxf://<path_to_data>?PROFILE=<profile_name>[&<custom_option>=<value>[...]]')
    FORMAT '[TEXT|CSV]' (delimiter[=|<space>][E]'<delim_value>') |
        'CUSTOM' (FORMATTER='pxfdelimited_import' <format_option>[=|<space>][E]'<value>')
    [DISTRIBUTED BY (<column_name> [, ... ] ) | DISTRIBUTED RANDOMLY];
Ключевое слово Значение

<table_name>

Имя создаваемой таблицы

<column_name>

Имя создаваемого столбца

<data_type>

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

LIKE <other_table>

Указывает таблицу, из которой внешняя таблица копирует все имена столбцов, типы данных и политику распределения

<path_to_data>

Путь к каталогу или файлу в объектном хранилище. Если в конфигурации сервера <server_name> указано свойство pxf.fs.basePath, значение <path_to_data> трактуется как относительный путь к указанному базовому пути. В противном случае путь считается абсолютным. Значение пути не должно содержать символ $

PROFILE=<profile_name>

Профиль указывается в виде пары <objstore>:<format>, где <objstore> — префикс объектного хранилища, а <format> — постфикс, идентифицирующий формат данных.

Поддерживаются следующие префиксы <objstore> и соответствующие объектные хранилища:

  • wasbs — Azure Blob Storage

  • adl — Azure Data Lake

  • gs — Google Cloud Storage

  • s3 — Amazon S3, MinIO и прочие S3-совместимые объектные хранилища

Поддерживаются следующие постфиксы <format> и соответствующие форматы данных:

  • text — для чтения и записи простых текстовых данных.

  • csv — для чтения и записи значений, разделенных запятыми (CSV), а также чтения данных, содержащих многобайтовые или многосимвольные разделители. Подробнее см. в разделе Чтение данных с многобайтовыми или многосимвольными разделителями.

  • text:multi — для чтения простых текстовых данных с одно- и многострочными закавыченными значениями, содержащими встроенные возвраты каретки и переводы строк.

FORMAT <value>

Определяет формат данных и может принимать одно из следующих значений:

  • TEXT — для чтения и записи простых текстовых данных.

  • CSV — для чтения и записи значений, разделенных запятыми (CSV), а также чтения данных, содержащих многобайтовые или многосимвольные разделители.

  • CUSTOM — для чтения данных, содержащих многобайтовые или многосимвольные разделители. Подробнее см. в разделе Чтение данных с многобайтовыми или многосимвольными разделителями.

Обратите внимание, что выражение HEADER, обычно указывающее на наличие заголовка в файле данных, не поддерживается внешними таблицами Greengage DB с использованием PXF. Если файл данных содержит строки заголовка, используйте опцию SKIP_HEADER_COUNT, чтобы указать количество строк, которые необходимо пропустить в начале каждого файла

delimiter

Символ, использующийся в качестве разделителя полей данных. В формате CSV значение <delim_value> по умолчанию — символ запятой (,). Вы можете использовать спецпоследовательности, начинающиеся с E'', например delimiter=E'\t'

DISTRIBUTED BY

При загрузке данных из таблицы Greengage DB во внешнюю пишущую таблицу рекомендуется указывать ту же политику распределения или имя столбца в обеих таблицах. Это позволит избежать дополнительного перемещения данных между сегментами при выполнении операции загрузки. Более подробную информацию о распределении таблиц можно получить в статье Распределение данных

<custom_option>

Одна из опций, описанных ниже, указываемая в строке LOCATION

SERVER=<server_name>

Имя конфигурации сервера, который используется для доступа к данным. Если значение не указано, используется сервер PXF по умолчанию

IGNORE_MISSING_PATH=<boolean>

Действие, которое необходимо выполнить, если <path_to_data> отсутствует или указан неверно. Если установлено значение false (по умолчанию), возвращается ошибка. Если установлено значение true, PXF игнорирует ошибку и возвращает пустой фрагмент

SKIP_HEADER_COUNT=<numlines>

Количество строк заголовка, которые необходимо пропустить в начале файла HDFS перед чтением данных. Значение по умолчанию — 0, строки не пропускаются

COMPRESSION_CODEC

Кодек сжатия, используемый при записи данных: default, bzip2, gzip или uncompressed (без сжатия). Если значение не указано (или указано uncompressed), сжатие не производится

NEWLINE

Указывает символ новой строки. Если для кастомной опции форматирования NEWLINE указано значение CR или CRLF, то такое же значение должно быть указано и для опции NEWLINE в выражении LOCATION
ПРИМЕЧАНИЕ

При обращении к объектному хранилищу S3:

  • Вы можете предоставить учетные данные S3, указав опции accesskey и secretkey в выражении LOCATION команды CREATE EXTERNAL TABLE.

  • Для получения данных в формате CSV можно использовать PXF с сервисом Amazon S3 Select.

Чтение данных с многобайтовыми или многосимвольными разделителями

Для чтения данных с многобайтовыми или многосимвольными разделителями поддерживаются только профили <objstore>:csv (где <objstore> — префикс объектного хранилища). В выражении FORMAT необходимо указать кастомный форматтер (функцию pxfdelimited_import) и настройки форматирования с помощью опций <format_option>. Практический пример чтения таких данных приведен в разделе Чтение данных с многобайтовыми разделителями.

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

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
    LOCATION ('pxf://<path_to_data>?PROFILE=<objstore>:csv'[&<custom_option>=<value>[...]]')
    FORMAT 'CUSTOM' (FORMATTER='pxfdelimited_import' <format_option>[=|<space>][E]'<value>');
Опция форматирования Описание Значение по умолчанию

DELIMITER=<delim_string>

Обязательный параметр. Одно- или многобайтовая строка, разделяющая столбцы данных. Строка может быть длиной до 32 байт и не может содержать символы кавычки или экранирования. Разделитель указывается либо явно, либо как байтовое представление.

Байтовое представление разделителя указывается в формате E'<value>'. Некоторые символы могут иметь разные байтовые представления в зависимости от кодировки, поэтому используется кодировка базы данных. Например, если кодировка базы данных — UTF8, кодировка файла — LATIN1, а разделитель — символ валюты (¤), то необходимо указать его байтовое представление в кодировке UTF8 — \xC2\xA4

Отсутствует

QUOTE=<char>

Однобайтовый ASCII-символ, используемый в качестве кавычки во всех столбцах

Отсутствует

ESCAPE=<char>

Однобайтовый ASCII-символ, используемый в качестве символа экранирования специальных символов (например, значений DELIMITER, QUOTE и NEWLINE или значения самого ESCAPE)

Отсутствует или принимает значение QUOTE, если оно указано

NEWLINE

Символ переноса строки, принимающий значения LF (символ перевода строки, 0x0A), CR (символ возврата каретки, 0x0D) или CR в сочетании с LF (CRLF, 0x0D 0x0A).

Обратите внимание, что если указано значение CR или CRLF, то такое же значение необходимо указать для опции NEWLINE в выражении LOCATION команды CREATE EXTERNAL TABLE

LF

При чтении данных с многобайтовым или многосимвольным разделителем поведение PXF зависит от настроек значений кавычки и символа экранирования.

Указан QUOTE Указан ESCAPE Поведение PXF

Нет

Нет

PXF читает данные без изменений

Да

Да

PXF читает данные между символами кавычек без изменений и производит деэкранирование символов кавычек и экранирования

Да

Нет (ESCAPE 'OFF')

PXF читает данные между символами кавычек без изменений

Нет

Да

PXF читает данные без изменений и производит деэкранирование символов разделителя, переноса строки и экранирования
ПРИМЕЧАНИЕ

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

Примеры

Предварительные требования

Для выполнения практических примеров подключитесь к мастер-хосту Greengage DB как gpadmin с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Затем создайте тестовую базу данных customers и подключитесь к ней:

DROP DATABASE IF EXISTS customers;
CREATE DATABASE customers;
\c customers

Чтобы создать внешнюю таблицу с использованием протокола PXF, предварительно зарегистрируйте в БД расширение PXF, как описано в разделе Регистрация PXF в БД документации PXF:

CREATE EXTENSION pxf;

Конфигурирование коннектора PXF S3

Для того чтобы подключиться к объектному хранилищу с помощью PXF, необходимо создать соответствующую конфигурацию сервера, а затем синхронизировать конфигурацию между хостами кластера Greengage DB:

  1. Подключитесь к мастер-хосту Greengage DB как gpadmin.

  2. Перейдите в каталог $PXF_BASE/servers и создайте каталог конфигурации сервера Hadoop с именем s3. Скопируйте необходимый для используемого объектного хранилища файл конфигурации сервера из $PXF_HOME/templates в $PXF_BASE/servers/s3. В примере используется файл s3-site.xml на основе шаблона minio-site.xml.

    $ mkdir $PXF_BASE/servers/s3
$ cd $PXF_BASE/servers/s3
$ cp $PXF_HOME/templates/s3-site.xml .

    В файле конфигурации укажите необходимые данные для подключения к объектному хранилищу:

    <?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <property>
        <name>fs.s3a.endpoint</name>
        <value>storage.example.com</value>
    </property>
    <property>
        <name>fs.s3a.access.key</name>
        <value>${ACCESS_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.secret.key</name>
        <value>${SECRET_KEY}</value>
    </property>
    <property>
        <name>fs.s3a.fast.upload</name>
        <value>true</value>
    </property>
    <property>
        <name>fs.s3a.path.style.access</name>
        <value>true</value>
    </property>
</configuration>

  3. Синхронизируйте конфигурацию между хостами кластера Greengage DB:

    $ pxf cluster sync

Чтение простых текстовых и CSV-данных

  1. В бакете customers на хосте S3 создайте файл orders.txt со следующим содержимым:

    id,name,price
1,Laptop,999.99
2,Smartphone,499.99
3,Tablet,299.99
4,Monitor,599.99
5,Keyboard,99.99

  2. На мастер-хосте Greengage DB создайте читающую внешнюю таблицу, ссылающуюся на файл orders.txt. В выражении LOCATION укажите PXF-профиль s3:text и конфигурацию сервера. Включите пропуск заголовка с помощью опции SKIP_HEADER_COUNT. В выражении FORMAT укажите TEXT в качестве формата данных и установите символ запятой (,) в качестве разделителя:

    CREATE EXTERNAL TABLE orders_s3 (
        id INTEGER,
        name VARCHAR,
        price NUMERIC    
    )
    
    LOCATION ('pxf://customers/orders.txt?PROFILE=s3:text&SERVER=s3&SKIP_HEADER_COUNT=1')
    FORMAT 'TEXT' (delimiter=E',');

  3. Выполните запрос к созданной внешней таблице:

    SELECT * FROM orders_s3;

    Вывод должен выглядеть следующим образом:

     id |    name    | price
----+------------+--------
  1 | Laptop     | 999.99
  2 | Smartphone | 499.99
  3 | Tablet     | 299.99
  4 | Monitor    | 599.99
  5 | Keyboard   |  99.99
(5 rows)

  4. Создайте еще одну читающую внешнюю таблицу, ссылающуюся на файл orders.txt. В выражении LOCATION укажите PXF-профиль s3:csv и конфигурацию сервера. Включите пропуск заголовка с помощью опции SKIP_HEADER_COUNT. В выражении FORMAT укажите CSV в качестве формата данных:

    CREATE EXTERNAL TABLE orders_s3_csv (
        id INTEGER,
        name VARCHAR,
        price NUMERIC    
    )

    LOCATION ('pxf://customers/orders.txt?PROFILE=s3:csv&SERVER=s3&SKIP_HEADER_COUNT=1')
    FORMAT 'CSV';

  5. Выполните запрос к созданной внешней таблице:

    SELECT * FROM orders_s3_csv;

    Вывод должен выглядеть следующим образом:

     id |    name    | price
----+------------+--------
  1 | Laptop     | 999.99
  2 | Smartphone | 499.99
  3 | Tablet     | 299.99
  4 | Monitor    | 599.99
  5 | Keyboard   |  99.99
(5 rows)

Чтение многострочных текстовых данных

  1. В бакете customers на хосте S3 создайте файл customers_multiline.txt со следующим содержимым:

    1:John:Doe:john.doe@example.com:"123 Elm Street
New York"
2:Jane:Smith:jane.smith@example.com:"456 Oak Street
Chicago"
3:Bob:Brown:bob.brown@example.com:"789 Pine Street
Los Angeles"
4:Rob:Stuart:rob.stuart@example.com:"119 Willow Street
New Orleans"

  2. На мастер-хосте Greengage DB создайте читающую внешнюю таблицу, ссылающуюся на файл customers_multiline.txt. В выражении LOCATION укажите PXF-профиль s3:text:multi и конфигурацию сервера. В выражении FORMAT укажите CSV в качестве формата данных и установите символ двоеточия (:) в качестве разделителя:

    CREATE EXTERNAL TABLE customers_s3_multiline (
        id INTEGER,
        first_name TEXT,
        last_name TEXT,
        email TEXT,
        address TEXT    
    )

    LOCATION ('pxf://customers/customers_multiline.txt?PROFILE=s3:text:multi&SERVER=s3')
    FORMAT 'CSV' (delimiter=':');

  3. Выполните запрос к созданной внешней таблице:

    SELECT * FROM customers_s3_multiline;

    Вывод должен выглядеть следующим образом:

     id | first_name | last_name |         email          |      address
----+------------+-----------+------------------------+-------------------
  1 | John       | Doe       | john.doe@example.com   | 123 Elm Street   +
    |            |           |                        | New York
  2 | Jane       | Smith     | jane.smith@example.com | 456 Oak Street   +
    |            |           |                        | Chicago
  3 | Bob        | Brown     | bob.brown@example.com  | 789 Pine Street  +
    |            |           |                        | Los Angeles
  4 | Rob        | Stuart    | rob.stuart@example.com | 119 Willow Street+
    |            |           |                        | New Orleans
(4 rows)

Чтение данных с многобайтовыми разделителями

  1. В бакете customers на хосте S3 создайте файл customers_multibyte.txt со следующим содержимым:

    "John Doe"⟼"john.doe@example.com"⟼"Note: \"Urgent\""
"Jane Smith"⟼"jane.smith@example.com"⟼""
"Bob Brown"⟼"bob.brown@example.com"⟼""
"Rob Stuart"⟼"rob.stuart@example.com"⟼"Note: \"Delayed\""

  2. На мастер-хосте Greengage DB создайте читающую внешнюю таблицу, ссылающуюся на файл customers_multibyte.txt. В выражении LOCATION укажите PXF-профиль s3:csv и конфигурацию сервера. В выражении FORMAT укажите pxfdelimited_import — встроенную кастомную функцию форматирования для операций чтения данных с разделителями. Укажите стрелку (⟼) в качестве символа разделителя, двойную кавычку (") в качестве символа кавычки и обратную косую черту (\) в качестве символа экранирования:

    CREATE EXTERNAL TABLE customers_s3_multibyte (
        name TEXT,
        email TEXT,
        notes TEXT    
    )

    LOCATION ('pxf://customers/customers_multibyte.txt?PROFILE=s3:csv&SERVER=s3')
    FORMAT 'CUSTOM' (FORMATTER='pxfdelimited_import', DELIMITER=E'⟼', QUOTE='"', ESCAPE='\');

  3. Выполните запрос к созданной внешней таблице:

    SELECT * FROM customers_s3_multibyte;

    Вывод должен выглядеть следующим образом:

        name    |         email          |      notes
------------+------------------------+-----------------
 John Doe   | john.doe@example.com   | Note: "Urgent"
 Jane Smith | jane.smith@example.com |
 Bob Brown  | bob.brown@example.com  |
 Rob Stuart | rob.stuart@example.com | Note: "Delayed"
(4 rows)

Запись текстовых данных

  1. На мастер-хосте Greengage DB создайте пишущую внешнюю таблицу, сохраняющую данные в бакет customers на хосте S3. В выражении LOCATION укажите PXF-профиль s3:text и конфигурацию сервера, а также выберите кодек сжатия gzip. В выражении FORMAT укажите TEXT в качестве формата данных и установите символ запятой (,) в качестве разделителя:

    CREATE WRITABLE EXTERNAL TABLE customers_w_s3 (
        name TEXT,
        email TEXT,
        address TEXT
    )

    LOCATION ('pxf://customers?PROFILE=s3:text&SERVER=s3&COMPRESSION_CODEC=gzip')
    FORMAT 'TEXT' (delimiter=E',');

  2. Вставьте данные в созданную таблицу:

    INSERT INTO customers_w_s3 (name, email, address) 
VALUES ('Alice Smith','alice.smith@example.com','42 Maple Avenue, Anytown'),
       ('Bob Johnson','bob.johnson@example.com','10 Oak Lane, Springfield'),
       ('Carol Williams','carol.williams@example.com','7 Pine Court, Hill Valley'),
       ('David Miller','david.miller@example.com','3 Birch Road, Pleasantville');

  3. Проверьте содержимое бакета customers. Набор созданных файлов должен выглядеть подобным образом:

    /customers/190-0000000021_1.gz
/customers/190-0000000021_2.gz

  4. На мастер-хосте Greengage DB создайте читающую внешнюю таблицу. В выражении LOCATION укажите префикс customers, чтобы выбрать все созданные файлы, PXF-профиль s3:text и конфигурацию сервера. В выражении FORMAT укажите TEXT в качестве формата данных и установите символ запятой (,) в качестве разделителя:

    CREATE EXTERNAL TABLE customers_s3_v (
        name TEXT,
        email TEXT,
        address TEXT
    )

    LOCATION ('pxf://customers?PROFILE=s3:text&SERVER=s3')
    FORMAT 'TEXT' (delimiter=E',');

  5. Выполните запрос к созданной внешней таблице:

    SELECT * FROM customers_s3_v;

    Вывод должен выглядеть следующим образом:

          name      |           email            |           address
----------------+----------------------------+-----------------------------
 Bob Johnson    | bob.johnson@example.com    | 10 Oak Lane, Springfield
 David Miller   | david.miller@example.com   | 3 Birch Road, Pleasantville
 Alice Smith    | alice.smith@example.com    | 42 Maple Avenue, Anytown
 Carol Williams | carol.williams@example.com | 7 Pine Court, Hill Valley
(4 rows)
Предыдущая
Hive
Следующая
ABORT