Spotify_Snowflake/SPOTIFY_SNOWFLAKE.sql at main · ShubhamMohanty680/Spotify_Snowflake · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
// Creating Storage Stage with AWS
CREATE OR REPLACE storage integration spotify_init
    TYPE= EXTERNAL_STAGE
    STORAGE_PROVIDER = S3
    ENABLED= TRUE
    STORAGE_AWS_ROLE_ARN= 'arn:aws:iam::975050103735:role/snowflake-s3-connection'
    STORAGE_ALLOWED_LOCATIONS= ('s3://spotify-etl-project-shubham')
    COMMENT= 'Creating Connection'

DESC integration spotify_init;


-- Creating Tables
create or replace TABLE SPOTIFY.PUBLIC.TBLALBUM (
	ALBUM_ID VARCHAR(100) PRIMARY KEY,
	NAME VARCHAR(100),
	RELEASE_DATE DATE,
	TOTAL_TRACKS NUMBER(38,0),
	URL STRING
);

create or replace TABLE SPOTIFY.PUBLIC.TBLARTIST (
	ARTIST_ID VARCHAR(100) PRIMARY KEY,
	ARTIST_NAME VARCHAR(100),
	EXTERNAL_URL STRING

);

create or replace TABLE SPOTIFY.PUBLIC.TBLSONGS (
	SONG_ID VARCHAR(100) PRIMARY KEY,
	SONG_NAME VARCHAR(100),
	DURATION_MS NUMBER(38,0),
	URL VARCHAR(16777216),
	POPULARITY NUMBER(38,0),
	SONG_ADDED TIMESTAMP_NTZ(9),
	ALBUM_ID VARCHAR(100),
	ARTIST_ID VARCHAR(100),
	foreign key (ALBUM_ID) references SPOTIFY.PUBLIC.TBLALBUM(ALBUM_ID),
	foreign key (ARTIST_ID) references SPOTIFY.PUBLIC.TBLARTIST(ARTIST_ID)
);

--  Creating File Format
CREATE OR REPLACE FILE FORMAT MANAGE_DB.FILE_FORMATS.CSV_FILEFORMAT
    type= csv
    field_delimiter= ","
    skip_header= 1
    null_if= ('NULL','null')
    FIELD_OPTIONALLY_ENCLOSED_BY= '"';

-- Creating Stage
CREATE OR REPLACE STAGE MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV
    URL= 's3://spotify-etl-project-shubham/transformed_data'
    STORAGE_INTEGRATION= spotify_init
    FILE_FORMAT= MANAGE_DB.FILE_FORMATS.CSV_FILEFORMAT

LIST @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV;

-- Checking copy command before using in snowpipe
COPY INTO SPOTIFY.PUBLIC.TBLALBUM
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/album_data/

COPY INTO SPOTIFY.PUBLIC.TBLARTIST
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/artist_data/

COPY INTO SPOTIFY.PUBLIC.TBLSONGS
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/songs_data/

--  Creating album Snowpipe

CREATE OR REPLACE pipe MANAGE_DB.pipes.album_pipe
auto_ingest=TRUE
AS
COPY INTO SPOTIFY.PUBLIC.TBLALBUM
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/album_data/


-- Creating artist Snowpipe
CREATE OR REPLACE pipe MANAGE_DB.pipes.artist_pipe
auto_ingest= TRUE
AS
COPY INTO SPOTIFY.PUBLIC.TBLARTIST
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/artist_data/

-- Creating songs Snowpipe
CREATE OR REPLACE pipe MANAGE_DB.PIPES.song_pipe
auto_ingest= TRUE
AS
COPY INTO SPOTIFY.PUBLIC.TBLSONGS
FROM @MANAGE_DB.EXTERNAL_STAGES.SPOTIFY_CSV/songs_data/


DESC PIPE MANAGE_DB.pipes.album_pipe;

DESC PIPE MANAGE_DB.pipes.artist_pipe;

DESC PIPE MANAGE_DB.pipes.song_pipe;


select * from SPOTIFY.PUBLIC.TBLALBUM;

SPOTIFY.PUBLIC.TBLARTIST
select * from spotify.public.tblsongs;

SELECT SYSTEM$PIPE_STATUS('MANAGE_DB.pipes.ARTIST_PIPE');