In the tutorial “Tutorial: Bulk Loading from a local file system using copy” what is the difference between my_stage and my_table permissions?

Question

I started to go through the first tutorial for how to load data into Snowflake from a local file.

This is what I have set up so far:

CREATE WAREHOUSE mywh;
CREATE DATABASE Mydb; 
Use Database mydb;


CREATE ROLE ANALYST;

grant usage on database mydb to role sysadmin;
grant usage on database mydb to role analyst;


grant usage, create file format, create stage, create table on schema mydb.public to role analyst;

grant operate, usage on warehouse mywh to role analyst;

//tutorial 1 loading data
CREATE FILE FORMAT mycsvformat
   TYPE = "CSV"
   FIELD_DELIMITER= ','
   SKIP_HEADER = 1;

CREATE FILE FORMAT myjsonformat
   TYPE="JSON"
   STRIP_OUTER_ARRAY = true; 
   
//create stage
CREATE OR REPLACE STAGE my_stage
   FILE_FORMAT = mycsvformat;
   
//Use snowsql for this and make sure that the role, db, and warehouse are seelcted: put file:///data/data.csv @my_stage;
  
   
 // put file on stage
 PUT file://contacts.csv @my
 
 List @~;
 
 list @%mytable;

Then in my active Snowsql when I run:

Put file:///Users/<user>/Documents/data/data.csv @my_table;

I have confirmed I am in the correct role Accountadmin: 002003 (02000): SQL compilation error:
Stage 'MYDB.PUBLIC.MY_TABLE' does not exist or not authorized.

So then I try to create the table in Snowsql and am successful:

create or replace table my_table(id varchar, link varchar, stuff string);

I still run into this error after I run:

Put file:///Users/<>/Documents/data/data.csv @my_table;

002003 (02000): SQL compilation error:
Stage 'MYDB.PUBLIC.MY_TABLE' does not exist or not authorized.

What is the difference between putting a file to a my_table and a my_stage in this scenario? Thanks for your help!

EDIT:

CREATE OR REPLACE TABLE myjsontable(json variant);
 
COPY INTO myjsontable
  FROM @my_stage/random.json.gz
  FILE_FORMAT = (TYPE= 'JSON')
  ON_ERROR = 'skip_file';
 
 CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(myjsontable, JOB_ID=>'enterid'));
 SELECT * FROM SAVE_COPY_ERRORS;
 //error for random: Error parsing JSON: invalid character outside of a string: '\\'
 //no error for generated
 
 SELECT * FROM Myjsontable;
 
REMOVE @My_stage pattern = '.*.csv.gz';


REMOVE @My_stage pattern = '.*.json.gz';

//yay your are done!

Mike Gohl Mike Gohl · Accepted Answer · 2019-12-20T20:48:52

The put command copies the file from your local drive to the stage. You should do the put to the stage, not that table.

put file:///Users/<>/Documents/data/data.csv @my_stage;

The copy command loads it from the stage.

In the tutorial “Tutorial: Bulk Loading from a local file system using copy” what is the difference between my_stage and my_table permissions?

2 Answers