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
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:
TYPE='CSV' SKIP_HEADER=0 FIELD_DELIMITER=';' COMPRESSION='AUTO' FIELD_OPTIONALLY_ENCLOSED_BY='"'
File load executed in Snowflake:
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:
TIMEFORMAT 'auto' DATEFORMAT 'auto' FORMAT CSV DELIMITER ';' COMPUPDATE OFF STATUPDATE OFF
File load executed in Redshift:
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:
FORMAT='CSV', CODEPAGE=65001, FIRSTROW=1, FIELDTERMINATOR=';', ROWTERMINATOR='\\r\\n'
File load executed in SQL database:
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:
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:
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:
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:
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:
FORMAT='CSV', SKIP_LEADING_ROWS=1, FIELD_DELIMITER=';', ALLOW_QUOTED_NEWLINES=TRUE, COMPRESSION='GZIP', ENCODING='UTF8'
File load executed in BigQuery:
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:
FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';')
File load executed in Databricks:
COPY INTO ...
FROM ...
FORMAT_OPTIONS('header'='false','quote'='\"', 'skipRows'='0', 'delimiter'=';');