CODEWITHSUNDEEP
snowflake-cloud-data-platform
Aryan Tyagi
Aryan Tyagi

Reputation: 71

Getting Null in all column for Snowflake even though data is present

Select * from table_name.I am working on a Snowflake table. I am getting a Null value on every column even though data is present in JSON. Can you please help me on this issue.

create or replace external table "database_name"."schema"."TABLE_Name" 
(Name varchar(20) as ( value:Name::varchar), 
Surname varchar(20) as (value:Surname::varchar),
City varchar(20) as (value:City::varchar),
Country varchar(20) as (value:Country::varchar),
Age varchar(20) as (value:Age::varchar),
date_part date as to_date(split_part(metadata$filename, '/', 2)
|| '/' || split_part(metadata$filename, '/', 3)
|| '/' || split_part(metadata$filename, '/', 4), 'YYYY/MM/DD'))
partition by (date_part)
location = @stage/logs/
file_format = parquet;

Upvotes: 0

Views: 881

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11046

JSON properties are case sensitive. If you specify a nonexistent property name it will return null. Correcting the case for the JSON properties should fix this.

create or replace external table "database_name"."schema"."TABLE_Name" 
(Name varchar(20) as ( value:NAME::varchar), 
Surname varchar(20) as (value:SURNAME::varchar),
City varchar(20) as (value:CITY::varchar),
Country varchar(20) as (value:COUNTRY::varchar),
Age varchar(20) as (value:AGE::varchar),
date_part date as to_date(split_part(metadata$filename, '/', 2)
|| '/' || split_part(metadata$filename, '/', 3)
|| '/' || split_part(metadata$filename, '/', 4), 'YYYY/MM/DD'))
partition by (date_part)
location = @stage/logs/
file_format = parquet;

Upvotes: 1

Related Questions