LOAD DATA

Syntax

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>]

Remarks

• Error Logging and Error Handling are discussed at the end of this topic.

• To specify the compression type of an input file, use the COMPRESSION clause. See Handling Data Compression for more information.

• If a CSV file appears to have the incorrect number of fields in any line, you can use the SKIP PARSER ERRORS option to skip the line. LOAD DATA reports a warning for every line that is skipped.

  Important

  Lines in a CSV file may appear to have the wrong number of fields if the FIELDS TERMINATED BY, FIELDS ENCLOSED BY, or ESCAPED BY clauses are incorrectly configured. If LOAD DATA incorrectly finds the start of the next line in a CSV after a parser error, it may parse all the subsequent lines incorrectly. For these reasons, investigate the CSV input and configuration settings mentioned above before using SKIP PARSER ERRORS.

• The SKIP ALL ERRORS option is inclusive of the SKIP PARSER ERRORS, SKIP DUPLICATE KEY ERRORS and SKIP CONSTRAINT ERRORS options, i.e., specifying the SKIP ALL ERRORS option in a LOAD DATA query applies the behavior of the other three options.

• The TERMINATED BY clause allows you to define field, column, and line delimiters so that the input data is interpreted and read correctly. For example, use FIELDS TERMINATED BYclause to load a CSV file where the fields are delimited by commas. Additionally, use the LINES TERMINATED BY '\r\n' clause if the lines in the CSV file are terminated by carriage return/newline pairs.

• The ENCLOSED BY or equivalent OPTIONALLY ENCLOSED BY clause allows you to specify a string that encloses the field values. For example, use the ENCLOSED BY '"' clause to load a CSV file where the fields are enclosed within double quotation. Note that LOAD DATA will still load a field value even if it is not enclosed.

• The ESCAPED BY clause allows you to specify the escape character. For example, if the input data contains special character(s), you may need to escape those characters to avoid misinterpretation. Also, you may need to redefine the default escape character to load a data set that contains the said character.

• Many characters can be an escape. If the FIELDS ESCAPED BY clause is empty, the character escape sequence will do nothing.

• The STARTING BY clause allows you to load only those lines of data that include a specified string (or prefix). While loading data, the STARTING BY clause skips the specified prefix and anything before it. It also skips the lines that do not contain the specified prefix.

  If no FIELDS or LINES clause is specified, then SingleStore uses the following defaults:

  SQL

  Copy

  FIELDS TERMINATED BY '\t'
  ENCLOSED BY ''
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
  STARTING BY ''

• The TRAILING NULLCOLS clause allows the input file to contain rows having fewer than the number of columns in the table. These missing fields must be trailing columns in the row; they are inserted as NULL values in the table. See Using the TRAILING NULLCOLS Clause.

• The NULL DEFINED BY clause inserts NULL field values in the table for fields in the input file having the value string_to_insert_as_null. The OPTIONALLY ENCLOSED option ensures that a quoted field is also treated as NULL, not an empty string. Refer to Using the NULL DEFINED BY Clause for more information.

  Note: If the string value 'NULL' is passed to a number-type column (for example, DECIMAL), it is parsed as a string and converted to 0. To insert NULL values instead, use the NULL DEFINED BY 'NULL' OPTIONALLY ENCLOSED clause. You can use the ENCLOSED BY clause in conjunction to specify the string that encloses the NULL values.

• The IGNORE <number> LINES clause ignores the specified lines from the beginning of the input file. For example, use IGNORE 1 LINES to skip the header line that contains the column names.

LOAD DATA from an AWS S3 Source

CSV files that are stored in an AWS S3 bucket can be loaded via a LOAD DATA query without a pipeline.

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>;

Examples

LOAD DATA INFILE 'foo.tsv'
  INTO TABLE foo (fourth, third, second, first);

LOAD DATA INFILE 'foo.tsv'
  INTO TABLE foo (bar, @, @, baz);

LOAD DATA INFILE 'foo.csv'
  INTO TABLE foo
  COLUMNS TERMINATED BY ',';

LOAD DATA INFILE 'foo.csv'  INTO TABLE foo  FIELDS TERMINATED BY ','  LINES TERMINATED BY '\r\n';

