def load(project_id, bq_client, src_dataset_id, dst_dataset_id):
"""
Transform safely loaded tables and store results in target dataset.
:param project_id: Identifies the BQ project
:param bq_client: a BigQuery client object
:param src_dataset_id: reference to source dataset object
:param dst_dataset_id: reference to destination dataset object
:return: List of BQ job_ids
"""
dst_dataset = Dataset(f'{bq_client.project}.{dst_dataset_id}')
dst_dataset.description = f'Vocabulary cleaned and loaded from {src_dataset_id}'
dst_dataset.labels = {'type': 'vocabulary'}
dst_dataset.location = "US"
bq_client.create_dataset(dst_dataset, exists_ok=True)
src_tables = list(bq_client.list_tables(dataset=src_dataset_id))
job_config = QueryJobConfig()
query_jobs = []
for src_table in src_tables:
schema = bq.get_table_schema(src_table.table_id)
destination = f'{project_id}.{dst_dataset_id}.{src_table.table_id}'
table = bq_client.create_table(Table(destination, schema=schema),
exists_ok=True)
job_config.destination = table
query = SELECT_TPL.render(project_id=project_id,
dataset_id=src_dataset_id,
table=src_table.table_id,
fields=schema)
query_job = bq_client.query(query, job_config=job_config)
LOGGER.info(f'table:{destination} job_id:{query_job.job_id}')
query_jobs.append(query_job)
query_job.result()
return query_jobs
def upload_tweets():
big_query_client = bigquery.Client.from_service_account_json('my-beam-project-b2834963a4ae.json')
dataset_ref = big_query_client.dataset('Tweets')
dataset = Dataset(dataset_ref)
dataset.description = 'This represents tweets of trending topics'
dataset = big_query_client.create_dataset(dataset)
SCHEMA = [
SchemaField('Tweets', 'STRING', mode='Nullable'),
]
table_ref = big_query_client.dataset('Tweets').table('tabletweet')
load_config = LoadJobConfig()
load_config.skip_leading_rows = 0
load_config.schema = SCHEMA
load_config.allow_quoted_newlines = True
load_config.ignore_unknown_values = False
load_config.max_bad_records = 200
with open('tweets.csv', 'rb') as readable:
big_query_client.load_table_from_file(
readable, table_ref, job_config=load_config)
print('tweets file uploaded to big query')
def createBigQuery():
biguery_name = 'quotedataset'
bigquery_description = 'this is a quotedataset test'
client = bigquery.Client()
dataset_ref = client.dataset(biguery_name)
dataset = Dataset(dataset_ref)
dataset.description = bigquery_description
dataset = client.create_dataset(dataset) # API request
print('Dataset {} created.'.format(dataset.dataset_id))
table_id = 'quotes_table'
table_ref = dataset_ref.table(table_id)
table = bigquery.Table(table_ref)
table.schema = (
bigquery.SchemaField('Quote', 'STRING', 'REPEATED'),
bigquery.SchemaField('Author', 'STRING', 'REPEATED'),
bigquery.SchemaField('Tags', 'STRING', 'REPEATED'),
)
table = client.create_table(table)
print('Created table {} in dataset {}.'.format(table_id, biguery_name))
def __create_dataset(self, data_set, data_set_description):
"""
This method create a DataSet in Google's Big Query.
:param data_set: Name of the dataSet
:type data_set: String
:param data_set_description: Short description about dataSet.
:type data_set_description: String.
"""
dataset_ref = self.client.dataset(data_set)
dataset = Dataset(dataset_ref)
dataset.description = data_set_description
dataset = self.client.create_dataset(dataset) # API request
def check_and_create_staging_dataset(dst_dataset_id, bucket_name, bq_client):
"""
:param dst_dataset_id: final destination to load the vocabulary in BigQuery
:param bucket_name: the location in GCS containing the vocabulary files
:param bq_client: google bigquery client
:return: staging dataset object
"""
staging_dataset_id = f'{dst_dataset_id}_staging'
staging_dataset = Dataset(f'{bq_client.project}.{staging_dataset_id}')
try:
bq_client.get_dataset(staging_dataset)
except NotFound:
staging_dataset.description = f'Vocabulary loaded from gs://{bucket_name}'
staging_dataset.labels = {'type': 'vocabulary', 'phase': 'staging'}
staging_dataset.location = "US"
staging_dataset = bq_client.create_dataset(staging_dataset)
LOGGER.info(f'Successfully created dataset {staging_dataset_id}')
return staging_dataset
def create_data_set(self, data_set_name):
"""
:param data_set_name: str - The name of the dataset to be created
:return: 0 indicates success
"""
data_set_ref = self.client.dataset(data_set_name)
data_set = Dataset(data_set_ref)
data_set.description = ''
data_set.location = 'EU'
try:
self.client.create_dataset(data_set) # API request
logging.info('Data set - ' + data_set_name +
' successfully created')
except Conflict:
logging.info('Data set - ' + data_set_name + ' already exists')
return 0
def make_dataset(project,
dataset_id,
friendly_name=None,
description=None,
default_table_expiration_ms=None,
location=None,
labels=None,
access_entries=None):
dataset_ref = DatasetReference(project, dataset_id)
dataset = Dataset(dataset_ref)
dataset.friendly_name = friendly_name
dataset.description = description
dataset.default_table_expiration_ms = default_table_expiration_ms
dataset.location = location
if labels is not None:
dataset.labels = labels
if access_entries is not None:
dataset.access_entries = access_entries
return dataset
def create_dataset(self, dataset_name, dataset_description):
dataset_ref = self.client.dataset(dataset_name)
dataset = Dataset(dataset_ref)
dataset.description = dataset_description
dataset = self.client.create_dataset(dataset)
return dataset
