Remarks

The syntax and semantics of loading data from a CSV, JSON, or Avro file are detailed below.

REPLACE, SKIP CONSTRAINT ERRORS, and SKIP DUPLICATE KEY ERRORS are supported with non-CSV pipelines.

During the import of data stored in any of these files, you can optionally apply operations to the data as follows:

• Use the WHERE clause to filter on incoming data. Only rows that satisfy the expression in the WHERE clause will be loaded into SingleStore. For an example of how to use the WHERE clause, see the examples section.

• Use the SET clause to set columns using specific values or expressions with variables. For example, if your input file has 9 columns but the table has a 10th column called foo, you can add SET foo=0 or SET foo=@myVariable. Note that column names may only be used on the left side of SET expressions.

• Use the CHARACTER SET clause to import files with any supported character set into SingleStore.

  For more information, see Character Encoding.

Refer to the Permission Matrix for the required permission.

The behavior of SingleStore’s LOAD DATA command has several functional differences from MySQL’s command:

• LOAD DATA will load the data into SingleStore in parallel to maximize performance. This makes LOAD DATA in SingleStore much faster on machines with a larger number of processors.

• LOAD DATA supports loading compressed .gz files.

• The only supported charset_name is utf8.

• While it is possible to execute LOAD DATA on leaf nodes, it should only be run on master aggregator or child aggregator node types. See Cluster Management Commands for more information. Note that when running this command on reference tables you must connect to the master aggregator.

The mysqlimport utility can also be used to import data into SingleStore. mysqlimport uses LOAD DATA internally.

The LOAD DATA operation does not fail when it encounters an error, instead it stores the error and continues ingesting data. SingleStore stores information about errors encountered during each LOAD DATA operation, but the number of errors is limited to 1000 for each partition by default. If this value is exceeded, the operation fails.

For example, if a table has 16 partitions, the total could be up to 16,000 errors (1000 per partition) before the LOAD DATA operation fails.

This prevents out-of-memory issues when unintentionally loading large files with incorrect formatting or an incorrect LOAD DATA statement. Use MAX_ERRORS at the end of the statement to change the limit on the number of errors (per partition) before LOAD DATA fails. For example, to specify LOAD DATA should fail when the first error is encountered, set MAX_ERRORS to 0.

Writing to multiple databases in a transaction is not supported.

CSV LOAD DATA

LOAD DATA [LOCAL] INFILE '<file_name>'
  [REPLACE | IGNORE | SKIP { ALL | CONSTRAINT | DUPLICATE KEY | PARSER } ERRORS]
  INTO TABLE <table_name>
  [CHARACTER SET <character_set_name>]
  [{FIELDS | COLUMNS}
  [TERMINATED BY '<string>']
      [[OPTIONALLY] ENCLOSED BY '<char>']
      [ESCAPED BY '<char>']
  ]
  [LINES
  [STARTING BY '<string>']
  [TERMINATED BY '<string>']
  ]
  [TRAILING NULLCOLS]
  [NULL DEFINED BY <string> [OPTIONALLY ENCLOSED]]
  [IGNORE <number> LINES]
  [ ({<column_name> | @<variable_name>}, ...) ]
  [SET <column_name> = <expression>,...]
  [WHERE <expression>,...]
  [MAX_ERRORS <number>]
  [ERRORS HANDLE <string>]

JSON LOAD DATA

LOAD DATA [LOCAL] INFILE 'file_name'
  [REPLACE | SKIP { CONSTRAINT | DUPLICATE KEY } ERRORS]
  INTO TABLE tbl_name
  FORMAT JSON
  subvalue_mapping
  [SET col_name = expr,...]
  [WHERE expr,...]
  [MAX_ERRORS number]
  [ERRORS HANDLE string]

subvalue_mapping:
  ( {col_name | @variable_name} <- subvalue_path [DEFAULT literal_expr], ...)

subvalue_path:
  {% | [%::]ident [::ident ...]}

