· 6 years ago · Oct 08, 2019, 09:32 PM
1from burgos.utils import trim
2
3class TPCDSCreateTableDDL(object):
4 customer_address = """
5create table if not exists customer_address
6(
7 ca_address_sk int4 not null ,
8 ca_address_id char(16) not null ,
9 ca_street_number char(10) ,
10 ca_street_name varchar(60) ,
11 ca_street_type char(15) ,
12 ca_suite_number char(10) ,
13 ca_city varchar(60) ,
14 ca_county varchar(30) ,
15 ca_state char(2) ,
16 ca_zip char(10) ,
17 ca_country varchar(20) ,
18 ca_gmt_offset numeric(5,2) ,
19 ca_location_type char(20)
20 ,primary key (ca_address_sk)
21) distkey(ca_address_sk);
22 """
23
24 customer_demographics = """
25create table if not exists customer_demographics
26(
27 cd_demo_sk int4 not null ,
28 cd_gender char(1) ,
29 cd_marital_status char(1) ,
30 cd_education_status char(20) ,
31 cd_purchase_estimate int4 ,
32 cd_credit_rating char(10) ,
33 cd_dep_count int4 ,
34 cd_dep_employed_count int4 ,
35 cd_dep_college_count int4
36 ,primary key (cd_demo_sk)
37)distkey (cd_demo_sk);
38 """
39
40 date_dim = """
41create table if not exists date_dim
42(
43 d_date_sk integer not null,
44 d_date_id char(16) not null,
45 d_date date,
46 d_month_seq integer ,
47 d_week_seq integer ,
48 d_quarter_seq integer ,
49 d_year integer ,
50 d_dow integer ,
51 d_moy integer ,
52 d_dom integer ,
53 d_qoy integer ,
54 d_fy_year integer ,
55 d_fy_quarter_seq integer ,
56 d_fy_week_seq integer ,
57 d_day_name char(9) ,
58 d_quarter_name char(6) ,
59 d_holiday char(1) ,
60 d_weekend char(1) ,
61 d_following_holiday char(1) ,
62 d_first_dom integer ,
63 d_last_dom integer ,
64 d_same_day_ly integer ,
65 d_same_day_lq integer ,
66 d_current_day char(1) ,
67 d_current_week char(1) ,
68 d_current_month char(1) ,
69 d_current_quarter char(1) ,
70 d_current_year char(1) ,
71 primary key (d_date_sk)
72) diststyle all;
73 """
74
75 warehouse = """
76create table if not exists warehouse
77(
78 w_warehouse_sk integer not null,
79 w_warehouse_id char(16) not null,
80 w_warehouse_name varchar(20) ,
81 w_warehouse_sq_ft integer ,
82 w_street_number char(10) ,
83 w_street_name varchar(60) ,
84 w_street_type char(15) ,
85 w_suite_number char(10) ,
86 w_city varchar(60) ,
87 w_county varchar(30) ,
88 w_state char(2) ,
89 w_zip char(10) ,
90 w_country varchar(20) ,
91 w_gmt_offset decimal(5,2) ,
92 primary key (w_warehouse_sk)
93) diststyle all;
94 """
95
96 ship_mode = """create table if not exists ship_mode
97(
98 sm_ship_mode_sk integer not null,
99 sm_ship_mode_id char(16) not null,
100 sm_type char(30) ,
101 sm_code char(10) ,
102 sm_carrier char(20) ,
103 sm_contract char(20) ,
104 primary key (sm_ship_mode_sk)
105) diststyle all;
106 """
107
108 time_dim = """
109create table if not exists time_dim
110(
111 t_time_sk integer not null,
112 t_time_id char(16) not null,
113 t_time integer ,
114 t_hour integer ,
115 t_minute integer ,
116 t_second integer ,
117 t_am_pm char(2) ,
118 t_shift char(20) ,
119 t_sub_shift char(20) ,
120 t_meal_time char(20) ,
121 primary key (t_time_sk)
122) diststyle all;
123 """
124
125 reason = """
126create table if not exists reason
127(
128 r_reason_sk integer not null,
129 r_reason_id char(16) not null,
130 r_reason_desc char(100) ,
131 primary key (r_reason_sk)
132) diststyle all ;
133 """
134
135 income_band = """
136create table if not exists income_band
137(
138 ib_income_band_sk integer not null,
139 ib_lower_bound integer ,
140 ib_upper_bound integer ,
141 primary key (ib_income_band_sk)
142) diststyle all;
143 """
144
145 item = """
146create table if not exists item
147(
148i_item_sk int4 not null,
149 i_item_id char(16) not null ,
150 i_rec_start_date date,
151 i_rec_end_date date,
152 i_item_desc varchar(200) ,
153 i_current_price numeric(7,2),
154 i_wholesale_cost numeric(7,2),
155 i_brand_id int4,
156 i_brand char(50) ,
157 i_class_id int4,
158 i_class char(50) ,
159 i_category_id int4,
160 i_category char(50) ,
161 i_manufact_id int4,
162 i_manufact char(50) ,
163 i_size char(20) ,
164 i_formulation char(20) ,
165 i_color char(20) ,
166 i_units char(10),
167 i_container char(10),
168 i_manager_id int4,
169 i_product_name char(50)
170 ,primary key (i_item_sk)
171) distkey(i_item_sk) sortkey(i_category);
172 """
173
174 store = """
175create table if not exists store
176(
177 s_store_sk integer not null,
178 s_store_id char(16) not null,
179 s_rec_start_date date,
180 s_rec_end_date date,
181 s_closed_date_sk integer ,
182 s_store_name varchar(50) ,
183 s_number_employees integer ,
184 s_floor_space integer ,
185 s_hours char(20) ,
186 s_manager varchar(40) ,
187 s_market_id integer ,
188 s_geography_class varchar(100) ,
189 s_market_desc varchar(100) ,
190 s_market_manager varchar(40) ,
191 s_division_id integer ,
192 s_division_name varchar(50) ,
193 s_company_id integer ,
194 s_company_name varchar(50) ,
195 s_street_number varchar(10) ,
196 s_street_name varchar(60) ,
197 s_street_type char(15) ,
198 s_suite_number char(10) ,
199 s_city varchar(60) ,
200 s_county varchar(30) ,
201 s_state char(2) ,
202 s_zip char(10) ,
203 s_country varchar(20) ,
204 s_gmt_offset decimal(5,2) ,
205 s_tax_precentage decimal(5,2) ,
206 primary key (s_store_sk)
207) diststyle all;
208 """
209
210 call_center = """
211create table if not exists call_center
212(
213 cc_call_center_sk integer not null,
214 cc_call_center_id char(16) not null,
215 cc_rec_start_date date,
216 cc_rec_end_date date,
217 cc_closed_date_sk integer ,
218 cc_open_date_sk integer ,
219 cc_name varchar(50) ,
220 cc_class varchar(50) ,
221 cc_employees integer ,
222 cc_sq_ft integer ,
223 cc_hours char(20) ,
224 cc_manager varchar(40) ,
225 cc_mkt_id integer ,
226 cc_mkt_class char(50) ,
227 cc_mkt_desc varchar(100) ,
228 cc_market_manager varchar(40) ,
229 cc_division integer ,
230 cc_division_name varchar(50) ,
231 cc_company integer ,
232 cc_company_name char(50) ,
233 cc_street_number char(10) ,
234 cc_street_name varchar(60) ,
235 cc_street_type char(15) ,
236 cc_suite_number char(10) ,
237 cc_city varchar(60) ,
238 cc_county varchar(30) ,
239 cc_state char(2) ,
240 cc_zip char(10) ,
241 cc_country varchar(20) ,
242 cc_gmt_offset decimal(5,2) ,
243 cc_tax_percentage decimal(5,2) ,
244 primary key (cc_call_center_sk)
245) diststyle all;
246 """
247
248 customer = """
249create table if not exists customer
250(
251 c_customer_sk int4 not null ,
252 c_customer_id char(16) not null ,
253 c_current_cdemo_sk int4 ,
254 c_current_hdemo_sk int4 ,
255 c_current_addr_sk int4 ,
256 c_first_shipto_date_sk int4 ,
257 c_first_sales_date_sk int4 ,
258 c_salutation char(10) ,
259 c_first_name char(20) ,
260 c_last_name char(30) ,
261 c_preferred_cust_flag char(1) ,
262 c_birth_day int4 ,
263 c_birth_month int4 ,
264 c_birth_year int4 ,
265 c_birth_country varchar(20) ,
266 c_login char(13) ,
267 c_email_address char(50) ,
268 c_last_review_date_sk int4 ,
269 primary key (c_customer_sk)
270) distkey(c_customer_sk);
271 """
272
273 web_site = """
274create table if not exists web_site
275(
276 web_site_sk integer not null,
277 web_site_id char(16) not null,
278 web_rec_start_date date,
279 web_rec_end_date date,
280 web_name varchar(50) ,
281 web_open_date_sk integer ,
282 web_close_date_sk integer ,
283 web_class varchar(50) ,
284 web_manager varchar(40) ,
285 web_mkt_id integer ,
286 web_mkt_class varchar(50) ,
287 web_mkt_desc varchar(100) ,
288 web_market_manager varchar(40) ,
289 web_company_id integer ,
290 web_company_name char(50) ,
291 web_street_number char(10) ,
292 web_street_name varchar(60) ,
293 web_street_type char(15) ,
294 web_suite_number char(10) ,
295 web_city varchar(60) ,
296 web_county varchar(30) ,
297 web_state char(2) ,
298 web_zip char(10) ,
299 web_country varchar(20) ,
300 web_gmt_offset decimal(5,2) ,
301 web_tax_percentage decimal(5,2) ,
302 primary key (web_site_sk)
303) diststyle all;
304 """
305
306 store_returns = """
307create table if not exists store_returns
308(
309sr_returned_date_sk int4 ,
310 sr_return_time_sk int4 ,
311 sr_item_sk int4 not null ,
312 sr_customer_sk int4 ,
313 sr_cdemo_sk int4 ,
314 sr_hdemo_sk int4 ,
315 sr_addr_sk int4 ,
316 sr_store_sk int4 ,
317 sr_reason_sk int4 ,
318 sr_ticket_number int8 not null,
319 sr_return_quantity int4 ,
320 sr_return_amt numeric(7,2) ,
321 sr_return_tax numeric(7,2) ,
322 sr_return_amt_inc_tax numeric(7,2) ,
323 sr_fee numeric(7,2) ,
324 sr_return_ship_cost numeric(7,2) ,
325 sr_refunded_cash numeric(7,2) ,
326 sr_reversed_charge numeric(7,2) ,
327 sr_store_credit numeric(7,2) ,
328 sr_net_loss numeric(7,2)
329 ,primary key (sr_item_sk, sr_ticket_number)
330) distkey(sr_item_sk) sortkey(sr_returned_date_sk);
331 """
332
333 household_demographics = """
334create table if not exists household_demographics
335(
336 hd_demo_sk integer not null,
337 hd_income_band_sk integer ,
338 hd_buy_potential char(15) ,
339 hd_dep_count integer ,
340 hd_vehicle_count integer ,
341 primary key (hd_demo_sk)
342) diststyle all;
343 """
344
345 web_page = """
346create table if not exists web_page
347(
348 wp_web_page_sk integer not null,
349 wp_web_page_id char(16) not null,
350 wp_rec_start_date date,
351 wp_rec_end_date date,
352 wp_creation_date_sk integer ,
353 wp_access_date_sk integer ,
354 wp_autogen_flag char(1) ,
355 wp_customer_sk integer ,
356 wp_url varchar(100) ,
357 wp_type char(50) ,
358 wp_char_count integer ,
359 wp_link_count integer ,
360 wp_image_count integer ,
361 wp_max_ad_count integer ,
362 primary key (wp_web_page_sk)
363) diststyle all;
364 """
365
366 promotion = """
367create table if not exists promotion
368(
369 p_promo_sk integer not null,
370 p_promo_id char(16) not null,
371 p_start_date_sk integer ,
372 p_end_date_sk integer ,
373 p_item_sk integer ,
374 p_cost decimal(15,2) ,
375 p_response_target integer ,
376 p_promo_name char(50) ,
377 p_channel_dmail char(1) ,
378 p_channel_email char(1) ,
379 p_channel_catalog char(1) ,
380 p_channel_tv char(1) ,
381 p_channel_radio char(1) ,
382 p_channel_press char(1) ,
383 p_channel_event char(1) ,
384 p_channel_demo char(1) ,
385 p_channel_details varchar(100) ,
386 p_purpose char(15) ,
387 p_discount_active char(1) ,
388 primary key (p_promo_sk)
389) diststyle all;
390 """
391
392 catalog_page = """
393create table if not exists catalog_page
394(
395 cp_catalog_page_sk integer not null,
396 cp_catalog_page_id char(16) not null,
397 cp_start_date_sk integer ,
398 cp_end_date_sk integer ,
399 cp_department varchar(50) ,
400 cp_catalog_number integer ,
401 cp_catalog_page_number integer ,
402 cp_description varchar(100) ,
403 cp_type varchar(100) ,
404 primary key (cp_catalog_page_sk)
405) diststyle all;
406 """
407
408 inventory = """
409create table if not exists inventory
410(
411 inv_date_sk int4 not null ,
412 inv_item_sk int4 not null ,
413 inv_warehouse_sk int4 not null ,
414 inv_quantity_on_hand int4
415 ,primary key (inv_date_sk, inv_item_sk, inv_warehouse_sk)
416) distkey(inv_item_sk) sortkey(inv_date_sk);
417 """
418
419 catalog_returns = """
420create table if not exists catalog_returns
421(
422 cr_returned_date_sk int4 ,
423 cr_returned_time_sk int4 ,
424 cr_item_sk int4 not null ,
425 cr_refunded_customer_sk int4 ,
426 cr_refunded_cdemo_sk int4 ,
427 cr_refunded_hdemo_sk int4 ,
428 cr_refunded_addr_sk int4 ,
429 cr_returning_customer_sk int4 ,
430 cr_returning_cdemo_sk int4 ,
431 cr_returning_hdemo_sk int4 ,
432 cr_returning_addr_sk int4 ,
433 cr_call_center_sk int4 ,
434 cr_catalog_page_sk int4 ,
435 cr_ship_mode_sk int4 ,
436 cr_warehouse_sk int4 ,
437 cr_reason_sk int4 ,
438 cr_order_number int8 not null,
439 cr_return_quantity int4 ,
440 cr_return_amount numeric(7,2) ,
441 cr_return_tax numeric(7,2) ,
442 cr_return_amt_inc_tax numeric(7,2) ,
443 cr_fee numeric(7,2) ,
444 cr_return_ship_cost numeric(7,2) ,
445 cr_refunded_cash numeric(7,2) ,
446 cr_reversed_charge numeric(7,2) ,
447 cr_store_credit numeric(7,2) ,
448 cr_net_loss numeric(7,2)
449 ,primary key (cr_item_sk, cr_order_number)
450) distkey(cr_item_sk) sortkey(cr_returned_date_sk);
451 """
452
453 web_returns = """
454create table if not exists web_returns
455(
456wr_returned_date_sk int4 ,
457 wr_returned_time_sk int4 ,
458 wr_item_sk int4 not null ,
459 wr_refunded_customer_sk int4 ,
460 wr_refunded_cdemo_sk int4 ,
461 wr_refunded_hdemo_sk int4 ,
462 wr_refunded_addr_sk int4 ,
463 wr_returning_customer_sk int4 ,
464 wr_returning_cdemo_sk int4 ,
465 wr_returning_hdemo_sk int4 ,
466 wr_returning_addr_sk int4 ,
467 wr_web_page_sk int4 ,
468 wr_reason_sk int4 ,
469 wr_order_number int8 not null,
470 wr_return_quantity int4 ,
471 wr_return_amt numeric(7,2) ,
472 wr_return_tax numeric(7,2) ,
473 wr_return_amt_inc_tax numeric(7,2) ,
474 wr_fee numeric(7,2) ,
475 wr_return_ship_cost numeric(7,2) ,
476 wr_refunded_cash numeric(7,2) ,
477 wr_reversed_charge numeric(7,2) ,
478 wr_account_credit numeric(7,2) ,
479 wr_net_loss numeric(7,2)
480 ,primary key (wr_item_sk, wr_order_number)
481) distkey(wr_order_number) sortkey(wr_returned_date_sk);
482 """
483
484 web_sales = """
485create table if not exists web_sales
486(
487 ws_sold_date_sk int4 ,
488 ws_sold_time_sk int4 ,
489 ws_ship_date_sk int4 ,
490 ws_item_sk int4 not null ,
491 ws_bill_customer_sk int4 ,
492 ws_bill_cdemo_sk int4 ,
493 ws_bill_hdemo_sk int4 ,
494 ws_bill_addr_sk int4 ,
495 ws_ship_customer_sk int4 ,
496 ws_ship_cdemo_sk int4 ,
497 ws_ship_hdemo_sk int4 ,
498 ws_ship_addr_sk int4 ,
499 ws_web_page_sk int4 ,
500 ws_web_site_sk int4 ,
501 ws_ship_mode_sk int4 ,
502 ws_warehouse_sk int4 ,
503 ws_promo_sk int4 ,
504 ws_order_number int8 not null,
505 ws_quantity int4 ,
506 ws_wholesale_cost numeric(7,2) ,
507 ws_list_price numeric(7,2) ,
508 ws_sales_price numeric(7,2) ,
509 ws_ext_discount_amt numeric(7,2) ,
510 ws_ext_sales_price numeric(7,2) ,
511 ws_ext_wholesale_cost numeric(7,2) ,
512 ws_ext_list_price numeric(7,2) ,
513 ws_ext_tax numeric(7,2) ,
514 ws_coupon_amt numeric(7,2) ,
515 ws_ext_ship_cost numeric(7,2) ,
516 ws_net_paid numeric(7,2) ,
517 ws_net_paid_inc_tax numeric(7,2) ,
518 ws_net_paid_inc_ship numeric(7,2) ,
519 ws_net_paid_inc_ship_tax numeric(7,2) ,
520 ws_net_profit numeric(7,2)
521 ,primary key (ws_item_sk, ws_order_number)
522) distkey(ws_order_number) sortkey(ws_sold_date_sk);
523 """
524
525 catalog_sales = """
526create table if not exists catalog_sales
527(
528 cs_sold_date_sk int4 ,
529 cs_sold_time_sk int4 ,
530 cs_ship_date_sk int4 ,
531 cs_bill_customer_sk int4 ,
532 cs_bill_cdemo_sk int4 ,
533 cs_bill_hdemo_sk int4 ,
534 cs_bill_addr_sk int4 ,
535 cs_ship_customer_sk int4 ,
536 cs_ship_cdemo_sk int4 ,
537 cs_ship_hdemo_sk int4 ,
538 cs_ship_addr_sk int4 ,
539 cs_call_center_sk int4 ,
540 cs_catalog_page_sk int4 ,
541 cs_ship_mode_sk int4 ,
542 cs_warehouse_sk int4 ,
543 cs_item_sk int4 not null ,
544 cs_promo_sk int4 ,
545 cs_order_number int8 not null ,
546 cs_quantity int4 ,
547 cs_wholesale_cost numeric(7,2) ,
548 cs_list_price numeric(7,2) ,
549 cs_sales_price numeric(7,2) ,
550 cs_ext_discount_amt numeric(7,2) ,
551 cs_ext_sales_price numeric(7,2) ,
552 cs_ext_wholesale_cost numeric(7,2) ,
553 cs_ext_list_price numeric(7,2) ,
554 cs_ext_tax numeric(7,2) ,
555 cs_coupon_amt numeric(7,2) ,
556 cs_ext_ship_cost numeric(7,2) ,
557 cs_net_paid numeric(7,2) ,
558 cs_net_paid_inc_tax numeric(7,2) ,
559 cs_net_paid_inc_ship numeric(7,2) ,
560 cs_net_paid_inc_ship_tax numeric(7,2) ,
561 cs_net_profit numeric(7,2)
562 ,primary key (cs_item_sk, cs_order_number)
563) distkey(cs_item_sk) sortkey(cs_sold_date_sk);
564 """
565
566 store_sales = """
567create table if not exists store_sales
568(
569ss_sold_date_sk int4 ,
570 ss_sold_time_sk int4 ,
571 ss_item_sk int4 not null ,
572 ss_customer_sk int4 ,
573 ss_cdemo_sk int4 ,
574 ss_hdemo_sk int4 ,
575 ss_addr_sk int4 ,
576 ss_store_sk int4 ,
577 ss_promo_sk int4 ,
578 ss_ticket_number int8 not null,
579 ss_quantity int4 ,
580 ss_wholesale_cost numeric(7,2) ,
581 ss_list_price numeric(7,2) ,
582 ss_sales_price numeric(7,2) ,
583 ss_ext_discount_amt numeric(7,2) ,
584 ss_ext_sales_price numeric(7,2) ,
585 ss_ext_wholesale_cost numeric(7,2) ,
586 ss_ext_list_price numeric(7,2) ,
587 ss_ext_tax numeric(7,2) ,
588 ss_coupon_amt numeric(7,2) ,
589 ss_net_paid numeric(7,2) ,
590 ss_net_paid_inc_tax numeric(7,2) ,
591 ss_net_profit numeric(7,2)
592 ,primary key (ss_item_sk, ss_ticket_number)
593) distkey(ss_item_sk) sortkey(ss_sold_date_sk);
594 """
595
596class TPCHCreateTableDDL(object):
597
598 customer = """
599 create table if not exists customer (
600 c_custkey int8 not null ,
601 c_name varchar(25) not null,
602 c_address varchar(40) not null,
603 c_nationkey int4 not null,
604 c_phone char(15) not null,
605 c_acctbal numeric(12,2) not null,
606 c_mktsegment char(10) not null,
607 c_comment varchar(117) not null,
608 Primary Key(C_CUSTKEY)
609 ) distkey(c_custkey) sortkey(c_custkey);
610 """
611
612 lineitem = """
613 create table if not exists lineitem (
614 l_orderkey int8 not null ,
615 l_partkey int8 not null,
616 l_suppkey int4 not null,
617 l_linenumber int4 not null,
618 l_quantity numeric(12,2) not null,
619 l_extendedprice numeric(12,2) not null,
620 l_discount numeric(12,2) not null,
621 l_tax numeric(12,2) not null,
622 l_returnflag char(1) not null,
623 l_linestatus char(1) not null,
624 l_shipdate date not null ,
625 l_commitdate date not null,
626 l_receiptdate date not null,
627 l_shipinstruct char(25) not null,
628 l_shipmode char(10) not null,
629 l_comment varchar(44) not null,
630 Primary Key(L_ORDERKEY, L_LINENUMBER)
631 ) distkey(l_orderkey) sortkey(l_shipdate,l_orderkey) ;
632 """
633
634 nation = """
635 create table if not exists nation (
636 n_nationkey int4 not null,
637 n_name char(25) not null ,
638 n_regionkey int4 not null,
639 n_comment varchar(152) not null,
640 Primary Key(N_NATIONKEY)
641 ) distkey(n_nationkey) sortkey(n_nationkey) ;
642 """
643
644 orders = """
645 create table if not exists orders (
646 o_orderkey int8 not null,
647 o_custkey int8 not null,
648 o_orderstatus char(1) not null,
649 o_totalprice numeric(12,2) not null,
650 o_orderdate date not null,
651 o_orderpriority char(15) not null,
652 o_clerk char(15) not null,
653 o_shippriority int4 not null,
654 o_comment varchar(79) not null,
655 Primary Key(O_ORDERKEY)
656 ) distkey(o_orderkey) sortkey(o_orderdate, o_orderkey) ;
657 """
658
659 part = """
660 create table if not exists part (
661 p_partkey int8 not null ,
662 p_name varchar(55) not null,
663 p_mfgr char(25) not null,
664 p_brand char(10) not null,
665 p_type varchar(25) not null,
666 p_size int4 not null,
667 p_container char(10) not null,
668 p_retailprice numeric(12,2) not null,
669 p_comment varchar(23) not null,
670 PRIMARY KEY (P_PARTKEY)
671 ) distkey(p_partkey) sortkey(p_partkey);
672 """
673
674 partsupp = """
675 create table if not exists partsupp (
676 ps_partkey int8 not null,
677 ps_suppkey int4 not null,
678 ps_availqty int4 not null,
679 ps_supplycost numeric(12,2) not null,
680 ps_comment varchar(199) not null,
681 Primary Key(PS_PARTKEY, PS_SUPPKEY)
682 ) distkey(ps_partkey) sortkey(ps_partkey);
683 """
684
685 region = """
686 create table if not exists region (
687 r_regionkey int4 not null,
688 r_name char(25) not null ,
689 r_comment varchar(152) not null,
690 Primary Key(R_REGIONKEY)
691 ) distkey(r_regionkey) sortkey(r_regionkey);
692 """
693
694 supplier = """
695 create table if not exists supplier (
696 s_suppkey int4 not null,
697 s_name char(25) not null,
698 s_address varchar(40) not null,
699 s_nationkey int4 not null,
700 s_phone char(15) not null,
701 s_acctbal numeric(12,2) not null,
702 s_comment varchar(101) not null,
703 Primary Key(S_SUPPKEY)
704 ) distkey(s_suppkey) sortkey(s_suppkey)
705 ;
706 """
707
708
709class PostgresSetupDDL(object):
710
711 tables = ('task_status', 'task_events', 'task_load_status', 'managed_datasets', 'benchmark_query_status')
712
713 benchmark_query_status = """
714CREATE TABLE IF NOT EXISTS benchmark_query_status(
715task_uuid uuid REFERENCES task_status (task_uuid) on delete cascade,
716scale varchar(10),
717stream smallint,
718query_template varchar(20),
719sequence_number smallint,
720dataset varchar(10),
721pid int,
722xid int,
723client_starttime timestamp,
724client_endtime timestamp,
725redshift_query_start timestamp,
726redshift_query_end timestamp,
727wlm_queue_ms double precision,
728compile_ms double precision,
729query_ids varchar(128),
730is_concurrency_scaled boolean
731)
732 """
733
734 managed_datasets = """
735create table if not exists managed_datasets (
736region varchar(24),
737bucket varchar(64),
738dataset varchar(64),
739version varchar(12),
740scale varchar(8),
741validated_on date,
742validated_by varchar(24),
743schemaname varchar(24),
744tablename varchar(32),
745manifest_key varchar(256),
746rows bigint,
747files int,
748file_size_bytes bigint,
749copy_sql varchar(2048)
750);
751 """
752
753 task_load_status = """
754CREATE TABLE IF NOT EXISTS task_load_status(
755task_uuid uuid REFERENCES task_status (task_uuid) on delete cascade,
756tablename VARCHAR(128),
757dataset VARCHAR(15),
758status VARCHAR(10),
759load_start TIMESTAMP,
760load_end TIMESTAMP,
761wlm_queue_time_ms double precision,
762redshift_query_start TIMESTAMP,
763redshift_query_end TIMESTAMP,
764rows_d BIGINT,
765size_d BIGINT,
766pid INT,
767query_id INT,
768querytext VARCHAR(512)
769);
770 """
771
772 task_status = """
773CREATE TABLE IF NOT EXISTS task_status (
774task_uuid uuid PRIMARY KEY,
775task_name varchar(128),
776task_version varchar(12),
777task_status varchar(24),
778task_request json,
779task_request_time timestamp without time zone,
780task_start_time timestamp without time zone,
781task_end_time timestamp without time zone,
782cluster_configuration json,
783execution_summary json
784);
785 """
786
787 task_events = """
788CREATE TABLE IF NOT EXISTS task_events (
789task_uuid uuid REFERENCES task_status (task_uuid) on delete cascade,
790task_event json,
791task_event_time timestamp without time zone
792);
793 """
794
795class CreateTableDDL(object):
796 tpcds = TPCDSCreateTableDDL
797 tpch = TPCHCreateTableDDL
798 postgres = PostgresSetupDDL
799
800def build_table_exists_sql(schema: str, table: str, echo: bool = False) -> str:
801 sql = f"""
802SELECT 1
803FROM pg_class c
804JOIN pg_namespace n ON n.oid = c.relnamespace
805WHERE c.relname = '{table}' AND n.nspname = '{schema}'
806AND c.relkind = 'r'
807 """
808 if echo:
809 print(sql)
810 return trim(sql)
811
812
813def build_create_table_sql(dataset: str, table: str, echo: bool = False) -> str:
814 sql = getattr(getattr(CreateTableDDL, dataset), table)
815 if echo:
816 print(sql)
817 return trim(sql)