CODEWITHSUNDEEP
snowflake-cloud-data-platform
rbachkaniwala
rbachkaniwala

Reputation: 59

COPY INTO query on Snowflake returns TABLE does not exist error

I am trying to load data from azure blob storage.

The data has already been staged.

But, the issue is when I try to run

copy into random_table_name
from @stage_name_i_created
file_format = (type='csv')
pattern ='*.csv'

Below is the error I encounter:

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

Basically, it says table does not exist, which it does not, but the syntax on website is the same as mine.

COPY INTO query on Snowflake returns TABLE does not exist error

Upvotes: 3

Views: 5257

Answers (7)

Leo
Leo

Reputation: 908

First run the below query to fetch the column headers

select $1  FROM @stage_name_i_created/filename.csv limit 1

Assuming below are the header lines from your csv file

   id;first_name;last_name;email;age;location

Create a file_format csv

create or replace file format semicolon
  type = 'CSV'
  field_delimiter = ';'
  skip_header=1;

Then you should define the datatype and field name as below

create or replace table <yourtable> as 
select $1::varchar  as id
,$2::varchar as first_name
,$3::varchar as last_name
,$4::varchar as email
,$5::int as age
,$6::varchar as location
FROM @stage_name_i_created/yourfile.csv  
(file_format => semicolon );

Upvotes: 1

Dumi
Dumi

Reputation: 126

In my case the table name is case-sensitive. Snowflake seems to convert everything to upper case. I changed the database/schema/table names to all upper-case and it started working.

Upvotes: 2

Monem_منعم
Monem_منعم

Reputation: 346

"COPY INTO" is not a query command, it is the actual data transfer execution from source to destination, which both must exist as others commented here but If you want just to query without loading the files then run the following SQL:

//Display list of files in the stage to verify stage

LIST @stage_name_i_created; 

//Create a file format 

CREATE OR REPLACE FILE FORMAT RANDOM_FILE_CSV 
type = csv
COMPRESSION = 'GZIP' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 0 FIELD_OPTIONALLY_ENCLOSED_BY = '\042' 
TRIM_SPACE = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' 
NULL_IF = ('\\N'); 


//Now select the data in the files
 
Select $1 as first_col,$2 as second_col  //can add as necessary number of columns ...etc
from @stage_name_i_created

(FILE_FORMAT => RANDOM_FILE_CSV)

More information can be found in the documentation link here https://docs.snowflake.com/en/user-guide/querying-stage.html

Upvotes: 0

Sonny Rivera
Sonny Rivera

Reputation: 1

The table does need to exist. You should check the documentation for COPY INTO. Other areas to consider are

  • do you have the right context set for the database & schema
  • does the user / role have access to the table or object.

It basically seems like you don't have the table defined yet. You should

  • ensure the table is created
  • ensure all columns in the CSV exist as columns in the table
  • ensure the order of the columns are the same as in the CSV
  • I'd check data types too.

Upvotes: 0

Rajib Deb
Rajib Deb

Reputation: 1774

rbachkaniwala, what do you mean by 'How do I create a table?( according to snowflake syntax it is not possible to create empty tables)'.

You can just do below to create a table

CREATE TABLE random_table_name (FIELD1 VARCHAR, FIELD2 VARCHAR)

Upvotes: 0

Luis Pe&#241;a
Luis Pe&#241;a

Reputation: 61

If your table exist, try by forcing the table path like this:

copy into <database>.<schema>.<random_table_name>
from @stage_name_i_created
file_format = (type='csv')
pattern ='*.csv'

or by steps like this:

use database <database_name>;
use schema <schema_name>;
copy into database.schema.random_table_name
   from @stage_name_i_created
   file_format = (type='csv')
   pattern ='*.csv';

Upvotes: 0

Mike Walton
Mike Walton

Reputation: 7339

The table must exist prior to running a COPY INTO command. In your post, you say that the table does not exist...so that is your issue.

Upvotes: 0

Related Questions