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.

Sources can include:

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

Supported formats include:

• CSV
• PARQUET
• NDJSON

BNF diagrams

Column list

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

Arguments

Additional information

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.

Examples

MAP examples

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'

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.

BULK INSERT using TRANSFORM with TUPLE() function

BULK INSERT statement with TUPLE()

BULK INSERT INTO tuple-demo(
    _id,
    time_col,
    stringset_col,
    ideset_col,
    stringsetq_col,
    idesetq_col
  )
  MAP (
    0 STRING,
    1 TIMESTAMP,
    2 STRINGSET,
    3 IDSET
  )
  TRANSFORM(
    @0,
    @1,
    @2,
    @3,
    TUPLE(@1,@2),
    TUPLE(@1,@3)
  )
  FROM x'
    A,2013-07-15T01:18:46Z,stringset1, 1
    B,2014-07-15T01:18:46Z,stringset2, 2
  '
  WITH
    BATCHSIZE 10000
    format 'CSV'
    input 'INLINE';

Further information

• BULK INSERT using CSV file
• BULK INSERT using NDJSON data source
• BULK INSERT using PARQUET data source