LOAD DATA INFILE 'foo.oddformat'
  INTO TABLE foo
  COLUMNS TERMINATED BY '|||';

LOAD DATA INFILE "[0-9]*.csv"
INTO TABLE cust(ID,NAME,ORDERS);

LOAD DATA INFILE "????.csv"
INTO TABLE cust(ID,NAME,ORDERS);

LOAD DATA INFILE "[!0-9]*.csv"
INTO TABLE cust(ID,NAME,ORDERS);

1,2,3
4,5
6

CREATE TABLE foo(a INT, b INT, c INT);

LOAD DATA INFILE 'numbers.csv' INTO TABLE foo COLUMNS TERMINATED BY ',' TRAILING NULLCOLS;

SELECT * FROM foo;

+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
|    4 |    5 | NULL |
|    6 | NULL | NULL |
+------+------+------+

cat data.csv

DTB,'',25
SPD,,40

SELECT * FROM stockN;

+------+-------------+-------+
| ID   | City        | Count |
+------+-------------+-------+
| XCN  | new york    |    45 |
| ZDF  | washington  |    20 |
| XCN  | chicago     |    32 |
+------+-------------+-------+

LOAD DATA INFILE '/data.csv'
INTO TABLE stockN
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
NULL DEFINED BY '';

SELECT * FROM stockN;

+------+-------------+-------+
| ID   | City        | Count |
+------+-------------+-------+
| XCN  | new york    |    45 |
| ZDF  | washington  |    20 |
| XCN  | chicago     |    32 |
| DTB  |             |    25 |
| SPD  | NULL        |    40 |
+------+-------------+-------+

LOAD DATA INFILE '/data.csv'
INTO TABLE stockN
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
NULL DEFINED BY '' OPTIONALLY ENCLOSED;

SELECT * FROM stockN;

+------+-------------+-------+
| ID   | City        | Count |
+------+-------------+-------+
| XCN  | new york    |    45 |
| ZDF  | washington  |    20 |
| XCN  | chicago     |    32 |
| DTB  | NULL        |    25 |
| SPD  | NULL        |    40 |
+------+-------------+-------+

LOAD DATA INFILE '/tmp/data.txt' INTO City IGNORE 1 LINES;

GALE\, ADAM, Brooklyn
FLETCHER\, RON, New York
WAKEFIELD\, CLARA, DC

DESC loadEsc;

+-------+-------------+------+------+---------+-------+
| Field | Type        | Null | Key  | Default | Extra |
+-------+-------------+------+------+---------+-------+
| Name  | varchar(40) | YES  |      | NULL    |       |
| City  | varchar(40) | YES  |      | NULL    |       |
+-------+-------------+------+------+---------+-------+

LOAD DATA INFILE '/contacts.csv'
INTO TABLE loadEsc COLUMNS TERMINATED BY ',' ESCAPED BY '\\' ;
SELECT * FROM loadEsc;

+-------------------+-----------+
| Name              | City      |
+-------------------+-----------+
| GALE, ADAM        |  Brooklyn |
| FLETCHER, RON     |  New York |
| WAKEFIELD, CLARA  |  DC       |
+-------------------+-----------+

GALE\, ADAM\, Brooklyn
FLETCHER\, RON, New York
WAKEFIELD\, CLARA, DC

LOAD DATA INFILE '/contacts.csv'
INTO TABLE loadEsc COLUMNS TERMINATED BY ',' ESCAPED BY '\\' ;

cat stockUpd.txt

###1,"xcg",
3,"dfg"
new product###4,"rfk",5

LOAD DATA INFILE 'stockUpd.txt'
INTO TABLE stock
  FIELDS TERMINATED BY ','
  LINES STARTING BY '###';

SELECT * FROM stock;

+----+------+----------+
| ID | Code | Quantity |
+----+------+----------+
|  1 |  xcg |       10 |
|  4 |  rfk |        5 |
+----+------+----------+

LOAD DATA INFILE 'foo.oddformat'
  INTO TABLE foo (bar, baz)
  WHERE bar = 5;

10-1-2016,1
4-15-2016,2
1-10-2017,3
4-10-2017,4

