def test_run_sync_query_submitsInteractiveQueryAndReturnsWithResultIterator(
self, bigquery_module_patch: bigquery):
ems_bigquery_client = self.__setup_client(bigquery_module_patch, [
Row((42, "hello"), {
"int_column": 0,
"str_column": 1
}),
Row((1024, "wonderland"), {
"int_column": 0,
"str_column": 1
})
])
result_rows_iterator = ems_bigquery_client.run_sync_query(self.QUERY)
result_rows = [row for row in result_rows_iterator]
arguments = self.client_mock.query.call_args_list[0][1]
assert self.QUERY == arguments["query"]
assert arguments["location"] == "EU"
assert QueryPriority.INTERACTIVE == arguments["job_config"].priority
assert arguments["job_id_prefix"] is None
assert isinstance(result_rows_iterator, Iterable)
assert len(result_rows) == 2
assert result_rows[0] == {"int_column": 42, "str_column": "hello"}
assert result_rows[1] == {
"int_column": 1024,
"str_column": "wonderland"
}
def test_w_int64_float64_bool(self):
from google.cloud.bigquery.table import Row
# "Standard" SQL dialect uses 'INT64', 'FLOAT64', 'BOOL'.
candidate = _Field("REQUIRED", "candidate", "STRING")
votes = _Field("REQUIRED", "votes", "INT64")
percentage = _Field("REQUIRED", "percentage", "FLOAT64")
incumbent = _Field("REQUIRED", "incumbent", "BOOL")
schema = [candidate, votes, percentage, incumbent]
rows = [
{"f": [{"v": "Phred Phlyntstone"}, {"v": 8}, {"v": 0.25}, {"v": "true"}]},
{"f": [{"v": "Bharney Rhubble"}, {"v": 4}, {"v": 0.125}, {"v": "false"}]},
{
"f": [
{"v": "Wylma Phlyntstone"},
{"v": 20},
{"v": 0.625},
{"v": "false"},
]
},
]
f2i = {"candidate": 0, "votes": 1, "percentage": 2, "incumbent": 3}
expected = [
Row(("Phred Phlyntstone", 8, 0.25, True), f2i),
Row(("Bharney Rhubble", 4, 0.125, False), f2i),
Row(("Wylma Phlyntstone", 20, 0.625, False), f2i),
]
coerced = self._call_fut(rows, schema)
self.assertEqual(coerced, expected)
def test_row(self):
from google.cloud.bigquery.table import Row
VALUES = (1, 2, 3)
row = Row(VALUES, {'a': 0, 'b': 1, 'c': 2})
self.assertEqual(row.a, 1)
self.assertEqual(row[1], 2)
self.assertEqual(row['c'], 3)
self.assertEqual(len(row), 3)
self.assertEqual(row.values(), VALUES)
self.assertEqual(set(row.keys()), set({'a': 1, 'b': 2, 'c': 3}.keys()))
self.assertEqual(set(row.items()),
set({'a': 1, 'b': 2, 'c': 3}.items()))
self.assertEqual(row.get('a'), 1)
self.assertEqual(row.get('d'), None)
self.assertEqual(row.get('d', ''), '')
self.assertEqual(row.get('d', default=''), '')
self.assertEqual(repr(row),
"Row((1, 2, 3), {'a': 0, 'b': 1, 'c': 2})")
self.assertFalse(row != row)
self.assertFalse(row == 3)
with self.assertRaises(AttributeError):
row.z
with self.assertRaises(KeyError):
row['z']
def dict_to_row(row_as_dict: dict) -> Row:
values = []
field_to_index = {}
for i, (key, value) in enumerate(row_as_dict.items()):
values.append(value)
field_to_index[key] = i
return Row(values=values, field_to_index=field_to_index)
def test_w_record_subfield(self):
from google.cloud.bigquery.table import Row
full_name = _Field('REQUIRED', 'full_name', 'STRING')
area_code = _Field('REQUIRED', 'area_code', 'STRING')
local_number = _Field('REQUIRED', 'local_number', 'STRING')
rank = _Field('REQUIRED', 'rank', 'INTEGER')
phone = _Field('NULLABLE', 'phone', 'RECORD',
fields=[area_code, local_number, rank])
color = _Field('REPEATED', 'color', 'STRING')
schema = [full_name, phone, color]
rows = [
{'f': [
{'v': 'Phred Phlyntstone'},
{'v': {'f': [{'v': '800'}, {'v': '555-1212'}, {'v': 1}]}},
{'v': [{'v': 'orange'}, {'v': 'black'}]},
]},
{'f': [
{'v': 'Bharney Rhubble'},
{'v': {'f': [{'v': '877'}, {'v': '768-5309'}, {'v': 2}]}},
{'v': [{'v': 'brown'}]},
]},
{'f': [
{'v': 'Wylma Phlyntstone'},
{'v': None},
{'v': []},
]},
]
phred_phone = {
'area_code': '800',
'local_number': '555-1212',
'rank': 1,
}
bharney_phone = {
'area_code': '877',
'local_number': '768-5309',
'rank': 2,
}
f2i = {'full_name': 0, 'phone': 1, 'color': 2}
expected = [
Row(('Phred Phlyntstone', phred_phone, ['orange', 'black']), f2i),
Row(('Bharney Rhubble', bharney_phone, ['brown']), f2i),
Row(('Wylma Phlyntstone', None, []), f2i),
]
coerced = self._call_fut(rows, schema)
self.assertEqual(coerced, expected)
def test_row(self):
from google.cloud.bigquery.table import Row
VALUES = (1, 2, 3)
row = Row(VALUES, {'a': 0, 'b': 1, 'c': 2})
self.assertEqual(row.a, 1)
self.assertEqual(row[1], 2)
self.assertEqual(row['c'], 3)
self.assertEqual(len(row), 3)
self.assertEqual(row.values(), VALUES)
self.assertEqual(repr(row),
"Row((1, 2, 3), {'a': 0, 'b': 1, 'c': 2})")
self.assertFalse(row != row)
self.assertFalse(row == 3)
with self.assertRaises(AttributeError):
row.z
with self.assertRaises(KeyError):
row['z']
def mock_bq_query_result():
return [
Row(['a', 'b', 'c'], {
'first': 0,
'second': 1,
'third': 3
}),
Row(['d', 'e', 'f'], {
'first': 0,
'second': 1,
'third': 3
}),
Row(['g', 'h', 'i'], {
'first': 0,
'second': 1,
'third': 3
}),
]
def test_that_parse_data_works_properly():
from google.cloud.bigquery.table import Row
test_schema = {'fields': [
{'mode': 'NULLABLE', 'name': 'column_x', 'type': 'STRING'}]}
field_to_index = {'column_x': 0}
values = ('row_value',)
test_page = [Row(values, field_to_index)]
test_output = gbq._parse_data(test_schema, test_page)
correct_output = DataFrame({'column_x': ['row_value']})
tm.assert_frame_equal(test_output, correct_output)
def _result_to_row(result):
vw = result['vw'] if 'vw' in result else None
return Row(
(datetime.date.fromtimestamp(int(result['t'] / 1000.0)),
result['T'].encode("ascii", "ignore").decode(), result['o'],
result['h'], result['l'], result['c'], result['v'], vw), {
c: i
for i, c in enumerate([
'date', 'symbol', 'open', 'high', 'low', 'close',
'volume', 'volume_weighted_price'
])
})
def test_w_record_subfield(self):
from google.cloud.bigquery.table import Row
full_name = _Field("REQUIRED", "full_name", "STRING")
area_code = _Field("REQUIRED", "area_code", "STRING")
local_number = _Field("REQUIRED", "local_number", "STRING")
rank = _Field("REQUIRED", "rank", "INTEGER")
phone = _Field(
"NULLABLE", "phone", "RECORD", fields=[area_code, local_number, rank]
)
color = _Field("REPEATED", "color", "STRING")
schema = [full_name, phone, color]
rows = [
{
"f": [
{"v": "Phred Phlyntstone"},
{"v": {"f": [{"v": "800"}, {"v": "555-1212"}, {"v": 1}]}},
{"v": [{"v": "orange"}, {"v": "black"}]},
]
},
{
"f": [
{"v": "Bharney Rhubble"},
{"v": {"f": [{"v": "877"}, {"v": "768-5309"}, {"v": 2}]}},
{"v": [{"v": "brown"}]},
]
},
{"f": [{"v": "Wylma Phlyntstone"}, {"v": None}, {"v": []}]},
]
phred_phone = {"area_code": "800", "local_number": "555-1212", "rank": 1}
bharney_phone = {"area_code": "877", "local_number": "768-5309", "rank": 2}
f2i = {"full_name": 0, "phone": 1, "color": 2}
expected = [
Row(("Phred Phlyntstone", phred_phone, ["orange", "black"]), f2i),
Row(("Bharney Rhubble", bharney_phone, ["brown"]), f2i),
Row(("Wylma Phlyntstone", None, []), f2i),
]
coerced = self._call_fut(rows, schema)
self.assertEqual(coerced, expected)
def create_bq_payload(data: Row):
"""
Takes in a row object from BigQuery and returns a dictionary
of key value pairs based on the row columns and values
Args:
data: The Row object from the BigQuery API
Returns:
A python dictionary object
"""
results = {k:v for k, v in data.items()}
return results
def test_w_int64_float64_bool(self):
from google.cloud.bigquery.table import Row
# "Standard" SQL dialect uses 'INT64', 'FLOAT64', 'BOOL'.
candidate = _Field('REQUIRED', 'candidate', 'STRING')
votes = _Field('REQUIRED', 'votes', 'INT64')
percentage = _Field('REQUIRED', 'percentage', 'FLOAT64')
incumbent = _Field('REQUIRED', 'incumbent', 'BOOL')
schema = [candidate, votes, percentage, incumbent]
rows = [
{'f': [
{'v': 'Phred Phlyntstone'},
{'v': 8},
{'v': 0.25},
{'v': 'true'},
]},
{'f': [
{'v': 'Bharney Rhubble'},
{'v': 4},
{'v': 0.125},
{'v': 'false'},
]},
{'f': [
{'v': 'Wylma Phlyntstone'},
{'v': 20},
{'v': 0.625},
{'v': 'false'},
]},
]
f2i = {'candidate': 0, 'votes': 1, 'percentage': 2, 'incumbent': 3}
expected = [
Row(('Phred Phlyntstone', 8, 0.25, True), f2i),
Row(('Bharney Rhubble', 4, 0.125, False), f2i),
Row(('Wylma Phlyntstone', 20, 0.625, False), f2i),
]
coerced = self._call_fut(rows, schema)
self.assertEqual(coerced, expected)
def test_that_parse_data_works_properly():
from google.cloud.bigquery.table import Row
test_schema = {
"fields": [{
"mode": "NULLABLE",
"name": "column_x",
"type": "STRING"
}]
}
field_to_index = {"column_x": 0}
values = ("row_value", )
test_page = [Row(values, field_to_index)]
test_output = gbq._parse_data(test_schema, test_page)
correct_output = DataFrame({"column_x": ["row_value"]})
tm.assert_frame_equal(test_output, correct_output)
def _df_row_to_bq_row(index, row):
return Row(
(index[0], index[1].date(), _nan_to_none(row['SimFinId']), _nan_to_none(row['Open']), _nan_to_none(row['High']), _nan_to_none(row['Low']), _nan_to_none(row['Close']), _nan_to_none(row['Adj. Close']), _nan_to_none(row['Dividend']), _nan_to_none(row['Volume']), _nan_to_none(row['Shares Outstanding'])),
{c: i for i, c in enumerate(['Ticker', 'Date', 'SimFinId', 'Open', 'High', 'Low', 'Close', 'Adj__Close', 'Dividend', 'Volume', 'Shares_Outstanding'])}
)
def test_select(self):
row = Row()
client = datastore.Client()