10. IMPORT#

10.1. Synopsis#

IMPORT INTO <table_name> FILES ( <import_option> [, ...] );

import_option ::= uris[=]['<input_path>']
                | format[=]{ CSV | TSV | PARQUET | <custom_format_definition> }
                | deduplicate[=]{ DISABLED | DROP | UPSERT }
                | push_mode[=]{ FAST | ASYNC }
                | max_errors[=]<count>

custom_format_definition ::= [ <format_option> [, ...] ]

format_option ::= delimiter[=]<quoted_character>
                | quote_character[=]<quoted_character>
                | first_row_is_header[={true|false}]

quoted_character ::= '<single_character>'

The uris option is mandatory. The other options are optional. You can put the options in any order. Specify each option no more than once. The equal sign (=) is optional.

10.2. Description#

Use IMPORT to load one file into an existing time series table. The client that runs the query reads the file. The QuasarDB servers do not read the file.

QuasarDB 3.14.3 and later support these features:

  • Import from an Apache Parquet file.

  • Match named file columns to table columns. The columns can be in a different order.

For CSV and TSV files, the default format includes a header row. For Parquet files, IMPORT reads the column names from the Parquet schema.

The file must contain $timestamp and all table columns. The column names must match the table schema. The file can put named columns in any order.

10.3. Parameters#

table_name

The name of an existing time series table.

input_path

The path to the input file on the client computer. The path can be absolute or relative to the client working directory. Enclose the path in single quotation marks (').

Specify exactly one path. One IMPORT statement cannot read multiple files.

format

The input format. The default value is CSV.

CSV

A comma-delimited text file with a header row.

TSV

A tab-delimited text file with a header row.

PARQUET

An Apache Parquet file. This value is available in QuasarDB 3.14.3 and later.

custom_format_definition

A delimited text format. It uses the CSV defaults. Specify one or more of these options:

delimiter

The one-character field delimiter. The default value is ,.

quote_character

The one-character quote mark. The default value is ".

first_row_is_header

Specify true if the first row contains column names. The default value is true. You can omit =true.

If you specify false, put $timestamp first. Then, put the table columns in table-schema order.

IMPORT removes leading and trailing spaces and tabs from text fields. You cannot configure this operation.

deduplicate

The action to take when the imported data contains a row that is already in the table. QuasarDB compares all columns to identify a duplicate row.

DISABLED

Insert the new row. This action can create duplicate rows. This value is the default.

DROP

Keep the existing row. Discard the new row.

UPSERT

Replace the existing row with the new row.

push_mode

The method that QuasarDB uses to write the data.

FAST

Update the time series buckets directly. This value is the default.

ASYNC

Send the update to the server for asynchronous processing. The command can return before the server inserts the data.

max_errors

The maximum number of data errors that IMPORT accepts. The default value is 0.

If the error count does not exceed this value, IMPORT continues. For a value conversion error, it writes a null value in a non-timestamp column. If the error count exceeds this value, IMPORT stops and returns an error.

10.4. File schema requirements#

10.4.1. Files with column names#

CSV and TSV headers and Parquet schemas must meet these requirements:

  • The file contains one $timestamp column.

  • The file contains each table column.

  • The file does not contain an unknown column.

  • The number of file columns equals the number of table columns plus $timestamp.

The column order does not have to match the table schema.

10.4.2. Files without column names#

For a delimited file without a header, IMPORT uses this column order:

  1. $timestamp

  2. Each table column, in table-schema order

10.4.3. Parquet type conversions#

QuasarDB 3.14.3 and later support these Parquet-to-QuasarDB conversions:

Parquet data exposed through Arrow

QuasarDB column type

String or binary

INT64, DOUBLE, STRING, SYMBOL, BLOB, or TIMESTAMP

64-bit integer

INT64 or DOUBLE

Double

DOUBLE or INT64

Timestamp

TIMESTAMP

64-bit date

TIMESTAMP

IMPORT returns an error for an unsupported conversion. A null Parquet value becomes a null QuasarDB value. The $timestamp value cannot be null.

10.5. Limitations#

  • The destination must be an existing time series table.

  • One statement imports one file into one table.

  • The file must contain $timestamp and all table columns.

  • $timestamp cannot contain a null value.

  • Unsorted timestamps can reduce import performance.

10.6. Examples#

10.6.1. Import a CSV file#

The first row contains the column names:

$timestamp,i,d,s
2024-01-23T16:00:00.016759000,12,12.34,mkopoi
2024-01-23T16:00:00.016760000,23,23.45,snjksa

IMPORT INTO test FILES (
  format=CSV,
  uris=['import.csv']
);

10.6.2. Import a Parquet file#

This feature requires QuasarDB 3.14.3 or later:

IMPORT INTO test FILES (
  format=PARQUET,
  uris=['C:/data/import.parquet']
);

10.6.3. Import a custom delimited file#

IMPORT INTO test FILES (
  format=[delimiter=';', quote_character='"', first_row_is_header=true],
  uris=['import.txt']
);

10.6.4. Import a file without a header#

The file must put $timestamp first. The other fields must follow the table-schema order.

IMPORT INTO test FILES (
  format=[delimiter=';', first_row_is_header=false],
  uris=['import-no-header.txt']
);

10.6.5. Control duplicate rows and data errors#

IMPORT INTO test FILES (
  format=CSV,
  deduplicate=UPSERT,
  push_mode=FAST,
  max_errors=10,
  uris=['import.csv']
);