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