BSON LOAD DATA

The LOAD DATA command supports loading BSON data from files using the FORMAT BSON clause. The LOAD DATA ... FORMAT BSON SQL statement is similar to LOAD DATA ... FORMAT JSON with the following exceptions:

• The FORMAT BSON clause does not support default values.

• The subvalue_mapping clause must be specified in the LOAD DATA ... FORMAT BSON SQL statement.

• The target columns in the subvalue_mapping clause must be BSON type columns. If the target columns are non-BSON type, they must be mapped to a user-defined variable and then assigned to the column using the SET clause.

Refer to JSON LOAD DATA for more information.

LOAD DATA [LOCAL] INFILE 'file_name'
  [REPLACE | SKIP { CONSTRAINT | DUPLICATE KEY } ERRORS]
  INTO TABLE tbl_name
  FORMAT BSON
  subvalue_mapping
  [SET col_name = expr,...]
  [WHERE expr,...]
  [MAX_ERRORS number]
  [ERRORS HANDLE string]

subvalue_mapping:
  ( {col_name | @variable_name} <- subvalue_path, ...)

subvalue_path:
  {% | [%::]ident [::ident ...]}

use dbm
db.bsonExport.insertMany( [
  { _id: 1, Code: "xv1f", Qty: 45 },
  { _id: 2, Code: "nm3w", Qty: 30 },
  { _id: 3, Code: "qoma", Qty: 20 },
  { _id: 4, Code: "hr3k", Qty: 15 } ] )

{ acknowledged: true,
  insertedIds: { '0': 1, '1': 2, '2': 3, '3': 4 } }

mongodump --uri="mongodb://<username>:<password>@<mongodb-endpoint>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true" --db="dbm" --collection="bsonExport" --out="<path_to_output_directory>"

CREATE TABLE bsonExport (
  _id BSON NOT NULL, 
  _more BSON NOT NULL COMMENT 'KAI_MORE', 
  `$_id` AS BSON_NORMALIZE_NO_ARRAY(`_id`) PERSISTED LONGBLOB COMMENT 'KAI_AUTO', 
  SHARD KEY (`$_id`), PRIMARY KEY (`$_id`));

LOAD DATA INFILE '<path_to_output_directory>/bsonExport.bson'  
  INTO TABLE bsonEx  FORMAT BSON (_id <- %::_id, @V1 <- %) 
  SET _more = BSON_EXCLUDE_MASK(@V1,'{"_id":1}');

SELECT _id:>JSON AS "_id", _more:>JSON AS "_more" FROM bsonEx;

+------+--------------------------+
| _id  | _more                    |
+------+--------------------------+
| 4    | {"Code":"hr3k","Qty":15} |
| 3    | {"Code":"qoma","Qty":20} |
| 2    | {"Code":"nm3w","Qty":30} |
| 1    | {"Code":"xv1f","Qty":45} |
+------+--------------------------+

Avro LOAD DATA

LOAD DATA [LOCAL] INFILE 'file_name'
  WHERE/SET/SKIP ERRORS[REPLACE | SKIP { CONSTRAINT | DUPLICATE KEY } ERRORS]
  INTO TABLE tbl_name
  FORMAT AVRO SCHEMA REGISTRY {"IP" | "Hostname"}
  subvalue_mapping
  [SET col_name = expr,...]
  [WHERE expr,...]
  [MAX_ERRORS number]
  [ERRORS HANDLE string]
  [SCHEMA 'avro_schema']

subvalue_mapping:
  ( {col_name | @variable_name} <- subvalue_path, ...)

subvalue_path:
  {% | [%::]ident [::ident ...]}

Loading Parquet Data

The LOAD DATA command supports loading Parquet files from AWS S3 or local files. You can also use the LOAD DATA clause in a CREATE PIPELINE .. FORMAT statement to create a pipeline that loads Parquet files.

