def test_can_generate_select_all(self):
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat[0].keys()), 88)
def test_can_sum_from_joined_column_passes(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Join',
query_func='join',
query_kwargs=
'{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"}\
,"columns_x":["donor_name","usd_commitment"],"columns_y":["part_code","part_name"]}',
source_id=2)
OperationStep.objects.create(
operation=self.op,
step_id=3,
name='Aggregate',
query_func='aggregate',
query_kwargs=
'{"group_by":["part_name"],"agg_func_name":"Sum", "operational_column":"usd_commitment"}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue(dat[0]['usd_commitment_Sum'] > 150)
def test_can_generate_filter(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name="Filter",
query_func="filter",
query_kwargs=
'{"filters":[{"field":"year", "value":1973, "func":"ge"},{"field":"short_description", "value":"%sector%|%wheat%", "func":"text_search"}]}',
source_id=1)
queries = query.build_query(operation=self.op)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat), 41)
def test_can_perform_multi_transform_divide_by_zero(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Transform',
query_func='multi_transform',
query_kwargs=
'{"trans_func_name":"divide", "operational_columns":["usd_disbursement","usd_disbursement_deflated"]}',
source_id=2)
queries = query.build_query(operation=self.op)
_, dat = fetch_data(queries, database="default")
self.assertTrue('usd_disbursement_divide' in dat[0].keys())
def test_can_generate_select_by_column(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name="Select",
query_func="select",
query_kwargs="{ \"columns\": [ \"year\" ] }",
source_id=1)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat[0].keys()), 1)
def test_can_catch_sql_err(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name="Select",
query_func="select",
query_kwargs="{ \"columns\": [ \"iso10\" ] }",
source_id=1)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue("error" in list(dat[0].keys()))
def test_can_catch_zip_err(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name="Filter",
query_func="filter",
query_kwargs=
'{"filters":[{"field":"year", "value":9999, "func":"ge"}]}',
source_id=1)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat), 0)
def test_can_generate_join(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Join',
query_func='join',
query_kwargs=
'{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"}}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat[0].keys()), 100)
def test_can_perform_scalar_transform(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Transform',
query_func='scalar_transform',
query_kwargs=
'{"trans_func_name":"text_search", "operational_column":"short_description", "operational_value":"%wheat%"}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue(dat[0]['short_description_text_search'])
def test_can_generate_avg_aggregate(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Aggregate',
query_func='aggregate',
query_kwargs=
'{"group_by":["year"],"agg_func_name":"Avg", "operational_column":"usd_commitment"}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue(dat[0]["usd_commitment_Avg"] > 9)
def test_can_perform_stddev(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Window StdDev',
query_func='window',
query_kwargs=
'{"window_fn":"StdDev","term":"usd_commitment","over":["year"]}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue(dat[0]['stddev'] > 8)
def test_can_perform_last_value(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Window last val',
query_func='window',
query_kwargs=
'{"window_fn":"LastValue","term":"usd_commitment","order_by":["year"]}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue(dat[0]['last_value'] > 14)
def test_can_generate_perform_dense_rank(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Window denserank',
query_func='window',
query_kwargs=
'{"window_fn":"DenseRank","order_by":["usd_commitment"]}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(dat[0]["dense_rank"], 1)
def test_can_sum(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Aggregate',
query_func='aggregate',
query_kwargs=
'{"group_by":["donor_code"],"agg_func_name":"Sum", "operational_column":"usd_commitment"}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertTrue('usd_commitment_Sum' in dat[0].keys())
def test_can_generate_join_for_specific_columns(self):
OperationStep.objects.create(
operation=self.op,
step_id=2,
name='Join',
query_func='join',
query_kwargs=
'{"table_name":"dac1_current","schema_name":"public", "join_on":{"year":"year"},\
"columns_x":["donor_name","usd_commitment"],"columns_y":["part_code","part_name"]}',
source_id=2)
queries = query.build_query(operation=self.op,
limit=1,
offset=0,
estimate_count=True)
_, dat = fetch_data(queries, database="default")
self.assertEqual(len(dat[0].keys()), 90)
def advanced_query_table(config, limit=None, offset=None, estimate_count=False):
limit = 10000 if limit is None or int(limit) > 10000 else limit
offset = offset or 0
queries = build_advanced_queries(config, limit, offset, estimate_count)
return fetch_data(queries)
def query_table(operation=None, limit=None, offset=None, estimate_count=None, operation_steps=None, frozen_table_id=None):
"""Build a query then execute it to return the matching data"""
limit = 10000 if limit is None or int(limit) > 10000 else limit
offset = offset or 0
queries = build_query(operation, steps=operation_steps, limit=limit, offset=offset, estimate_count=estimate_count, frozen_table_id=frozen_table_id)
return fetch_data(queries)