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
|
# name: test/sql/json/test_json_tpch_sf001.test_slow
# description: Test TPCH with JSON
# group: [json]
require json
require tpch
statement ok
call dbgen(sf=0.01)
query IIIIIIIIIIIII nosort q0
select * from lineitem order by all
----
# create lineitem json table
statement ok
create table lineitem_j as
select json_quote(struct_pack(l_orderkey,l_partkey,l_suppkey,l_linenumber,l_quantity,l_extendedprice,l_discount,l_tax,l_returnflag,
l_linestatus,l_shipdate,l_commitdate,l_receiptdate,l_shipinstruct,l_shipmode,l_comment)) as j
from lineitem
# get the json structure
query T
select json_structure(j) from lineitem_j limit 1
----
{"l_orderkey":"UBIGINT","l_partkey":"UBIGINT","l_suppkey":"UBIGINT","l_linenumber":"UBIGINT","l_quantity":"DOUBLE","l_extendedprice":"DOUBLE","l_discount":"DOUBLE","l_tax":"DOUBLE","l_returnflag":"VARCHAR","l_linestatus":"VARCHAR","l_shipdate":"VARCHAR","l_commitdate":"VARCHAR","l_receiptdate":"VARCHAR","l_shipinstruct":"VARCHAR","l_shipmode":"VARCHAR","l_comment":"VARCHAR"}
# transform the structure back to what it was and verify it is the same as on the original lineitem table
query IIIIIIIIIIIII nosort q0
with transformed as (
select json_transform(j, '{"l_orderkey":"INTEGER","l_partkey":"INTEGER","l_suppkey":"INTEGER","l_linenumber":"INTEGER","l_quantity":"DECIMAL(15,2)","l_extendedprice":"DECIMAL(15,2)","l_discount":"DECIMAL(15,2)","l_tax":"DECIMAL(15,2)","l_returnflag":"VARCHAR","l_linestatus":"VARCHAR","l_shipdate":"DATE","l_commitdate":"DATE","l_receiptdate":"DATE","l_shipinstruct":"VARCHAR","l_shipmode":"VARCHAR","l_comment":"VARCHAR"}') as j
from lineitem_j
)
select j.l_orderkey,j.l_partkey,j.l_suppkey,j.l_linenumber,j.l_quantity,j.l_extendedprice,j.l_discount,j.l_tax,
j.l_returnflag,j.l_linestatus,j.l_shipdate,j.l_commitdate,j.l_receiptdate,j.l_shipinstruct,j.l_shipmode,j.l_comment
from transformed
order by all
----
# run TPCH-Q1 straight on JSON
query IIIIIIIIII
with transformed as (
select json_transform(j, '{"l_orderkey":"INTEGER","l_partkey":"INTEGER","l_suppkey":"INTEGER","l_linenumber":"INTEGER","l_quantity":"DECIMAL(15,2)","l_extendedprice":"DECIMAL(15,2)","l_discount":"DECIMAL(15,2)","l_tax":"DECIMAL(15,2)","l_returnflag":"VARCHAR","l_linestatus":"VARCHAR","l_shipdate":"DATE","l_commitdate":"DATE","l_receiptdate":"DATE","l_shipinstruct":"VARCHAR","l_shipmode":"VARCHAR","l_comment":"VARCHAR"}') as j
from lineitem_j
)
SELECT
j.l_returnflag,
j.l_linestatus,
sum(j.l_quantity) AS sum_qty,
sum(j.l_extendedprice) AS sum_base_price,
sum(j.l_extendedprice * (1 - j.l_discount)) AS sum_disc_price,
sum(j.l_extendedprice * (1 - j.l_discount) * (1 + j.l_tax)) AS sum_charge,
avg(j.l_quantity) AS avg_qty,
avg(j.l_extendedprice) AS avg_price,
avg(j.l_discount) AS avg_disc,
count(*) AS count_order
FROM
transformed
WHERE
j.l_shipdate <= CAST('1998-09-02' AS date)
GROUP BY
j.l_returnflag,
j.l_linestatus
ORDER BY
j.l_returnflag,
j.l_linestatus
----
<FILE>:extension/tpch/dbgen/answers/sf0.01/q01.csv
|
