-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcatalog_sales_parquet_ext.sql
95 lines (89 loc) · 3.07 KB
/
catalog_sales_parquet_ext.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
-- Generate EXTERNAL tables for catalog_sales.
-- use command:
-- psql -v p1=2 -f catalog_sales_parquet_ext.sql
-- p1 parameter is a partition number you want to add at the end of table name
\set hdfs_location '\'' 'pxf://data/catalog_sales.parquet/partition_':p1'?PROFILE=hdfs:parquet' '\''
\echo :hdfs_location
-- CREATE EXTENSION pxf;
-- GRANT SELECT ON PROTOCOL pxf TO gpadmin;
-- GRANT INSERT ON PROTOCOL pxf TO gpadmin;
DROP EXTERNAL TABLE tpcds.catalog_sales_parquet_writable_ext_:p1;
DROP TABLE tpcds.catalog_sales_parquet_writable_ext_:p1;
CREATE WRITABLE EXTERNAL TABLE tpcds.catalog_sales_parquet_writable_ext_:p1 (
cs_sold_date_sk integer,
cs_sold_time_sk integer,
cs_ship_date_sk integer,
cs_bill_customer_sk integer,
cs_bill_cdemo_sk integer,
cs_bill_hdemo_sk integer,
cs_bill_addr_sk integer,
cs_ship_customer_sk integer,
cs_ship_cdemo_sk integer,
cs_ship_hdemo_sk integer,
cs_ship_addr_sk integer,
cs_call_center_sk integer,
cs_catalog_page_sk integer,
cs_ship_mode_sk integer,
cs_warehouse_sk integer,
cs_item_sk integer,
cs_promo_sk integer,
cs_order_number bigint,
cs_quantity integer,
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)
)
LOCATION (:hdfs_location)
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
DROP EXTERNAL TABLE tpcds.catalog_sales_parquet_readable_ext_:p1;
DROP TABLE tpcds.catalog_sales_parquet_readable_ext_:p1;
CREATE EXTERNAL TABLE tpcds.catalog_sales_parquet_readable_ext_:p1 (
cs_sold_date_sk integer,
cs_sold_time_sk integer,
cs_ship_date_sk integer,
cs_bill_customer_sk integer,
cs_bill_cdemo_sk integer,
cs_bill_hdemo_sk integer,
cs_bill_addr_sk integer,
cs_ship_customer_sk integer,
cs_ship_cdemo_sk integer,
cs_ship_hdemo_sk integer,
cs_ship_addr_sk integer,
cs_call_center_sk integer,
cs_catalog_page_sk integer,
cs_ship_mode_sk integer,
cs_warehouse_sk integer,
cs_item_sk integer,
cs_promo_sk integer,
cs_order_number bigint,
cs_quantity integer,
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)
)
LOCATION (:hdfs_location)
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');