BULK INSERT statement

BULK INSERT is a SQL statement that:

• reads data from various sources,
• maps data locations and data types,
• performs optional lightweight data transformations,
• then loads the data to a table using INSERT statements

In some cases, the data will be pre-sorted by internal FeatureBase partition before being ingested by multiple concurrent routines.

Before you begin

Prepare your data source:

• file(s)
• URL(s)
• inline blob

The data source must be in one of the following formats:

• Comma separated value (CSV) format
• Lean about New Line Delimited JSON (NDJSON) format
• Learn about Apache ORC format
• Learn about Apache PARQUET format

BNF diagrams

Column list

Run SHOW TABLE <tablename> to learn the structure of a destination table

Map list

Transform clause

With clause

Syntax

BULK INSERT
  INTO
    table_name [(column_name,...)]
  MAP (position type_name,...)
  [TRANSFORM (expr,...)]
  FROM
    ['path/file_name' | 'URL' | x'records']
  WITH
    [
      [BATCHSIZE integer_literal]
      [ROWSLIMIT integer_literal]
      [INPUT ['path/file_name' | 'INLINE' | 'STREAM' |'URL']]
      [FORMAT
        ['CSV' [HEADER_ROW] [CSV_EMPTY_STRING_AS_NULL] [CSV_NULL_AS_NULL] [NULL_AS_EMPTY_SET]] |
        ['NDJSON' [ALLOW_MISSING_VALUES] [NULL_AS_EMPTY_SET]] |
        ['PARQUET' [NULL_AS_EMPTY_SET]] |
        ['ORC' [NULL_AS_EMPTY_SET]]
      ]
      ...
    ]

Arguments

Additional information

MAP and TRANSFORM clauses for SETQ data types

SETQ data types are handled differently in the MAP and TRANSFORM clauses:

• MAP the numeric identifier for your SETQ data type to an equivalent IDSET or STRINGSET data type
• Combine the MAP identified TIMESTAMP and SET data types together in the TRANSFORM clause as follows:

TUPLE(@<timestamp-map-value>,<@set-map-value>)

Transform additional

The TRANSFORM clause must include:

• SQL expressions that match those in the column_list, and
• specify data transformations using ordinal positions defined in the MAP clause

Any variables are evaluated during execution for each row.

TRANSFORM with TUPLE() function

When the TUPLE() function is used in a TRANSFORM clause, the following values are returned:

• Tuple() function

CSV Value Assignment

There are special assignments for certain literal values when inserting CSV data.

NDJSON Value Assignment

There are special assignments for certain literal values when inserting NDJSON data.

BULK INSERT examples

The following SQL examples demonstrate BULK INSERT using different clauses and data sources.

MAP and TRANSFORM example for SETQ data types

BULK INSERT INTO doctest (
  _id,
  idsetqcol,
  stringsetqcol,
  timestampcol)
MAP(
  0 ID,
  1 IDSET,
  2 STRINGSET,
  3 TIMESTAMP
  )
TRANSFORM(
    @0,
    TUPLE(@3,@1),
    TUPLE(@3,@2),
    @3
)
FROM x'004,456;567;678;789,this;is;the;first;row,2023-11-22T04:46:59Z'
WITH
    BATCHSIZE 1
    FORMAT 'CSV'
    input 'INLINE';

TRANSFORM examples

TRANSFORM (
    @0 + 10, -- offset the new _id value by 10
    @1,      -- pass through unchanged
    TUPLE(@3,@4),      -- create tuple to load time quantum fields
    CASE     -- clean up state names
        WHEN @2 = 'Texas' then 'TX' end
        WHEN @2 = 'California' then 'CA' end
        ELSE @2
    end
)

FROM examples

Using INLINE argument

The contents of an inline string literal are treated as a file and read line-by-line.

Single line string literal

'this is a single-line string literal'

Multi-line string literal

Multi line (prepend with x)

x'this
is
a
multi-line
string
literal'

Place closing quotation marks on the final line of any multi-line string literal.

Using INLINE with quotation marks

FROM clause quotation marks must be escaped before the BULK statement is run, even when CSV values are quoted.

BULK INSERT with read from CSV file

bulk replace
    into insert_test (_id, int1, string1, timestamp1)
    map (0 id, 1 int, 2 string)
    transform (@0, @1, @2, current_timestamp)
from
    '/dev/queries/insert_test.csv'
with
    format 'CSV'
    input 'FILE';

BULK INSERT with STREAM

Using input STREAM requires making an HTTP multipart form POST request. The argument to FROM may be '*' and all files attached to the request will be streamed in and ingested together. The fbsql CLI tool implements the multipart POST and you would use it as follows:

\file myfile.csv
\file myfile2.csv
\file myfile3.csv

bulk replace
  into insert_test (_id, int1, string1, timestamp1)
  map (0 id, 1 int, 2 string)
  transform (@0, @1, @2, current_timestamp)
  from
    '*'
  with
    format 'CSV'
    input 'STREAM';

This would ingest all three files in a single request.