def test_escaped_brackets(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
test_graph.add_query('simple_query',
'{{ text }} SELECT * FROM {source}')
query = test_graph.compile('simple_query', {'source': 'test-source'})
self.assertEqual(
query,
'{ text } SELECT * FROM `test-project.test-dataset.test-source`')
def test_cyclic_graph_detection(self):
query_graph = template.QueryGraph('test-project', 'test-dataset')
# Adding components of the circle are fine...
query_graph.add_query('query1', 'SELECT * FROM {query2}')
query_graph.add_query('query2', 'SELECT * FROM {query3}')
# ...but completing the circle should throw an error.
with self.assertRaises(template.CyclicGraphError):
query_graph.add_query('query3', 'SELECT * FROM {query1}')
def test_column_order(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
features.aggregate(
test_graph, 'bool_aggregate', 'source',
[features.BoolCondition('feature1', 'condition'), 'id'])
query = test_graph.compile('bool_aggregate', {'source': 'test-source'})
self.assertEqual(
query, 'SELECT MAX(feature1) feature1, id id FROM '
'(SELECT id id, CASE WHEN condition THEN 1 ELSE 0 END feature1 FROM '
'`test-project.test-dataset.test-source`) GROUP BY id')
def test_aggregate_count(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
features.aggregate(
test_graph, 'count_aggregate', 'source',
['id', features.CountCondition('feature1', 'condition')])
query = test_graph.compile('count_aggregate',
{'source': 'test-source'})
self.assertEqual(
query, 'SELECT id id, SUM(feature1) feature1 FROM '
'(SELECT id id, CASE WHEN condition THEN 1 ELSE 0 END feature1 FROM '
'`test-project.test-dataset.test-source`) GROUP BY id')
def test_map_values(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
features.select(test_graph, 'map_select', 'source',
[('column1',
features.map_values([('condition1', 'value1'),
('condition2', 'value2')],
default='value3')),
('column2', 'source2')])
query = test_graph.compile('map_select', {'source': 'test-source'})
self.assertEqual(
query, 'SELECT CASE WHEN condition1 THEN value1 '
'WHEN condition2 THEN value2 ELSE value3 END column1, '
'source2 column2 FROM '
'`test-project.test-dataset.test-source`')
def test_select_by_date(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
features.aggregate(test_graph, 'select_by_date', 'source', [
'id',
features.SelectByDate('feature1', 'value', 'date', 'cond', 'MIN')
])
query = test_graph.compile('select_by_date', {'source': 'test-source'})
self.assertEqual(
query,
'SELECT id id, MAX(CASE WHEN feature1_date = feature1_agg_date '
'THEN feature1 ELSE null END) feature1 FROM (SELECT id id, '
'CASE WHEN cond THEN value ELSE null END feature1, '
'CASE WHEN cond THEN date ELSE null END feature1_date, '
'MIN(CASE WHEN cond THEN date ELSE null END) OVER (PARTITION BY id) '
'feature1_agg_date '
'FROM `test-project.test-dataset.test-source`) GROUP BY id')
def test_graph_dependencies(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
test_graph.add_query(
'outer_query', 'SELECT * FROM {inner_query} JOIN {other_source}')
test_graph.add_query('inner_query', 'SELECT * FROM {source}')
full_query = test_graph.compile('outer_query', {
'source': 'test-source',
'other_source': 'other'
})
self.assertEqual(
full_query, 'SELECT * FROM '
'(SELECT * FROM `test-project.test-dataset.test-source`) '
'JOIN `test-project.test-dataset.other`')
partial_query = test_graph.compile('outer_query', {
'inner_query': 'mock-table',
'other_source': 'other'
})
self.assertEqual(
partial_query,
'SELECT * FROM `test-project.test-dataset.mock-table` '
'JOIN `test-project.test-dataset.other`')
def test_select(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
features.select(test_graph, 'simple_select', 'source',
[('column1', 'source1'), ('column2', 'source2')])
query = test_graph.compile('simple_select', {'source': 'test-source'})
self.assertEqual(
query, 'SELECT source1 column1, source2 column2 FROM '
'`test-project.test-dataset.test-source`')
features.select(test_graph,
'complex_select',
'source', [('column1', 'source1'),
('column2', 'source2')],
where=['condition1', 'condition2'],
group_by=['group_by_1', 'group_by_2'],
order_by=['order1', 'order2'])
query = test_graph.compile('complex_select', {'source': 'test-source'})
self.assertEqual(
query, 'SELECT source1 column1, source2 column2 FROM '
'`test-project.test-dataset.test-source` '
'WHERE condition1, condition2 '
'GROUP BY group_by_1, group_by_2 '
'ORDER BY order1, order2')
def test_escaped_brackets_no_bq(self):
test_graph = template.QueryGraph()
test_graph.add_query('simple_query',
'{{ text }} SELECT * FROM {source}')
query = test_graph.compile('simple_query', {'source': 'test-source'})
self.assertEqual(query, '{ text } SELECT * FROM test-source')
def test_no_default_project_or_dataset(self):
test_graph = template.QueryGraph()
test_graph.add_query('simple_query', 'SELECT * FROM {source}')
query = test_graph.compile('simple_query', {'source': 'test-source'})
self.assertEqual(query, 'SELECT * FROM test-source')
def test_dotted_source(self):
test_graph = template.QueryGraph('test-project', 'test-dataset')
test_graph.add_query('simple_query', 'SELECT * FROM {source}')
query = test_graph.compile('simple_query',
{'source': 'test-dataset.test-source'})
self.assertEqual(query, 'SELECT * FROM `test-dataset.test-source`')