SSC-FDM-BQ0005

Parsing the CSV header is not supported in external tables, columns must be renamed to match the original names

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Currently, Snowflake external tables do not support parsing the header of CSV files. When a external table with no explicit column list and CSV file format is found, SnowConvert will produce the SKIP_HEADER file format option to avoid runtime errors, however, this will cause the table column names to have the autogenerated names c1, c2, ..., cN.

This FDM is generated to notify that the header can not be parsed and that manually renaming the columns is necessary to preserve the original column names.

Code Example

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
OPTIONS(
  FORMAT='CSV',
  URIS=['gs://sc_external_table_bucket/folder_with_csv/Employees.csv']
);

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE OR REPLACE TEMPORARY FILE FORMAT SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT
TYPE = CSV
SKIP_HEADER = 1;

CREATE OR REPLACE EXTERNAL TABLE test.my_external_table_csv
--** SSC-FDM-BQ0005 - PARSING THE CSV HEADER IS NOT SUPPORTED IN EXTERNAL TABLES, COLUMNS MUST BE RENAMED TO MATCH THE ORIGINAL NAMES **
USING TEMPLATE (
SELECT
  ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME', COLUMN_NAME, 'TYPE', TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION', EXPRESSION))
FROM
  TABLE(INFER_SCHEMA(LOCATION => '@EXTERNAL_STAGE/folder_with_csv/Employees.csv', FILE_FORMAT => 'SC_TEST_MY_EXTERNAL_TABLE_CSV_FORMAT'))
)
!!!RESOLVE EWI!!! /*** SSC-EWI-0032 - 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

• Please rename the table columns to their original names from the file to complete the transformation

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