hawq load

Acts as an interface to the external table parallel loading feature. Executes a load specification defined in a YAML-formatted control file to invoke the HAWQ parallel file server (gpfdist).

Synopsis

hawq load -f <control_file> [-l <log_file>]   
  [--gpfdist_timeout <seconds>] 
  [[-v | -V] 
  [-q]]
  [-D]
  [<connection_options>]

hawq load -? 

hawq load --version

where:

<connection_options> =
  [-h <host>] 
  [-p <port>] 
  [-U <username>] 
  [-d <database>]
  [-W]

Prerequisites

The client machine where hawq load is executed must have the following:

• Python 2.6.2 or later, pygresql (the Python interface to PostgreSQL), and pyyaml. Note that Python and the required Python libraries are included with the HAWQ server installation, so if you have HAWQ installed on the machine where hawq load is running, you do not need a separate Python installation. Note: HAWQ Loaders for Windows supports only Python 2.5 (available from www.python.org).

• The gpfdist parallel file distribution program installed and in your $PATH. This program is located in $GPHOME/bin of your HAWQ server installation.

• Network access to and from all hosts in your HAWQ array (master and segments).

• Network access to and from the hosts where the data to be loaded resides (ETL servers).

Description

hawq load is a data loading utility that acts as an interface to HAWQ’s external table parallel loading feature. Using a load specification defined in a YAML formatted control file, hawq load executes a load by invoking the HAWQ parallel file server (gpfdist), creating an external table definition based on the source data defined, and executing an INSERT operation to load the source data into the target table in the database.

The operation, including any SQL commands specified in the SQL collection of the YAML control file (see Control File Format), are performed as a single transaction to prevent inconsistent data when performing multiple, simultaneous load operations on a target table.

Arguments

Options

Connection Options

Control File Format

The hawq load control file uses the YAML 1.1 document format and then implements its own schema for defining the various steps of a HAWQ load operation. The control file must be a valid YAML document.

The hawq load program processes the control file document in order and uses indentation (spaces) to determine the document hierarchy and the relationships of the sections to one another. The use of white space is significant. White space should not be used simply for formatting purposes, and tabs should not be used at all.

The basic structure of a load control file is:

---
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
    - 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'
    - NULL_AS: 'null_string'
    - FORCE_NOT_NULL: true | false
    - QUOTE: 'csv_quote_character'
    - HEADER: true | false
    - ENCODING: database_encoding
    - ERROR_LIMIT: integer
    - ERROR_TABLE: schema.table_name
   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
   SQL:
    - BEFORE: "sql_command"
    - AFTER: "sql_command"

Control File Schema Elements

The control file contains the schema elements for:

• Version
• Database
• User
• Host
• Port
• GPLOAD file

INSERT INTO target_table SELECT * FROM input_data;

Notes

If your database object names were created using a double-quoted identifier (delimited identifier), you must specify the delimited name within single quotes in the hawq load control file. For example, if you create a table as follows:

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

Your YAML-formatted hawq load control file would refer to the above table and column names as follows:

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

Log File Format

Log files output by hawq load have the following format:

timestamp|level|message

Where <timestamp> takes the form: YYYY-MM-DD HH:MM:SS, <level> is one of DEBUG, LOG, INFO, ERROR, and <message> is a normal text message.

Some INFO messages that may be of interest in the log files are (where # corresponds to the actual number of seconds, units of data, or failed rows):

INFO|running time: #.## seconds
INFO|transferred #.# kB of #.# kB.
INFO|hawq load succeeded
INFO|hawq load succeeded with warnings
INFO|hawq load failed
INFO|1 bad row
INFO|# bad rows

Examples

Run a load job as defined in my_load.yml:

$ hawq load -f my_load.yml

Example load control file:

---
VERSION: 1.0.0.1
DATABASE: ops
USER: gpadmin
HOST: mdw-1
PORT: 5432
GPLOAD:
   INPUT:
    - SOURCE:
         LOCAL_HOSTNAME:
           - etl1-1
           - etl1-2
           - etl1-3
           - etl1-4
         PORT: 8081
         FILE: 
           - /var/load/data/*
    - COLUMNS:
           - name: text
           - amount: float4
           - category: text
           - desc: text
           - date: date
    - FORMAT: text
    - DELIMITER: '|'
    - ERROR_LIMIT: 25
    - ERROR_TABLE: payables.err_expenses
   OUTPUT:
    - TABLE: payables.expenses
    - MODE: INSERT
   SQL:
   - BEFORE: "INSERT INTO audit VALUES('start', current_timestamp)"
   - AFTER: "INSERT INTO audit VALUES('end', 
current_timestamp)"

See Also

gpfdist, CREATE EXTERNAL TABLE