CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shruti
Shruti

Reputation: 135

Snowflake External Table Not Working as expected

I have created one file format (CSV) and then one external table to load csv data from azure blob storage.

The external table is showing all columns as NULL except the "Value" column

File Format Code

COMPRESSION = 'NONE'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 0
FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
EMPTY_FIELD_AS_NULL = FALSE
TRIM_SPACE = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('NULL');

EXTERNAL TABLE CODE

CREATE OR REPLACE EXTERNAL TABLE EXT_DIM_TESTTABLE
(
  COL1                  VARCHAR (1000)          AS (value:"COL1"::string),
  COL2              VARCHAR (1000)          AS (value:"COL2"::string),
  COL3          VARCHAR (1000)          AS (value:"COL3"::string),
  COL4          VARCHAR (1000)          AS (value:"COL4"::string),
  COL5          VARCHAR (1000)          AS (value:"COL5"::string),
  COL6          VARCHAR (1000)          AS (value:"COL6"::string)
)
WITH 
LOCATION=@TESTSTAGE
AUTO_REFRESH = true
FILE_FORMAT = 'FILE_TESTFORMAT_CSV'
PATTERN='.*TEST_DATA.csv';

Now when I select * from EXT_DIM_TESTTABLE, all columns shows NULL except VALUE one,

VALUE column is coming as below, the column names are not taken as "Col1" / "Col2" etc. but the values are correct. Rest all columns are NULL

{ "c1": "TESTING", "c2": "TESTING", "c3": "TESTING", "c4": "TESTING", "c5": "TESTING", "c6": "TESTING" } not sure what is missing here?

Upvotes: 1

Views: 803

Answers (1)

Mehul Shroff
Mehul Shroff

Reputation: 11

It seems you are using value:"COL1"::string incorrectly.

Can you try using below DDL for external table?

CREATE OR REPLACE EXTERNAL TABLE EXT_DIM_TESTTABLE
(
  COL1 VARCHAR(1000) AS (value:c1::string),
  COL2 VARCHAR(1000) AS (value:c2::string),
  COL3 VARCHAR(1000) AS (value:C3::string),
  COL4 VARCHAR(1000) AS (value:c4::string),
  COL5 VARCHAR(1000) AS (value:c5::string),
  COL6 VARCHAR(1000) AS (value:c6::string)
)
WITH 
LOCATION=@TESTSTAGE
AUTO_REFRESH = true
FILE_FORMAT = 'FILE_TESTFORMAT_CSV'
PATTERN='.*TEST_DATA.csv'

;

Upvotes: 1

Related Questions