snowflake load data into database using stage objects

Stage object provides a shared internal storage used by Snowflake to store and stage file that can later be used to load into a table. 

CREATE OR REPLACE DATABASE mydatabase;

CREATE OR REPLACE TEMPORARY TABLE mycsvtable (

  id INTEGER,

  last_name STRING,

  first_name STRING,

  company STRING,

  email STRING,

  workphone STRING,

  cellphone STRING,

  streetaddress STRING,

  city STRING,

  postalcode STRING);

CREATE OR REPLACE TEMPORARY TABLE myjsontable (

  json_data VARIANT);

-- Create a warehouse

CREATE OR REPLACE WAREHOUSE mywarehouse WITH

  WAREHOUSE_SIZE='X-SMALL'

  AUTO_SUSPEND = 120

  AUTO_RESUME = TRUE

  INITIALLY_SUSPENDED=TRUE;

You can do that by using the following command. First we create a file format

CREATE OR REPLACE FILE FORMAT mycsvformat

  TYPE = 'CSV'

  FIELD_DELIMITER = '|'

  SKIP_HEADER = 1;

Next we create our stage called my_csv_stage

CREATE OR REPLACE STAGE my_csv_stage

  FILE_FORMAT = mycsvformat;

Goto your catalog and then find MyDatabase->Public->Stages->My_CSV_STAGE and load up your files as shown here:-

As you can see we have quite a number of files. Next we will load it into our table. 

COPY INTO mycsvtable

  FROM @my_csv_stage

  FILE_FORMAT = (FORMAT_NAME = mycsvformat)

  PATTERN='.*contacts[1-5].csv'

  ON_ERROR = 'skip_file';

And once your data is loaded, you can easily retrieve it using the following command