OPT_FILE_FORMAT_OPTIONS

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Microsoft Fabric, Google BigQuery, Databricks SQL

OPT_FILE_FORMAT_OPTIONS is a load option that overrides file formatting options in file loads.

Affected command per supported database management system:

Target DBMS	Command
Snowflake	COPY INTO
Amazon Redshift	COPY
Azure SQL Database	BULK INSERT
Azure Synapse SQL	COPY INTO
Azure Synapse DW	COPY INTO
Google BigQuery	LOAD DATA INTO
Databricks SQL	COPY INTO

See also:

Usage

OPTION

OPT_FILE_FORMAT_OPTIONS: sql_code

Notes

OPT_FILE_FORMAT_OPTIONS can be used with file loads.

OPT_FILE_FORMAT_OPTIONS is also supported in OVERRIDE_FILE_LOAD load steps where it will be resolved in place of the <file_format> variable.

Examples

Snowflake file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"'

File load executed in Snowflake:

SQL

COPY INTO ...
FROM ...
FILE_FORMAT=(TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"');

Amazon Redshift file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF

File load executed in Redshift:

SQL

COPY ...
FROM ...
TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF;

Azure SQL Database file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n'

File load executed in SQL database:

SQL

BULK INSERT ...
FROM ...
WITH ( ... ,
FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n');

Azure Synapse SQL file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2

Note that OPT_FILE_FORMAT_OPTIONS overrides the entire WITH clause so also the CREDENTIAL argument must be defined.

File load executed in Synapse:

SQL

COPY INTO ...
FROM ...
WITH (CREDENTIAL=(IDENTITY='Managed Identity'), FILE_TYPE = 'CSV', FIELDQUOTE = '"', FIELDTERMINATOR=',', COMPRESSION = 'Gzip', ENCODING = 'UTF8', FIRSTROW = 2);

Microsoft Fabric file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FILE_TYPE = 'CSV', CREDENTIAL=(IDENTITY='SOURCE_DATA_WITH_SIGNATURE', SECRET='<secret>'), FIELDQUOTE = '\"', FIELDTERMINATOR=';', ENCODING = 'UTF8', FIRSTROW = 1

Note that OPT_FILE_FORMAT_OPTIONS overrides the entire WITH clause so also the CREDENTIAL argument must be defined.

File load executed in Fabric:

SQL

COPY INTO ...
FROM ...
WITH (FILE_TYPE = 'CSV', CREDENTIAL=(IDENTITY='SOURCE_DATA_WITH_SIGNATURE', SECRET='<secret>'), FIELDQUOTE = '\"', FIELDTERMINATOR=';', ENCODING = 'UTF8', FIRSTROW = 1);

Google BigQuery file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8'

File load executed in BigQuery:

SQL

LOAD DATA INTO ...
FROM FILES(
  FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8', 
  uris=[...]
);

See OPT_LOAD_WITH_BQ_API to use bq load instead of LOAD DATA INTO.

Databricks SQL file load

File format set with OPT_FILE_FORMAT_OPTIONS:

SQL

FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';')

File load executed in Databricks:

SQL

COPY INTO ...
FROM ...
FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';');