Repository navigation
Expand file tree
/
Copy pathsetup_guide.sql
More file actions
156 lines (128 loc) · 4.2 KB
/
Copy pathsetup_guide.sql
File metadata and controls
156 lines (128 loc) · 4.2 KB
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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
-- setting a role and creating a database and schema for lab data
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE DATABASE cortex_hol;
CREATE OR REPLACE SCHEMA raw_pos;
-- create temp warehouse for loading files from stage
CREATE OR REPLACE WAREHOUSE demo_build_wh
WAREHOUSE_SIZE = 'xlarge'
WAREHOUSE_TYPE = 'standard'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
INITIALLY_SUSPENDED = TRUE
COMMENT = 'demo build warehouse for hol assets';
USE WAREHOUSE demo_build_wh;
-- create file format
CREATE OR REPLACE FILE FORMAT cortex_hol.raw_pos.csvformat
TYPE = 'csv'
FIELD_OPTIONALLY_ENCLOSED_BY = '"';
-- create stage for cortex hol data
CREATE OR REPLACE STAGE cortex_hol.raw_pos.s3load
COMMENT = 'Quickstarts S3 Stage Connection'
url = 's3://coalesce-hol-data/cortex-hol-data/'
file_format = cortex_hol.raw_pos.csvformat;
-- menu table build
CREATE OR REPLACE TABLE cortex_hol.raw_pos.menu
(
menu_id NUMBER(19,0),
menu_type_id NUMBER(38,0),
menu_type VARCHAR(16777216),
truck_brand_name VARCHAR(16777216),
menu_item_id NUMBER(38,0),
menu_item_name VARCHAR(16777216),
item_category VARCHAR(16777216),
item_subcategory VARCHAR(16777216),
cost_of_goods_usd NUMBER(38,4),
sale_price_usd NUMBER(38,4),
menu_item_health_metrics_obj VARIANT
);
-- order_header table build
CREATE OR REPLACE TABLE cortex_hol.raw_pos.order_header
(
order_id NUMBER(38,0),
truck_id NUMBER(38,0),
location_id FLOAT,
customer_id NUMBER(38,0),
discount_id VARCHAR(16777216),
shift_id NUMBER(38,0),
shift_start_time TIME(9),
shift_end_time TIME(9),
order_channel VARCHAR(16777216),
order_ts TIMESTAMP_NTZ(9),
served_ts VARCHAR(16777216),
order_currency VARCHAR(3),
order_amount NUMBER(38,4),
order_tax_amount VARCHAR(16777216),
order_discount_amount VARCHAR(16777216),
order_total NUMBER(38,4)
);
-- order_detail table build
CREATE OR REPLACE TABLE cortex_hol.raw_pos.order_detail
(
order_detail_id NUMBER(38,0),
order_id NUMBER(38,0),
menu_item_id NUMBER(38,0),
discount_id VARCHAR(16777216),
line_number NUMBER(38,0),
quantity NUMBER(5,0),
unit_price NUMBER(38,4),
price NUMBER(38,4),
order_item_discount_amount VARCHAR(16777216)
);
-- customer loyalty table build
CREATE OR REPLACE TABLE cortex_hol.raw_pos.customers
(
customer_id NUMBER(38,0),
first_name VARCHAR(16777216),
last_name VARCHAR(16777216),
city VARCHAR(16777216),
country VARCHAR(16777216),
postal_code VARCHAR(16777216),
preferred_language VARCHAR(16777216),
gender VARCHAR(16777216),
favourite_brand VARCHAR(16777216),
marital_status VARCHAR(16777216),
children_count VARCHAR(16777216),
sign_up_date DATE,
birthday_date DATE,
e_mail VARCHAR(16777216),
phone_number VARCHAR(16777216)
);
-- review table build
CREATE OR REPLACE TABLE cortex_hol.raw_pos.reviews (
REVIEW_ID STRING,
CUSTOMER_ID INT,
STAR_RATING INT,
REVIEW_TEXT STRING,
REVIEW_DATE DATE
);
--new orders table
CREATE OR REPLACE TABLE cortex_hol.raw_pos.new_orders (
ORDER_TS TIMESTAMP_NTZ,
ORDER_AMOUNT NUMBER(12,2),
MENU_ITEM_NAME STRING
);
-- menu table load
COPY INTO cortex_hol.raw_pos.menu
FROM @cortex_hol.raw_pos.s3load/menu/;
-- order_header table load
COPY INTO cortex_hol.raw_pos.order_header
FROM @cortex_hol.raw_pos.s3load/order-header/;
-- order_detail table load
COPY INTO cortex_hol.raw_pos.order_detail
FROM @cortex_hol.raw_pos.s3load/order-detail/;
-- reviews table load
COPY INTO cortex_hol.raw_pos.customers
FROM @cortex_hol.raw_pos.s3load/customer/;
COPY INTO cortex_hol.raw_pos.reviews
FROM @cortex_hol.raw_pos.s3load/reviews/;
COPY INTO cortex_hol.raw_pos.new_orders
FROM @cortex_hol.raw_pos.s3load/new_orders/;
-- remove xlarge warehouse
DROP WAREHOUSE IF EXISTS demo_build_wh;
-- provide usage to new tables for pc_coalesce_role
GRANT USAGE ON DATABASE cortex_hol TO ROLE pc_coalesce_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE cortex_hol TO ROLE pc_coalesce_role;
GRANT ALL ON SCHEMA cortex_hol.raw_pos TO ROLE pc_coalesce_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cortex_hol.raw_pos TO ROLE pc_coalesce_role;
-- setup completion note
SELECT 'Setup for your HOL is now complete' AS note;