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)