CREATE TABLE foo (EventDate date, EventId int);

LOAD DATA INFILE 'date_event.csv'
  INTO TABLE foo
  FIELDS TERMINATED BY ','
  (@EventDate, EventId)
  SET EventDate = STR_TO_DATE(@EventDate, '%m-%d-%Y')
  WHERE ABS(MONTHS_BETWEEN(EventDate, date('2016-10-15'))) < 3;

SELECT * FROM t;

+------------+---------+
| EventDate  | EventId |
+------------+---------+
| 2016-10-01 |       1 |
| 2017-01-10 |       3 |
+------------+---------+

CREATE TABLE cust(name VARCHAR(32), id INT(11), orders INT(11), SORT KEY(id), UNIQUE KEY(id) USING HASH, SHARD KEY(id));

Chris,7214,6
Elen,8301,4
Adam,3412,5
Rachel,9125,2
Susan,8301,7
George,3412,9

LOAD DATA INFILE '/order_files/orders.csv' REPLACE INTO TABLE cust FIELDS TERMINATED BY ',';

SELECT * FROM cust ORDER BY name;

+--------+------+--------+
| name   | id   | orders |
+--------+------+--------+
| Chris  | 7214 |      6 |
| George | 3412 |      9 |
| Rachel | 9125 |      2 |
| Susan  | 8301 |      7 |
+--------+------+--------+

Updating Duplicate Key Data

The following examples will show how to use the VALUES() function and a SELECT statement to update data when there are duplicate keys.

CREATE TABLE orders(comp_name VARCHAR(32), comp_id INT(11), total_orders INT(11), 
  SORT KEY(comp_id), UNIQUE KEY(comp_id) USING HASH, SHARD KEY(comp_id));

INSERT INTO orders VALUES ('Feedfire',5246146,4),
('Gabvine',4917885,8),('Devbug',5679096,12),
('Zoomzone',6273216,0),('Browsecat',9803299,2),
('Gabvine',4917885,2),('Devbug',5679096,7),
('Feednation',7823499,4)
ON DUPLICATE KEY UPDATE total_orders = VALUES(total_orders) + total_orders;

SELECT * FROM orders;

+-----------+---------+--------------+
|comp_name  | comp_id | total_orders |
+-----------+---------+--------------+
|Devbug	    | 5679096 |	          19 |
|Feednation | 7823499 |	           4 |
|Browsecat  | 9803299 |            2 |
|Zoomzone   | 6273216 |	           0 |
|Gabvine    | 4917885 |	          10 |
|Feedfire   | 5246146 |	           4 |
+-----------+---------+--------------+

CREATE TABLE new_orders(comp_name VARCHAR(32), comp_id INT(11), total_orders INT(11), 
  SORT KEY(comp_id), UNIQUE KEY(comp_id) USING HASH, SHARD KEY(comp_id));

INSERT INTO new_orders VALUES('Skynoodle',9727555,4),
('Skynoodle',9727555,6),
('Tagchat',7124266,5),
('Zoomzone',6273216,0),
('Devpulse',6726155,1),
('Browsecat',9803299,3),
ON DUPLICATE KEY UPDATE total_orders = VALUES(total_orders) + total_orders;

SELECT * FROM new_orders;

+-----------+---------+--------------+ 
|comp_name  | comp_id | total_orders |
+-----------+---------+--------------+ 
|Browsecat  | 9803299 |             3| 
|Devpulse   |6726155  |             1|
|Zoomzone   |6273216  |             0|
|Tagchat    |7124266  |             5|
|Skynoodle  |9727555  |            10|
+-----------+---------+--------------+

INSERT INTO orders (comp_name, comp_id, total_orders)
SELECT * FROM new_orders
ON DUPLICATE KEY DELETE WHEN VALUES(total_orders) = 0 
  ELSE UPDATE comp_name = VALUES(comp_name), 
  total_orders = VALUES(total_orders); 

Loading a Fixed Length File

This example demonstrates how to load the contents of the file fixed_length.csv, whose contents are shown below.

APE602020-06-01
TR 252019-08-07
HSW8 2019-10-11
YTR122020-09-02

LOAD DATA inserts each extracted row from fixed_length.csv into the table foo. Define the table as follows:

