forked from fivetran/benchmark
-
Notifications
You must be signed in to change notification settings - Fork 0
/
500-PopulateAzure.sql
executable file
·618 lines (569 loc) · 23 KB
/
500-PopulateAzure.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
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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
-- drop table call_center;
-- drop table catalog_page;
-- drop table catalog_returns;
-- drop table catalog_sales ;
-- drop table customer_address ;
-- drop table customer_demographics ;
-- drop table customer ;
-- drop table date_dim ;
-- drop table household_demographics ;
-- drop table income_band;
-- drop table inventory ;
-- drop table item ;
-- drop table promotion ;
-- drop table reason;
-- drop table ship_mode;
-- drop table store_returns ;
-- drop table store_sales ;
-- drop table store ;
-- drop table time_dim ;
-- drop table warehouse;
-- drop table web_page;
-- drop table web_returns ;
-- drop table web_sales ;
-- drop table web_site ;
create table call_center(
cc_call_center_sk bigint
, cc_call_center_id nvarchar(16)
, cc_rec_start_date nvarchar(10)
, cc_rec_end_date nvarchar(10)
, cc_closed_date_sk bigint
, cc_open_date_sk bigint
, cc_name nvarchar(50)
, cc_class nvarchar(50)
, cc_employees int
, cc_sq_ft int
, cc_hours nvarchar(20)
, cc_manager nvarchar(40)
, cc_mkt_id int
, cc_mkt_class nvarchar(50)
, cc_mkt_desc nvarchar(100)
, cc_market_manager nvarchar(40)
, cc_division int
, cc_division_name nvarchar(50)
, cc_company int
, cc_company_name nvarchar(50)
, cc_street_number nvarchar(10)
, cc_street_name nvarchar(60)
, cc_street_type nvarchar(15)
, cc_suite_number nvarchar(10)
, cc_city nvarchar(60)
, cc_county nvarchar(30)
, cc_state nvarchar(2)
, cc_zip nvarchar(10)
, cc_country nvarchar(20)
, cc_gmt_offset float
, cc_tax_percentage float
);
create table catalog_page(
cp_catalog_page_sk bigint
, cp_catalog_page_id nvarchar(16)
, cp_start_date_sk bigint
, cp_end_date_sk bigint
, cp_department nvarchar(50)
, cp_catalog_number int
, cp_catalog_page_number int
, cp_description nvarchar(100)
, cp_type nvarchar(100)
);
create table catalog_returns(
cr_returned_date_sk bigint,
cr_returned_time_sk bigint,
cr_item_sk bigint,
cr_refunded_customer_sk bigint,
cr_refunded_cdemo_sk bigint,
cr_refunded_hdemo_sk bigint,
cr_refunded_addr_sk bigint,
cr_returning_customer_sk bigint,
cr_returning_cdemo_sk bigint,
cr_returning_hdemo_sk bigint,
cr_returning_addr_sk bigint,
cr_call_center_sk bigint,
cr_catalog_page_sk bigint,
cr_ship_mode_sk bigint,
cr_warehouse_sk bigint,
cr_reason_sk bigint,
cr_order_number bigint,
cr_return_quantity int,
cr_return_amount float,
cr_return_tax float,
cr_return_amt_inc_tax float,
cr_fee float,
cr_return_ship_cost float,
cr_refunded_cash float,
cr_reversed_charge float,
cr_store_credit float,
cr_net_loss float
);
create table catalog_sales (
cs_sold_date_sk bigint,
cs_sold_time_sk bigint,
cs_ship_date_sk bigint,
cs_bill_customer_sk bigint,
cs_bill_cdemo_sk bigint,
cs_bill_hdemo_sk bigint,
cs_bill_addr_sk bigint,
cs_ship_customer_sk bigint,
cs_ship_cdemo_sk bigint,
cs_ship_hdemo_sk bigint,
cs_ship_addr_sk bigint,
cs_call_center_sk bigint,
cs_catalog_page_sk bigint,
cs_ship_mode_sk bigint,
cs_warehouse_sk bigint,
cs_item_sk bigint,
cs_promo_sk bigint,
cs_order_number bigint,
cs_quantity int,
cs_wholesale_cost float,
cs_list_price float,
cs_sales_price float,
cs_ext_discount_amt float,
cs_ext_sales_price float,
cs_ext_wholesale_cost float,
cs_ext_list_price float,
cs_ext_tax float,
cs_coupon_amt float,
cs_ext_ship_cost float,
cs_net_paid float,
cs_net_paid_inc_tax float,
cs_net_paid_inc_ship float,
cs_net_paid_inc_ship_tax float,
cs_net_profit float
);
create table customer_address (
ca_address_sk bigint,
ca_address_id nvarchar(16),
ca_street_number nvarchar(10),
ca_street_name nvarchar(60),
ca_street_type nvarchar(15),
ca_suite_number nvarchar(10),
ca_city nvarchar(60),
ca_county nvarchar(30),
ca_state nvarchar(2),
ca_zip nvarchar(10),
ca_country nvarchar(20),
ca_gmt_offset float,
ca_location_type nvarchar(20)
);
create table customer_demographics (
cd_demo_sk bigint,
cd_gender nvarchar(1),
cd_marital_status nvarchar(1),
cd_education_status nvarchar(20),
cd_purchase_estimate int,
cd_credit_rating nvarchar(10),
cd_dep_count int,
cd_dep_employed_count int,
cd_dep_college_count int
);
create table customer (
c_customer_sk bigint,
c_customer_id nvarchar(16),
c_current_cdemo_sk bigint,
c_current_hdemo_sk bigint,
c_current_addr_sk bigint,
c_first_shipto_date_sk bigint,
c_first_sales_date_sk bigint,
c_salutation nvarchar(10),
c_first_name nvarchar(20),
c_last_name nvarchar(30),
c_preferred_cust_flag nvarchar(1),
c_birth_day int,
c_birth_month int,
c_birth_year int,
c_birth_country nvarchar(20),
c_login nvarchar(13),
c_email_address nvarchar(50),
c_last_review_date nvarchar(10)
);
create table date_dim (
d_date_sk bigint,
d_date_id nvarchar(16),
d_date nvarchar(10),
d_month_seq int,
d_week_seq int,
d_quarter_seq int,
d_year int,
d_dow int,
d_moy int,
d_dom int,
d_qoy int,
d_fy_year int,
d_fy_quarter_seq int,
d_fy_week_seq int,
d_day_name nvarchar(9),
d_quarter_name nvarchar(6),
d_holiday nvarchar(1),
d_weekend nvarchar(1),
d_following_holiday nvarchar(1),
d_first_dom int,
d_last_dom int,
d_same_day_ly int,
d_same_day_lq int,
d_current_day nvarchar(1),
d_current_week nvarchar(1),
d_current_month nvarchar(1),
d_current_quarter nvarchar(1),
d_current_year nvarchar(1)
);
create table household_demographics (
hd_demo_sk bigint,
hd_income_band_sk bigint,
hd_buy_potential nvarchar(15),
hd_dep_count int,
hd_vehicle_count int
);
create table income_band(
ib_income_band_sk bigint
, ib_lower_bound int
, ib_upper_bound int
);
create table inventory (
inv_date_sk bigint,
inv_item_sk bigint,
inv_warehouse_sk bigint,
inv_quantity_on_hand int
);
create table item (
i_item_sk bigint,
i_item_id nvarchar(16),
i_rec_start_date nvarchar(10),
i_rec_end_date nvarchar(10),
i_item_desc nvarchar(200),
i_current_price float,
i_wholesale_cost float,
i_brand_id int,
i_brand nvarchar(50),
i_class_id int,
i_class nvarchar(50),
i_category_id int,
i_category nvarchar(50),
i_manufact_id int,
i_manufact nvarchar(50),
i_size nvarchar(20),
i_formulation nvarchar(20),
i_color nvarchar(20),
i_units nvarchar(10),
i_container nvarchar(10),
i_manager_id int,
i_product_name nvarchar(50)
);
create table promotion (
p_promo_sk bigint,
p_promo_id nvarchar(16),
p_start_date_sk bigint,
p_end_date_sk bigint,
p_item_sk bigint,
p_cost float,
p_response_target int,
p_promo_name nvarchar(50),
p_channel_dmail nvarchar(1),
p_channel_email nvarchar(1),
p_channel_catalog nvarchar(1),
p_channel_tv nvarchar(1),
p_channel_radio nvarchar(1),
p_channel_press nvarchar(1),
p_channel_event nvarchar(1),
p_channel_demo nvarchar(1),
p_channel_details nvarchar(100),
p_purpose nvarchar(15),
p_discount_active nvarchar(1)
);
create table reason(
r_reason_sk bigint
, r_reason_id nvarchar(16)
, r_reason_desc nvarchar(100)
);
create table ship_mode(
sm_ship_mode_sk bigint
, sm_ship_mode_id nvarchar(16)
, sm_type nvarchar(30)
, sm_code nvarchar(10)
, sm_carrier nvarchar(20)
, sm_contract nvarchar(20)
);
create table store_returns (
sr_returned_date_sk bigint,
sr_return_time_sk bigint,
sr_item_sk bigint,
sr_customer_sk bigint,
sr_cdemo_sk bigint,
sr_hdemo_sk bigint,
sr_addr_sk bigint,
sr_store_sk bigint,
sr_reason_sk bigint,
sr_ticket_number bigint,
sr_return_quantity int,
sr_return_amt float,
sr_return_tax float,
sr_return_amt_inc_tax float,
sr_fee float,
sr_return_ship_cost float,
sr_refunded_cash float,
sr_reversed_charge float,
sr_store_credit float,
sr_net_loss float
);
create table store_sales (
ss_sold_date_sk bigint,
ss_sold_time_sk bigint,
ss_item_sk bigint,
ss_customer_sk bigint,
ss_cdemo_sk bigint,
ss_hdemo_sk bigint,
ss_addr_sk bigint,
ss_store_sk bigint,
ss_promo_sk bigint,
ss_ticket_number bigint,
ss_quantity int,
ss_wholesale_cost float,
ss_list_price float,
ss_sales_price float,
ss_ext_discount_amt float,
ss_ext_sales_price float,
ss_ext_wholesale_cost float,
ss_ext_list_price float,
ss_ext_tax float,
ss_coupon_amt float,
ss_net_paid float,
ss_net_paid_inc_tax float,
ss_net_profit float
);
create table store (
s_store_sk bigint,
s_store_id nvarchar(16),
s_rec_start_date nvarchar(10),
s_rec_end_date nvarchar(10),
s_closed_date_sk bigint,
s_store_name nvarchar(50),
s_number_employees int,
s_floor_space int,
s_hours nvarchar(20),
s_manager nvarchar(40),
s_market_id int,
s_geography_class nvarchar(100),
s_market_desc nvarchar(100),
s_market_manager nvarchar(40),
s_division_id int,
s_division_name nvarchar(50),
s_company_id int,
s_company_name nvarchar(50),
s_street_number nvarchar(10),
s_street_name nvarchar(60),
s_street_type nvarchar(15),
s_suite_number nvarchar(10),
s_city nvarchar(60),
s_county nvarchar(30),
s_state nvarchar(2),
s_zip nvarchar(10),
s_country nvarchar(20),
s_gmt_offset float,
s_tax_precentage float
);
create table time_dim (
t_time_sk bigint,
t_time_id nvarchar(16),
t_time int,
t_hour int,
t_minute int,
t_second int,
t_am_pm nvarchar(2),
t_shift nvarchar(20),
t_sub_shift nvarchar(20),
t_meal_time nvarchar(20)
);
create table warehouse(
w_warehouse_sk bigint
, w_warehouse_id nvarchar(16)
, w_warehouse_name nvarchar(20)
, w_warehouse_sq_ft int
, w_street_number nvarchar(10)
, w_street_name nvarchar(60)
, w_street_type nvarchar(15)
, w_suite_number nvarchar(10)
, w_city nvarchar(60)
, w_county nvarchar(30)
, w_state nvarchar(2)
, w_zip nvarchar(10)
, w_country nvarchar(20)
, w_gmt_offset float
);
create table web_page(
wp_web_page_sk bigint
, wp_web_page_id nvarchar(16)
, wp_rec_start_date nvarchar(10)
, wp_rec_end_date nvarchar(10)
, wp_creation_date_sk bigint
, wp_access_date_sk bigint
, wp_autogen_flag nvarchar(1)
, wp_customer_sk bigint
, wp_url nvarchar(100)
, wp_type nvarchar(50)
, wp_char_count int
, wp_link_count int
, wp_image_count int
, wp_max_ad_count int
);
create table web_returns (
wr_returned_date_sk bigint,
wr_returned_time_sk bigint,
wr_item_sk bigint,
wr_refunded_customer_sk bigint,
wr_refunded_cdemo_sk bigint,
wr_refunded_hdemo_sk bigint,
wr_refunded_addr_sk bigint,
wr_returning_customer_sk bigint,
wr_returning_cdemo_sk bigint,
wr_returning_hdemo_sk bigint,
wr_returning_addr_sk bigint,
wr_web_page_sk bigint,
wr_reason_sk bigint,
wr_order_number bigint,
wr_return_quantity int,
wr_return_amt float,
wr_return_tax float,
wr_return_amt_inc_tax float,
wr_fee float,
wr_return_ship_cost float,
wr_refunded_cash float,
wr_reversed_charge float,
wr_account_credit float,
wr_net_loss float
);
create table web_sales (
ws_sold_date_sk bigint,
ws_sold_time_sk bigint,
ws_ship_date_sk bigint,
ws_item_sk bigint,
ws_bill_customer_sk bigint,
ws_bill_cdemo_sk bigint,
ws_bill_hdemo_sk bigint,
ws_bill_addr_sk bigint,
ws_ship_customer_sk bigint,
ws_ship_cdemo_sk bigint,
ws_ship_hdemo_sk bigint,
ws_ship_addr_sk bigint,
ws_web_page_sk bigint,
ws_web_site_sk bigint,
ws_ship_mode_sk bigint,
ws_warehouse_sk bigint,
ws_promo_sk bigint,
ws_order_number bigint,
ws_quantity int,
ws_wholesale_cost float,
ws_list_price float,
ws_sales_price float,
ws_ext_discount_amt float,
ws_ext_sales_price float,
ws_ext_wholesale_cost float,
ws_ext_list_price float,
ws_ext_tax float,
ws_coupon_amt float,
ws_ext_ship_cost float,
ws_net_paid float,
ws_net_paid_inc_tax float,
ws_net_paid_inc_ship float,
ws_net_paid_inc_ship_tax float,
ws_net_profit float
);
create table web_site (
web_site_sk bigint,
web_site_id nvarchar(16),
web_rec_start_date nvarchar(10),
web_rec_end_date nvarchar(10),
web_name nvarchar(50),
web_open_date_sk bigint,
web_close_date_sk bigint,
web_class nvarchar(50),
web_manager nvarchar(40),
web_mkt_id int,
web_mkt_class nvarchar(50),
web_mkt_desc nvarchar(100),
web_market_manager nvarchar(40),
web_company_id int,
web_company_name nvarchar(50),
web_street_number nvarchar(10),
web_street_name nvarchar(60),
web_street_type nvarchar(15),
web_suite_number nvarchar(10),
web_city nvarchar(60),
web_county nvarchar(30),
web_state nvarchar(2),
web_zip nvarchar(10),
web_country nvarchar(20),
web_gmt_offset float,
web_tax_percentage float
);
copy into call_center
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/call_center/'
with (file_type = 'CSV', fieldterminator = '|');
copy into catalog_page
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/catalog_page/'
with (file_type = 'CSV', fieldterminator = '|');
copy into catalog_returns
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/catalog_returns/'
with (file_type = 'CSV', fieldterminator = '|');
copy into catalog_sales
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/catalog_sales/'
with (file_type = 'CSV', fieldterminator = '|');
copy into customer
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/customer/'
with (file_type = 'CSV', fieldterminator = '|');
copy into customer_address
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/customer_address/'
with (file_type = 'CSV', fieldterminator = '|');
copy into customer_demographics
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/customer_demographics/'
with (file_type = 'CSV', fieldterminator = '|');
copy into date_dim
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/date_dim/'
with (file_type = 'CSV', fieldterminator = '|');
copy into household_demographics
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/household_demographics/'
with (file_type = 'CSV', fieldterminator = '|');
copy into income_band
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/income_band/'
with (file_type = 'CSV', fieldterminator = '|');
copy into inventory
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/inventory/'
with (file_type = 'CSV', fieldterminator = '|');
copy into item
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/item/'
with (file_type = 'CSV', fieldterminator = '|');
copy into promotion
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/promotion/'
with (file_type = 'CSV', fieldterminator = '|');
copy into reason
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/reason/'
with (file_type = 'CSV', fieldterminator = '|');
copy into ship_mode
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/ship_mode/'
with (file_type = 'CSV', fieldterminator = '|');
copy into store
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/store/'
with (file_type = 'CSV', fieldterminator = '|');
copy into store_returns
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/store_returns/'
with (file_type = 'CSV', fieldterminator = '|');
copy into store_sales
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/store_sales/'
with (file_type = 'CSV', fieldterminator = '|');
copy into time_dim
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/time_dim/'
with (file_type = 'CSV', fieldterminator = '|');
copy into warehouse
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/warehouse/'
with (file_type = 'CSV', fieldterminator = '|');
copy into web_page
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/web_page/'
with (file_type = 'CSV', fieldterminator = '|');
copy into web_returns
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/web_returns/'
with (file_type = 'CSV', fieldterminator = '|');
copy into web_sales
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/web_sales/'
with (file_type = 'CSV', fieldterminator = '|');
copy into web_site
from 'https://fivetranbenchmark.blob.core.windows.net/tpcds/tpcds_1000_dat/web_site/'
with (file_type = 'CSV', fieldterminator = '|');