| 18 | from google.api_core.exceptions import Conflict, NotFound |
| 19 | |
| 20 | def main(args): |
| 21 | # Construct a BigQuery client object. |
| 22 | client = bigquery.Client() |
| 23 | # If it does not already exist, create dataset to store table. |
| 24 | dataset_id = f"{args.project_id}.{args.dataset_name}" |
| 25 | dataset = bigquery.Dataset(dataset_id) |
| 26 | dataset.location = args.dataset_location |
| 27 | try: |
| 28 | dataset = client.create_dataset(dataset, timeout=30) |
| 29 | print("Created dataset {}.{}".format(client.project, dataset.dataset_id)) |
| 30 | except Conflict as e: |
| 31 | if ("ALREADY_EXISTS" in e.details[0]['detail']): |
| 32 | print(f"Dataset {dataset_id} already exists.") |
| 33 | else: |
| 34 | print(f"Unable to create dataset. Error with code {e.code} and message {e.message}") |
| 35 | return |
| 36 | except Exception as e: |
| 37 | print(f"Unable to create dataset. Error with code {e.code} and message {e.message}") |
| 38 | return |
| 39 | |
| 40 | # Verify table exists. |
| 41 | table_id = f"{dataset_id}.{args.table_name}" |
| 42 | try: |
| 43 | table = client.get_table(table_id) |
| 44 | print(f"Table {table_id} already exists. Run script with a new --table_name argument.") |
| 45 | return |
| 46 | except NotFound: |
| 47 | pass |
| 48 | except Exception as e: |
| 49 | print(f"Unable to verify if table exists. Error with code {e.code} and message {e.message}") |
| 50 | return |
| 51 | |
| 52 | # Create query job that writes the top 10 names to a table. |
| 53 | job_config = bigquery.QueryJobConfig(destination=table_id) |
| 54 | sql = """ |
| 55 | SELECT |
| 56 | name, |
| 57 | SUM(number) AS total |
| 58 | FROM |
| 59 | `bigquery-public-data.usa_names.usa_1910_2013` |
| 60 | GROUP BY |
| 61 | name |
| 62 | ORDER BY |
| 63 | total DESC |
| 64 | LIMIT |
| 65 | 10; |
| 66 | """ |
| 67 | |
| 68 | # Start the query, passing in the extra configuration. |
| 69 | query_job = client.query(sql, job_config=job_config) # Make an API request. |
| 70 | query_job.result() # Wait for the job to complete. |
| 71 | |
| 72 | print(f"Query results loaded to the table {table_id}") |
| 73 | |
| 74 | if __name__ == "__main__": |
| 75 | parser = argparse.ArgumentParser(description="A script to create BigQuery query job.") |