LOAD DATA S3 '<bucket name>'
CONFIG '{"region" : "<region_name>"}' 
CREDENTIALS '{"aws_access_key_id" : "<key_id> ", 
             "aws_secret_access_key": "<access_key>"}' 
INTO TABLE <table_name>
       (`<col_a>` <- %, 
 `<col_b>` <- % DEFAULT NULL , 
  ) FORMAT PARQUET;

LOAD DATA LINK <link_name> '<bucket name>/<path>'
INTO TABLE <table_name>(`<col_a>` <- %,
`<col_b>` <- % DEFAULT NULL ,
) FORMAT PARQUET;

LOAD DATA INFILE '<path_to_file/file_name>'
INTO TABLE <table_name>
    (val1 <- source1, 
     val2 <- source2
     [ ... ]
) [COMPRESSION { AUTO | NONE | LZ4 | GZIP }]
[ ... ]
FORMAT PARQUET;

Handling Data Compression

The COMPRESSION clause specifies how LOAD DATA handles the compression of an input file.

LOAD DATA INFILE 'filename' COMPRESSION { AUTO | NONE | LZ4 | GZIP } INTO TABLE ...

LOAD DATA INFILE 'filename' INTO TABLE `tablename` COMPRESSION { AUTO | NONE | LZ4 | GZIP } ...

LOCAL

LOCAL affects the expected file location, the search behavior for relative path names, and Error Handling behavior.

When you specify LOCAL, the client reads file_name and sends it to the server. The expected location of the file is within the client directory. If file_name is a relative path, it is relative to the current working directory of the client.

When LOCAL is not specified, the file is read by the server, and needs to be located on the related server host. If a relative file path name is specified, it is searched for as relative to the server’s data directory, or as relative to the directory of the default database in cases where no leading components are given.

Because files need to be sent from the client to the server, specifying LOCAL can be slower. However, it does have the security advantage in that the client user must be able to read the file(s) being loaded. Where LOCAL is not specified, the server needs access to the full data directory, meaning that any user who has the permissions to LOAD DATA or CREATE PIPELINE can read the directory. This is because those permissions include FILE READ Permissions Matrix

LOCAL does not support globbing (such as using wildcards in directory or filenames).

An example of using LOCAL follows:

LOAD DATA LOCAL INFILE '/example-directory/foo.csv'
  INTO TABLE foo
  COLUMNS TERMINATED BY ',';

Error Logging

When you run the LOAD DATA command and use the ERRORS HANDLE clause, LOAD DATA logs errors to the information_schema.LOAD_DATA_ERRORS table. The logged errors are the erroneous lines that LOAD DATA encountered as it processed the input file.

See the next section for example data that LOAD DATA ... ERRORS HANDLE populates in the information_schema.LOAD_DATA_ERRORS table.

Use the CLEAR LOAD ERRORS command to remove errors from information_schema.LOAD_DATA_ERRORS.

Error Handling

LOAD DATA has several options to handle errors that it encounters as it processes the input file. When you write a LOAD DATA statement, you can decide which option to use.

• By default, LOAD DATA returns errors to the client application. Errors are returned one at a time.

• To ignore duplicate key/index value errors in the input file, use the REPLACE clause to replace existing rows with input rows. This clause first deletes the existing rows that have the same value for a primary key or unique index as the input rows, and then inserts the new row.

• To skip errors in the input file, use the SKIP ... ERRORS clause. Data in the erroneous lines will not be inserted into the destination table.

• To ignore errors in the input file, use the IGNORE clause. This clause replaces invalid values with their defaults, discards extra fields, or discards erroneous lines completely.

• When LOCAL is specified, duplicate-key and data interpretation errors do not stop the operation. When LOCAL is not specified, duplicate-key and data interpretation stop the operation.

The four error handling options are discussed in the following topics.

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time TIMESTAMP NOT NULL
  );

1,372,Apples,2016-05-09
3,307,Oranges,2016-07-31,1000
2,138,Pears,2016-07-14
2,236,Bananas,2016-06-23

