("Phred Phlyntstone", 32),

("Bharney Rhubble", 33),

("Wylma Phlyntstone", 29),

("Bhettye Rhubble", 27),

bigquery.SchemaField("transaction_time", "TIMESTAMP", mode="REQUIRED"),

bigquery.SchemaField("transaction_id", "INTEGER", mode="REQUIRED"),

bigquery.SchemaField("user_email", "STRING", mode="REQUIRED"),

bigquery.SchemaField("store_code", "STRING", mode="REQUIRED"),

bigquery.SchemaField(

"items",

"RECORD",

mode="REPEATED",

fields=[

bigquery.SchemaField("item_code", "STRING", mode="REQUIRED"),

bigquery.SchemaField("quantity", "INTEGER", mode="REQUIRED"),

bigquery.SchemaField("comments", "STRING", mode="NULLABLE"),

bigquery.SchemaField("expiration_date", "DATE", mode="REQUIRED"),

],

),

from google.cloud.bigquery.table import _parse_schema_resource

json_filename = os.path.join(WHERE, filename)

with open(json_filename, "r") as schema_file:

"""Run-time configuration to be modified at set-up.

CLIENT = None

def setUp(self):

self.to_delete = []

def tearDown(self):

def _still_in_use(bad_request):

return any(

error["reason"] == "resourceInUse" for error in bad_request._errors

)

retry_in_use = RetryErrors(BadRequest, error_predicate=_still_in_use)

retry_storage_errors_conflict = RetryErrors(

(Conflict, TooManyRequests, InternalServerError, ServiceUnavailable)

)

for doomed in self.to_delete:

if isinstance(doomed, storage.Bucket):

retry_storage_errors_conflict(doomed.delete)(force=True)

elif isinstance(doomed, (Dataset, bigquery.DatasetReference)):

retry_in_use(Config.CLIENT.delete_dataset)(doomed, delete_contents=True)

elif isinstance(doomed, (Table, bigquery.TableReference)):

retry_in_use(Config.CLIENT.delete_table)(doomed)

else:

doomed.delete()

def test_get_service_account_email(self):

client = Config.CLIENT

got = client.get_service_account_email()

self.assertIsInstance(got, six.text_type)

self.assertIn("@", got)

def _create_bucket(self, bucket_name, location=None):

storage_client = storage.Client()

bucket = storage_client.bucket(bucket_name)

retry_storage_errors(bucket.create)(location=location)

self.to_delete.append(bucket)

return bucket

def test_create_dataset(self):

DATASET_ID = _make_dataset_id("create_dataset")

dataset = self.temp_dataset(DATASET_ID)

self.assertTrue(_dataset_exists(dataset))

self.assertEqual(dataset.dataset_id, DATASET_ID)

self.assertEqual(dataset.project, Config.CLIENT.project)

def test_get_dataset(self):

dataset_id = _make_dataset_id("get_dataset")

client = Config.CLIENT

dataset_arg = Dataset(client.dataset(dataset_id))

dataset_arg.friendly_name = "Friendly"

dataset_arg.description = "Description"

dataset = retry_403(client.create_dataset)(dataset_arg)

self.to_delete.append(dataset)

dataset_ref = client.dataset(dataset_id)

# Get with a reference.

got = client.get_dataset(dataset_ref)

self.assertEqual(got.friendly_name, "Friendly")

self.assertEqual(got.description, "Description")

# Get with a string.

got = client.get_dataset(dataset_id)

self.assertEqual(got.friendly_name, "Friendly")

self.assertEqual(got.description, "Description")

# Get with a fully-qualified string.

got = client.get_dataset("{}.{}".format(client.project, dataset_id))

self.assertEqual(got.friendly_name, "Friendly")

self.assertEqual(got.description, "Description")

def test_update_dataset(self):

dataset = self.temp_dataset(_make_dataset_id("update_dataset"))

self.assertTrue(_dataset_exists(dataset))

self.assertIsNone(dataset.friendly_name)

self.assertIsNone(dataset.description)

self.assertEqual(dataset.labels, {})

dataset.friendly_name = "Friendly"

dataset.description = "Description"

dataset.labels = {"priority": "high", "color": "blue"}

ds2 = Config.CLIENT.update_dataset(

dataset, ("friendly_name", "description", "labels")

)

self.assertEqual(ds2.friendly_name, "Friendly")

self.assertEqual(ds2.description, "Description")

self.assertEqual(ds2.labels, {"priority": "high", "color": "blue"})

ds2.labels = {

"color": "green", # change

"shape": "circle", # add

"priority": None, # delete

}

ds3 = Config.CLIENT.update_dataset(ds2, ["labels"])

self.assertEqual(ds3.labels, {"color": "green", "shape": "circle"})

# If we try to update using d2 again, it will fail because the

# previous update changed the ETag.

ds2.description = "no good"

with self.assertRaises(PreconditionFailed):

Config.CLIENT.update_dataset(ds2, ["description"])

def test_list_datasets(self):

datasets_to_create = [

"new" + unique_resource_id(),

"newer" + unique_resource_id(),

"newest" + unique_resource_id(),

]

for dataset_id in datasets_to_create:

self.temp_dataset(dataset_id)

# Retrieve the datasets.

iterator = Config.CLIENT.list_datasets()

all_datasets = list(iterator)

self.assertIsNone(iterator.next_page_token)

created = [

dataset

for dataset in all_datasets

if dataset.dataset_id in datasets_to_create

and dataset.project == Config.CLIENT.project

]

self.assertEqual(len(created), len(datasets_to_create))

def test_list_datasets_w_project(self):

# Retrieve datasets from a different project.

iterator = Config.CLIENT.list_datasets(project="bigquery-public-data")

all_datasets = frozenset([dataset.dataset_id for dataset in iterator])

self.assertIn("usa_names", all_datasets)

def test_create_table(self):

dataset = self.temp_dataset(_make_dataset_id("create_table"))

table_id = "test_table"

table_arg = Table(dataset.table(table_id), schema=SCHEMA)

self.assertFalse(_table_exists(table_arg))

table = retry_403(Config.CLIENT.create_table)(table_arg)

self.to_delete.insert(0, table)

self.assertTrue(_table_exists(table))

self.assertEqual(table.table_id, table_id)

def test_create_table_w_time_partitioning_w_clustering_fields(self):

from google.cloud.bigquery.table import TimePartitioning

from google.cloud.bigquery.table import TimePartitioningType

dataset = self.temp_dataset(_make_dataset_id("create_table_tp_cf"))

table_id = "test_table"

table_arg = Table(

dataset.table(table_id), schema=TIME_PARTITIONING_CLUSTERING_FIELDS_SCHEMA

)

self.assertFalse(_table_exists(table_arg))

table_arg.time_partitioning = TimePartitioning(field="transaction_time")

table_arg.clustering_fields = ["user_email", "store_code"]

table = retry_403(Config.CLIENT.create_table)(table_arg)

self.to_delete.insert(0, table)

self.assertTrue(_table_exists(table))

self.assertEqual(table.table_id, table_id)

time_partitioning = table.time_partitioning

self.assertEqual(time_partitioning.type_, TimePartitioningType.DAY)

self.assertEqual(time_partitioning.field, "transaction_time")

self.assertEqual(table.clustering_fields, ["user_email", "store_code"])

def test_delete_dataset_with_string(self):

dataset_id = _make_dataset_id("delete_table_true")

dataset_ref = Config.CLIENT.dataset(dataset_id)

retry_403(Config.CLIENT.create_dataset)(Dataset(dataset_ref))

self.assertTrue(_dataset_exists(dataset_ref))

Config.CLIENT.delete_dataset(dataset_id)

self.assertFalse(_dataset_exists(dataset_ref))

def test_delete_dataset_delete_contents_true(self):

dataset_id = _make_dataset_id("delete_table_true")

dataset = retry_403(Config.CLIENT.create_dataset)(

Dataset(Config.CLIENT.dataset(dataset_id))

)

table_id = "test_table"

table_arg = Table(dataset.table(table_id), schema=SCHEMA)

table = retry_403(Config.CLIENT.create_table)(table_arg)

Config.CLIENT.delete_dataset(dataset, delete_contents=True)

self.assertFalse(_table_exists(table))

def test_delete_dataset_delete_contents_false(self):

from google.api_core import exceptions

dataset = self.temp_dataset(_make_dataset_id("delete_table_false"))

table_id = "test_table"

table_arg = Table(dataset.table(table_id), schema=SCHEMA)

retry_403(Config.CLIENT.create_table)(table_arg)

with self.assertRaises(exceptions.BadRequest):

Config.CLIENT.delete_dataset(dataset)

def test_get_table_w_public_dataset(self):

public = "bigquery-public-data"

dataset_id = "samples"

table_id = "shakespeare"

table_ref = DatasetReference(public, dataset_id).table(table_id)

# Get table with reference.

table = Config.CLIENT.get_table(table_ref)

self.assertEqual(table.table_id, table_id)

self.assertEqual(table.dataset_id, dataset_id)

self.assertEqual(table.project, public)

schema_names = [field.name for field in table.schema]

self.assertEqual(schema_names, ["word", "word_count", "corpus", "corpus_date"])

# Get table with string.

table = Config.CLIENT.get_table("{}.{}.{}".format(public, dataset_id, table_id))

self.assertEqual(table.table_id, table_id)

self.assertEqual(table.dataset_id, dataset_id)

self.assertEqual(table.project, public)

def test_list_partitions(self):

table_ref = DatasetReference(

"bigquery-public-data", "ethereum_blockchain"

).table("blocks")

all_rows = Config.CLIENT.list_partitions(table_ref)

self.assertIn("20180801", all_rows)

self.assertGreater(len(all_rows), 1000)

def test_list_tables(self):

dataset_id = _make_dataset_id("list_tables")

dataset = self.temp_dataset(dataset_id)

# Retrieve tables before any are created for the dataset.

iterator = Config.CLIENT.list_tables(dataset)

all_tables = list(iterator)

self.assertEqual(all_tables, [])

self.assertIsNone(iterator.next_page_token)

# Insert some tables to be listed.

tables_to_create = [

"new" + unique_resource_id(),

"newer" + unique_resource_id(),

"newest" + unique_resource_id(),

]

for table_name in tables_to_create:

table = Table(dataset.table(table_name), schema=SCHEMA)

created_table = retry_403(Config.CLIENT.create_table)(table)

self.to_delete.insert(0, created_table)

# Retrieve the tables.

iterator = Config.CLIENT.list_tables(dataset)

all_tables = list(iterator)

self.assertIsNone(iterator.next_page_token)

created = [

table

for table in all_tables

if (table.table_id in tables_to_create and table.dataset_id == dataset_id)

]

self.assertEqual(len(created), len(tables_to_create))

# List tables with a string ID.

iterator = Config.CLIENT.list_tables(dataset_id)

self.assertGreater(len(list(iterator)), 0)

# List tables with a fully-qualified string ID.

iterator = Config.CLIENT.list_tables(

"{}.{}".format(Config.CLIENT.project, dataset_id)

)

self.assertGreater(len(list(iterator)), 0)

def test_update_table(self):

dataset = self.temp_dataset(_make_dataset_id("update_table"))

TABLE_NAME = "test_table"

table_arg = Table(dataset.table(TABLE_NAME), schema=SCHEMA)

self.assertFalse(_table_exists(table_arg))

table = retry_403(Config.CLIENT.create_table)(table_arg)

self.to_delete.insert(0, table)

self.assertTrue(_table_exists(table))

self.assertIsNone(table.friendly_name)

self.assertIsNone(table.description)

self.assertEqual(table.labels, {})

table.friendly_name = "Friendly"

table.description = "Description"

table.labels = {"priority": "high", "color": "blue"}

table2 = Config.CLIENT.update_table(

table, ["friendly_name", "description", "labels"]

)

self.assertEqual(table2.friendly_name, "Friendly")

self.assertEqual(table2.description, "Description")

self.assertEqual(table2.labels, {"priority": "high", "color": "blue"})

table2.description = None

table2.labels = {

"color": "green", # change

"shape": "circle", # add

"priority": None, # delete

}

table3 = Config.CLIENT.update_table(table2, ["description", "labels"])

self.assertIsNone(table3.description)

self.assertEqual(table3.labels, {"color": "green", "shape": "circle"})

# If we try to update using table2 again, it will fail because the

# previous update changed the ETag.

table2.description = "no good"

with self.assertRaises(PreconditionFailed):

Config.CLIENT.update_table(table2, ["description"])

def test_update_table_schema(self):

dataset = self.temp_dataset(_make_dataset_id("update_table"))

TABLE_NAME = "test_table"

table_arg = Table(dataset.table(TABLE_NAME), schema=SCHEMA)

self.assertFalse(_table_exists(table_arg))

table = retry_403(Config.CLIENT.create_table)(table_arg)

self.to_delete.insert(0, table)

self.assertTrue(_table_exists(table))

voter = bigquery.SchemaField("voter", "BOOLEAN", mode="NULLABLE")

schema = table.schema

schema.append(voter)

table.schema = schema

updated_table = Config.CLIENT.update_table(table, ["schema"])

self.assertEqual(len(updated_table.schema), len(schema))

for found, expected in zip(updated_table.schema, schema):

self.assertEqual(found.name, expected.name)

self.assertEqual(found.field_type, expected.field_type)

self.assertEqual(found.mode, expected.mode)

@staticmethod

def _fetch_single_page(table, selected_fields=None):

iterator = Config.CLIENT.list_rows(table, selected_fields=selected_fields)

page = six.next(iterator.pages)

return list(page)

def _create_table_many_columns(self, rowcount):

# Generate a table of maximum width via CREATE TABLE AS SELECT.

# first column is named 'rowval', and has a value from 1..rowcount

# Subsequent column is named col_<N> and contains the value N*rowval,

# where N is between 1 and 9999 inclusive.

dsname = _make_dataset_id("wide_schema")

dataset = self.temp_dataset(dsname)

table_id = "many_columns"

table_ref = dataset.table(table_id)

self.to_delete.insert(0, table_ref)

colprojections = ",".join(

["r * {} as col_{}".format(n, n) for n in range(1, 10000)]

)

sql = """

