dagshub-snowflake/snowflake_config.txt

MY_SNOWFLAKE_DATABASE.DAGSHUB_LEGO_SET_PROJECT.COLORSCREATE DATABASE my_snowflake_database;

CREATE SCHEMA dagshub_lego_set_project;

USE DATABASE my_snowflake_database;
USE SCHEMA dagshub_lego_set_project;

CREATE OR REPLACE STORAGE INTEGRATION mys3integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::088438813775:role/mysnowflakerole'
  STORAGE_ALLOWED_LOCATIONS = ('s3://lego-set-s3/');

DESC INTEGRATION mys3integration;

create table colors
(
    id       int  null,
    name     text null,
    rgb      text null,
    is_trans text null
);

create or replace warehouse mywarehouse with 
	warehouse_size = 'X-SMALL'
    auto_suspend = 120
    auto_resume = true
    initially_suspended = true;

create or replace file format mycsvformat
	type = 'CSV'
    field_delimiter = ','
    record_delimiter = '\n'
    skip_header = 1
 	FIELD_OPTIONALLY_ENCLOSED_BY = '0x22'
;
    
create or replace stage mycsvstage
	storage_integration = mys3integration
	file_format = mycsvformat
    url = 's3://lego-set-s3/data/';

show stages;
list @~;

alter warehouse mywarehouse resume;

copy into colors
	from @mycsvstage/colors.csv
    on_error = 'skip_file';

select * from colors;

create table inventories
(
    id      int  null,
    version int  null,
    set_num text null
);

create table inventory_parts
(
    inventory_id int  null,
    part_num     text null,
    color_id     int  null,
    quantity     int  null,
    is_spare     text null
);

create table inventory_sets
(
    inventory_id int  null,
    set_num      text null,
    quantity     int  null
);

create table part_categories
(
    id   int  null,
    name text null
);

create table parts
(
    part_num    text null,
    name        text null,
    part_cat_id int  null
);

create table set_price
(
    id         text   null,
    name       text   null,
    category   text   null,
    year       double null,
    parts      double null,
    img_link   text   null,
    set_link   text   null,
    raw_price  text   null,
    mean_price double null
);

create table sets
(
    set_num   text null,
    name      text null,
    year      int  null,
    theme_id  int  null,
    num_parts int  null
);

create table themes
(
    id        int  null,
    name      text null,
    parent_id int  null
);

copy into inventories
	from @mycsvstage/inventories.csv
    on_error = 'skip_file';

copy into inventory_parts
	from @mycsvstage/inventory_parts.csv
    on_error = 'skip_file';

copy into inventory_sets
	from @mycsvstage/inventory_sets.csv
    on_error = 'skip_file';

copy into parts
	from @mycsvstage/parts.csv
    on_error = 'skip_file';

copy into part_categories
	from @mycsvstage/part_categories.csv
    on_error = 'skip_file';

copy into sets
	from @mycsvstage/sets.csv
    on_error = 'skip_file';

copy into set_price
	from @mycsvstage/set_price.csv
    on_error = 'skip_file';

copy into themes
	from @mycsvstage/themes.csv
    on_error = 'skip_file';