AUTOINCREMENT primary key for snowflake bulk loading

Question

I would like to upload data into snowflake table. The snowflake table has a primary key field with AUTOINCREMENT.

When I tried to upload data into snowflake without a primary key field, I've received following error message:

The COPY failed with error: Number of columns in file (2) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error

Does anyone know if I can bulk load data into a table that has an AUTOINCREMENT primary key?

knozawa

score 5 · Accepted Answer · edited May 30 '20 at 17:12

5

You can query the stage file using file format to load your data. I have created sample table like below. First column set autoincrement:

-- Create the target table
create or replace table Employee (
  empidnumber autoincrement start 1 increment 1,
  name varchar,
  salary varchar
  );

I have staged one sample file into snowflake internal stage to load data into table and I have queried stage file using following and then I have executed following copy cmd:

copy into mytable (name, salary )from (select $1, $2 from @test/test.csv.gz                                );

And it loaded the table with incremented values.

edited May 30 '20 at 17:12

Dharman

30,962
25
85
135

answered Feb 21 '20 at 11:18

Sriga

1,165
4
11

Thank you! Auto increment PK using bulk load worked when I used "COPY INTO". – knozawa Feb 29 '20 at 02:08
I used ```empidnumber autoincrement start 1 increment 1``` but when I delete the old records the ID dose not starts from 1 , ex. if there were 10 records If I delete this and insert again the ID starts with 11, any suggestions for this? – JohnB Jun 08 '23 at 05:17
Try reset the start value if you want to start from the beginning – Sriga Jun 08 '23 at 20:42

score 3 · Answer 2 · answered Feb 20 '20 at 19:32

3

The docs have the following example which suggests this can be done: https://docs.snowflake.net/manuals/user-guide/data-load-transform.html#include-autoincrement-identity-columns-in-loaded-data

-- Omit the sequence column in the COPY statement
copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

Could you please try this syntax and see if it works for you?

answered Feb 20 '20 at 19:32

Mike Donovan

369
1
2

Looks like they've updated the COPY statement. I wrote my comment based on old information – Brock Feb 20 '20 at 20:12
Thank you! Auto increment PK using bulk load worked when I used "COPY INTO". – knozawa Feb 29 '20 at 02:08

score 1 · Answer 3 · edited Apr 22 '22 at 04:42

Create the target table

create or replace table mytable (
  col1 number autoincrement start 1 increment 1,
  col2 varchar,
  col3 varchar
  );

Stage a data file in the internal user stage

put file:///tmp/myfile.csv @~;

Query the staged data file

select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

Omit the sequence column in the COPY statement

copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+

score 0 · Answer 4 · answered Jan 02 '23 at 10:58

Adding of PRIMARY KEY is different in SNOWFLAKE when compared to SQL

syntax for adding primary key with auto increment

CREATE OR REPLACE TABLE EMPLOYEES (
  NAME VARCHAR(100),
  SALARY VARCHAR(100),
  EMPLOYEE_ID AUTOINCREMENT START 1 INCREMENT 1,
  );

START 1 = STARTING THE PRIMARY KEY AT NUMBER 1 (WE CAN START AT ANY NUMBER WE WANT ) INCREMENT = FOR THE ID ADD THE NUMBER 1 TO PREVIOUS EXISTING NUMBER ( WE CAN GIVE ANYTHING WE WANT)

AUTOINCREMENT primary key for snowflake bulk loading

4 Answers4