""".format(

dsname, table_id, colprojections, rowcount

)

query_job = Config.CLIENT.query(sql)

query_job.result()

self.assertEqual(query_job.statement_type, "CREATE_TABLE_AS_SELECT")

self.assertEqual(query_job.ddl_operation_performed, "CREATE")

self.assertEqual(query_job.ddl_target_table, table_ref)

return table_ref

def test_query_many_columns(self):

# Test working with the widest schema BigQuery supports, 10k columns.

row_count = 2

table_ref = self._create_table_many_columns(row_count)

rows = list(

Config.CLIENT.query(

"SELECT * FROM `{}.{}`".format(table_ref.dataset_id, table_ref.table_id)

)

)

self.assertEqual(len(rows), row_count)

# check field representations adhere to expected values.

correctwidth = 0

badvals = 0

for r in rows:

vals = r._xxx_values

rowval = vals[0]

if len(vals) == 10000:

correctwidth = correctwidth + 1

for n in range(1, 10000):

if vals[n] != rowval * (n):

badvals = badvals + 1

self.assertEqual(correctwidth, row_count)

self.assertEqual(badvals, 0)

def test_insert_rows_then_dump_table(self):

NOW_SECONDS = 1448911495.484366

NOW = datetime.datetime.utcfromtimestamp(NOW_SECONDS).replace(tzinfo=UTC)

ROWS = [

("Phred Phlyntstone", 32, NOW),

("Bharney Rhubble", 33, NOW + datetime.timedelta(seconds=10)),

("Wylma Phlyntstone", 29, NOW + datetime.timedelta(seconds=20)),

("Bhettye Rhubble", 27, None),

]

ROW_IDS = range(len(ROWS))

dataset = self.temp_dataset(_make_dataset_id("insert_rows_then_dump"))

TABLE_ID = "test_table"

schema = [

bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),

bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),

bigquery.SchemaField("now", "TIMESTAMP"),

]

table_arg = Table(dataset.table(TABLE_ID), schema=schema)

self.assertFalse(_table_exists(table_arg))

table = retry_403(Config.CLIENT.create_table)(table_arg)

self.to_delete.insert(0, table)

self.assertTrue(_table_exists(table))

errors = Config.CLIENT.insert_rows(table, ROWS, row_ids=ROW_IDS)

self.assertEqual(len(errors), 0)

rows = ()

# Allow for "warm up" before rows visible. See

# https://cloud.google.com/bigquery/streaming-data-into-bigquery#dataavailability

# 8 tries -> 1 + 2 + 4 + 8 + 16 + 32 + 64 = 127 seconds

retry = RetryResult(_has_rows, max_tries=8)

rows = retry(self._fetch_single_page)(table)

row_tuples = [r.values() for r in rows]

by_age = operator.itemgetter(1)

self.assertEqual(sorted(row_tuples, key=by_age), sorted(ROWS, key=by_age))

def test_load_table_from_local_avro_file_then_dump_table(self):

from google.cloud.bigquery.job import SourceFormat

from google.cloud.bigquery.job import WriteDisposition

TABLE_NAME = "test_table_avro"

ROWS = [

("violet", 400),

("indigo", 445),

("blue", 475),

("green", 510),

("yellow", 570),

("orange", 590),

("red", 650),

]

dataset = self.temp_dataset(_make_dataset_id("load_local_then_dump"))

table_ref = dataset.table(TABLE_NAME)

table = Table(table_ref)

self.to_delete.insert(0, table)

with open(os.path.join(WHERE, "data", "colors.avro"), "rb") as avrof:

config = bigquery.LoadJobConfig()

config.source_format = SourceFormat.AVRO

config.write_disposition = WriteDisposition.WRITE_TRUNCATE

job = Config.CLIENT.load_table_from_file(

avrof, table_ref, job_config=config

)

# Retry until done.

job.result(timeout=JOB_TIMEOUT)

self.assertEqual(job.output_rows, len(ROWS))

table = Config.CLIENT.get_table(table)

rows = self._fetch_single_page(table)

row_tuples = [r.values() for r in rows]

by_wavelength = operator.itemgetter(1)

self.assertEqual(

sorted(row_tuples, key=by_wavelength), sorted(ROWS, key=by_wavelength)

)

@unittest.skipIf(pandas is None, "Requires `pandas`")

@unittest.skipIf(pyarrow is None, "Requires `pyarrow`")

def test_load_table_from_dataframe_w_automatic_schema(self):

"""Test that a DataFrame with dtypes that map well to BigQuery types