CREATE TABLE foo(a CHAR(3), b INT, c DATETIME);

Run the LOAD DATA statement:

LOAD DATA INFILE '/fixed_length.csv'
  INTO TABLE foo (@current_row)
  SET a = TRIM(SUBSTR(@current_row,1,3)),
      b = TRIM(SUBSTR(@current_row,4,2)),
      c = TRIM(SUBSTR(@current_row,6,10));

SUBSTR() extracts a substring from a string and TRIM() removes the padding (spaces in this case) from the beginning and the ending of a string. For example, after the LOAD DATA statement extracts the line HSW8 2019-10-11 in fixed_length.csv, it does the following to set b: * It extracts, from HSW8 2019-10-11, the substring starting at position 4 having a length of 2. The resulting substring is 8. * It removes the leading whitespace from 8 to yield 8.

Retrieve the data from foo:

SELECT * from foo ORDER BY a;

+------+------+---------------------+
| a    | b    | c                   |
+------+------+---------------------+
| APE  |   60 | 2020-06-01 00:00:00 |
| HSW  |    8 | 2019-10-11 00:00:00 |
| TR   |   25 | 2019-08-07 00:00:00 |
| YTR  |   12 | 2020-09-02 00:00:00 |
+------+------+---------------------+

Loading Data using Hex Field Terminator Syntax

Loading data into a table can be performed using a hexadecimal field terminator.

CREATE TABLE <table name>(a int, b int);

LOAD DATA infile "/tmp/<file name>.csv" 
INTO TABLE test (a, b) fields terminated by 0x2c;

SELECT * FROM <table name>;

**
+------+------+
|  a   | b    |
+------+------+
|    1 |    2 |
+------+------+

echo 1. 2 > hex_test.csv

hexdump -C hex_test.csv

00000000  31 2e 20 32 0a                            |1. 2.|
00000005

Syntax

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 ...]}

Semantics

Error Logging and Error Handling are discussed at the end of this topic.

Extract specified subvalues from each JSON value in file_name. Assign them to specified columns of a new row in tbl_name, or to variables used for a column assignment in a SET clause. If a specified subvalue can’t be found in an input JSON, assign the DEFAULT clause literal instead. Discard rows which don’t match the WHERE clause.

To specify the compression type of an input file, use the COMPRESSION clause. See Handling Data Compression for more information.

The file named by file_name must consist of concatenated UTF-8 encoded JSON values, optionally separated by whitespace. Newline-delimited JSON is accepted, for example.

Non-standard JSON values like NaN, Infinity, and -Infinity must not occur in file_name.

If file_name ends in .gz or .lz4, it will be decompressed.

JSON LOAD DATA supports a subset of the error recovery options allowed by CSV LOAD DATA. Their behavior is as described under CSV LOAD DATA.

Like CSV LOAD DATA, JSON LOAD DATA allows you to use globbing to load data from multiple files. See the CSV Examples

Writing to multiple databases in a transaction is not supported.

JSON LOAD DATA Examples

To use an ENCLOSED BY <char> as a terminating field, a TERMINATED BY clause is needed. For clarity, instances of an ENCLOSED BY <char> appearing within a field value can be duplicated, and they will be understood as a singular occurrence of the character.

If an ENCLOSED BY "" is used, quotes are treated as follows:

• "The ""NEW"" employee"  → The "NEW" employee

• The "NEW" employee → The "NEW" employee

• The ""NEW"" employee → The ""NEW"" employee

Example 1

If example.json consists of:

{"a":{"b":1}, "c":null}
{"a":{"b":2}, "d":null}

Then it can be loaded as follows:

CREATE TABLE t(a INT);

LOAD DATA LOCAL INFILE "example.json" INTO TABLE t(a <- a::b) FORMAT JSON;

SELECT * FROM t;

+------+
| a    |
+------+
|    1 |
|    2 |
+------+

Example 2

If example2.json consists of:

{"b":true, "s":"A\u00AE\u0022A", "n":-1.4820790816978637e-25, "a":[1,2], "o":{"subobject":1}}
{"b":false}
"hello"

Then we can perform a more complicated LOAD DATA:

