SSC-EWI-BQ0015

External table requires an external stage to access a external location, define and replace the EXTERNAL_STAGE placeholder

This EWI is deprecated; please refer to SSC-EWI-0032 for the latest version of this EWI.

Description

When transforming the CREATE EXTERNAL TABLE statement, SnowConvert will generate an EXTERNAL_STAGE placeholder that has to be replaced with the external stage created for connecting with the external location from Snowflake.

Please refer to the following guides to set up the necessary Storage Integration and External Stage in your Snowflake account:

• For external tables referencing Amazon S3

• For external tables referencing Google Cloud Storage

• For external tables referencing Azure Blob Storage

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE EXTERNAL TABLE test.Employees_test
(
  Employee_id INTEGER,
  Name STRING,
  Mail STRING,
  Position STRING,
  Salary INTEGER
)
OPTIONS(
  FORMAT='CSV',
  SKIP_LEADING_ROWS=1,
  URIS=['gs://sc_external_table_bucket/folder_with_csv/Employees.csv']
);

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE EXTERNAL TABLE test.Employees_test
(
  Employee_id INTEGER AS CAST(GET_IGNORE_CASE($1, 'c1') AS INTEGER),
  Name STRING AS CAST(GET_IGNORE_CASE($1, 'c2') AS STRING),
  Mail STRING AS CAST(GET_IGNORE_CASE($1, 'c3') AS STRING),
  Position STRING AS CAST(GET_IGNORE_CASE($1, 'c4') AS STRING),
  Salary INTEGER AS CAST(GET_IGNORE_CASE($1, 'c5') AS INTEGER)
)
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0015 - EXTERNAL TABLE REQUIRES AN EXTERNAL STAGE TO ACCESS gs://sc_external_table_bucket, DEFINE AND REPLACE THE EXTERNAL_STAGE PLACEHOLDER ***/!!!
LOCATION = @EXTERNAL_STAGE
AUTO_REFRESH = false
PATTERN = 'folder_with_csv/Employees.csv'
FILE_FORMAT = (TYPE = CSV SKIP_HEADER =1);

Recommendations

• Set up your external connection in the Snowflake account and replace the EXTERNAL_STAGE placeholder to complete the transformation.

• If you need more support, you can email us at [email protected]