COPY INTO in snowflake throws table does not exist

Asked Jan 31 '22 at 17:17

Active Jan 31 '22 at 19:52

Viewed 729 times

3

I am trying to copy data to snowflake from local and I am getting

snowflake.connector.errors.ProgrammingError: 001757 (42601): SQL compilation error: Table 'RAW_DATA' does not exist

the same code is working in Jupiter notebook but it doesn't work in vs code. My role is accountadmin so no issue with the permissions.

Code I am trying to run is this

`COPY INTO RAW_DATA file_format=(FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)`

sql snowflake-cloud-data-platform snowflake-schema

edited Jan 31 '22 at 17:38

Aakash aggarwal

asked Jan 31 '22 at 17:17
Aakash aggarwal

443

2

6

21

4 Answers4

3

I suspect you are using a different Database/schema than the Jupiter notebook

`select current_warehouse(), current_database(), current_schema();`

for me gives:

CURRENT_WAREHOUSE() CURRENT_DATABASE() CURRENT_SCHEMA()

COMPUTE_WH TEST TMP

Question

I am trying to copy data to snowflake from local and I am getting

snowflake.connector.errors.ProgrammingError: 001757 (42601): SQL compilation error: Table 'RAW_DATA' does not exist

the same code is working in Jupiter notebook but it doesn't work in vs code. My role is accountadmin so no issue with the permissions.

Code I am trying to run is this

COPY INTO RAW_DATA file_format=(FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)

score 3 · Answer 1

I suspect you are using a different Database/schema than the Jupiter notebook

select current_warehouse(), current_database(), current_schema();

for me gives:

score 1 · Answer 2 · answered Jan 31 '22 at 17:28

1

Try replacing 'TABLE' with your table name.

Example:

COPY INTO mytable FROM 's3://mybucket/./../a.csv';

answered Jan 31 '22 at 17:28

ratloser

116
1
7

here TABLE is my table name only and it doesn't work – Aakash aggarwal Jan 31 '22 at 17:31

Robert Long · Answer 3 · 2022-01-31T17:39:34.550

There is a syntax error. You cannot name use TABLE as a table name. TABLE is a reserved keyword https://docs.snowflake.com/en/sql-reference/reserved-keywords.html

From the docs: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

COPY INTO [<namespace>.]<table_name>
     FROM { internalStage | externalStage | externalLocation }
[ FILES = ( '<file_name>' [ , '<file_name>' ] [ , ... ] ) ]
[ PATTERN = '<regex_pattern>' ]
[ FILE_FORMAT = ( { FORMAT_NAME = '[<namespace>.]<file_format_name>' |
                    TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
[ copyOptions ]
[ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]

So instead of

COPY INTO TABLE FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)

try

 COPY INTO MYTABLE FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)

Of course you will need to create MYTABLE first.

Then `COPY INTO ROW_DATA FILE_FORMAT = (FIELD_OPTIONALLY_ENCLOSED_BY ='"' SKIP_HEADER =1)` — Robert Long, Jan 31 '22 at 17:42

score 1 · Answer 4 · answered Jan 31 '22 at 17:41

It was not working with

COPY INTO RAW_DATA file_format=(FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)

But Then I tried

table_name = "RAW_DATA"
f"COPY INTO {table_name} file_format=(FIELD_OPTIONALLY_ENCLOSED_BY ='"' skip_header=1)"

it started working. Not sure why but this seems working for now