Link Search Menu Expand Document


Copies data from S3 to firebolt table.

  • Topic ToC


[INTO] <table_name> [ <column_mapping> ] 
FROM <externalLocation>
[ WHERE <condition> ]
[ LIMIT <count> ]
[ OFFSET <start> ]
[ WITH <options> ]

    ( <column_name> [DEFAULT <default_value>] [ { $<source_column_index> | <source_column_name> } ] [, ...] )

    [ CREDENTIALS = ( <credentials> ) ] 
    [ PATTERN = <regex_pattern> ]
    [ TYPE = { **AUTO** | CSV | TSV | PARQUET } ]
    [ AUTO_CREATE = { **TRUE** | FALSE } ]
    [ ERROR_FILE = <externalLocation> ]
    [ ERROR_FILE_CREDENTIALS = <credentials> ]
    [ MAX_ERRORS_PER_FILE = { integer | '<percentage>' } ]
    [ <csv_options> ]

    { AWS_KEY_ID = '<aws_key_id>' AWS_SECRET_KEY = '<aws_secret_key>' }

    [ HEADER = { **FALSE** | TRUE  } ]
    [ DELIMITER = 'character' ]
    [ ESCAPE = 'character' ]
    [ NULL_STRING = 'string' ]
    [ EMPTY_FIELD_AS_NULL = { **TRUE** | FALSE } ]
    [ SKIP_BLANK_LINES = { **FALSE** | TRUE } ]
    [ DATE_FORMAT = <date_format> ]
    [ TIMESTAMP_FORMAT = <timestamp_format> ]


Parameter Description
<table_name> The name of the target table.
<column_mapping> Optional. Only available of target table exists. Specify mapping between src schema and target schema. With this feature one can choose for each column in the target table a column from src files by name or by index.
<column_name> The name of a target column in the table. If <source_column_index/name> not specified, the src columns will be mapped by this name.
<default_value> A value that replace each NULL value generated by the src. This value must be convertible to the target column datatype.
<source_column_index> The index of the src column that will be mapped (same index for all the files). This value range is [1,2^64-1]. It needs to follow the $ character.
<source_column_name> The name of the src column that will be mapped (same name for all the files).
<externalLocation> The path to an S3 location where the query result file or files are saved. If ends with / it interpreted as directory otherwise as single file. Folder example: s3://my_bucket/my_folder/. File example: s3://my_bucket/my_folder/my_file.
CREDENTIALS The Amazon S3 credentials for accessing the specified <externalLocation>. See CREDENTIALS.
PATTERN A regular expression pattern string (glob). For additional explenation visit:
TYPE Specify the file type Firebolt expects to ingest given the PATTERN. If not specified it will be auto detected by the suffix of a file. If a file referenced using PATTERN does not conform to the specified TYPE, an error occurs.
AUTO_CREATE Specify if the table can be automatically created by working alongside with automatic schema discovery. By default, automatic table creation is enabled (value: TRUE). If value is TRUE and the target table already exists, AUTO_CREATE is ignored.
ERROR_FILE Defines S3 location where rejected records will be stored. See <externalLocation> parameter definition above for format details. By default or if the specified path doesn’t exist, no error files will be created. If specified a child directory is created based on the time of load submission in the format YearMonthDay -HourMinuteSecond + QueryID (Ex. 20220330-173205//). In this folder, two types of files are written: 1) the reason (error) file: rejected_rows.csv. and the data (row) file: error_reasons.csv.
ERROR_FILE_CREDENTIALS The Amazon S3 credentials for accessing the specified <externalLocation> for error file creation. See CREDENTIALS.
MAX_ERRORS_PER_FILE Specify the maximum number of rejected rows per file. Can be integer or literal text in the format ‘integer%’ eg. ‘100%’. By default no errors are allowed (0). If ‘100%’ is given then all errors are allowed. If the threshold is passed an error will be shown.
HEADER Specify that the file contains a header line with the names of each column in the file. If set to true the first line will be interpreted as file schema column names.
DELIMITER Specify the delimiter character between fields. By default is ,.
QUOTE Specify the quote character. By default: DOUBLE_QUOTE ("). If specified SINGLE_QUOTE it means '
ESCAPE Specify the escape character. By default is the QUOTE character.
NULL_STRING Specify the null string. By default is empty which means no null string.
EMPTY_FIELD_AS_NULL Specify whether empty fields are treated as null.
SKIP_BLANK_LINES Specify whether to skip blank lines.
DATE_FORMAT Specify the date format for parsing text into date columns. Will apply only and for all columns that will be ingested to date columns. For supported formats see formats in TO_DATE
TIMESTAMP_FORMAT Specify the timestamp format for parsing text into timestamp columns. Will apply only and for all columns that will be ingested to timestamp columns. For supported formats see formats in TO_TIMESTAMP

notes: Non-existing columns: By default if a column does not exist in the source file it will produce nulls. For CSV format it applies to missing fields as well.

CSV: new line sequence will be \n or \n\r or \r.


COPY all files under a directory, including all columns in the files

COPY INTO FROM 's3://Bucket/directory/';

COPY with explicit list of columns

COPY INTO test_table (c1 DEFAULT 34, c3 $6) FROM 's3://Bucket/directory/' WITH

COPY without auto-schema discovery

COPY INTO test_table FROM 's3://Bucket/directory/' WITH