CREATE TABLE t(b bool NOT NULL, s TEXT, n DOUBLE, a INT, o JSON NOT NULL, whole longblob);

LOAD DATA LOCAL INFILE "example2.json" INTO TABLE t FORMAT JSON(
  b <- b default true,
  s <- s default NULL,
  n <- n default NULL,
  @avar <- a default NULL,
  o <- o default '{"subobject":"replaced"}',
  whole <- %)
  SET a = json_extract_double(@avar, 1)
  WHERE b = true;

SELECT * FROM t;

+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
| b | s     | n                       | a    | o                        | whole                                                                                         |
+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+
| 1 | A®"A  | -1.4820790816978637e-25 |    2 | {"subobject":1}          | {"b":true, "s":"A\u00AE\u0022A", "n":-1.4820790816978637e-25, "a":[1,2], "o":{"subobject":1}} |
| 1 | NULL  |                    NULL | NULL | {"subobject":"replaced"} | hello                                                                                         |
+---+-------+-------------------------+------+--------------------------+-----------------------------------------------------------------------------------------------+

There are several things to note in the example above:

• true was converted to "1" for columns b, but not for column whole. "1" was further converted to the BOOL value 1.

• The escapes"\u00AE" and "\u0022" were converted to UTF-8 for column s, but not for column whole. Note that whole would have become invalid JSON if we had translated "\u0022".

• The second row was discarded because it failed to match the WHERE clause.

• None of the paths in subvalue_mapping could be found in the third row, so DEFAULT literals like '{"subobject":"replaced"}' were assigned instead.

• We assigned a to an intermediate variable so that we could extract an array element in the SET clause.

• The top-level JSON values in example2.json were not all JSON objects. "hello" is a valid top-level JSON value.

CREATE TABLE employees(emp_id int, data JSON);

csv file contents

emp_id,data
159,"{""name"": ""Damien Karras"", ""age"": 38, ""city"": ""New York""}"

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    IGNORE 1 LINES;

SELECT * FROM employees;

+--------+-----------------------------------------------------+
| emp_id | data                                                |
+--------+-----------------------------------------------------+
|    159 | {"age":38,"city":"New York","name":"Damien Karras"} |
+--------+-----------------------------------------------------+

csv file contents

emp_id,data
298,"{\"name\": \"Bill Denbrough\", \"age\": 25, \"city\": \"Bangor\"}"

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
    INTO TABLE employees
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
    IGNORE 1 LINES;

SELECT * FROM employees;

+--------+-----------------------------------------------------+
| emp_id | data                                                |
+--------+-----------------------------------------------------+
|    298 | {"age":25,"city":"Bangor","name":"Bill Denbrough"}  |
|    159 | {"age":38,"city":"New York","name":"Damien Karras"} |
+--------+-----------------------------------------------------+

csv file contents

emp_id,data
410,"{"name": "Annie Wilkes", "age": 45, "city":"Silver Creek"}"

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
    FIELDS TERMINATED BY ','
    ENCLOSED BY '{'
    IGNORE 1 LINES;

ERROR 1262 (01000): Leaf Error (127.0.0.1:3307): Row 1 was truncated; it contained more data than there were input columns

csv file contents

emp_id,data
089,{"name": "Wilbur Whateley","age": 62,"city": "Dunwich"}

LOAD DATA INFILE '/tmp/<file_name>.csv' INTO TABLE employees
    FIELDS TERMINATED BY ','
    ENCLOSED BY '{'
    IGNORE 1 LINES;

SELECT * FROM employees;

+--------+------------------------------------------------------+
| emp_id | data                                                 |
+--------+------------------------------------------------------+
|    298 | {"age":25,"city":"Bangor","name":"Bill Denbrough"}   |
|    159 | {"age":38,"city":"New York","name":"Damien Karras"}  |
|     89 | {"age":62,"city":"Dunwich","name":"Wilbur Whateley"} |
+--------+------------------------------------------------------+

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>;

Syntax

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 ...]}

Loading BSON Data from a File

The following example restores a MongoDB® backup into SingleStore.

This example uses the following sample data set.

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 } }

Create a binary export of the MongoDB® data using the mongodump tool:

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

