def test_incorrect_conditions(self): """Ensure that render query can handle incorrectly formatted conditions. """ from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'asdfasdfasdf': 'start_time', 'ffd': 1371566954, 'comparator': '<=', 'type': 'INTEGER'}, {'field': 'start_time', 'value': {'value': 1371556954, 'negate': False}, 'compoorattor': '>=', 'type': 'INTEGER'} ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] ORDER BY " "timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_incorrect_order(self): """Ensure that render query can handle inccorectly formatted order.""" result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'feeld': 'timestamp', 'dir': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_empty_select(self): """Ensure that render query corrently handles no selection.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={}, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT * FROM [dataset.2013_06_appspot_1] " "WHERE (start_time <= INTEGER('1371566954')) AND " "(start_time >= INTEGER('1371556954')) ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)
def test_sec_to_micro_formatting(self): """Ensure that render query runs sec_to_micro formatting on a select. """ from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp', 'format': 'SEC_TO_MICRO-INTEGER-SEC_TO_TIMESTAMP'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, " "SEC_TO_TIMESTAMP(INTEGER(start_time*1000000)) as " "timestamp, resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_empty_conditions(self): """Ensure that render query can handle an empty list of conditions.""" from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)
def test_incorrect_order(self): """Ensure that render query can handle inccorectly formatted order.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'feeld': 'timestamp', 'dir': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ") self.assertEqual(result, expected_query)
def test_no_alias(self): """Ensure that render query runs without an alias for a select.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {}, 'status': {}, 'resource': {} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'} ], order_by={'field': 'start_time', 'direction': 'desc'}) expected_query = ("SELECT status , start_time , resource FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY start_time desc") self.assertEqual(result, expected_query)
def test_multiple_negated_condition_values(self): """Ensure that render query can handle conditions with multiple negated values. """ from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'resource', 'comparators': [{'condition': 'CONTAINS', 'value': 'foo', 'negate': True}, {'condition': 'CONTAINS', 'value': 'baz', 'negate': True}, {'condition': 'CONTAINS', 'value': 'bar', 'negate': True}], 'type': 'STRING'} ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (NOT resource " "CONTAINS STRING('foo') AND NOT resource CONTAINS " "STRING('baz') AND NOT resource CONTAINS " "STRING('bar')) ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_no_table_or_dataset(self): """Ensure that render query returns None if there is no dataset or table. """ from bigquery.query_builder import render_query result = render_query( dataset=None, tables=None, select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'field': 'timestamp', 'direction': 'desc'}) self.assertIsNone(result)
def test_empty_groupings(self): """Ensure that render query can handle an empty list of groupings.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, groupings=[], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] ORDER BY " "timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_negated_condition_value(self): """Ensure that render query can handle conditions with negated values. """ from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'resource', 'comparators': [{'condition': 'CONTAINS', 'value': 'foo', 'negate': True}], 'type': 'STRING'} ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (NOT resource " "CONTAINS STRING('foo')) ORDER BY timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_multi_tables(self): """Ensure that render query arguments work with multiple tables.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1', '2013_07_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], groupings=['timestamp', 'status'], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1], " "[dataset.2013_07_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) GROUP BY timestamp, status " "ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_sec_to_micro_formatting(self): """Ensure that render query runs sec_to_micro formatting on a select. """ from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp', 'format': 'SEC_TO_MICRO-INTEGER-SEC_TO_TIMESTAMP' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, " "SEC_TO_TIMESTAMP(INTEGER(start_time*1000000)) as " "timestamp, resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_multi_tables(self): """Ensure that render query arguments work with multiple tables.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1', '2013_07_appspot_1'], select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], groupings=['timestamp', 'status'], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1], " "[dataset.2013_07_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) GROUP BY timestamp, status " "ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_incorrect_order(self): """Ensure that render query can handle inccorectly formatted order.""" from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], order_by={ 'feeld': 'timestamp', 'dir': 'desc' }) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ") self.assertEqual(result, expected_query)
def test_formatting_duplicate_columns(self): """Ensure that render query runs with formatting a select for a column selected twice. """ from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': [ { 'alias': 'timestamp', 'format': 'INTEGER-FORMAT_UTC_USEC' }, { 'alias': 'day', 'format': ('SEC_TO_MICRO-INTEGER-' 'FORMAT_UTC_USEC-LEFT:10') } ], 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, " "FORMAT_UTC_USEC(INTEGER(start_time)) as timestamp, " "LEFT(FORMAT_UTC_USEC(INTEGER(start_time*1000000))," "10) as day, resource as url FROM " "[dataset.2013_06_appspot_1] WHERE " "(start_time <= INTEGER('1371566954')) AND " "(start_time >= INTEGER('1371556954')) ORDER BY " "timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_full_query(self): """Ensure that all the render query arguments work together.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ { 'field': 'start_time', 'comparators': [ { 'condition': '<=', 'value': 1371566954, 'negate': False } ], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [ { 'condition': '>=', 'value': 1371556954, 'negate': False } ], 'type': 'INTEGER' } ], groupings=['timestamp', 'status'], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM [dataset.2013_06_appspot_1]" " WHERE (start_time <= INTEGER('1371566954')) AND " "(start_time >= INTEGER('1371556954')) GROUP BY " "timestamp, status ORDER BY timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_multiple_negated_condition_values(self): """Ensure that render query can handle conditions with multiple negated values. """ from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[{ 'field': 'resource', 'comparators': [{ 'condition': 'CONTAINS', 'value': 'foo', 'negate': True }, { 'condition': 'CONTAINS', 'value': 'baz', 'negate': True }, { 'condition': 'CONTAINS', 'value': 'bar', 'negate': True }], 'type': 'STRING' }], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (NOT resource " "CONTAINS STRING('foo') AND NOT resource CONTAINS " "STRING('baz') AND NOT resource CONTAINS " "STRING('bar')) ORDER BY timestamp desc") self.assertEqual(result, expected_query)
def test_no_table_or_dataset(self): """Ensure that render query returns None if there is no dataset or table. """ from bigquery.query_builder import render_query result = render_query(dataset=None, tables=None, select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], order_by={ 'field': 'timestamp', 'direction': 'desc' }) self.assertIsNone(result)
def test_multiple_condition_values(self): """Ensure that render query can handle conditions with multiple values. """ result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'resource', 'comparators': [{'condition': 'CONTAINS', 'value': 'foo', 'negate': False}, {'condition': 'CONTAINS', 'value': 'bar', 'negate': True}, {'condition': 'CONTAINS', 'value': 'baz', 'negate': False}], 'type': 'STRING'} ], order_by={'fields': ['timestamp'], 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) AND " "((resource CONTAINS STRING('foo') AND resource " "CONTAINS STRING('baz')) AND (NOT resource CONTAINS " "STRING('bar'))) ORDER BY timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_incorrect_conditions(self): """Ensure that render query can handle incorrectly formatted conditions. """ from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp' }, 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[{ 'asdfasdfasdf': 'start_time', 'ffd': 1371566954, 'comparator': '<=', 'type': 'INTEGER' }, { 'field': 'start_time', 'value': { 'value': 1371556954, 'negate': False }, 'compoorattor': '>=', 'type': 'INTEGER' }], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)
def test_no_alias(self): """Ensure that render query runs without an alias for a select.""" from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {}, 'status': {}, 'resource': {} }, conditions=[{ 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }], order_by={ 'field': 'start_time', 'direction': 'desc' }) expected_query = ("SELECT status , start_time , resource FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY start_time desc") self.assertEqual(result, expected_query)
def test_empty_groupings(self): """Ensure that render query can handle an empty list of groupings.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, groupings=[], order_by={'field': 'timestamp', 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1] ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)
def test_empty_select(self): """Ensure that render query corrently handles no selection.""" from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={}, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT * FROM [dataset.2013_06_appspot_1] " "WHERE (start_time <= INTEGER('1371566954')) AND " "(start_time >= INTEGER('1371556954')) ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)
def test_sec_to_micro_formatting(self): """Ensure that render query runs sec_to_micro formatting on a select. """ result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': { 'alias': 'timestamp', 'format': 'SEC_TO_MICRO-INTEGER-SEC_TO_TIMESTAMP' }, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], order_by={'fields': ['timestamp'], 'direction': 'desc'}) expected_query = ("SELECT status as status, " "SEC_TO_TIMESTAMP(INTEGER(start_time*1000000)) as " "timestamp, resource as url FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_no_alias(self): """Ensure that render query runs without an alias for a select.""" from bigquery.query_builder import render_query result = render_query( dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': {}, 'status': {}, 'resource': {} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'} ], order_by={'field': 'start_time', 'direction': 'desc'}) expected_query = ("SELECT status , start_time , resource FROM " "[dataset.2013_06_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) ORDER BY start_time desc") expected_select = (field.strip() for field in expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = (expected_query[len('SELECT '):].split('FROM')[1] .strip()) result_select = (field.strip() for field in result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1].strip() six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_multi_tables(self): """Ensure that render query arguments work with multiple tables.""" result = render_query( dataset='dataset', tables=['2013_06_appspot_1', '2013_07_appspot_1'], select={ 'start_time': {'alias': 'timestamp'}, 'status': {'alias': 'status'}, 'resource': {'alias': 'url'} }, conditions=[ {'field': 'start_time', 'comparators': [{'condition': '<=', 'value': 1371566954, 'negate': False}], 'type': 'INTEGER'}, {'field': 'start_time', 'comparators': [{'condition': '>=', 'value': 1371556954, 'negate': False}], 'type': 'INTEGER'}, ], groupings=['timestamp', 'status'], order_by={'fields': ['timestamp'], 'direction': 'desc'}) expected_query = ("SELECT status as status, start_time as timestamp, " "resource as url FROM " "[dataset.2013_06_appspot_1], " "[dataset.2013_07_appspot_1] WHERE (start_time " "<= INTEGER('1371566954')) AND (start_time >= " "INTEGER('1371556954')) GROUP BY timestamp, status " "ORDER BY timestamp desc") expected_select = (expected_query[len('SELECT '):] .split('FROM')[0].strip().split(', ')) expected_from = expected_query[len('SELECT '):].split('FROM')[1] result_select = (result[len('SELECT '):].split('FROM')[0] .strip().split(', ')) result_from = result[len('SELECT '):].split('FROM')[1] six.assertCountEqual(self, expected_select, result_select) six.assertCountEqual(self, expected_from, result_from)
def test_formatting_duplicate_columns(self): """Ensure that render query runs with formatting a select for a column selected twice. """ from bigquery.query_builder import render_query result = render_query(dataset='dataset', tables=['2013_06_appspot_1'], select={ 'start_time': [{ 'alias': 'timestamp', 'format': 'INTEGER-FORMAT_UTC_USEC' }, { 'alias': 'day', 'format': ('SEC_TO_MICRO-INTEGER-' 'FORMAT_UTC_USEC-LEFT:10') }], 'status': { 'alias': 'status' }, 'resource': { 'alias': 'url' } }, conditions=[ { 'field': 'start_time', 'comparators': [{ 'condition': '<=', 'value': 1371566954, 'negate': False }], 'type': 'INTEGER' }, { 'field': 'start_time', 'comparators': [{ 'condition': '>=', 'value': 1371556954, 'negate': False }], 'type': 'INTEGER' }, ], order_by={ 'field': 'timestamp', 'direction': 'desc' }) expected_query = ("SELECT status as status, " "FORMAT_UTC_USEC(INTEGER(start_time)) as timestamp, " "LEFT(FORMAT_UTC_USEC(INTEGER(start_time*1000000))," "10) as day, resource as url FROM " "[dataset.2013_06_appspot_1] WHERE " "(start_time <= INTEGER('1371566954')) AND " "(start_time >= INTEGER('1371556954')) ORDER BY " "timestamp desc") self.assertEqual(result, expected_query)