LOAD DATA INFILE 'orders.csv'
  INTO TABLE orders
  FIELDS TERMINATED BY ',';

ERROR 1262 (01000): Row 2 was truncated; it contained more data than there were input columns

ERROR 1062 (23000): Leaf Error (127.0.0.1:3308): Duplicate entry '2' for key 'PRIMARY'

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time DATETIME NOT NULL
  );

INSERT INTO orders VALUES(4,236,"Bananas",2016-06-23);

1,372,Apples,2016-05-09
4,138,Pears,2016-07-14
3,307,Oranges,2016-07-31

LOAD DATA INFILE 'orders.csv'
  REPLACE
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time TIMESTAMP NOT NULL
  );

1,372,Apples,2016-05-09
2,138,Pears,2016-07-14
2,236,Bananas,2016-06-23
3,307,Oranges,2016-07-31

LOAD DATA INFILE 'orders.csv'
  SKIP DUPLICATE KEY ERRORS
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+---------------------------+--------------------------------+
| load_data_line_number | load_data_line            | error_message                  |
+-----------------------+---------------------------+--------------------------------+
|                     3 | 2,236,Bananas,2016-06-23  | Duplicate entry for unique key |
+-----------------------+---------------------------+--------------------------------+

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_properties JSON NOT NULL
  );

1,372,Apples,{"order-date":"2016-05-09"}
2,138,Pears,{"order-date"}
3,236,Bananas,{"order-date":"2016-06-23"}
4,307,Oranges,\N

LOAD DATA INFILE 'orders.csv'
  SKIP CONSTRAINT ERRORS
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+-----------------------------+--------------------------------------------------------------+
| load_data_line_number | load_data_line              | error_message                                                |
+-----------------------+-----------------------------+--------------------------------------------------------------+
|                     2 | 2,138,Pears,{"order-date"}  | Invalid JSON value for column 'order_properties'             |
|                     4 | 4,307,Oranges,\N            | NULL supplied to NOT NULL column 'order_properties' at row 4 |
+-----------------------+-----------------------------+--------------------------------------------------------------+

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_properties JSON NOT NULL
  );

1,372,Apples,{"order-date":"2016-05-09"}
2,138,Pears
1,236,Bananas,{"order-date":"2016-06-23"}
4,307,Oranges,\N

LOAD DATA INFILE 'orders.csv'
  SKIP ALL ERRORS
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+--------------------------------------------+--------------------------------------------------------------+
| load_data_line_number | load_data_line                             | error_message                                                |
+-----------------------+--------------------------------------------+--------------------------------------------------------------+
|                     2 | 2,138,Pears                                | Row 2 doesn't contain data for all columns                   |
|                     3 | 1,236,Bananas,{"order-date":"2016-06-23"}. | Duplicate entry for unique key                               |
|                     4 | 4,307,Oranges,\N                           | NULL supplied to NOT NULL column 'order_properties' at row 4 |
+-----------------------+--------------------------------------------+--------------------------------------------------------------+

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time DATETIME NOT NULL
  );

1,372,Apples,2016-05-09
2,138,Pears,2016-07-14
2,236,Bananas,2016-06-23
3,307,Oranges,2016-07-31

LOAD DATA INFILE 'orders.csv'
  IGNORE
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+---------------------------+--------------------------------+
| load_data_line_number | load_data_line            | error_message                  |
+-----------------------+---------------------------+--------------------------------+
|                     3 | 2,236,Bananas,2016-06-23  | Duplicate entry for unique key |
+-----------------------+---------------------------+--------------------------------+

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time DATETIME NOT NULL
  );

1,372,Apples,2016-05-09
2,138,Pears,2016-07-14
3,236,Bananas,2016-06-23
4,307,Oranges,\N

