Adding a Column with default value in snowflake but copy data activity(copying from a text file which has required columns) in ADF fails

user_12750789 0
Viewed 145

I have a table with 4 columns (SCENARIO, YEAR, PERIOD, INSERT_DTS). I have created the table as below:

create or replace TABLE TEST_DB.STG_TEST.HFM_STG (
    SCENARIO VARCHAR(50),
    YEAR VARCHAR(4),
    PERIOD VARCHAR(3),
    INSERT_DTS TIMESTAMP DEFUALT CURRENT_TIMEstamp()
);

when I tried to copy the data from a text file into Snowflake using Copy Activity I get the below ERROR message - "Number of columns in file (3) does not match that of the corresponding table (4)"

Any solution to overcome this issue, as column INSERT_DTS supposed to be a default value ?

snowflake-schema snowflake-cloud-data-platform azure
1 Answers

You need to use the keyword DEFAULT for value in INSERT:

create or replace table XYZ (x varchar(200), y varchar(200), z varchar(200), t timestamp default current_timestamp());
insert into XYZ values ('X', 'Y', 'Z', DEFAULT);
SELECT * FROM XYZ;

enter image description here

See here

For COPY INTO you can do something like this:

COPY INTO XYZ (x, y, z) FROM (SELECT $1, $2, $3 FROM @~/worksheet_data/metadata);

enter image description here

The above will insert into column t the default value.

user_77985480
Related