Quickstart using the Cloud Console

You can use the Google Cloud Console as a visual interface to complete tasks like running queries, loading data, and exporting data. This quickstart shows you how to query tables in a public dataset and how to load sample data into BigQuery using the Cloud Console.

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

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

    Go to the project selector page

  3. BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to Enable the BigQuery API.

    Enable the API

  4. BigQuery provides a sandbox if you do not want to provide a credit card or enable billing for your project. The steps in this topic work for a project whether or not your project has billing enabled. If you optionally want to enable billing, see Learn how to enable billing.

Query a public dataset

The Cloud Console provides an interface to query tables, including public datasets offered by BigQuery.

In this example, you query the USA Name Data public dataset to determine the most common names in the US between 1910 and 2013.

BigQuery public datasets are displayed by default in the Cloud Console. To open the public datasets project manually, enter the following URL in your browser.

https://console.cloud.google.com/bigquery?p=bigquery-public-data&page=project

To query data in a public dataset, follow these steps:

  1. Go to the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. If the Editor tab isn't visible, then click Compose new query .

    Editor tab.

  3. Copy and paste the following query into the Editor field.

    SELECT
  name, gender,
  SUM(number) AS total
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
  name, gender
ORDER BY
  total DESC
LIMIT
  10

  4. If the query is valid, then a check mark appears along with the amount of data that the query will process. This metric helps you determine the cost of running the query. If the query is invalid, then an exclamation point appears along with an error message.

    Query validator.

  5. Click Run. The query results page appears below the query window. At the top of the query results page, the time elapsed and the data processed by the query are displayed. Below the Query complete... message, a table displays the query results with a header row containing the name of each column you selected in the query.

    Query results in the Cloud Console.

Load data into a table

Next, load data into a table and query it.

Download the data

The file you're downloading contains approximately 7 MB of data about popular baby names, and it is provided by the US Social Security Administration.

  1. Download the baby names zip file.

  2. Extract the file onto your machine.

    The zip file contains a NationalReadMe.pdf file that describes the dataset. Learn more about the dataset.

  3. Open the file named yob2014.txt to see what the data looks like. The file is a comma-separated value (CSV) file with the following three columns: name, sex (M or F), and number of children with that name. The file has no header row.

  4. Note the location of the yob2014.txt file so that you can find it later.

Create a dataset

Next, create a dataset in the Cloud Console to store the data.

  1. If necessary, open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. In the Explorer panel, click your project name.

  3. In the details panel, click Create dataset .

  4. On the Create dataset page, do the following:

    • For Dataset ID, enter babynames.

    • For Data location, choose United States (US). Currently, the public datasets are stored in the US multi-region location. For simplicity, place your dataset in the same location.

      Create dataset page.

  5. Leave all of the other default settings in place and click Create dataset.

Load the data into a new table

Next, load the data into a new table.

  1. In the Explorer panel, click the babynames dataset that you created.

  2. In the details panel, click Create table .

    Use the default values for all settings unless otherwise indicated.

  3. On the Create table page, do the following:

    • In the Source section, for Create table from, choose Upload.
    • For Select file, click Browse, navigate to the yob2014.txt file, and click Open.
    • For File format, choose CSV.
    • In the Destination section, for Table name, enter names_2014.

    • In the Schema section, click the Edit as text toggle and paste the following schema definition into the box.

        name:string,gender:string,count:integer

      New table page.

  4. Click Create table.

  5. Wait for BigQuery to create the table and load the data. When BigQuery finishes loading the data, a check mark appears in the Job history panel.

Preview the table

To preview the first few rows of the data, follow these steps:

  1. In the Explorer panel, expand babynames and select names_2014.

  2. In the details panel, click Preview. BigQuery displays the first few rows of the table.

    Cloud Console table preview.

Query the table

Now that you've loaded data into a table, you can query it. The process is identical to the previous example, except that this time, you're querying your table instead of a public table.

  1. Click Compose new query . A new query editor tab opens.

  2. Copy and paste the following query into the query text area. This query retrieves the top five baby names for US males in 2014.

    SELECT
  name,
  count
FROM
  `babynames.names_2014`
WHERE
  gender = 'M'
ORDER BY
  count DESC
LIMIT
  5

  3. Click Run. The results are displayed below the query window.

    Names query results.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this quickstart, follow these steps.

  1. If necessary, open the BigQuery page in the Cloud Console.

    Go to the BigQuery page

  2. In the Explorer panel, click the babynames dataset that you created.

  3. In the details panel, click Delete dataset. This action deletes the dataset, the table, and all the data.

  4. In the Delete dataset dialog, confirm the delete command by typing the name of your dataset (babynames) and click Delete.

What's next