LOAD DATA INFILE 'orders.csv'
  IGNORE
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+------------------+--------------------------------------------------------+
| load_data_line_number | load_data_line   | error_message                                          |
+-----------------------+------------------+--------------------------------------------------------+
|                     4 | 4,307,Oranges,\N | NULL supplied to NOT NULL column 'order_time' at row 4 |
+-----------------------+------------------+--------------------------------------------------------+

SELECT * FROM orders ORDER BY 1;

+----+-------------+------------------+---------------------+
| id | customer_id | item_description | order_time          |
+----+-------------+------------------+---------------------+
|  1 |         372 | Apples           | 2016-05-09 00:00:00 |
|  2 |         138 | Pears            | 2016-07-14 00:00:00 |
|  3 |         236 | Bananas          | 2016-06-23 00:00:00 |
|  4 |         307 | Oranges          | 0000-00-00 00:00:00 |
+----+-------------+------------------+---------------------+

CREATE TABLE orders(
  id BIGINT PRIMARY KEY,
  customer_id INT,
  item_description VARCHAR(255),
  order_time DATETIME NOT NULL
  );

1,372,Apples,2016-05-09
2,138,Pears
3,236,Bananas,2016-06-23
4,307,Oranges,2016-07-31,Berries

LOAD DATA INFILE 'orders.csv'
  IGNORE
  INTO TABLE orders
  FIELDS TERMINATED BY ','
  ERRORS HANDLE 'orders_errors';

SELECT load_data_line_number, load_data_line, error_message
  FROM INFORMATION_SCHEMA.LOAD_DATA_ERRORS
  WHERE handle = 'orders_errors'
  ORDER BY load_data_line_number;

+-----------------------+----------------------------------+---------------------------------------------------------------------------+
| load_data_line_number | load_data_line                   | error_message                                                             |
+-----------------------+----------------------------------+---------------------------------------------------------------------------+
|                     2 | 2,138,Pears                      | Row 2 doesn't contain data for all columns                                |
|                     4 | 4,307,Oranges,2016-07-31,Berries | Row 4 was truncated; it contained more data than there were input columns |
+-----------------------+----------------------------------+---------------------------------------------------------------------------+

SELECT * FROM orders ORDER BY 1;

+----+-------------+------------------+---------------------+
| id | customer_id | item_description | order_time          |
+----+-------------+------------------+---------------------+
|  1 |         372 | Apples           | 2016-05-09 00:00:00 |
|  2 |         138 | Pears            | 0000-00-00 00:00:00 |
|  3 |         236 | Bananas          | 2016-06-23 00:00:00 |
|  4 |         307 | Oranges          | 2016-07-31 00:00:00 |
+----+-------------+------------------+---------------------+

Performance Considerations

Shard Keys

Loading data into a table with a shard key requires reading the necessary columns on the aggregator to compute the shard key before sending data to the leaves. For CSV LOAD DATA only, SingleStore recommends that columns included in the shard key appear earlier in input rows, if either the shard key design or the input format is flexible. Order does not significantly affect the performance of Avro or JSON LOAD DATA.

Keyless Sharding

Loading data into a keylessly sharded table (no shard key is declared, or shard() is specified) will result in batches of data loaded into different partitions, in a round-robin fashion. See Sharding for more information.

Retrieve loading status

The information_schema.LMV_LOAD_DATA_STATUS table reports information about rows and bytes read by in-progress LOAD DATA queries.

It also reports activity and database names, which you can use to find corresponding rows in workload profiling tables. See Management View Reference for more details.

SELECT * FROM information_schema.LMV_LOAD_DATA_STATUS;

+------+------------------------------------+---------------+------------+-----------+
| ID   | ACTIVITY_NAME                      | DATABASE_NAME | BYTES_READ | ROWS_READ |
+------+------------------------------------+---------------+------------+-----------+
| 2351 | load_data_company_0e8dec6d07d9cba5 | trades        |   94380647 |    700512 |
+------+------------------------------------+---------------+------------+-----------+

Related Topics

• INSERT

• REPLACE

• Performing Upserts

• Sharding