/
ddl.sql
622 lines (587 loc) · 27.8 KB
/
ddl.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
619
620
621
622
create table dbgen_version
(
dv_version varchar(32),
dv_create_date date ,
dv_create_time timestamp,
dv_cmdline_args varchar(200)
);
create table customer_address
(
ca_address_sk int4 not null ,
ca_address_id char(16) not null ,
ca_street_number char(10) ,
ca_street_name varchar(60) ,
ca_street_type char(15) ,
ca_suite_number char(10) ,
ca_city varchar(60) ,
ca_county varchar(30) ,
ca_state char(2) ,
ca_zip char(10) ,
ca_country varchar(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type char(20)
,primary key (ca_address_sk)
) distkey(ca_address_sk);
create table customer_demographics
(
cd_demo_sk int4 not null ,
cd_gender char(1) ,
cd_marital_status char(1) ,
cd_education_status char(20) ,
cd_purchase_estimate int4 ,
cd_credit_rating char(10) ,
cd_dep_count int4 ,
cd_dep_employed_count int4 ,
cd_dep_college_count int4
,primary key (cd_demo_sk)
)distkey (cd_demo_sk);
create table date_dim
(
d_date_sk integer not null,
d_date_id char(16) not null,
d_date date,
d_month_seq integer ,
d_week_seq integer ,
d_quarter_seq integer ,
d_year integer ,
d_dow integer ,
d_moy integer ,
d_dom integer ,
d_qoy integer ,
d_fy_year integer ,
d_fy_quarter_seq integer ,
d_fy_week_seq integer ,
d_day_name char(9) ,
d_quarter_name char(6) ,
d_holiday char(1) ,
d_weekend char(1) ,
d_following_holiday char(1) ,
d_first_dom integer ,
d_last_dom integer ,
d_same_day_ly integer ,
d_same_day_lq integer ,
d_current_day char(1) ,
d_current_week char(1) ,
d_current_month char(1) ,
d_current_quarter char(1) ,
d_current_year char(1) ,
primary key (d_date_sk)
) diststyle all;
create table warehouse
(
w_warehouse_sk integer not null,
w_warehouse_id char(16) not null,
w_warehouse_name varchar(20) ,
w_warehouse_sq_ft integer ,
w_street_number char(10) ,
w_street_name varchar(60) ,
w_street_type char(15) ,
w_suite_number char(10) ,
w_city varchar(60) ,
w_county varchar(30) ,
w_state char(2) ,
w_zip char(10) ,
w_country varchar(20) ,
w_gmt_offset decimal(5,2) ,
primary key (w_warehouse_sk)
) diststyle all;
create table ship_mode
(
sm_ship_mode_sk integer not null,
sm_ship_mode_id char(16) not null,
sm_type char(30) ,
sm_code char(10) ,
sm_carrier char(20) ,
sm_contract char(20) ,
primary key (sm_ship_mode_sk)
) diststyle all;
create table time_dim
(
t_time_sk integer not null,
t_time_id char(16) not null,
t_time integer ,
t_hour integer ,
t_minute integer ,
t_second integer ,
t_am_pm char(2) ,
t_shift char(20) ,
t_sub_shift char(20) ,
t_meal_time char(20) ,
primary key (t_time_sk)
) diststyle all;
create table reason
(
r_reason_sk integer not null,
r_reason_id char(16) not null,
r_reason_desc char(100) ,
primary key (r_reason_sk)
) diststyle all ;
create table income_band
(
ib_income_band_sk integer not null,
ib_lower_bound integer ,
ib_upper_bound integer ,
primary key (ib_income_band_sk)
) diststyle all;
create table item
(
i_item_sk int4 not null,
i_item_id char(16) not null ,
i_rec_start_date date,
i_rec_end_date date,
i_item_desc varchar(200) ,
i_current_price numeric(7,2),
i_wholesale_cost numeric(7,2),
i_brand_id int4,
i_brand char(50) ,
i_class_id int4,
i_class char(50) ,
i_category_id int4,
i_category char(50) ,
i_manufact_id int4,
i_manufact char(50) ,
i_size char(20) ,
i_formulation char(20) ,
i_color char(20) ,
i_units char(10),
i_container char(10),
i_manager_id int4,
i_product_name char(50)
,primary key (i_item_sk)
) distkey(i_item_sk) sortkey(i_category);
create table store
(
s_store_sk integer not null,
s_store_id char(16) not null,
s_rec_start_date date,
s_rec_end_date date,
s_closed_date_sk integer ,
s_store_name varchar(50) ,
s_number_employees integer ,
s_floor_space integer ,
s_hours char(20) ,
s_manager varchar(40) ,
s_market_id integer ,
s_geography_class varchar(100) ,
s_market_desc varchar(100) ,
s_market_manager varchar(40) ,
s_division_id integer ,
s_division_name varchar(50) ,
s_company_id integer ,
s_company_name varchar(50) ,
s_street_number varchar(10) ,
s_street_name varchar(60) ,
s_street_type char(15) ,
s_suite_number char(10) ,
s_city varchar(60) ,
s_county varchar(30) ,
s_state char(2) ,
s_zip char(10) ,
s_country varchar(20) ,
s_gmt_offset decimal(5,2) ,
s_tax_precentage decimal(5,2) ,
primary key (s_store_sk)
) diststyle all;
create table call_center
(
cc_call_center_sk integer not null,
cc_call_center_id char(16) not null,
cc_rec_start_date date,
cc_rec_end_date date,
cc_closed_date_sk integer ,
cc_open_date_sk integer ,
cc_name varchar(50) ,
cc_class varchar(50) ,
cc_employees integer ,
cc_sq_ft integer ,
cc_hours char(20) ,
cc_manager varchar(40) ,
cc_mkt_id integer ,
cc_mkt_class char(50) ,
cc_mkt_desc varchar(100) ,
cc_market_manager varchar(40) ,
cc_division integer ,
cc_division_name varchar(50) ,
cc_company integer ,
cc_company_name char(50) ,
cc_street_number char(10) ,
cc_street_name varchar(60) ,
cc_street_type char(15) ,
cc_suite_number char(10) ,
cc_city varchar(60) ,
cc_county varchar(30) ,
cc_state char(2) ,
cc_zip char(10) ,
cc_country varchar(20) ,
cc_gmt_offset decimal(5,2) ,
cc_tax_percentage decimal(5,2) ,
primary key (cc_call_center_sk)
) diststyle all;
create table customer
(
c_customer_sk int4 not null ,
c_customer_id char(16) not null ,
c_current_cdemo_sk int4 ,
c_current_hdemo_sk int4 ,
c_current_addr_sk int4 ,
c_first_shipto_date_sk int4 ,
c_first_sales_date_sk int4 ,
c_salutation char(10) ,
c_first_name char(20) ,
c_last_name char(30) ,
c_preferred_cust_flag char(1) ,
c_birth_day int4 ,
c_birth_month int4 ,
c_birth_year int4 ,
c_birth_country varchar(20) ,
c_login char(13) ,
c_email_address char(50) ,
c_last_review_date_sk int4 ,
primary key (c_customer_sk)
) distkey(c_customer_sk);
create table web_site
(
web_site_sk integer not null,
web_site_id char(16) not null,
web_rec_start_date date,
web_rec_end_date date,
web_name varchar(50) ,
web_open_date_sk integer ,
web_close_date_sk integer ,
web_class varchar(50) ,
web_manager varchar(40) ,
web_mkt_id integer ,
web_mkt_class varchar(50) ,
web_mkt_desc varchar(100) ,
web_market_manager varchar(40) ,
web_company_id integer ,
web_company_name char(50) ,
web_street_number char(10) ,
web_street_name varchar(60) ,
web_street_type char(15) ,
web_suite_number char(10) ,
web_city varchar(60) ,
web_county varchar(30) ,
web_state char(2) ,
web_zip char(10) ,
web_country varchar(20) ,
web_gmt_offset decimal(5,2) ,
web_tax_percentage decimal(5,2) ,
primary key (web_site_sk)
) diststyle all;
create table store_returns
(
sr_returned_date_sk int4 ,
sr_return_time_sk int4 ,
sr_item_sk int4 not null ,
sr_customer_sk int4 ,
sr_cdemo_sk int4 ,
sr_hdemo_sk int4 ,
sr_addr_sk int4 ,
sr_store_sk int4 ,
sr_reason_sk int4 ,
sr_ticket_number int8 not null,
sr_return_quantity int4 ,
sr_return_amt numeric(7,2) ,
sr_return_tax numeric(7,2) ,
sr_return_amt_inc_tax numeric(7,2) ,
sr_fee numeric(7,2) ,
sr_return_ship_cost numeric(7,2) ,
sr_refunded_cash numeric(7,2) ,
sr_reversed_charge numeric(7,2) ,
sr_store_credit numeric(7,2) ,
sr_net_loss numeric(7,2)
,primary key (sr_item_sk, sr_ticket_number)
) distkey(sr_item_sk) sortkey(sr_returned_date_sk);
create table household_demographics
(
hd_demo_sk integer not null,
hd_income_band_sk integer ,
hd_buy_potential char(15) ,
hd_dep_count integer ,
hd_vehicle_count integer ,
primary key (hd_demo_sk)
) diststyle all;
create table web_page
(
wp_web_page_sk integer not null,
wp_web_page_id char(16) not null,
wp_rec_start_date date,
wp_rec_end_date date,
wp_creation_date_sk integer ,
wp_access_date_sk integer ,
wp_autogen_flag char(1) ,
wp_customer_sk integer ,
wp_url varchar(100) ,
wp_type char(50) ,
wp_char_count integer ,
wp_link_count integer ,
wp_image_count integer ,
wp_max_ad_count integer ,
primary key (wp_web_page_sk)
) diststyle all;
create table promotion
(
p_promo_sk integer not null,
p_promo_id char(16) not null,
p_start_date_sk integer ,
p_end_date_sk integer ,
p_item_sk integer ,
p_cost decimal(15,2) ,
p_response_target integer ,
p_promo_name char(50) ,
p_channel_dmail char(1) ,
p_channel_email char(1) ,
p_channel_catalog char(1) ,
p_channel_tv char(1) ,
p_channel_radio char(1) ,
p_channel_press char(1) ,
p_channel_event char(1) ,
p_channel_demo char(1) ,
p_channel_details varchar(100) ,
p_purpose char(15) ,
p_discount_active char(1) ,
primary key (p_promo_sk)
) diststyle all;
create table catalog_page
(
cp_catalog_page_sk integer not null,
cp_catalog_page_id char(16) not null,
cp_start_date_sk integer ,
cp_end_date_sk integer ,
cp_department varchar(50) ,
cp_catalog_number integer ,
cp_catalog_page_number integer ,
cp_description varchar(100) ,
cp_type varchar(100) ,
primary key (cp_catalog_page_sk)
) diststyle all;
create table inventory
(
inv_date_sk int4 not null ,
inv_item_sk int4 not null ,
inv_warehouse_sk int4 not null ,
inv_quantity_on_hand int4
,primary key (inv_date_sk, inv_item_sk, inv_warehouse_sk)
) distkey(inv_item_sk) sortkey(inv_date_sk);
create table catalog_returns
(
cr_returned_date_sk int4 ,
cr_returned_time_sk int4 ,
cr_item_sk int4 not null ,
cr_refunded_customer_sk int4 ,
cr_refunded_cdemo_sk int4 ,
cr_refunded_hdemo_sk int4 ,
cr_refunded_addr_sk int4 ,
cr_returning_customer_sk int4 ,
cr_returning_cdemo_sk int4 ,
cr_returning_hdemo_sk int4 ,
cr_returning_addr_sk int4 ,
cr_call_center_sk int4 ,
cr_catalog_page_sk int4 ,
cr_ship_mode_sk int4 ,
cr_warehouse_sk int4 ,
cr_reason_sk int4 ,
cr_order_number int8 not null,
cr_return_quantity int4 ,
cr_return_amount numeric(7,2) ,
cr_return_tax numeric(7,2) ,
cr_return_amt_inc_tax numeric(7,2) ,
cr_fee numeric(7,2) ,
cr_return_ship_cost numeric(7,2) ,
cr_refunded_cash numeric(7,2) ,
cr_reversed_charge numeric(7,2) ,
cr_store_credit numeric(7,2) ,
cr_net_loss numeric(7,2)
,primary key (cr_item_sk, cr_order_number)
) distkey(cr_item_sk) sortkey(cr_returned_date_sk);
create table web_returns
(
wr_returned_date_sk int4 ,
wr_returned_time_sk int4 ,
wr_item_sk int4 not null ,
wr_refunded_customer_sk int4 ,
wr_refunded_cdemo_sk int4 ,
wr_refunded_hdemo_sk int4 ,
wr_refunded_addr_sk int4 ,
wr_returning_customer_sk int4 ,
wr_returning_cdemo_sk int4 ,
wr_returning_hdemo_sk int4 ,
wr_returning_addr_sk int4 ,
wr_web_page_sk int4 ,
wr_reason_sk int4 ,
wr_order_number int8 not null,
wr_return_quantity int4 ,
wr_return_amt numeric(7,2) ,
wr_return_tax numeric(7,2) ,
wr_return_amt_inc_tax numeric(7,2) ,
wr_fee numeric(7,2) ,
wr_return_ship_cost numeric(7,2) ,
wr_refunded_cash numeric(7,2) ,
wr_reversed_charge numeric(7,2) ,
wr_account_credit numeric(7,2) ,
wr_net_loss numeric(7,2)
,primary key (wr_item_sk, wr_order_number)
) distkey(wr_order_number) sortkey(wr_returned_date_sk);
create table web_sales
(
ws_sold_date_sk int4 ,
ws_sold_time_sk int4 ,
ws_ship_date_sk int4 ,
ws_item_sk int4 not null ,
ws_bill_customer_sk int4 ,
ws_bill_cdemo_sk int4 ,
ws_bill_hdemo_sk int4 ,
ws_bill_addr_sk int4 ,
ws_ship_customer_sk int4 ,
ws_ship_cdemo_sk int4 ,
ws_ship_hdemo_sk int4 ,
ws_ship_addr_sk int4 ,
ws_web_page_sk int4 ,
ws_web_site_sk int4 ,
ws_ship_mode_sk int4 ,
ws_warehouse_sk int4 ,
ws_promo_sk int4 ,
ws_order_number int8 not null,
ws_quantity int4 ,
ws_wholesale_cost numeric(7,2) ,
ws_list_price numeric(7,2) ,
ws_sales_price numeric(7,2) ,
ws_ext_discount_amt numeric(7,2) ,
ws_ext_sales_price numeric(7,2) ,
ws_ext_wholesale_cost numeric(7,2) ,
ws_ext_list_price numeric(7,2) ,
ws_ext_tax numeric(7,2) ,
ws_coupon_amt numeric(7,2) ,
ws_ext_ship_cost numeric(7,2) ,
ws_net_paid numeric(7,2) ,
ws_net_paid_inc_tax numeric(7,2) ,
ws_net_paid_inc_ship numeric(7,2) ,
ws_net_paid_inc_ship_tax numeric(7,2) ,
ws_net_profit numeric(7,2)
,primary key (ws_item_sk, ws_order_number)
) distkey(ws_order_number) sortkey(ws_sold_date_sk);
create table catalog_sales
(
cs_sold_date_sk int4 ,
cs_sold_time_sk int4 ,
cs_ship_date_sk int4 ,
cs_bill_customer_sk int4 ,
cs_bill_cdemo_sk int4 ,
cs_bill_hdemo_sk int4 ,
cs_bill_addr_sk int4 ,
cs_ship_customer_sk int4 ,
cs_ship_cdemo_sk int4 ,
cs_ship_hdemo_sk int4 ,
cs_ship_addr_sk int4 ,
cs_call_center_sk int4 ,
cs_catalog_page_sk int4 ,
cs_ship_mode_sk int4 ,
cs_warehouse_sk int4 ,
cs_item_sk int4 not null ,
cs_promo_sk int4 ,
cs_order_number int8 not null ,
cs_quantity int4 ,
cs_wholesale_cost numeric(7,2) ,
cs_list_price numeric(7,2) ,
cs_sales_price numeric(7,2) ,
cs_ext_discount_amt numeric(7,2) ,
cs_ext_sales_price numeric(7,2) ,
cs_ext_wholesale_cost numeric(7,2) ,
cs_ext_list_price numeric(7,2) ,
cs_ext_tax numeric(7,2) ,
cs_coupon_amt numeric(7,2) ,
cs_ext_ship_cost numeric(7,2) ,
cs_net_paid numeric(7,2) ,
cs_net_paid_inc_tax numeric(7,2) ,
cs_net_paid_inc_ship numeric(7,2) ,
cs_net_paid_inc_ship_tax numeric(7,2) ,
cs_net_profit numeric(7,2)
,primary key (cs_item_sk, cs_order_number)
) distkey(cs_item_sk) sortkey(cs_sold_date_sk);
create table store_sales
(
ss_sold_date_sk int4 ,
ss_sold_time_sk int4 ,
ss_item_sk int4 not null ,
ss_customer_sk int4 ,
ss_cdemo_sk int4 ,
ss_hdemo_sk int4 ,
ss_addr_sk int4 ,
ss_store_sk int4 ,
ss_promo_sk int4 ,
ss_ticket_number int8 not null,
ss_quantity int4 ,
ss_wholesale_cost numeric(7,2) ,
ss_list_price numeric(7,2) ,
ss_sales_price numeric(7,2) ,
ss_ext_discount_amt numeric(7,2) ,
ss_ext_sales_price numeric(7,2) ,
ss_ext_wholesale_cost numeric(7,2) ,
ss_ext_list_price numeric(7,2) ,
ss_ext_tax numeric(7,2) ,
ss_coupon_amt numeric(7,2) ,
ss_net_paid numeric(7,2) ,
ss_net_paid_inc_tax numeric(7,2) ,
ss_net_profit numeric(7,2)
,primary key (ss_item_sk, ss_ticket_number)
) distkey(ss_item_sk) sortkey(ss_sold_date_sk);
/*
Text files needed to load test data under s3://redshift-downloads/TPC-DS/2.13/3TB are publicly available.
To load the sample data, you must provide authentication for your cluster to access Amazon S3 on your behalf.
You can provide authentication by referencing an IAM role that you have created. You can set an IAM_Role as the default for your cluster or you can directly provide the ARN of an IAM_Role.
For more information https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html
The COPY commands include a placeholder for IAM_Role, in this code IAM_Role clause is set to use the default IAM_Role. If your cluster does not have a IAM_Role set as default then please follow the instructions provided here:
https://docs.aws.amazon.com/redshift/latest/mgmt/default-iam-role.html
For more information check samples in https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html
**Note** another option to provide IAM_Role is to provide IAM_Role ARN in IAM_Role clause. For example
copy call_center from 's3://redshift-downloads/TPC-DS/2.13/3TB/call_center/' IAM_Role 'Replace text inside the quotes with Redshift cluster IAM_Role ARN' gzip delimiter '|' EMPTYASNULL region 'us-east-1';
*/
copy call_center from 's3://redshift-downloads/TPC-DS/2.13/3TB/call_center/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy catalog_page from 's3://redshift-downloads/TPC-DS/2.13/3TB/catalog_page/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy catalog_returns from 's3://redshift-downloads/TPC-DS/2.13/3TB/catalog_returns/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy catalog_sales from 's3://redshift-downloads/TPC-DS/2.13/3TB/catalog_sales/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy customer_address from 's3://redshift-downloads/TPC-DS/2.13/3TB/customer_address/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy customer_demographics from 's3://redshift-downloads/TPC-DS/2.13/3TB/customer_demographics/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy customer from 's3://redshift-downloads/TPC-DS/2.13/3TB/customer/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy date_dim from 's3://redshift-downloads/TPC-DS/2.13/3TB/date_dim/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy household_demographics from 's3://redshift-downloads/TPC-DS/2.13/3TB/household_demographics/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy income_band from 's3://redshift-downloads/TPC-DS/2.13/3TB/income_band/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy inventory from 's3://redshift-downloads/TPC-DS/2.13/3TB/inventory/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy item from 's3://redshift-downloads/TPC-DS/2.13/3TB/item/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy promotion from 's3://redshift-downloads/TPC-DS/2.13/3TB/promotion/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy reason from 's3://redshift-downloads/TPC-DS/2.13/3TB/reason/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy ship_mode from 's3://redshift-downloads/TPC-DS/2.13/3TB/ship_mode/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy store from 's3://redshift-downloads/TPC-DS/2.13/3TB/store/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy store_returns from 's3://redshift-downloads/TPC-DS/2.13/3TB/store_returns/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy store_sales from 's3://redshift-downloads/TPC-DS/2.13/3TB/store_sales/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy time_dim from 's3://redshift-downloads/TPC-DS/2.13/3TB/time_dim/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy warehouse from 's3://redshift-downloads/TPC-DS/2.13/3TB/warehouse/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy web_page from 's3://redshift-downloads/TPC-DS/2.13/3TB/web_page/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy web_returns from 's3://redshift-downloads/TPC-DS/2.13/3TB/web_returns/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy web_sales from 's3://redshift-downloads/TPC-DS/2.13/3TB/web_sales/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
copy web_site from 's3://redshift-downloads/TPC-DS/2.13/3TB/web_site/' iam_role default gzip delimiter '|' EMPTYASNULL region 'us-east-1';
select count(*) from call_center; -- 48
select count(*) from catalog_page; -- 36000
select count(*) from catalog_returns; -- 432018033
select count(*) from catalog_sales; -- 4320078880
select count(*) from customer; -- 30000000
select count(*) from customer_address; -- 15000000
select count(*) from customer_demographics; -- 1920800
select count(*) from date_dim; -- 73049
select count(*) from household_demographics; -- 7200
select count(*) from income_band; -- 20
select count(*) from inventory; -- 1033560000
select count(*) from item; -- 360000
select count(*) from promotion; -- 1800
select count(*) from reason; -- 67
select count(*) from ship_mode; -- 20
select count(*) from store; -- 1350
select count(*) from store_returns; -- 863989652
select count(*) from store_sales; -- 8639936081
select count(*) from time_dim; -- 86400
select count(*) from warehouse; -- 22
select count(*) from web_page; -- 3600
select count(*) from web_returns; -- 216003761
select count(*) from web_sales; -- 2159968881
select count(*) from web_site; -- 66