def test_join_topk():
"""Tests a top k with a join
:return: None
"""
limit = 5
query_plan = QueryPlan()
# Query plan
ts1 = query_plan.add_operator(SQLTableScan('supplier.csv',
'select * from S3Object;', False, 'ts1', query_plan, False))
ts1_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_3'], 's_nationkey')], 'ts1_project', query_plan, False))
ts2 = query_plan.add_operator(SQLTableScan('nation.csv',
'select * from S3Object;', False, 'ts2', query_plan, False))
ts2_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_0'], 'n_nationkey')], 'ts2_project', query_plan, False))
j = query_plan.add_operator(HashJoin(JoinExpression('s_nationkey', 'n_nationkey'), 'j', query_plan, False))
t = query_plan.add_operator(Limit(limit, 't', query_plan, False))
c = query_plan.add_operator(Collate('c', query_plan, False))
ts1.connect(ts1_project)
ts2.connect(ts2_project)
j.connect_left_producer(ts1_project)
j.connect_right_producer(ts2_project)
j.connect(t)
t.connect(c)
# Write the plan graph
query_plan.write_graph(os.path.join(ROOT_DIR, "../tests-output"), gen_test_id())
# Start the query
query_plan.execute()
# Assert the results
# num_rows = 0
# for t in c.tuples():
# num_rows += 1
# print("{}:{}".format(num_rows, t))
c.print_tuples()
field_names = ['s_nationkey', 'n_nationkey']
assert len(c.tuples()) == limit + 1
assert c.tuples()[0] == field_names
num_rows = 0
for t in c.tuples():
num_rows += 1
# Assert that the nation_key in table 1 has been joined with the record in table 2 with the same nation_key
if num_rows > 1:
lt = IndexedTuple.build(t, field_names)
assert lt['s_nationkey'] == lt['n_nationkey']
# Write the metrics
query_plan.print_metrics()
def join_p_partkey_l_partkey_op(name, query_plan):
"""with part_lineitem_join as (select * from part_scan, lineitem_scan where p_partkey = l_partkey)
:param query_plan:
:param name:
:return:
"""
return HashJoin(JoinExpression('p_partkey', 'l_partkey'), name, query_plan,
False)
def join_l_partkey_p_partkey_op(name, query_plan):
"""with part_lineitem_join_avg_group_join as (
select * from part_lineitem_join, lineitem_part_avg_group_project where p_partkey = l_partkey
)
:return:
"""
return HashJoin(JoinExpression('l_partkey', 'p_partkey'), name, query_plan,
False)
def test_join_empty():
"""Executes a join where no records are returned. We tst this as it's somewhat peculiar with s3 select, in so much
as s3 does not return column names when selecting data, meaning, unlike a traditional DBMS, no field names tuple
should be present in the results.
:return: None
"""
query_plan = QueryPlan()
# Query plan
supplier_scan = query_plan.add_operator(
SQLTableScan('supplier.csv', 'select * from S3Object limit 0;', False, 'supplier_scan', query_plan, False))
supplier_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_3'], 's_nationkey')], 'supplier_project', query_plan, False))
nation_scan = query_plan.add_operator(
SQLTableScan('nation.csv', 'select * from S3Object limit 0;', False, 'nation_scan', query_plan, False))
nation_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_0'], 'n_nationkey')], 'nation_project', query_plan, False))
supplier_nation_join = query_plan.add_operator(
HashJoin(JoinExpression('s_nationkey', 'n_nationkey'), 'supplier_nation_join', query_plan, False))
collate = query_plan.add_operator(Collate('collate', query_plan, False))
supplier_scan.connect(supplier_project)
nation_scan.connect(nation_project)
supplier_nation_join.connect_left_producer(supplier_project)
supplier_nation_join.connect_right_producer(nation_project)
supplier_nation_join.connect(collate)
# Write the plan graph
query_plan.write_graph(os.path.join(ROOT_DIR, "../tests-output"), gen_test_id())
# Start the query
query_plan.execute()
# Assert the results
# num_rows = 0
# for t in collate.tuples():
# num_rows += 1
# print("{}:{}".format(num_rows, t))
assert len(collate.tuples()) == 0
# Write the metrics
query_plan.print_metrics()
def join_op(query_plan):
return HashJoin(JoinExpression('l_partkey', 'p_partkey'), 'lineitem_part_join', query_plan, False)
def join_part_lineitem_operator_def(name, query_plan):
# type: (str, QueryPlan) -> HashJoin
return HashJoin(JoinExpression('p_partkey', 'l_partkey'), name, query_plan,
False)
def test_join_baseline():
"""Tests a join
:return: None
"""
query_plan = QueryPlan(is_async=True, buffer_size=1024)
# Query plan
supplier_scan = query_plan.add_operator(
SQLTableScan('region.csv', 'select * from S3Object;', False, 'supplier_scan', query_plan, True))
supplier_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_0'], 'r_regionkey')], 'supplier_project', query_plan, True))
nation_scan = query_plan.add_operator(
SQLTableScan('nation.csv', 'select * from S3Object;', False, 'nation_scan', query_plan, True))
nation_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_2'], 'n_regionkey')], 'nation_project', query_plan, True))
supplier_nation_join = query_plan.add_operator(
HashJoin(JoinExpression('r_regionkey', 'n_regionkey'), 'supplier_nation_join', query_plan, True))
collate = query_plan.add_operator(Collate('collate', query_plan, True))
supplier_scan.connect(supplier_project)
nation_scan.connect(nation_project)
supplier_nation_join.connect_left_producer(supplier_project)
supplier_nation_join.connect_right_producer(nation_project)
supplier_nation_join.connect(collate)
# Write the plan graph
query_plan.write_graph(os.path.join(ROOT_DIR, "../tests-output"), gen_test_id())
# Start the query
query_plan.execute()
tuples = collate.tuples()
collate.print_tuples(tuples)
# Write the metrics
query_plan.print_metrics()
# Shut everything down
query_plan.stop()
field_names = ['r_regionkey', 'n_regionkey']
assert len(tuples) == 25 + 1
assert tuples[0] == field_names
num_rows = 0
for t in tuples:
num_rows += 1
# Assert that the nation_key in table 1 has been joined with the record in table 2 with the same nation_key
if num_rows > 1:
lt = IndexedTuple.build(t, field_names)
assert lt['r_regionkey'] == lt['n_regionkey']
def test_r_to_l_join():
"""Tests a join
:return: None
"""
query_plan = QueryPlan()
# Query plan
supplier_scan = query_plan.add_operator(
SQLTableScan('supplier.csv', 'select * from S3Object;', False, 'supplier_scan', query_plan, False))
supplier_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_3'], 's_nationkey')], 'supplier_project', query_plan, False))
nation_scan = query_plan.add_operator(
SQLTableScan('nation.csv', 'select * from S3Object;', False, 'nation_scan', query_plan, False))
nation_project = query_plan.add_operator(
Project([ProjectExpression(lambda t_: t_['_0'], 'n_nationkey')], 'nation_project', query_plan, False))
supplier_nation_join = query_plan.add_operator(
HashJoin(JoinExpression('n_nationkey', 's_nationkey'), 'supplier_nation_join', query_plan, False))
collate = query_plan.add_operator(Collate('collate', query_plan, False))
supplier_scan.connect(supplier_project)
nation_scan.connect(nation_project)
supplier_nation_join.connect_left_producer(nation_project)
supplier_nation_join.connect_right_producer(supplier_project)
supplier_nation_join.connect(collate)
# Write the plan graph
query_plan.write_graph(os.path.join(ROOT_DIR, "../tests-output"), gen_test_id())
# Start the query
query_plan.execute()
# Assert the results
# num_rows = 0
# for t in collate.tuples():
# num_rows += 1
# print("{}:{}".format(num_rows, t))
# collate.print_tuples()
field_names = ['n_nationkey', 's_nationkey']
assert len(collate.tuples()) == 10000 + 1
assert collate.tuples()[0] == field_names
num_rows = 0
for t in collate.tuples():
num_rows += 1
# Assert that the nation_key in table 1 has been joined with the record in table 2 with the same nation_key
if num_rows > 1:
lt = IndexedTuple.build(t, field_names)
assert lt['s_nationkey'] == lt['n_nationkey']
# Write the metrics
query_plan.print_metrics()