Data_Warehouse_ETL/02_load_data.sql at main · teevirta/Data_Warehouse_ETL · 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
-- LOAD DATA INTO TABLES

SELECT 'LOADING DATA TO TABLES' AS info;

INSERT INTO company_dim (
    company_id,
    name,
    link,
    link_google,
    thumbnail
)
SELECT
    company_id,
    name,
    link,
    link_google,
    thumbnail
FROM read_csv(
    'https://storage.googleapis.com/sql_de/company_dim.csv');


INSERT INTO skills_dim (
    skill_id,
    skills,
    type
)
SELECT
    skill_id,
    skills,
    type
FROM read_csv(
    'https://storage.googleapis.com/sql_de/skills_dim.csv');


INSERT INTO job_postings_fact(
    job_id,
    company_id,
    job_title_short,
    job_title,
    job_location,
    job_via,
    job_schedule_type,
    job_work_from_home,
    search_location,
    job_posted_date,
    job_no_degree_mention,
    job_health_insurance,
    job_country,
    salary_rate,
    salary_year_avg,
    salary_hour_avg
)
SELECT
    job_id,
    company_id,
    job_title_short,
    job_title,
    job_location,
    job_via,
    job_schedule_type,
    job_work_from_home,
    search_location,
    job_posted_date,
    job_no_degree_mention,
    job_health_insurance,
    job_country,
    salary_rate,
    salary_year_avg,
    salary_hour_avg
FROM read_csv(
    'https://storage.googleapis.com/sql_de/job_postings_fact.csv');


INSERT INTO skills_job_dim (
    job_id,
    skill_id
)
SELECT
    job_id,
    skill_id
FROM read_csv(
    'https://storage.googleapis.com/sql_de/skills_job_dim.csv');


-- VALIDATIONS

SELECT 'Company Dim' AS table_name, COUNT(*) AS record_count FROM company_dim
UNION ALL
SELECT 'Skills Dim', COUNT(*) FROM skills_dim
UNION ALL
SELECT 'Job Postings Fact', COUNT(*) FROM job_postings_fact
UNION ALL
SELECT 'Skills Job Dim', COUNT(*) FROM skills_job_dim;

SELECT * FROM company_dim LIMIT 5;
SELECT * FROM skills_dim LIMIT 5;
SELECT * FROM job_postings_fact LIMIT 5;
SELECT * FROM skills_job_dim LIMIT 5;