I am trying to create external table in snowflake, which should be refreshed whenever we have a new file in the source directory. External table is not producing any result even though my stage shows data.

• Source Sample File (employees.csv)

id,firstName,lastName,address
1001,abcdef,ghijkl,seattle
1002,mnopqr,stuvwx,houston

• File Format (tried with different options):

CREATE OR REPLACE FILE FORMAT ff_comma
    TYPE = 'CSV' 
    FIELD_DELIMITER = ',' 
    -- FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
    SKIP_HEADER = 1
    COMPRESSION = 'NONE'
    RECORD_DELIMITER = '\n'
    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')
;

• Stage (Hard coded full path for now, later on will have source directory only where files is placed on daily basis)

CREATE OR REPLACE STAGE stage_testing_ext
  STORAGE_INTEGRATION = storage_integration_dev
  URL = 's3://bucket_name/dir_1/employees.csv'
  FILE_FORMAT = ff_comma
;
LIST @stage_testing_ext;
SELECT $1,$2,$3,$4 FROM @stage_testing_ext;

Query Output

• Table I created standard table and it is returning data from stage.

CREATE OR REPLACE TABLE testing 
AS
SELECT 
    $1 AS id
  , $2 AS first_name
  , $3 AS last_name
  , $4 AS address
FROM @stage_testing_ext;

SELECT * FROM testing;
ID  FIRST_NAME  LAST_NAME   ADDRESS
1001    abcdef  ghijkl  seattle
1002    mnopqr  stuvwx  houston

• External Table (Tried with different options but no luck):

CREATE OR REPLACE EXTERNAL TABLE ext_testing(
    id          INT     AS (value:c1::int)
  , first_name  VARCHAR AS (value:c2::string)
  , last_name   VARCHAR AS (value:$1::varchar)
  , address     VARCHAR AS ($1::varchar)
)
WITH 
  LOCATION = @stage_testing_ext
  FILE_FORMAT = ff_comma
;
SELECT * FROM ext_testing;

Query Output

I am expecting to have source data available/displayed when external table is queried.

I am trying to create external table in snowflake, which should be refreshed whenever we have a new file in the source directory. External table is not producing any result even though my stage shows data.

• Source Sample File (employees.csv)

id,firstName,lastName,address
1001,abcdef,ghijkl,seattle
1002,mnopqr,stuvwx,houston

• File Format (tried with different options):

CREATE OR REPLACE FILE FORMAT ff_comma
    TYPE = 'CSV' 
    FIELD_DELIMITER = ',' 
    -- FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
    SKIP_HEADER = 1
    COMPRESSION = 'NONE'
    RECORD_DELIMITER = '\n'
    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')
;

• Stage (Hard coded full path for now, later on will have source directory only where files is placed on daily basis)

CREATE OR REPLACE STAGE stage_testing_ext
  STORAGE_INTEGRATION = storage_integration_dev
  URL = 's3://bucket_name/dir_1/employees.csv'
  FILE_FORMAT = ff_comma
;
LIST @stage_testing_ext;
SELECT $1,$2,$3,$4 FROM @stage_testing_ext;

Query Output

• Table I created standard table and it is returning data from stage.

CREATE OR REPLACE TABLE testing 
AS
SELECT 
    $1 AS id
  , $2 AS first_name
  , $3 AS last_name
  , $4 AS address
FROM @stage_testing_ext;

SELECT * FROM testing;
ID  FIRST_NAME  LAST_NAME   ADDRESS
1001    abcdef  ghijkl  seattle
1002    mnopqr  stuvwx  houston

• External Table (Tried with different options but no luck):

CREATE OR REPLACE EXTERNAL TABLE ext_testing(
    id          INT     AS (value:c1::int)
  , first_name  VARCHAR AS (value:c2::string)
  , last_name   VARCHAR AS (value:$1::varchar)
  , address     VARCHAR AS ($1::varchar)
)
WITH 
  LOCATION = @stage_testing_ext
  FILE_FORMAT = ff_comma
;
SELECT * FROM ext_testing;

Query Output

I am expecting to have source data available/displayed when external table is queried.

• sql
• snowflake-cloud-data-platform
• external-tables

• You can try to manually refresh the external table ALTER EXTERNAL TABLE ... REFRESH and check metadata information_schema.external_table_files. Not able to populate EXTERNAL TABLE ... – Lukasz Szozda Commented Feb 6 at 18:25
• Thanks Lukasz. Issue here is, external table is not reading anything from STAGE. I might have some typo or missing some thing in table creation. – DEARINE Commented Feb 6 at 18:31

1 Answer 1

From the documentation

Add virtual columns as expressions using the VALUE column and/or the METADATA$FILENAME pseudocolumn.

Examples given in documentation are also based on value column like

mycol varchar as (value:c1::varchar)

Instead of $1::varchar , you can try to use value:c3::varchar for last_name and value:c4::varchar for address.