df_data = collections.OrderedDict(

[

("bool_col", pandas.Series([True, False, True], dtype="bool")),

(

"ts_col",

pandas.Series(

[

datetime.datetime(2010, 1, 2, 3, 44, 50),

datetime.datetime(2011, 2, 3, 14, 50, 59),

datetime.datetime(2012, 3, 14, 15, 16),

],

dtype="datetime64[ns]",

).dt.tz_localize(pytz.utc),

),

(

"dt_col",

pandas.Series(

[

datetime.datetime(2010, 1, 2, 3, 44, 50),

datetime.datetime(2011, 2, 3, 14, 50, 59),

datetime.datetime(2012, 3, 14, 15, 16),

],

dtype="datetime64[ns]",

),

),

("float32_col", pandas.Series([1.0, 2.0, 3.0], dtype="float32")),

("float64_col", pandas.Series([4.0, 5.0, 6.0], dtype="float64")),

("int8_col", pandas.Series([-12, -11, -10], dtype="int8")),

("int16_col", pandas.Series([-9, -8, -7], dtype="int16")),

("int32_col", pandas.Series([-6, -5, -4], dtype="int32")),

("int64_col", pandas.Series([-3, -2, -1], dtype="int64")),

("uint8_col", pandas.Series([0, 1, 2], dtype="uint8")),

("uint16_col", pandas.Series([3, 4, 5], dtype="uint16")),

("uint32_col", pandas.Series([6, 7, 8], dtype="uint32")),

]

)