This command creates a bsonExport.bson file in the target output directory.

Create a table in your SingleStore database to store the BSON data:

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 the bsonExport.bson file into SingleStore using the following command:

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}');

The BSON data has been ingested and is now stored in your SingleStore database.

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} |
+------+--------------------------+

Syntax for LOAD DATA Local Infile

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 ...]}

See the associated GitHub repo.

Syntax for LOAD DATA AWS S3 Source

Avro-formatted data stored in an AWS S3 bucket can use a LOAD DATA query without a pipeline. This streamlines the process of loading cloud-stored data into tables.

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 AVRO;

This data can also be loaded from S3 with a connection link. Refer to CREATE LINK for more information on connection links.

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

Semantics

Error Logging and Error Handling are discussed at the end of this topic.

LOAD DATA for Avro does not support file name globbing (for example: LOAD DATA INFILE '/data/nfs/gp1/*.avro). LOAD DATA for Avro only supports loading a single file per statement.

Extract specified subvalues from each Avro value in file_name. Assign them to specified columns of a new row in tbl_name, or to variables used for a column assignment in a SET clause. Discard rows which don’t match the WHERE clause.

To specify the compression type of an input file, use the COMPRESSION clause. See Handling Data Compression for more information.

Avro LOAD DATA expects Avro data in one of two sub-formats, depending on the SCHEMA clause.

If no SCHEMA clause is provided, file_name must name an Avro Object Container File as described in version 1.8.2 of the Avro specification. In addition, the following restrictions hold:

• The compression codec of the file must be null.

• Array and map values must not have more than 16384 elements.

• The type name of a record must not be used in a symbolic reference to previously defined name in any of its fields. It may still be used in a symbolic reference outside the record definition, however.

  For example, self-referential schemas like the following are rejected by LOAD DATA:

  JSON

  Copy

  {
    "type": "record",
    "name": "PseudoLinkedList",
    "fields" : [{"name": "value", "type": "long"},
                {"name": "next", "type": ["null", "PseudoLinkedList"]}]
  }

If a SCHEMA clause is provided, the file must be a raw stream consisting of only the concatenated binary encodings of instances of avro_schema. avro_schema must be a SQL string containing a JSON Avro schema. The restrictions on Object Container Files also apply to raw stream files.

All optional Avro schema attributes except the namespace attribute are ignored. Notably, logicalType attributes are ignored.

If file_name ends in .gz or .lz4, it will be decompressed.

Avro LOAD DATA supports a subset of the error recovery options allowed by CSV LOAD DATA. Their behavior is as described under CSV LOAD DATA.

Writing to multiple databases in a transaction is not supported.

The SCHEMA REGISTRY {"IP" | "Hostname"} option allows LOAD DATA to pull the schema from a schema registry. For more information, see the Avro Schema Evolution With Pipelines topic.

[
    "int",
    { "type" : "record",
      "name" : "a",
      "namespace" : "n",
      "fields" : [{ "name" : "f1",
                    "type" : "int" }]
    }
]

Avro LOAD DATA Examples

Example 1

Consider an Avro Object Container Fileexample.avro with the following schema:

{
  "type": "record",
  "name": "data",
  "fields": [{ "name": "id", "type": "long"},
             { "name": "payload", "type": [ "null",
                                            "string" ]}]
}

example.avro contains three Avro values whose JSON encodings are:

{"id":1,"payload":{"string":"first"}}
{"id":1,"payload":{"string":"second"}}
{"id":1,"payload":null}

example.avro can be loaded as follows:

CREATE TABLE t(payload TEXT, input_record JSON);

LOAD DATA LOCAL INFILE "example.avro"
  INTO TABLE t
  FORMAT AVRO
  ( payload <- %::payload::string,
  input_record <- % );

SELECT * FROM t;

+---------+----------------------------------------+
| payload | input_record                           |
+---------+----------------------------------------+
| first   | {"id":1,"payload":{"string":"first"}}  |
| second  | {"id":1,"payload":{"string":"second"}} |
| NULL    | {"id":1,"payload":null}                |
+---------+----------------------------------------+

LOAD DATA was able to parse example.avro because Avro Object Container Files have a header which contains their schema.

Example 2

Consider a file named example.raw_avro, with the same values as example.avro from Example 1 but in the raw stream format. That is, example.raw_avro consists of the binary encoded values and nothing else. We add a SCHEMA clause to tell LOAD DATA to expect a raw stream with the provided schema:

CREATE TABLE t(payload TEXT, input_record JSON);
LOAD DATA LOCAL INFILE "example.raw_avro"
  INTO TABLE t
  FORMAT AVRO
  ( payload <- %::payload::string,
  input_record <- % )
  schema
  '{
    "type": "record",
    "name": "data",
    "fields": [{ "name": "id", "type": "long"},
      { "name": "payload", "type": [ "null", "string" ]}]
  }';

