External Table not returning values

Question

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.

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
– 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
– DEARINE, Commented Feb 6 at 18:31

samhita · Accepted Answer · 2025-02-07 00:27:33Z

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.

answered Feb 7 at 0:27

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

External Table not returning values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related