Sign in

1. Products
2. Google Cloud Platform
3. Google BigQuery

Google BigQuery

Feedback on this document

Run a Query

In this exercise, you will try running queries against some public data exposed by BigQuery.

Open the BigQuery browser tool . Google BigQuery has several public data tables that you can run queries against. For more information, see a full description of each sample . You can see these public tables in the browser tool by clicking the publicdata:samples tab on the left side to expand it ( publicdata is the project ID, and samples is the dataset that holds all the tables in that project).

Let's run a few basic queries against these tables. To run a query, click the Compose Query button, then enter the query SQL in the textbox at the top of the page and click Run Query . The results (or an error code) will be displayed below the query box. In the query text, tables should be qualified in the format datasetId . tableId . You can see a complete description of BigQuery's SQL syntax on the Query Reference page .

Try out the following queries in the textbox:

• A few simple SELECT queries

  Heaviest 10 children
  SELECT weight_pounds, state, year, gestation_weeks FROM publicdata:samples.natality
ORDER BY weight_pounds DESC LIMIT 10;

  

  A popular myth debunked!
  SELECT word FROM publicdata:samples.shakespeare WHERE word="huzzah";
  

• A few count queries

  How many works of Shakespeare are there?
  SELECT corpus FROM publicdata:samples.shakespeare GROUP BY corpus;
  
  Which are the longest works?
  SELECT corpus, sum(word_count) AS wordcount FROM publicdata:samples.shakespeare GROUP BY corpus ORDER BY wordcount DESC;
  
  
  
  

Create a Table

Now that you've had a chance to run queries against an existing table, let's try creating a new table and running queries against it.

The US Government records lists of baby names given to children born each year. Download the national data file from the government's Popular Baby Names page and unzip it. This zipped file consists of several comma-delimited value (CSV) files, each listing the baby names for a single year. The file names list the year described by that file.

The format of each CSV file is name , gender , count where gender is either 'M' or 'F', and count is the number of children given that name in that year.

1. Create a new dataset to hold your table:
• Click the down arrow next to the project name in the browser tool and select Create new dataset . Choose a dataset name that isn't already listed on the left side navigation bar. The 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.
2. Create your table:

1. Select the down arrow next to your dataset and click Create new table to open the "Create Table" dialog.
2. Select File upload .
3. In Table ID , enter a name for your new table. Naming rules are the same as for your dataset.
4. In Schema , enter the schema of the baby names file. A CSV schema is specified as column1_name : data_type , column2_name : data_type ,... The data in the baby names file is stored as baby_name , gender , count_of_names with data types string, string, integer. Therefore, you should enter something like this for your schema:
name:string,gender:string,count:integer

   The values "name", "gender", and "count" are custom labels assigned to the columns in your new table.

   Note: BigQuery also supports uploading JSON files, which have a different schema structure.

   
   
5. Select Choose file and browse to one of the yob****.txt files that you downloaded and unzipped.
6. Click OK to upload your data.

When your table has been created and the data uploaded, the new table should appear in your dataset. Now you can run queries against it, as you did before. Here are a few queries you can try (substitute the correct dataset ID and table ID for your table):

• Top five men's names

  SELECT name,count FROM datasetId . tableId WHERE gender = 'M' ORDER BY count DESC LIMIT 5;

• An interesting name choice

  SELECT name,count FROM datasetId . tableId WHERE name CONTAINS 'Abc'

Export a Table

You can export query results as a CSV file to your local computer, or as a permanent table to your dataset. To export query results after you run a query, click Download as CSV to download the results as a CSV file to your local computer. You can also store the results as a persistent table to any dataset in which you have write permissions by clicking Save as Table .