SELECT * FROM t;

+---------+----------------------------------------+
| payload | input_record                           |
+---------+----------------------------------------+
| first   | {"id":1,"payload":{"string":"first"}}  |
| second  | {"id":1,"payload":{"string":"second"}} |
| NULL    | {"id":1,"payload":null}                |
+---------+----------------------------------------+

Example 3

Consider an Object Container Fileexample3.avro with a more complicated payload than Example 1. We illustrate extracting values from nested unions and records, and also indirectly extracting elements of nested maps and arrays.

{ "type": "record",
  "namespace": "ns",
  "name": "data",
  "fields": [
      { "name": "id", "type": "long" },
      { "name": "payload", "type":
        [ "null",
          { "type": "record",
            "name": "payload_record",
            "namespace": "ns",
            "fields": [
                { "name": "f_bytes", "type": "bytes"},
                { "name": "f_string", "type": "string"},
                { "name": "f_map", "type":
                  { "type": "map",
                    "values": { "type": "array",
                                "items": "int" }}}
            ]
          }
        ]
      }
  ]
}

The raw JSON encoding of the contents of this file can be seen in column c_whole_raw after the following LOAD DATA:

CREATE TABLE t (
  c_id bigint,
  c_bytes longblob,
  c_string longblob,
  c_array_second int,
  c_whole_raw longblob,
  c_whole_json json
  );

LOAD DATA INFILE "example3.avro"
  INTO TABLE t
  FORMAT AVRO
  ( c_id <- %::id,
  c_bytes <- %::payload::`ns.payload_record`::f_bytes,
  c_string <- %::payload::`ns.payload_record`::f_string,
  @v_map <- %::payload::`ns.payload_record`::f_map,
  c_whole_raw <- %,
  c_whole_json <- %)
  SET c_array_second = JSON_EXTRACT_JSON(@v_map, "a", 1);

SELECT * FROM t;

*** 1. row ***
          c_id: 1
       c_bytes: NULL
      c_string: NULL
c_array_second: NULL
   c_whole_raw: {"id":1,"payload":null}
  c_whole_json: {"id":1,"payload":null}
*** 2. row ***
          c_id: 2
       c_bytes: "A
      c_string: "A
c_array_second: 2
   c_whole_raw: {"id":2,"payload":{"ns.payload_record":{"f_bytes":"\u0022\u0041","f_string":"\"A","f_map":{"a":[1,2]}}}}
  c_whole_json: {"id":2,"payload":{"ns.payload_record":{"f_bytes":"\"A","f_map":{"a":[1,2]},"f_string":"\"A"}}}

There are several things to note:

• We attempted to extract subvalues of the payload_record branch of the union-type payload field. Since that wasn’t the selected member of the union in record 1, LOAD DATA assigned NULL to c_bytes and @v_map.

• We assigned the JSON encoding of f_map to @v_map and then performed JSON map and array lookups in the SET clause to ultimately extract 2.

• f_string and f_bytes had the same contents, but we can see how their different Avro types affected their JSON encodings and interacted with the SQL JSON type

  • The JSON encoding of the Avro string value f_string, as seen in c_whole_raw, encodes special characters like " as the escape sequence \".

  • The JSON encoding of the Avro bytes value f_bytes, as seen in c_whole_raw, encodes every byte with a JSON escape.

  • When converting the JSON encoding of record 2 to the SQL JSON type while assigning to c_whole_json, LOAD DATA normalized both representations of the byte sequence "A to \"A.

