Skip to content

Instantly share code, notes, and snippets.

@chathurawidanage
Last active May 13, 2022 14:37
Show Gist options
  • Save chathurawidanage/029e81cfb0ff2978d572873c693b327c to your computer and use it in GitHub Desktop.
Save chathurawidanage/029e81cfb0ff2978d572873c693b327c to your computer and use it in GitHub Desktop.
from theseus import TheseusContext
tc = TheseusContext(config_options={
"EXECUTOR_THREADS": 1
}, output_type="cudf")
tc.create_table('ex', 'example.parquet')
tc.create_table('nation', 'nation.parquet')
tc.create_table('region', 'region.parquet')
tc.create_table(
'customer', '/home/chathura/miniconda3/envs/theseus/Theseus-testing-files/data/tpch/customer_0_0.parquet')
tc.create_table(
'partsupp', '/home/chathura/miniconda3/envs/theseus/Theseus-testing-files/data/tpch/partsupp_0_0.parquet')
tc.create_table(
'part', '/home/chathura/miniconda3/envs/theseus/Theseus-testing-files/data/tpch/part_0_0.parquet')
tc.create_table(
'lineitem', '/home/chathura/miniconda3/envs/theseus/Theseus-testing-files/data/tpch/lineitem_0_0.parquet')
tc.create_table(
'supplier', '/home/chathura/miniconda3/envs/theseus/Theseus-testing-files/data/tpch/supplier_0_0.parquet')
smpl_qry = "select * from ex where ex.x not in (select e.x from ex e where e.x > 10)"
qry = """
select p.p_brand, p.p_type, p.p_size,
count(ps.ps_suppkey) as supplier_cnt
from partsupp ps
inner join part p on p.p_partkey = ps.ps_partkey
where
p.p_brand <> 'Brand#45'
and p.p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps.ps_supplycost < p.p_retailprice
group by
p.p_brand, p.p_type, p.p_size
order by
supplier_cnt desc, p.p_brand, p.p_type, p.p_size
"""
qry2 = """
with jn as(
select * from nation n join region r on n.x = r.a and n.y = r.b
),
p1 as (
select a,x from jn
),
p2 as(
select b,y from jn
)
select * from p1 join p2 on p1.a = p2.y
"""
qry15 = "with revenue (suplier_no, total_revenue) as ( select l_suppkey, sum(l_extendedprice * (1-l_discount)) from lineitem where l_shipdate >= date '1996-01-01' and l_shipdate < date '1996-01-01' + interval '3' month group by l_suppkey ) select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue where s_suppkey = suplier_no and total_revenue = ( select max(total_revenue) from revenue ) order by s_suppkey"
qry3 = "select n.x/n.z from nation n left join region r on n.x = r.a left join ex e on e.x = n.y"
res = tc.sql(qry15)
print(res)
tc.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment