Использование gpload

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

Contents

Обзор синтаксиса
Управляющий файл
Запуск gpload
Примеры

Утилита gpload облегчает загрузку данных в таблицы с помощью параллельного файлового сервера gpfdist (Greenplum Parallel File Server). Используя спецификацию загрузки, описанную в управляющем YAML-файле, gpload запускает утилиту gpfdist, создает внешнюю таблицу на основе исходных данных и запускает операцию INSERT, UPDATE или MERGE для загрузки данных в целевую таблицу БД.

Работа gpload, включая дополнительные команды SQL, описанные в секции SQL управляющего YAML-файла, выполняется в рамках одной транзакции. Это позволяет избежать неконсистентности данных в случаях, когда параллельно выполняются несколько операций загрузки данных в одну таблицу.

ПРИМЕЧАНИЕ

Утилиты gpfdist и gpload совместимы только с мажорной версией Greengage DB, в комплекте с которой они поставляются (например, 6.x).

Требования

Клиентская машина, на которой запускается gpload, должна удовлетворять следующим требованиям:

Python 2.6.2 или более поздней версии с установленными библиотеками pygresql (Python-интерфейс для взаимодействия с PostgreSQL) и pyyaml.

Обратите внимание, что Python и требуемые библиотеки входят в поставку сервера Greengage DB. При запуске gpload на машине c установленным Greengage DB дополнительной установки Python не требуется. gpload для Windows поддерживает только Python 2.5 (доступен для загрузки с сайта python.org).
Установленная утилита параллельной загрузки файлов gpfdist. gpfdist находится в каталоге $GPHOME/bin на мастер-хосте и на каждом сегмент-хосте Greengage DB. Для установки утилиты на произвольный хост скопируйте ее в удобное место и обновите переменную среды PATH.
Входящее и исходящее сетевое подключение ко всем хостам кластера Greengage DB.
Входящее и исходящее сетевое подключение ко всем хостам (ETL-серверам), на которых располагаются внешние данные для загрузки.

Синтаксис команды gpload в общем виде выглядит следующим образом. Полное описание опций см. в справочнике по утилите gpload.

gpload -f <control_file>
       [ -l <log_file> ]
       [ -h <hostname> ]
       [ -p <port> ]
       [ -U <username> ]
       [ -d <database> ]
       [ -W ]
       [ --gpfdist_timeout <seconds> ]
       [ --no_auto_trans ]
       [ --max_retries <retry_times> ]
       [ [ -v | -V ] [-q ] ]
       [ -D ]

gpload -?

gpload --version

С помощью управляющего файла описывается процесс загрузки. В управляющем файле указываются параметры подключения к Greengage DB, конфигурация утилиты gpfdist, настройки внешней таблицы и формат исходных данных.

Файл использует формат YAML 1.1 и собственную схему для описания шагов операции загрузки. Файл должен быть валидным YAML-документом. Утилита gpload обрабатывает содержимое документа в заданном порядке и использует отступы (пробелы) для определения иерархии и взаимосвязей между его разделами.

ПРИМЕЧАНИЕ

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

Структура управляющего файла приведена ниже. Полное описание ключей приведено в разделе Формат управляющего файла.

---
VERSION: 1.0.0.1
DATABASE: <db_name>
USER: <db_username>
HOST: <master_hostname>
PORT: <master_port>
GPLOAD:
  INPUT:
    - SOURCE:
        LOCAL_HOSTNAME:
          - <hostname_or_ip>
        PORT: <http_port>
      | PORT_RANGE: [ <start_port_range>, <end_port_range> ]
        FILE:
          - </path/to/input_file>
        SSL: true | false
        CERTIFICATES_PATH: </path/to/certificates>
    - FULLY_QUALIFIED_DOMAIN_NAME: true | false
    - COLUMNS:
        - <field_name>: <data_type>
    - TRANSFORM: '<transformation>'
    - TRANSFORM_CONFIG: '<configuration-file-path>'
    - MAX_LINE_LENGTH: <integer>
    - FORMAT: text | csv
    - DELIMITER: '<delimiter_character>'
    - ESCAPE: '<escape_character>' | 'OFF'
    - NEWLINE: 'LF' | 'CR' | 'CRLF'
    - NULL_AS: '<null_string>'
    - FILL_MISSING_FIELDS: true | false
    - FORCE_NOT_NULL: <column_name> [, ...]
    - QUOTE: '<csv_quote_character>'
    - HEADER: true | false
    - ENCODING: <database_encoding>
    - ERROR_LIMIT: <integer>
    - LOG_ERRORS: true | false
  EXTERNAL:
    - SCHEMA: <schema> | '%'
  OUTPUT:
    - TABLE: <schema.table_name>
    - MODE: insert | update | merge
    - MATCH_COLUMNS:
        - <target_column_name>
    - UPDATE_COLUMNS:
        - <target_column_name>
    - UPDATE_CONDITION: '<boolean_condition>'
    - MAPPING:
        <target_column_name>: <source_column_name> | '<expression>'
  PRELOAD:
    - TRUNCATE: true | false
    - REUSE_TABLES: true | false
    - STAGING_TABLE: <external_table_name>
    - FAST_MATCH: true | false
  SQL:
    - BEFORE: "<sql_command>"
    - AFTER: "<sql_command>"

