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

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

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

С помощью коннектора PXF object store можно прочитать один или несколько однострочных и многострочных текстовых файлов из объектного хранилища, загрузив каждый файл в отдельную строку столбца таблицы. Этот метод может быть полезен для чтения нескольких файлов в одну внешнюю таблицу Greengage DB, например, когда отдельные JSON-файлы содержат по одной записи. Загрузка таким способом поддерживается только для текстовых и JSON-файлов, включая файлы со встроенными переносами строк.

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

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

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

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> TEXT|JSON | LIKE <other_table> )
    LOCATION ('pxf://<path_to_data>?PROFILE=<objstore>:text:multi&FILE_AS_ROW=true[&<custom_option>=<value>[...]]')
    FORMAT 'CSV';
Ключевое слово Значение

<table_name>

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

<column_name> TEXT|JSON

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

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

Более подробную информацию см. в разделе О работе с JSON

LIKE <other_table>

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

<path_to_data>

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

При чтении нескольких файлов убедитесь, что все они имеют один и тот же тип (текст или JSON). При чтении нескольких JSON-файлов убедитесь, что в каждом файле содержится полная запись, а тип записей — один и тот же

PROFILE=<objstore>:text:multi

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

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

  • wasbs — Azure Blob Storage

  • adl — Azure Data Lake

  • gs — Google Cloud Storage

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

FILE_AS_ROW=true

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

FORMAT

Для чтения многострочных текстовых данных из объектного хранилища требуется указать формат CSV

<custom_option>

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

SERVER=<server_name>

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

IGNORE_MISSING_PATH

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

Примеры

Эти примеры демонстрируют настройку и использование коннектора PXF object store для чтения многострочного текста из объектного хранилища с помощью внешних таблиц.

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

Для выполнения практических примеров подключитесь к мастер-хосту 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 и создайте каталог конфигурации сервера S3 с именем s3. Скопируйте необходимый для используемого объектного хранилища файл конфигурации сервера из $PXF_HOME/templates в $PXF_BASE/servers/s3. В примере используется файл конфигурации на основе шаблона minio-site.xml.

    $ mkdir $PXF_BASE/servers/s3
$ cd $PXF_BASE/servers/s3
$ cp $PXF_HOME/templates/minio-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>
    ПРИМЕЧАНИЕ

    Обратите внимание, что учетные данные для подключения к объектному хранилищу указываются в виде переменных окружения ACCESS_KEY и SECRET_KEY.

    Установить значения этих переменных можно следующим образом:

    $ export ACCESS_KEY=<access_key>
$ export SECRET_KEY=<secret_key>

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

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

    $ pxf cluster sync

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

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

    1,John,Doe,john.doe@example.com,123 Elm Street
    2,Jane,Smith,jane.smith@example.com,456 Oak Street
3,Bob,Brown,bob.brown@example.com,789 Pine Street
4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
    5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue
Unit 2B
Second Floor"
6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road
Suite 300
Business Park"
7,Eve,Wilson,eve.wilson@example.com,"PO Box 555
Anytown, USA
Zip Code: 12345"

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

    CREATE EXTERNAL TABLE customers_text (
    customers_data TEXT
    )
    LOCATION ('pxf://customers/text?PROFILE=s3:text:multi&SERVER=s3&FILE_AS_ROW=true')
    FORMAT 'CSV';

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

    SELECT * FROM customers_text;

    Вывод должен выглядеть следующим образом. Обратите внимание, что запрос возвращает по одной строке на файл, поэтому количество строк в выводе совпадает с количеством прочитанных файлов:

                           customers_data
-------------------------------------------------------------
 1,John,Doe,john.doe@example.com,123 Elm Street
 5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue+
 Unit 2B                                                    +
 Second Floor"                                              +
 6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road    +
 Suite 300                                                  +
 Business Park"                                             +
 7,Eve,Wilson,eve.wilson@example.com,"PO Box 555            +
 Anytown, USA                                               +
 Zip Code: 12345"
 2,Jane,Smith,jane.smith@example.com,456 Oak Street         +
 3,Bob,Brown,bob.brown@example.com,789 Pine Street          +
 4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
(3 rows)

  4. Включите расширенный режим отображения psql и повторите запрос к таблице customers_text:

    $ psql -d customers -x -c 'SELECT * FROM customers_text;'

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

    -[ RECORD 1 ]--+------------------------------------------------------------
customers_data | 1,John,Doe,john.doe@example.com,123 Elm Street
-[ RECORD 2 ]--+------------------------------------------------------------
customers_data | 2,Jane,Smith,jane.smith@example.com,456 Oak Street
               | 3,Bob,Brown,bob.brown@example.com,789 Pine Street
               | 4,Rob,Stuart,rob.stuart@example.com,119 Willow Street