Syntax for LOAD DATA AWS S3 or Local File Source

Parquet-formatted data stored in an AWS S3 bucket or the local filesystem can be loaded via a LOAD DATA query without a pipeline. This streamlines the process of loading cloud-stored data into tables. Other LOAD DATA clauses (SET, WHERE, etc.) are supported (but not shown) in the following syntax examples.

For S3:

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;

This data can also be loaded from S3 by using a connection link. Refer to CREATE LINK for more information on. connection links.

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

For local file:

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

Syntax for LOAD DATA Local Infile

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

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

Arguments

• AUTO: This is the default setting, it tells LOAD DATA to identify the compression type from the input file’s extension.

• NONE: Specifies that the input file is uncompressed.

• LZ4: Specifies that the input file is compressed with LZ4 compression algorithm.

• GZIP: Specifies that the input file is compressed with GZIP compression algorithm.

Remarks

• If COMPRESSION is set to NONE, LZ4, or GZIP, LOAD DATA will not use the extension of the input file to determine the type of compression. For example, if you load a file test.gz and specify the COMPRESSION as NONE, then LOAD DATA will handle test.gz as an uncompressed file.

Default Error Handling

By default, LOAD DATA returns errors to the client application. Errors are returned one at a time. If it returns an error, no data will be inserted into the destination table.

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'

REPLACE Error Handling

SingleStore’s REPLACE behavior allows you to replace the existing rows with the new rows; only those rows that have the same value for a primary key or unique index as the input rows are replaced. In case of an error that arises due to duplicate key value, it first deletes the conflicting row in the destination that has the duplicate key value and then inserts the new row from the source file.

LOAD DATA inserts source file rows into the destination table in the order in which the rows appear in the source file. When REPLACE is specified, source files that contain duplicate unique or primary key values will be handled in the following way:

• If the destination table’s schema specifies a unique or primary key column, and

• The source file contains a row with the same primary or unique key value as the destination table, then

• The row in the destination table that has the same unique or primary key value as the row in the source file will be deleted and a new row from the source file that matches the primary key value will be inserted into the destination table.

Note: If the source file contains multiple rows with the same primary or unique key value as the destination table, then only the last row in the source file with the same primary or unique key value (as the destination table) replaces the existing row in the destination table.

Note: REPLACE cannot be combined with SKIP DUPLICATE KEY ERRORS. The default behavior of SingleStore is to throw an error for duplicate keys. However, both REPLACE and SKIP DUPLICATE KEY ERRORS does not throw a duplicate key error; REPLACE replaces the old row with the new row, while SKIP DUPLICATE KEY ERRORS discards the new row and retains the old row.

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';

SKIP ... ERRORS Error Handling

SingleStore’s SKIP ... ERRORS behavior allows you to specify an error scenario that, when encountered, discards an offending row. Three kinds of error scenarios can be skipped:

• SKIP DUPLICATE KEY ERRORS: Any row in the source data that contains a duplicate unique or primary key will be discarded. If the row contains invalid data other than a duplicate key, an error will be generated. See SKIP DUPLICATE KEY ERRORS below.

• SKIP CONSTRAINT ERRORS: Inclusive of SKIP DUPLICATE KEY ERRORS. If a row violates a column’s NOT NULL constraint, or the row contains invalid JSON or Geospatial values, the row will be discarded. If the row contains invalid data outside the scope of constraint or invalid value errors, an error will be generated. See SKIP CONSTRAINT ERRORS below.

• SKIP ALL ERRORS: Inclusive of SKIP DUPLICATE KEY ERRORS and SKIP CONSTRAINT ERRORS. Also includes any parsing errors in the row caused by issues such as an invalid number of fields. See SKIP ALL ERRORS below.

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 |
+-----------------------+--------------------------------------------+--------------------------------------------------------------+

IGNORE Error Handling

SingleStore’s IGNORE behavior is identical to MySQL’s IGNORE behavior, and exists only to support backwards compatibility with applications written for MySQL. IGNORE either discards malformed rows, discards extra fields, or replaces invalid values with default data type values. In addition, if an inserted row would have produced an error if IGNORE was not specified, it will be converted to a warning instead.

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;