ПРИМЕЧАНИЕ

Операции MERGE и UPDATE не поддерживаются, если имя целевой таблицы или колонки является ключевым словом, содержит заглавные буквы или заключено в двойные кавычки (" "). Если имена объектов базы данных заключены в двойные кавычки (являются идентификаторами с разделителем), то в управляющем файле gpload их необходимо указывать внутри одинарных кавычек (').

Например, имеется следующая таблица:

CREATE TABLE "MyTable" ("MyColumn" text);

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

...
  - COLUMNS:
    - '"MyColumn"': text
OUTPUT:
  - TABLE: public.'"MyTable"'
...

Создайте управляющий YAML-файл.
Запустите утилиту gpload, передав в опции -f созданный управляющий файл, например:
```
$ gpload -f example_load.yml
```

В данных примерах демонстрируется загрузка данных в таблицу БД данными из внешнего файла с помощью методов INSERT, UPDATE и MERGE.

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

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

CREATE DATABASE customers;
\c customers

CREATE TABLE orders (
    id INTEGER,
    name VARCHAR,
    price NUMERIC
);

CREATE TABLE audit (
    event VARCHAR,
    timestamp TIMESTAMP
);

В данном примере демонстрируется загрузка данных в таблицу БД методом INSERT.

Очистите таблицу orders и заполните ее данными:

TRUNCATE TABLE orders;

INSERT INTO orders (id, name, price)
VALUES
    (1,'Laptop',999.99),
    (2,'Smartphone',499.99),
    (3,'Tablet',299.99);

Очистите дополнительную таблицу audit:
```
TRUNCATE TABLE audit;
```
В каталоге /tmp мастер-хоста создайте файл orders.csv, содержащий данные для загрузки:
```
$ cat > orders.csv <<EOF
id,name,price
4,Monitor,599.99
5,Keyboard,99.99
EOF
```

На мастер-хосте создайте управляющий файл load.yml:

$ cat > load.yml <<EOF
---
VERSION: 1.0.0.1
DATABASE: customers
USER: gpadmin
HOST: mdw
PORT: 5432
GPLOAD:
  INPUT:
    - SOURCE:
        PORT: 8081
        FILE:
          - /tmp/orders.csv
    - COLUMNS:
        - id: INTEGER
        - name: VARCHAR
        - price: NUMERIC
    - FORMAT: CSV
    - HEADER: true
  OUTPUT:
    - TABLE: orders
    - MODE: INSERT
  SQL:
    - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
    - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"
EOF

Запустите утилиту gpload, передав в опции -f созданный управляющий файл. В таблицу orders добавятся новые строки. В таблицу audit будут внесены временные метки начала и окончания загрузки.
```
$ gpload -f load.yml
```
Вывод должен выглядеть следующим образом:
```
gpload session started
INFO|setting schema 'public' for table 'orders'
INFO|started gpfdist -p 8000 -P 9000 -f "/tmp/orders.csv" -t 30
INFO|running time: 0.10 seconds
INFO|rows Inserted          = 2
INFO|rows Updated           = 0
INFO|data formatting errors = 0
INFO|gpload succeeded
```

Выполните запрос к таблице orders:

SELECT * FROM orders;

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

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

Выполните запрос к таблице audit:
```
SELECT * FROM audit;
```
Вывод должен выглядеть следующим образом, отображая временные метки начала и окончания загрузки:
```
 event |         timestamp
-------+----------------------------
 start | 2025-05-25 12:45:47.931476
 end   | 2025-05-25 12:45:50.134581
```

В данном примере демонстрируется загрузка данных в таблицу БД методом UPDATE.

Очистите таблицу orders и заполните ее данными:

TRUNCATE TABLE orders;

INSERT INTO orders (id, name, price)
VALUES
    (1,'Laptop',999.99),
    (2,'Smartphone',499.99),
    (3,'Tablet',299.99),
    (4,'Monitor',599.99),
    (5,'Keyboard', 99.99);

Очистите дополнительную таблицу audit:
```
TRUNCATE TABLE audit;
```
В каталоге /tmp мастер-хоста создайте файл orders.csv, содержащий данные для загрузки. Обратите внимание, что в данных содержатся строки, уже имеющиеся в таблице orders, но с отличными значениями в колонке price.
```
$ cat > orders.csv <<EOF
id,name,price
1,Laptop,1299.99
4,Monitor,899.99
5,Keyboard,59.99
EOF
```

На мастер-хосте создайте управляющий файл load.yml:

$ cat > load.yml <<EOF
---
VERSION: 1.0.0.1
DATABASE: customers
USER: gpadmin
HOST: mdw
PORT: 5432
GPLOAD:
  INPUT:
    - SOURCE:
        FILE:
          - /tmp/orders.csv
    - COLUMNS:
        - id: integer
        - name: text
        - price: numeric
    - FORMAT: CSV
    - HEADER: true
  OUTPUT:
    - TABLE: orders
    - MODE: UPDATE
    - MATCH_COLUMNS:
      - id
    - UPDATE_COLUMNS:
      - price
  SQL:
    - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
    - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"
EOF

Запустите утилиту gpload, передав в опции -f созданный управляющий файл. Значения в колонке price обновятся в тех строках, где совпадают значения колонки id в таблице orders и в исходном файле orders.csv. В таблицу audit будут внесены временные метки начала и окончания загрузки.
```
$ gpload -f load.yml
```
Вывод должен выглядеть следующим образом:
```
INFO|gpload session started
INFO|setting schema 'public' for table 'orders'
INFO|started gpfdist -p 8000 -P 9000 -f "/tmp/orders.csv" -t 30
INFO|running time: 0.13 seconds
INFO|rows Inserted          = 0
INFO|rows Updated           = 3
INFO|data formatting errors = 0
INFO|gpload succeeded
```

Выполните запрос к таблице orders:

SELECT * FROM orders;

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

 id |    name    |  price
----+------------+---------
  1 | Laptop     | 1299.99
  2 | Smartphone |  499.99
  3 | Tablet     |  299.99
  4 | Monitor    |  899.99
  5 | Keyboard   |   59.99

Выполните запрос к таблице audit:
```
SELECT * FROM audit;
```
Вывод должен выглядеть следующим образом, отображая временные метки начала и окончания загрузки:
```
 event |         timestamp
-------+----------------------------
 start | 2025-05-25 16:24:47.931476
 end   | 2025-05-25 16:24:50.134581
```

В данном примере демонстрируется загрузка данных в таблицу БД методом MERGE.

Очистите таблицу orders и заполните ее данными:

TRUNCATE TABLE orders;

INSERT INTO orders (id, name, price)
VALUES
    (1,'Laptop',999.99),
    (2,'Smartphone',499.99),
    (3,'Tablet',299.99),
    (4,'Monitor',599.99),
    (5,'Keyboard', 99.99);

Очистите дополнительную таблицу audit:
```
TRUNCATE TABLE audit;
```
В каталоге /tmp мастер-хоста создайте файл orders.csv, содержащий данные для загрузки. Обратите внимание, что в данных содержатся строки, уже имеющиеся в таблице orders, но с отличными значениями в колонке price (строки 2, 4 и 5), а также новые строки 6 и 7.
```
$ cat > orders.csv <<EOF
id,name,price
1,Laptop,999.99
2,Smartphone,799.99
3,Tablet,299.99
4,Monitor,849.99
5,Keyboard,59.99
6,Webcam,29.99
7,Speakers,129.99
EOF
```

На мастер-хосте создайте управляющий файл load.yml:

$ cat > load.yml <<EOF
---
VERSION: 1.0.0.1
DATABASE: customers
USER: gpadmin
HOST: mdw
PORT: 5432
GPLOAD:
    INPUT:
      - SOURCE:
          FILE:
            - /tmp/orders.csv
      - COLUMNS:
          - id: integer
          - name: text
          - price: numeric
      - FORMAT: CSV
      - HEADER: true
    OUTPUT:
      - TABLE: orders
      - MODE: MERGE
      - MATCH_COLUMNS:
        - id
      - UPDATE_COLUMNS:
        - price
    SQL:
        - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
        - AFTER: "INSERT INTO audit VALUES('end', current_timestamp)"
EOF

Запустите утилиту gpload, передав в опции -f созданный управляющий файл. Значения в колонке price обновятся в тех строках, где совпадают значения колонки id в таблице orders и в исходном файле orders.csv. Если совпадающего значения id нет, в таблицу будет вставлена вся строка из файла orders.csv. В таблицу audit будут внесены временные метки начала и окончания загрузки.
```
$ gpload -f load.yml
```
Вывод должен выглядеть следующим образом:
```
INFO|gpload session started
INFO|setting schema 'public' for table 'orders'
INFO|started gpfdist -p 8000 -P 9000 -f "/tmp/orders.csv" -t 30
INFO|running time: 0.14 seconds
INFO|rows Inserted          = 2
INFO|rows Updated           = 5
INFO|data formatting errors = 0
INFO|gpload succeeded
```

Выполните запрос к таблице orders:

SELECT * FROM orders;

Вывод должен выглядеть следующим образом, отображая обновленные строки 2, 4, 5 и добавленные строки 6 и 7:

 id |    name    | price
----+------------+--------
  1 | Laptop     | 999.99
  2 | Smartphone | 799.99
  3 | Tablet     | 299.99
  4 | Monitor    | 849.99
  5 | Keyboard   |  59.99
  6 | Webcam     |  29.99
  7 | Speakers   | 129.99

Выполните запрос к таблице audit:
```
SELECT * FROM audit;
```
Вывод должен выглядеть следующим образом, отображая временные метки начала и окончания загрузки:
```
 event |         timestamp
-------+----------------------------
 start | 2025-05-25 15:24:47.931476
 end   | 2025-05-25 15:24:50.134581
```

Использование gpfdist

Форматирование внешних данных

Использование gpload

Обзор синтаксиса

Управляющий файл

Запуск gpload

Примеры

Метод INSERT

Метод UPDATE

Метод MERGE