dataframe = pandas.DataFrame(df_data, columns=df_data.keys())

dataset_id = _make_dataset_id("bq_load_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_dataframe_w_automatic_schema".format(

Config.CLIENT.project, dataset_id

)

load_job = Config.CLIENT.load_table_from_dataframe(dataframe, table_id)

load_job.result()

table = Config.CLIENT.get_table(table_id)

self.assertEqual(

tuple(table.schema),

(

bigquery.SchemaField("bool_col", "BOOLEAN"),

bigquery.SchemaField("ts_col", "TIMESTAMP"),

bigquery.SchemaField("dt_col", "DATETIME"),

bigquery.SchemaField("float32_col", "FLOAT"),

bigquery.SchemaField("float64_col", "FLOAT"),

bigquery.SchemaField("int8_col", "INTEGER"),

bigquery.SchemaField("int16_col", "INTEGER"),

bigquery.SchemaField("int32_col", "INTEGER"),

bigquery.SchemaField("int64_col", "INTEGER"),

bigquery.SchemaField("uint8_col", "INTEGER"),

bigquery.SchemaField("uint16_col", "INTEGER"),

bigquery.SchemaField("uint32_col", "INTEGER"),

),

)

self.assertEqual(table.num_rows, 3)

@unittest.skipIf(pandas is None, "Requires `pandas`")

@unittest.skipIf(pyarrow is None, "Requires `pyarrow`")

def test_load_table_from_dataframe_w_nulls(self):

"""Test that a DataFrame with null columns can be uploaded if a

# Schema with all scalar types.

scalars_schema = (

bigquery.SchemaField("bool_col", "BOOLEAN"),

bigquery.SchemaField("bytes_col", "BYTES"),

bigquery.SchemaField("date_col", "DATE"),

bigquery.SchemaField("dt_col", "DATETIME"),

bigquery.SchemaField("float_col", "FLOAT"),

bigquery.SchemaField("geo_col", "GEOGRAPHY"),

bigquery.SchemaField("int_col", "INTEGER"),

bigquery.SchemaField("num_col", "NUMERIC"),

bigquery.SchemaField("str_col", "STRING"),

bigquery.SchemaField("time_col", "TIME"),

bigquery.SchemaField("ts_col", "TIMESTAMP"),

)

table_schema = scalars_schema + (

# TODO: Array columns can't be read due to NULLABLE versus REPEATED

# mode mismatch. See:

# https://issuetracker.google.com/133415569#comment3

# bigquery.SchemaField("array_col", "INTEGER", mode="REPEATED"),

# TODO: Support writing StructArrays to Parquet. See:

# https://jira.apache.org/jira/browse/ARROW-2587

# bigquery.SchemaField("struct_col", "RECORD", fields=scalars_schema),

)

num_rows = 100

nulls = [None] * num_rows

df_data = collections.OrderedDict(

[

("bool_col", nulls),

("bytes_col", nulls),

("date_col", nulls),

("dt_col", nulls),

("float_col", nulls),

("geo_col", nulls),

("int_col", nulls),

("num_col", nulls),

("str_col", nulls),

("time_col", nulls),

("ts_col", nulls),

]

)

dataframe = pandas.DataFrame(df_data, columns=df_data.keys())

dataset_id = _make_dataset_id("bq_load_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_dataframe_w_nulls".format(

Config.CLIENT.project, dataset_id

)

# Create the table before loading so that schema mismatch errors are

# identified.

table = retry_403(Config.CLIENT.create_table)(

Table(table_id, schema=table_schema)

)

self.to_delete.insert(0, table)

job_config = bigquery.LoadJobConfig(schema=table_schema)

load_job = Config.CLIENT.load_table_from_dataframe(

dataframe, table_id, job_config=job_config

)

load_job.result()

table = Config.CLIENT.get_table(table)

self.assertEqual(tuple(table.schema), table_schema)

self.assertEqual(table.num_rows, num_rows)

@unittest.skipIf(pandas is None, "Requires `pandas`")

@unittest.skipIf(pyarrow is None, "Requires `pyarrow`")

def test_load_table_from_dataframe_w_required(self):

"""Test that a DataFrame with required columns can be uploaded if a

table_schema = (

bigquery.SchemaField("name", "STRING", mode="REQUIRED"),

bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),

)