-[ RECORD 3 ]--+------------------------------------------------------------
customers_data | 5,Alice,Johnson,alice.johnson@example.com,"101 Maple Avenue
               | Unit 2B
               | Second Floor"
               | 6,Charlie,Davis,charlie.davis@example.com,"123 Elm Road
               | Suite 300
               | Business Park"
               | 7,Eve,Wilson,eve.wilson@example.com,"PO Box 555
               | Anytown, USA
               | Zip Code: 12345"

Чтение JSON-файлов

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

    {
  "customers": [
    {
      "id": 101,
      "name": "Alice Smith",
      "ordered_items": [
        "laptop",
        "monitor"
      ]
    },
    {
      "id": 102,
      "name": "Bob Johnson",
      "ordered_items": [
        "keyboard",
        "mouse",
        "pad"
      ]
    }
  ]
}
    {
  "customers": [
    {
      "id": 103,
      "name": "Charlie Brown",
      "ordered_items": [
        "headphones"
      ]
    }
  ]
}

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

    CREATE EXTERNAL TABLE customers_json (
    customers_data JSON
    )
    LOCATION ('pxf://customers/json?PROFILE=s3:text:multi&SERVER=s3&FILE_AS_ROW=true')
    FORMAT 'CSV';

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

    SELECT * FROM customers_json;

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

             customers_data
--------------------------------
 {                             +
   "customers": [              +
     {                         +
       "id": 103,              +
       "name": "Charlie Brown",+
       "ordered_items": [      +
         "headphones"          +
       ]                       +
     }                         +
   ]                           +
 }
 {                             +
   "customers": [              +
     {                         +
       "id": 101,              +
       "name": "Alice Smith",  +
       "ordered_items": [      +
         "laptop",             +
         "monitor"             +
       ]                       +
     },                        +
     {                         +
       "id": 102,              +
       "name": "Bob Johnson",  +
       "ordered_items": [      +
         "keyboard",           +
         "mouse",              +
         "pad"                 +
       ]                       +
     }                         +
   ]                           +
 }
(2 rows)

  4. Включите расширенный режим отображения psql и повторите запрос к таблице customers_json:

    $ psql -d customers -x -c 'SELECT * FROM customers_json;'

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

    -[ RECORD 1 ]--+-------------------------------
customers_data | {
               |   "customers": [
               |     {
               |       "id": 103,
               |       "name": "Charlie Brown",
               |       "ordered_items": [
               |         "headphones"
               |       ]
               |     }
               |   ]
               | }
-[ RECORD 2 ]--+-------------------------------
customers_data | {
               |   "customers": [
               |     {
               |       "id": 101,
               |       "name": "Alice Smith",
               |       "ordered_items": [
               |         "laptop",
               |         "monitor"
               |       ]
               |     },
               |     {
               |       "id": 102,
               |       "name": "Bob Johnson",
               |       "ordered_items": [
               |         "keyboard",
               |         "mouse",
               |         "pad"
               |       ]
               |     }
               |   ]
               | }

  5. Используйте встроенную функцию Greengage DB json_array_elements(), чтобы извлечь элементы узла customers и просмотреть их в виде отдельных записей:

    SELECT json_array_elements(customers_data -> 'customers') AS customer FROM customers_json;

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

    -[ RECORD 1 ]----------------------------
customer | {
         |       "id": 101,
         |       "name": "Alice Smith",
         |       "ordered_items": [
         |         "laptop",
         |         "monitor"
         |       ]
         |     }
-[ RECORD 2 ]----------------------------
customer | {
         |       "id": 102,
         |       "name": "Bob Johnson",
         |       "ordered_items": [
         |         "keyboard",
         |         "mouse",
         |         "pad"
         |       ]
         |     }
-[ RECORD 3 ]----------------------------
customer | {
         |       "id": 103,
         |       "name": "Charlie Brown",
         |       "ordered_items": [
         |         "headphones"
         |       ]
         |     }

  6. Используйте оператор -> (Получение элемента JSON-массива), чтобы извлечь первый элемент массива ordered_items каждого узла customer:

    SELECT json_array_elements(customers_data -> 'customers') -> 'ordered_items' -> 0 AS first_item FROM customers_json;

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

    -[ RECORD 1 ]------------
first_item | "headphones"
-[ RECORD 2 ]------------
first_item | "laptop"
-[ RECORD 3 ]------------
first_item | "keyboard"
Предыдущая
SequenceFile
Следующая
Текст фиксированной ширины