LOAD DATA

Function

Copying data from a file to a table is accomplished with the LOAD DATA command.

Precautions

• When the parameter enable_copy_server_files is turned off, only the initial user is allowed to execute the LOAD DATA command. When the parameter enable_copy_server_files is turned on, users with SYSADMIN privileges or users who inherited the built-in role gs_role_copy_files privileges are allowed to execute.files privilege, but prohibits the execution of database configuration files, key files, certificate files and audit logs by default to prevent users from overstepping their authority to view or modify sensitive files. can only be used for tables, not for views.
• Can only be used for tables, not for views.
• Column-stored tables and tables of appearance are not supported.
• Requires insert permission on the table being inserted. The replace option also requires delete permission on the table.
• If a list of fields is declared, LOAD will only copy the data of the declared fields between the file and the table. If there are any fields in the table that are not in the field list, default values will be inserted for those fields.
• The declared data source file, which must be accessible to the server.
• If any row of the data file contains more or fewer fields than expected. dolphin.sql_mode for strict mode will throw an error. loose mode the missing fields will be inserted as NULL. if the field has a NOT NULL constraint then the type base value will be inserted.
• \\N is NULL, if you want to enter the actual data value\\N , use \\N.

Syntax

        LOAD DATA
        INFILE 'file_name'
        [REPLACE | IGNORE]
        INTO TABLE tbl_name
        [CHARACTER SET charset_name]
        [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
        ]
        [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
        ]
        [IGNORE number {LINES | ROWS}]
        [(col_name_or_user_var
        [, col_name_or_user_var] ...)]

Parameter Description

• REPLACE

  The inserted data will only work when there is a primary or unique key conflict, the conflicting rows in the table will be deleted first, and then the inserted data will be continued.

• IGNORE

  The inserted data will only work when there is a primary key or unique key conflict, the conflicting rows will be ignored and the data will be inserted.

• tbl_name

  Name of the table (can have schema modifiers).

  Range of values: the name of a table that already exists.

• col_name

  Optional list of fields to be copied.

  Range of values: if no field list is declared, all fields will be used.

• ESCAPED BY 'char'

  Used to specify the escape character, which can only be specified as a single-byte character.

  The default value is double quotes. When the same as the ENCLOSED BY value, it is replaced with '\0'.

• LINES TERMINATED BY 'string'

  Specifies the line break style for exported data files.

  Range of values: multi-character line breaks are supported, but the line breaks cannot exceed 10 bytes. Common line breaks, such as \\r, \\n, \\r\\n (set to 0x0D, 0x0A, 0x0D0A effect is the same), other characters or strings, such as $, #.

  Note:

  • The LINES TERMINATED BY parameter cannot be the same as the delimiter, null parameter.
  • The LINES TERMINATED BY parameter cannot contain: .abcdefghijklmnopqrstuvwxyz0123456789.

• CHARACTER SET 'charset_name'

  Specifies the name of the file encoding format.

  Range of values: valid encoding format.

  Default value: current encoding format.

• [OPTIONALLY] ENCLOSED BY 'char'

  Specify the wrapper, the data inside the full wrapper will be parsed as a column of parameters, OPTIONALLY has no practical meaning.

  Default value: double quotes.

  Note:

  • The ENCLOSED BY parameter cannot be the same as the delimiter parameter.
  • The ENCLOSED BY parameter can only be a single-byte character.

• FIELDS | COLUMNS TERMINATED BY 'string'

  The string that separates the individual fields in the file. The maximum length of the separator is no more than 10 bytes.

  Default: The default is the horizontal tab.

• IGNORE number {LINES | ROWS}

  Specifies that the first number of rows of the data file will be skipped when the data is exported.

Examples

-- Create the load_t1 table.
MogDB=# CREATE TABLE load_t1
(
    SM_SHIP_MODE_SK           INTEGER               NOT NULL,
    SM_SHIP_MODE_ID           CHAR(16)              NOT NULL,
    SM_TYPE                   CHAR(30)                      ,
    SM_CODE                   CHAR(10)                      ,
    SM_CARRIER                CHAR(20)                      ,
    SM_CONTRACT               CHAR(20)
);
-- /home/omm/test.csv
1,a,b,c,d,e
,a,b,c,d,e
3,\N,a,b,c,d
\N,a,b,c,d,e

-- Copy the data from the file /home/omm/test.csv to the table load_t1.
MogDB=# LOAD DATA INFILE '/home/omm/test.csv' INTO TABLE load_t1;

-- Copy data from /home/omm/test.csv file to table load_t1 with the following parameters: field separator '\t' (fields terminated by E'\t') line breaks '\r' (lines terminated by E'\r') skip the first two lines (IGNORE 2 LINES).
MogDB=# LOAD DATA INFILE '/home/omm/test.csv' INTO TABLE load_t1 fields terminated by ',' lines terminated by E'\n' IGNORE 2 LINES;

MogDB=# select * from load_t1;
 sm_ship_mode_sk | sm_ship_mode_id  |            sm_type             |  sm_code   |      sm_carrier      |     sm_contract
-----------------+------------------+--------------------------------+------------+----------------------+----------------------
               3 |                  | a                              | b          | c                    | d
               0 | a                | b                              | c          | d                    | e
(2 rows)

-- Delete table load_t1.
MogDB=# DROP TABLE load_t1;