records = [{"name": "Chip", "age": 2}, {"name": "Dale", "age": 3}]

dataframe = pandas.DataFrame(records, columns=["name", "age"])

job_config = bigquery.LoadJobConfig(schema=table_schema)

dataset_id = _make_dataset_id("bq_load_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_dataframe_w_required".format(

Config.CLIENT.project, dataset_id

)

# Create the table before loading so that schema mismatch errors are

# identified.

table = retry_403(Config.CLIENT.create_table)(

Table(table_id, schema=table_schema)

)

self.to_delete.insert(0, table)

job_config = bigquery.LoadJobConfig(schema=table_schema)

load_job = Config.CLIENT.load_table_from_dataframe(

dataframe, table_id, job_config=job_config

)

load_job.result()

table = Config.CLIENT.get_table(table)

self.assertEqual(tuple(table.schema), table_schema)

self.assertEqual(table.num_rows, 2)

@unittest.skipIf(pandas is None, "Requires `pandas`")

@unittest.skipIf(pyarrow is None, "Requires `pyarrow`")

def test_load_table_from_dataframe_w_explicit_schema(self):

# Schema with all scalar types.

scalars_schema = (

bigquery.SchemaField("bool_col", "BOOLEAN"),

bigquery.SchemaField("bytes_col", "BYTES"),

bigquery.SchemaField("date_col", "DATE"),

bigquery.SchemaField("dt_col", "DATETIME"),

bigquery.SchemaField("float_col", "FLOAT"),

bigquery.SchemaField("geo_col", "GEOGRAPHY"),

bigquery.SchemaField("int_col", "INTEGER"),

bigquery.SchemaField("num_col", "NUMERIC"),

bigquery.SchemaField("str_col", "STRING"),

bigquery.SchemaField("time_col", "TIME"),

bigquery.SchemaField("ts_col", "TIMESTAMP"),

)

table_schema = scalars_schema + (

# TODO: Array columns can't be read due to NULLABLE versus REPEATED

# mode mismatch. See:

# https://issuetracker.google.com/133415569#comment3

# bigquery.SchemaField("array_col", "INTEGER", mode="REPEATED"),

# TODO: Support writing StructArrays to Parquet. See:

# https://jira.apache.org/jira/browse/ARROW-2587

# bigquery.SchemaField("struct_col", "RECORD", fields=scalars_schema),

)

df_data = collections.OrderedDict(

[

("bool_col", [True, None, False]),

("bytes_col", [b"abc", None, b"def"]),

(

"date_col",

[datetime.date(1, 1, 1), None, datetime.date(9999, 12, 31)],

),

(

"dt_col",

[

datetime.datetime(1, 1, 1, 0, 0, 0),

None,

datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),

],

),

("float_col", [float("-inf"), float("nan"), float("inf")]),

(

"geo_col",

[

"POINT(30 10)",

None,

"POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))",

],

),

("int_col", [-9223372036854775808, None, 9223372036854775807]),

(

"num_col",

[

decimal.Decimal("-99999999999999999999999999999.999999999"),

None,

decimal.Decimal("99999999999999999999999999999.999999999"),

],

),

("str_col", [u"abc", None, u"def"]),

(

"time_col",

[datetime.time(0, 0, 0), None, datetime.time(23, 59, 59, 999999)],

),

(

"ts_col",

[

datetime.datetime(1, 1, 1, 0, 0, 0, tzinfo=pytz.utc),

None,

datetime.datetime(

9999, 12, 31, 23, 59, 59, 999999, tzinfo=pytz.utc

),

],

),

]

)

