1st login using snowsql
snowsql -a <accountName> -u <username>
now using your personal staging area which is designated by: @~
get @~/data_0_0_0.json.gz file:///Users/michael/.snowsql
you can list what is in your stage with:
list @~ or ls @~; +-----------------------------------------------------+------+----------------------------------+-------------------------------+ | name | size | md5 | last_modified | |-----------------------------------------------------+------+----------------------------------+-------------------------------| | data_0_0_0.json.gz | 160 | d2b06257b0779baf3ecc1896083d73b6 | Sun, 17 Jul 2022 02:09:10 GMT | | junk.txt.gz | 48 | 3e0b0da76b0958370bb49553b5ab41ec | Sat, 16 Jul 2022 20:36:28 GMT |
gunzip data_0_0_0.json.gz
$ cat data_0_0_0.json
{"city":"Salt Lake City","first_name":"Ryan","id":1,"last_name":"Dalton","state":"UT"}
{"city":"Birmingham","first_name":"Upton","id":2,"last_name":"Conway","state":"AL"}
{"city":"Columbus","first_name":"Kibo","id":3,"last_name":"Horton","state":"GA"}
and oh BTW create a table, add some rows and unload that to a compressed json file – to setup the steps above…
create or replace table mytable (
id number(8) not null,
first_name varchar(255) default null,
last_name varchar(255) default null,
city varchar(255),
state varchar(255)
);
insert into mytable (id,first_name,last_name,city,state)
values
(1,'Ryan','Dalton','Salt Lake City','UT'),
(2,'Upton','Conway','Birmingham','AL'),
(3,'Kibo','Horton','Columbus','GA');
list @~;
ls @~;
copy into @~
from (select object_construct('id', id, 'first_name', first_name, 'last_name', last_name, 'city', city, 'state', state) from mytable)
file_format = (type = json);