This page explains how to use the bq command-line tool to run queries, load data, and export data.
Before you begin
Before you begin this quickstart, use the Google Cloud Platform Console to create or select a project and enable billing. You also need to install the Google Cloud SDK.
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
Select or create a Google Cloud Platform project.
Make sure that billing is enabled for your Google Cloud Platform project.
- Install and initialize the Cloud SDK.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, go to Enable the BigQuery API.
Examine a table
BigQuery offers a number of sample tables that you can run queries against. In this quickstart, you'll run some queries against the Shakespeare table, which contains an entry for every word in every play.
To examine the schema of a specific table, run
bq show projectId:datasetId.tableId
where the project and dataset IDs can be omitted if they are the default values
for your bq tool. The following example examines the
shakespeare table in the
bq show publicdata:samples.shakespeare
Table publicdata:samples.shakespeare Last modified Schema Total Rows Total Bytes Expiration ----------------- ------------------------------------ ------------ ------------- ------------ 26 Aug 14:43:49 |- word: string (required) 164656 6432064 |- word_count: integer (required) |- corpus: string (required) |- corpus_date: integer (required)
Run the help command
bq help to get detailed information about the bq command-line tool.
Include a command name to get information about a specific command. For example,
the following call to
bq help retrieves information about the
bq help query
Run a query
Run a query to see how many times the substring "raisin" appears in Shakespeare's works.
To run a query, run the command
bq query "[SQL_STATEMENT]".
Escape any quotation marks inside the
Use a different quotation mark type than the surrounding marks (
The following example does a word count for the number of times that the
substring "raisin" appears in all of Shakespeare's works. The query shown is
case-sensitive; string comparisons are case-sensitive, unless you use the SQL
bq query "SELECT word, SUM(word_count) as count FROM publicdata:samples.shakespeare WHERE word CONTAINS 'raisin' GROUP BY word"
Waiting on job_dcda37c0bbed4c669b04dfd567859b90 ... (0s) Current status: DONE +---------------+-------+ | word | count | +---------------+-------+ | Praising | 4 | | raising | 5 | | raisins | 1 | | praising | 8 | | dispraising | 2 | | dispraisingly | 1 | +---------------+-------+
If you search for a word that isn't in Shakespeare's works, no results are returned. For example, the following search for "huzzah" returns no matches.
bq query "SELECT word FROM publicdata:samples.shakespeare WHERE word = 'huzzah' IGNORE CASE"
Waiting on job_e19 ... (4s) Current status: DONE $
Create a new table
Now create your own table. Every table must live inside a dataset, which is simply a group of tables. A dataset is assigned to a single project.
Step 1: Download custom data
The custom data contains approximately 7 MB of data about popular baby names, and is provided by the US Social Security Administration.
Unzip the file onto your hard drive.
The zip file contains a read me file that describes the dataset schema. Learn more about the dataset.
Open the file named
yob2010.txtto see what it looks like. The file is a comma-separated value (CSV) file with the following three columns: name, sex (
F), and number of children with that name. The file has no header row.
Copy or move the
yob2010.txtfile into the directory you are using to run bq commands.
Step 2: Create a new dataset
bq lscommand to see whether your default project has any existing datasets.
datasetId ------------- olddataset
bq lsagain to list the datasets in a specific project by including the project ID followed by a colon (:). The following example lists the datasets in the
bq ls publicdata:
datasetId ----------- samples
bq mkcommand to create a new dataset named
babynamesin your default project. A dataset name can be up to 1,024 characters long, and consist of A-Z, a-z, 0-9, and the underscore, but it cannot start with a number or underscore, or have spaces.
bq mk babynames
Dataset 'myprojectid:babynames' successfully created.
bq lsto confirm that the dataset now appears as part of the default project:
datasetId ------------- olddataset babynames
Step 3: Upload the table
bq load command creates or updates a table and loads data in a single step.
bq loadcommand to load your source file into a new table called
babynamesdataset you created above. By default, this runs synchronously, and will take a few seconds to complete.
bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer
bq loadcommand arguments:
Waiting on job_4f0c0878f6184119abfdae05f5194e65 ... (35s) Current status: DONE
bq lsto confirm that the table now appears in the dataset:
bq ls babynames
tableId Type ----------- ------- names2010 TABLE
bq showto see the schema:
bq show babynames.names2010
Table myprojectid:babynames.names2010 Last modified Schema Total Rows Total Bytes Expiration ----------------- ------------------- ------------ ------------- ------------ 13 Mar 15:31:00 |- name: string 34041 653855 |- gender: string |- count: integer
By default, when you load data, BigQuery expects UTF-8 encoded data. If you have
data that is in ISO-8859-1 (or Latin-1) encoding and are having problems with
your loaded data, you can tell BigQuery to treat your data as Latin-1
explicitly, using the
For more information, see Character Encodings.
Step 4: Run queries
Run the following command to return the most popular girls' names:
bq query "SELECT name,count FROM babynames.names2010 WHERE gender = 'F' ORDER BY count DESC LIMIT 5"
Waiting on job_58c0f5ca52764ef1902eba611b71c651 ... (0s) Current status: DONE +----------+-------+ | name | COUNT | +----------+-------+ | Isabella | 22731 | | Sophia | 20477 | | Emma | 17179 | | Olivia | 16860 | | Ava | 15300 | +----------+-------+
Run the following command to see the most unusual boys' names.
The minimum count is 5 because the source data omits names
with fewer than 5 occurrences.
bq query "SELECT name,count FROM babynames.names2010 WHERE gender = 'M' ORDER BY count ASC LIMIT 5"
Waiting on job_556ba2e5aad340a7b2818c3e3280b7a3 ... (1s) Current status: DONE +----------+-------+ | name | COUNT | +----------+-------+ | Aarian | 5 | | Aaidan | 5 | | Aamarion | 5 | | Aadhavan | 5 | | Aaqib | 5 | +----------+-------+
To avoid incurring charges to your GCP account for the resources used in this quickstart:
bq rmcommand to remove the
babynamesdataset. Use the
-rflag to delete all tables in the dataset, include the
bq rm -r babynames
Confirm the delete command by typing
To learn more about the bq command-line tool, see
To learn more about loading data, see Loading data into BigQuery.
To learn more about querying data, see Querying data.
To learn how to export data out of BigQuery, see Exporting data from BigQuery.
To learn how to create an application using the Google BigQuery API, see Create a simple application with the API.