dataframe = pandas.DataFrame(df_data, dtype="object", columns=df_data.keys())

dataset_id = _make_dataset_id("bq_load_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_dataframe_w_explicit_schema".format(

Config.CLIENT.project, dataset_id

)

job_config = bigquery.LoadJobConfig(schema=table_schema)

load_job = Config.CLIENT.load_table_from_dataframe(

dataframe, table_id, job_config=job_config

)

load_job.result()

table = Config.CLIENT.get_table(table_id)

self.assertEqual(tuple(table.schema), table_schema)

self.assertEqual(table.num_rows, 3)

def test_load_table_from_json_basic_use(self):

table_schema = (

bigquery.SchemaField("name", "STRING", mode="REQUIRED"),

bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),

bigquery.SchemaField("birthday", "DATE", mode="REQUIRED"),

bigquery.SchemaField("is_awesome", "BOOLEAN", mode="REQUIRED"),

)

json_rows = [

{"name": "John", "age": 18, "birthday": "2001-10-15", "is_awesome": False},

{"name": "Chuck", "age": 79, "birthday": "1940-03-10", "is_awesome": True},

]

dataset_id = _make_dataset_id("bq_system_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_json_basic_use".format(

Config.CLIENT.project, dataset_id

)

# Create the table before loading so that schema mismatch errors are

# identified.

table = retry_403(Config.CLIENT.create_table)(

Table(table_id, schema=table_schema)

)

self.to_delete.insert(0, table)

job_config = bigquery.LoadJobConfig(schema=table_schema)

load_job = Config.CLIENT.load_table_from_json(

json_rows, table_id, job_config=job_config

)

load_job.result()

table = Config.CLIENT.get_table(table)

self.assertEqual(tuple(table.schema), table_schema)

self.assertEqual(table.num_rows, 2)

def test_load_table_from_json_schema_autodetect(self):

json_rows = [

{"name": "John", "age": 18, "birthday": "2001-10-15", "is_awesome": False},

{"name": "Chuck", "age": 79, "birthday": "1940-03-10", "is_awesome": True},

]

dataset_id = _make_dataset_id("bq_system_test")

self.temp_dataset(dataset_id)

table_id = "{}.{}.load_table_from_json_basic_use".format(

Config.CLIENT.project, dataset_id

)

# Use schema with NULLABLE fields, because schema autodetection

# defaults to field mode NULLABLE.

table_schema = (

bigquery.SchemaField("name", "STRING", mode="NULLABLE"),

bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),

bigquery.SchemaField("birthday", "DATE", mode="NULLABLE"),

bigquery.SchemaField("is_awesome", "BOOLEAN", mode="NULLABLE"),

)

# create the table before loading so that the column order is predictable

table = retry_403(Config.CLIENT.create_table)(

Table(table_id, schema=table_schema)

)

self.to_delete.insert(0, table)

# do not pass an explicit job config to trigger automatic schema detection

load_job = Config.CLIENT.load_table_from_json(json_rows, table_id)

load_job.result()

table = Config.CLIENT.get_table(table)

self.assertEqual(tuple(table.schema), table_schema)

self.assertEqual(table.num_rows, 2)

def test_load_avro_from_uri_then_dump_table(self):

from google.cloud.bigquery.job import CreateDisposition

from google.cloud.bigquery.job import SourceFormat

from google.cloud.bigquery.job import WriteDisposition

table_name = "test_table"

rows = [

("violet", 400),

("indigo", 445),

("blue", 475),

("green", 510),