OPT_STORAGE_INTEGRATION

Target database: Snowflake, Azure SQL Database, Azure Synapse SQL, BigQuery

OPT_STORAGE_INTEGRATION is a load option that can be used to define the use of a

Storage integration (Snowflake)
External data source (Azure SQL, Azure Synapse SQL)
External connection (BigQuery)

in a file load.

See also:

Usage

OPTION

OPT_STORAGE_INTEGRATION: sql_code

Notes

OPT_STORAGE_INTEGRATION can be used in file loads.

OPT_STORAGE_INTEGRATION is also supported in OVERRIDE_FILE_LOAD load steps where it will be resolved in place of the <storage_integration> (or <data_source>) variable.

Examples

Snowflake file load

Storage integration set with OPT_STORAGE_INTEGRATION:

OPTION

OPT_STORAGE_INTEGRATION: ade_taxi_data

File load executed in Snowflake:

SQL

COPY INTO ...
FROM ...
STORAGE_INTEGRATION = ade_taxi_data
FILE_FORMAT = (...);

Azure SQL Database file load

External data source set with OPT_STORAGE_INTEGRATION:

OPTION

OPT_STORAGE_INTEGRATION: ade_taxi_data

File load executed in Azure SQL Database:

SQL

BULK INSERT ...
FROM ...
WITH (DATA_SOURCE = ade_taxi_data, ...);

Azure Synapse SQL file load

Affects only in single file load where the parameter defines the EXTERNAL table from where the data is to be loaded

External data source set with OPT_STORAGE_INTEGRATION:

OPTION

OPT_STORAGE_INTEGRATION: ade_taxi_data

File load executed in Azure SQL Database:

SQL

INSERT INTO <target_schema>.<target_entity_name> (<column_definition>) 
SELECT <column_definition> FROM <target_schema>.ade_taxi_data;

BigQuery file load

External connection set with OPT_STORAGE_INTEGRATION:

OPTION

OPT_STORAGE_INTEGRATION: `ade_taxi_data`

File load executed in BigQuery:

CODE

LOAD DATA INTO ...
FROM FILES(...) 
WITH CONNECTION `ade_taxi_data`;