R1KLyhtt

1USE sakila;
2    
3
4-- Film
5    
6
7DROP TABLE IF EXISTS src_film;
8CREATE TABLE src_film LIKE film;
9INSERT src_film SELECT * FROM film;
10
11DROP TABLE IF EXISTS hub_film;
12CREATE TABLE hub_film (
13	`key` INT NOT NULL auto_increment primary key,
14    `id` INT NOT NULL,
15    `created_at` datetime,
16    `updated_at` datetime,
17    `source` varchar(100)
18);
19
20INSERT INTO hub_film (id, created_at, updated_at, source)
21	SELECT 
22		film_id, 
23        NOW(), 
24        NOW(), 
25        'sakila' 
26	FROM src_film
27ON DUPLICATE KEY UPDATE updated_at=NOW();
28
29
30-- Customer
31
32
33DROP TABLE IF EXISTS src_customer;
34CREATE TABLE src_customer LIKE customer; -- using LIKE copies the data types and indexes over
35INSERT src_customer SELECT * FROM customer;
36    
37DROP TABLE IF EXISTS hub_customer;
38CREATE TABLE hub_customer (
39	`key` INT NOT NULL auto_increment primary key,
40    `id` INT NOT NULL,
41    `email` varchar(100),
42    `created_at` datetime,
43    `updated_at` datetime,
44    `source` varchar(100)
45);
46
47INSERT INTO hub_customer (id, email, created_at, updated_at, source)
48	SELECT 
49		customer_id, 
50        email,
51        NOW(),
52        NOW(),
53        'sakila'
54	FROM src_customer
55ON DUPLICATE KEY UPDATE updated_at=NOW();
56
57
58DROP TABLE IF EXISTS dim_customer;
59CREATE TABLE dim_customer
60	SELECT 
61		h.`key`, 
62        c.*,
63        CONCAT(c.first_name,' ',c.last_name) as full_name
64    FROM src_customer c
65    JOIN hub_customer h ON c.customer_id = h.id;
66
67
68-- Store
69
70
71DROP TABLE IF EXISTS src_store;
72CREATE TABLE src_store LIKE store;
73INSERT src_store SELECT * FROM store;
74
75DROP TABLE IF EXISTS hub_store;
76CREATE TABLE hub_store LIKE hub_film; -- Easier to write
77
78INSERT INTO hub_store (id, created_at, updated_at, source)
79	SELECT
80		s.store_id,
81        NOW(),
82        NOW(),
83        'sakila'
84	FROM src_store s
85ON DUPLICATE KEY UPDATE updated_at=NOW();
86
87DROP TABLE IF EXISTS dim_store;
88CREATE TABLE dim_store
89	SELECT
90		h.`key`,
91        s.*
92	FROM src_store s
93    JOIN hub_store h on s.store_id = h.id