gs_loader

Overview

• gs_loader is used to import data. gs_loader converts the syntax supported by the control file to the \COPY syntax, uses the existing \COPY function to import data, and records the \COPY result in logs.
• Before using gs_loader, ensure that the gs_loader version is consistent with the gsql version and database version.

Log Level Configuration

Set the log level for developers to view. After the setting, the tool running information is printed on the console.

Permission

The application scenarios are classified into separation-of-duties and non-separation-of-duties scenarios. You can set enableSeparationOfDuty to on or off to enable or disable the separation of duties function.

• If enableSeparationOfDuty is set to off:

  The user can be a common user or the database administrator. If the user is a common user, the administrator needs to grant permissions to the common user. The administrator account can be used directly.

  Create a user.

  CREATE USER load_user WITH PASSWORD '************';

  Create related tables and grant permissions.

  GRANT ALL ON FUNCTION copy_error_log_create() TO load_user;
  GRANT ALL ON SCHEMA public TO load_user;
  SELECT copy_error_log_create();
  SELECT copy_summary_create();
  GRANT ALL PRIVILEGES ON  public.pgxc_copy_error_log To load_user;
  GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

• If enableSeparationOfDuty is set to on:

  The user can be a common user or the database administrator. Create the pgxc_copy_error_log and gs_copy_summary tables in their respective schemas and add indexes. No permission granting is required.

  Create a user.

  CREATE USER load_user WITH PASSWORD '********';

  Create related tables and add indexes.

  CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
  CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
  CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
  CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

Usage Environment

You need to add the tool path to PATH. gs_loader supports SSL encrypted communication. The method of using gs_loader is the same as that of using gsql. For details, refer to the Administrator Guide.

Adding System Catalogs

The gs_copy_summary table is added to record the COPY execution result summary, including the number of successful rows, number of error rows, number of ignored rows, and number of empty rows.

The copy_summary_create function is added to create the gs_copy_summary table.

The format of the gs_copy_summary table is as follows:

relname     | public.sqlldr_tbl
begintime   | 2021-09-03 16:00:11.7129-04
endtime     | 2021-09-03 16:00:15.259908-04
id          | 21870
pid         | 47582725060352
readrows    | 100000
skiprows    | 0
loadrows    | 111
errorrows   | 0
whenrows    | 99889
allnullrows | 0
detail      | 111 Rows successfully loaded.
            | 0 Rows not loaded due to data errors.
            | 99889 Rows not loaded because all WHEN clauses were failed.
            | 0 Rows not loaded because all fields were null.
            | 

Usage Guidelines

1. Create a user and related tables, and add indexes.
   • If the separation of duties function is disabled for common users only:

     Create a user.

     CREATE USER load_user WITH PASSWORD '************';

     Create related tables and grant permissions.

     GRANT ALL ON FUNCTION copy_error_log_create() TO load_user;
     GRANT ALL ON SCHEMA public TO load_user;
     SELECT copy_error_log_create();
     SELECT copy_summary_create();
     GRANT ALL PRIVILEGES ON  public.pgxc_copy_error_log To load_user;
     GRANT ALL PRIVILEGES ON  public.gs_copy_summary To load_user;

   • If the separation of duties function is enabled for common users and administrators:

     Create a user.

     CREATE USER load_user WITH PASSWORD '********';

     Create related tables and add indexes.

     CREATE TABLE load_user.pgxc_copy_error_log (relname varchar, begintime timestamptz, filename varchar, lineno int8, rawrecord text, detail text);
     CREATE INDEX copy_error_log_relname_idx ON load_user.pgxc_copy_error_log(relname);
     CREATE TABLE load_user.gs_copy_summary(relname varchar, begintime timestamptz, endtime timestamptz, id bigint, pid bigint, readrows bigint, skiprows bigint, loadrows bigint, errorrows bigint, whenrows bigint, allnullrows bigint, detail text);
     CREATE INDEX gs_copy_summary_idx ON load_user.gs_copy_summary(id);

2. Create a table and a control file, and prepare a data file.

   Create the loader_tbl table.

   1 2 3 4 5 6 7

   CREATE TABLE loader_tbl ( ID NUMBER, NAME VARCHAR2(20), CON VARCHAR2(20), DT DATE );

   Create the loader.ctl control file.

   1 2 3 4 5 6 7 8 9 10 11

   LOAD DATA truncate into table loader_tbl WHEN (2:2) = ',' fields terminated by ',' trailing nullcols ( id integer external, name char(32), con ":id || '-' || :name", dt date )

   Create the data.csv data file.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   1,OK,,2007-07-8 2,OK,,2008-07-8 3,OK,,2009-07-8 4,OK,,2007-07-8 43,DISCARD,,2007-07-8 ,,, 32,DISCARD,,2007-07-8 a,ERROR int,,2007-07-8 8,ERROR date,,2007-37-8 ,,,, , 8,ERROR fields,,2007-37-8 ,,, 5,OK,,2021-07-30

3. Import the data.

   Before importing data, ensure that the gs_loader tool has the execute permission. Ensure that the current path has the write permission on files. (The gs_loader generates some temporary files during the processing and automatically deletes them after the import is complete.)

   1	gs_loader control=loader.ctl data=data.csv db=testdb bad=loader.bad errors=5 port=8000 passwd=************ user=load_user

   Execution result:

   1 2 3 4 5 6

   gs_loader: version 0.1 10 Rows successfully loaded. log file is: loader.log

Parameter Description

• All parameters are in lowercase and are compatible with the gsql login mode, including -p port number, -h host, -d database, -U username, and -W password.
• gs_loader uses a .bad file to record errors from the rawrecord column in an error table. The error table does not record rawrecord if an error cannot be read by certain code. In this case, a blank line is recorded in the .bad file.

Control Files

• Syntax

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  LOAD [ DATA ] [CHARACTERSET char_set_name] [INFILE [directory_path] [filename ] ] [BADFILE [directory_path] [filename ] ] [{ INSERT | APPEND | REPLACE | TRUNCATE }] INTO TABLE table_name [{ INSERT | APPEND | REPLACE | TRUNCATE }] [FIELDS CSV] [TERMINATED [BY] { 'string' }] [OPTIONALLY ENCLOSED BY { 'string' }] [TRAILING NULLCOLS] [ WHEN { (start:end) | column_name } {= | !=} 'string' ] [( col_name [ [ POSITION ({ start:end }) ] ["sql_string"] ] | [ FILLER [column_type [external] ] ] | [ CONSTANT "string" ] | [ SEQUENCE ( { COUNT | MAX | integer } [, incr] ) ]|[NULLIF (COL=BLANKS)] [, ...] )]