In this tutorial, you learn how to usebq, the Python-based command-lineinterface (CLI) tool for BigQuery to create a dataset, load sampledata, and query tables. After completing this tutorial, you'll be familiar withbq and how to work with BigQuery by using a CLI.

For a complete reference of allbq commands and flags, see thebq command-line tool reference.

To follow step-by-step guidance for this task directly in the Google Cloud console, clickGuide me:

Guide me

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide,verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

1. Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

   For new projects, the BigQuery API is automatically enabled.

2. Optional:Enable billing for the project. If you don't want to enable billing or provide a credit card, the steps in this document still work. BigQuery provides you a sandbox to perform the steps. For more information, seeEnable the BigQuery sandbox.Note: If your project has a billing account and you want to use the BigQuery sandbox, thendisable billing for your project.

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, aCloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

Required roles

To get the permissions that you need to create a dataset, create a table, load data, and query data, ask your administrator to grant you the following IAM roles on the project:

• Run load jobs and query jobs:BigQuery Job User (roles/bigquery.jobUser)
• Create a dataset, create a table, load data into a table, and query a table:BigQuery Data Editor (roles/bigquery.dataEditor)

For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Download the file that contains the source data

The file that you're downloading contains approximately 7 MB of data aboutpopular baby names. It's provided by the US Social Security Administration.

For more information about the data, see the Social Security Administration'sBackground information for popular names.

1. Download the US Social Security Administration's data by opening thefollowing URL in a new browser tab:

   https://www.ssa.gov/OACT/babynames/names.zip

2. Extract the file.

   For more information about the dataset schema, see theNationalReadMe.pdf file you extracted.

3. To see what the data looks like, open theyob2024.txt file. This filecontains comma-separated values for name, assigned sex at birth, and numberof children with that name. The file has no header row.

4. Move the file to your working directory.

   • If you're working in Cloud Shell, clickmore_vertMoreUpload, clickChoose Files, choose theyob2024.txt file, and then clickUpload.

   • If you're working in a local shell, copy or move the fileyob2024.txtinto the directory where you're running the bq tool.

Create a dataset

1. If you launched Cloud Shell from the documentation, enter thefollowing command to set your project ID. This prevents you from having tospecify the project ID in each CLI command.

   gcloudconfigsetprojectPROJECT_ID

   ReplacePROJECT_ID with your project ID.

1. Enter the following command to create a dataset namedbabynames:

   bqmk--datasetbabynames

   The output is similar to the following:

   Dataset 'babynames' successfully created.

2. Confirm that the datasetbabynames now appears in your project:

   bqls--datasets=true

   The output is similar to the following:

     datasetId-------------  babynames

Load data into a table

1. In thebabynames dataset, load the source fileyob2024.txt into anew table namednames2024:

   bqloadbabynames.names2024yob2024.txtname:string,assigned_sex_at_birth:string,count:integer

   The output is similar to the following:

   Upload complete.Waiting on bqjob_r3c045d7cbe5ca6d2_0000018292f0815f_1 ... (1s) Current status: DONE

2. Confirm that the tablenames2024 now appears in thebabynames dataset:

   bqls--format=prettybabynames

   The output is similar to the following. Some columns are omitted to simplifythe output.

   +-----------+-------+|  tableId  | Type  |+-----------+-------+| names2024 | TABLE |+-----------+-------+

3. Confirm that the table schema of your newnames2024 table isname: string,assigned_sex_at_birth: string, andcount: integer:

   bqshowbabynames.names2024

   The output is similar to the following. Some columns are omitted to simplifythe output.

     Last modified        Schema                      Total Rows   Total Bytes----------------- ------------------------------- ------------ ------------14 Mar 17:16:45   |- name: string                    31904       607494                  |- assigned_sex_at_birth: string                  |- count: integer

Query table data

1. Determine the most popular girls' names in the data:

   bqquery\'SELECT      name,      count    FROM      babynames.names2024    WHERE      assigned_sex_at_birth = "F"    ORDER BY      count DESC    LIMIT 5'

   The output is similar to the following:

   +-----------+-------+|   name    | count |+-----------+-------+| Olivia    | 14718 || Emma      | 13485 || Amelia    | 12740 || Charlotte | 12552 || Mia       | 12113 |+-----------+-------+

2. Determine the least popular boys' names in the data:

   bqquery\'SELECT      name,      count    FROM      babynames.names2024    WHERE      assigned_sex_at_birth = "M"    ORDER BY      count ASC    LIMIT 5'

   The output is similar to the following:

   +---------+-------+|  name   | count |+---------+-------+| Aaran   |     5 || Aadiv   |     5 || Aadarsh |     5 || Aarash  |     5 || Aadrik  |     5 |+---------+-------+

   The minimum count is 5 because the source data omits names with fewer than5 occurrences.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, delete the Google Cloud project with the resources.

Delete the project

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to theManage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then clickDelete.
3. In the dialog, type the project ID, and then clickShut down to delete the project.

Delete the resources

If you used an existing project, delete the resources that you created:

1. Delete thebabynames dataset:

   bqrm--recursive=truebabynames

   The--recursive flag deletes all tables in the dataset, including thenames2024 table.

   The output is similar to the following:

   rm: remove dataset 'myproject:babynames'? (y/N)

2. To confirm the delete command, entery.

What's next

• Learn more aboutusing the bq tool.
• Learn about theBigQuery sandbox.
• Learn more aboutloading data into BigQuery.
• Learn more aboutquerying data in BigQuery.