The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Managing Datasets and Tables

Next ►
►

In this section, we will cover how to manage datasets and tables in BigQuery. This includes creating, updating, and deleting datasets and tables, as well as understanding their properties and configurations.

Key Concepts

  1. Datasets: Containers that hold tables, views, and other resources.
  2. Tables: Structured data storage within datasets, consisting of rows and columns.
  3. Schemas: Definitions of the structure of a table, including column names, types, and modes.

Creating Datasets

To create a dataset in BigQuery, you can use the BigQuery Console, the bq command-line tool, or the BigQuery API.

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. In the navigation pane, click on your project name.
  3. Click on the "Create dataset" button.
  4. Fill in the dataset ID, data location, and other optional settings.
  5. Click "Create dataset".

Using the bq Command-Line Tool

bq mk --dataset --location=US my_project:my_dataset

Using the BigQuery API

from google.cloud import bigquery

client = bigquery.Client()
dataset_id = "{}.{}".format(client.project, "my_dataset")
dataset = bigquery.Dataset(dataset_id)
dataset.location = "US"
dataset = client.create_dataset(dataset)
print("Created dataset {}.{}".format(client.project, dataset.dataset_id))

Creating Tables

Tables can be created in a similar manner using the BigQuery Console, the bq command-line tool, or the BigQuery API.

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. Navigate to the dataset where you want to create the table.
  3. Click on the "Create table" button.
  4. Choose the source of the table (e.g., empty table, CSV, JSON).
  5. Define the schema of the table.
  6. Click "Create table".

Using the bq Command-Line Tool

bq mk --table my_project:my_dataset.my_table name:STRING,age:INTEGER

Using the BigQuery API

from google.cloud import bigquery

client = bigquery.Client()
table_id = "{}.{}.{}".format(client.project, "my_dataset", "my_table")
schema = [
    bigquery.SchemaField("name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)
print("Created table {}.{}.{}".format(client.project, table.dataset_id, table.table_id))

Updating Datasets and Tables

Updating Datasets

You can update dataset properties such as description and labels.

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. Navigate to the dataset you want to update.
  3. Click on the dataset name.
  4. Click on the "Edit" button.
  5. Update the desired properties.
  6. Click "Save".

Using the bq Command-Line Tool

bq update --description "New description" my_project:my_dataset

Using the BigQuery API

dataset.description = "New description"
dataset = client.update_dataset(dataset, ["description"])
print("Updated dataset {}.{}".format(client.project, dataset.dataset_id))

Updating Tables

You can update table properties such as schema, description, and labels.

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. Navigate to the table you want to update.
  3. Click on the table name.
  4. Click on the "Edit schema" button.
  5. Update the schema as needed.
  6. Click "Save".

Using the bq Command-Line Tool

bq update --table --description "New description" my_project:my_dataset.my_table

Using the BigQuery API

table.description = "New description"
table = client.update_table(table, ["description"])
print("Updated table {}.{}.{}".format(client.project, table.dataset_id, table.table_id))

Deleting Datasets and Tables

Deleting Datasets

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. Navigate to the dataset you want to delete.
  3. Click on the dataset name.
  4. Click on the "Delete dataset" button.
  5. Confirm the deletion.

Using the bq Command-Line Tool

bq rm -r -d my_project:my_dataset

Using the BigQuery API

client.delete_dataset(dataset_id, delete_contents=True, not_found_ok=True)
print("Deleted dataset '{}'".format(dataset_id))

Deleting Tables

Using the BigQuery Console

  1. Open the BigQuery Console.
  2. Navigate to the table you want to delete.
  3. Click on the table name.
  4. Click on the "Delete table" button.
  5. Confirm the deletion.

Using the bq Command-Line Tool

bq rm -t my_project:my_dataset.my_table

Using the BigQuery API

client.delete_table(table_id, not_found_ok=True)
print("Deleted table '{}'".format(table_id))

Practical Exercises

Exercise 1: Create a Dataset and Table

  1. Create a dataset named test_dataset in your project.
  2. Create a table named test_table within test_dataset with the following schema:
    • id (INTEGER)
    • name (STRING)
    • created_at (TIMESTAMP)

Solution

bq mk --dataset --location=US my_project:test_dataset
bq mk --table my_project:test_dataset.test_table id:INTEGER,name:STRING,created_at:TIMESTAMP

Exercise 2: Update Table Schema

  1. Add a new column email (STRING) to the test_table.

Solution

bq update --table --schema id:INTEGER,name:STRING,created_at:TIMESTAMP,email:STRING my_project:test_dataset.test_table

Exercise 3: Delete a Table

  1. Delete the test_table from test_dataset.

Solution

bq rm -t my_project:test_dataset.test_table

Summary

In this section, we covered the essential operations for managing datasets and tables in BigQuery. You learned how to create, update, and delete datasets and tables using the BigQuery Console, the bq command-line tool, and the BigQuery API. These skills are fundamental for organizing and managing your data effectively in BigQuery.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved