datafusion-python/benchmarks/tpch/create_tables.sql at feat/distributed-extensions-example · apache/datafusion-python · GitHub

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
-- Schema derived from TPC-H schema under the terms of the TPC Fair Use Policy.
-- TPC-H queries are Copyright 1993-2022 Transaction Processing Performance Council.

CREATE EXTERNAL TABLE customer (
    c_custkey INT NOT NULL,
    c_name VARCHAR NOT NULL,
    c_address VARCHAR NOT NULL,
    c_nationkey INT NOT NULL,
    c_phone VARCHAR NOT NULL,
    c_acctbal DECIMAL(15, 2) NOT NULL,
    c_mktsegment VARCHAR NOT NULL,
    c_comment VARCHAR NOT NULL,
    c_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/customer.csv';

CREATE EXTERNAL TABLE lineitem (
    l_orderkey INT NOT NULL,
    l_partkey INT NOT NULL,
    l_suppkey INT NOT NULL,
    l_linenumber INT NOT NULL,
    l_quantity DECIMAL(15, 2) NOT NULL,
    l_extendedprice DECIMAL(15, 2) NOT NULL,
    l_discount DECIMAL(15, 2) NOT NULL,
    l_tax DECIMAL(15, 2) NOT NULL,
    l_returnflag VARCHAR NOT NULL,
    l_linestatus VARCHAR NOT NULL,
    l_shipdate DATE NOT NULL,
    l_commitdate DATE NOT NULL,
    l_receiptdate DATE NOT NULL,
    l_shipinstruct VARCHAR NOT NULL,
    l_shipmode VARCHAR NOT NULL,
    l_comment VARCHAR NOT NULL,
    l_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/lineitem.csv';

CREATE EXTERNAL TABLE nation (
    n_nationkey INT NOT NULL,
    n_name VARCHAR NOT NULL,
    n_regionkey INT NOT NULL,
    n_comment VARCHAR NOT NULL,
    n_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/nation.csv';

CREATE EXTERNAL TABLE orders (
    o_orderkey INT NOT NULL,
    o_custkey INT NOT NULL,
    o_orderstatus VARCHAR NOT NULL,
    o_totalprice DECIMAL(15, 2) NOT NULL,
    o_orderdate DATE NOT NULL,
    o_orderpriority VARCHAR NOT NULL,
    o_clerk VARCHAR NOT NULL,
    o_shippriority INT NULL,
    o_comment VARCHAR NOT NULL,
    o_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/orders.csv';

CREATE EXTERNAL TABLE part (
    p_partkey INT NOT NULL,
    p_name VARCHAR NOT NULL,
    p_mfgr VARCHAR NOT NULL,
    p_brand VARCHAR NOT NULL,
    p_type VARCHAR NOT NULL,
    p_size INT NULL,
    p_container VARCHAR NOT NULL,
    p_retailprice DECIMAL(15, 2) NOT NULL,
    p_comment VARCHAR NOT NULL,
    p_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/part.csv';

CREATE EXTERNAL TABLE partsupp (
    ps_partkey INT NOT NULL,
    ps_suppkey INT NOT NULL,
    ps_availqty INT NOT NULL,
    ps_supplycost DECIMAL(15, 2) NOT NULL,
    ps_comment VARCHAR NOT NULL,
    ps_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/partsupp.csv';

CREATE EXTERNAL TABLE region (
    r_regionkey INT NOT NULL,
    r_name VARCHAR NOT NULL,
    r_comment VARCHAR NOT NULL,
    r_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/region.csv';

CREATE EXTERNAL TABLE supplier (
    s_suppkey INT NOT NULL,
    s_name VARCHAR NOT NULL,
    s_address VARCHAR NOT NULL,
    s_nationkey INT NOT NULL,
    s_phone VARCHAR NOT NULL,
    s_acctbal DECIMAL(15, 2) NOT NULL,
    s_comment VARCHAR NOT NULL,
    s_extra VARCHAR NOT NULL,
)
STORED AS CSV
OPTIONS (
    format.delimiter '|',
    format.has_header true
)
LOCATION '$PATH/supplier.csv';