-
Notifications
You must be signed in to change notification settings - Fork 0
/
load_data.sql
83 lines (80 loc) · 1.32 KB
/
load_data.sql
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
CREATE OR REPLACE TABLE
`bq_demo.denorm` AS (
SELECT
c.*,
o.order_num AS order_num,
order_date,
line_item_num,
li.prod_code AS prod_code,
qty,
prod_name,
prod_desc,
prod_price
FROM
`roi-bq-demo.bq_demo.customer` c
LEFT JOIN
`roi-bq-demo.bq_demo.order` o
ON
c.cust_id = o.cust_id
LEFT JOIN
`roi-bq-demo.bq_demo.line_item` AS li
ON
o.order_num = li.order_num
LEFT JOIN
`roi-bq-demo.bq_demo.product` AS p
ON
li.prod_code = p.prod_code);
CREATE OR REPLACE TABLE
`bq_demo.nested_once` AS (
WITH
dlow AS (
SELECT
*
FROM
`bq_demo.denorm` )
SELECT
cust_id,
cust_name,
cust_address,
cust_state,
cust_zip,
cust_email,
cust_phone,
order_num,
order_date,
ARRAY_AGG( STRUCT(line_item_num,
prod_code,
qty,
prod_name,
prod_desc,
prod_price)) AS line_items
FROM
dlow
GROUP BY
order_num,
order_date,
cust_phone,
cust_email,
cust_zip,
cust_state,
cust_address,
cust_name,
cust_id);
CREATE OR REPLACE TABLE
`bq_demo.table_nested_partitioned`
PARTITION BY
order_date AS (
SELECT
*
FROM
`bq_demo.nested_once`);
CREATE OR REPLACE TABLE
`bq_demo.table_nested_partitioned_clustered`
PARTITION BY
order_date
CLUSTER BY
cust_zip AS (
SELECT
*
FROM
`bq_demo.nested_once`)