Visualizing BigQuery Data in a Jupyter Notebook

BigQuery is a petabyte-scale analytics data warehouse that you can use to run SQL queries over vast amounts of data in near realtime.

Data visualization tools can help you make sense of your BigQuery data and help you analyze the data interactively. You can use visualization tools to help you identify trends, respond to them, and make predictions using your data. In this tutorial, you use the BigQuery Python client library and Pandas in a Jupyter notebook to visualize data in the BigQuery natality sample table.

Objectives

In this tutorial you:

  • Set up an environment to run Jupyter notebooks
  • Query and visualize BigQuery data using BigQuery Python client library and Pandas

Costs

BigQuery is a paid product and you incur BigQuery usage costs when accessing BigQuery. BigQuery query pricing provides the first 1 TB per month free of charge. For more information, see BigQuery pricing.

Before you begin

Before you begin this tutorial, use the Google Cloud Platform Console to create or select a project and enable billing.

  1. Sign in to your Google Account.

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

  2. Select or create a GCP project.

    Go to the Manage resources page

  3. Make sure that billing is enabled for your project.

    Learn how to enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

Setting up a Local Jupyter Environment

In this tutorial, you use a locally hosted Jupyter notebook. Complete the following steps to install Jupyter, set up authentication, and install the required Python libraries.

  1. Run the following command in your terminal to install the latest version of the BigQuery Python client library, including the Pandas library, which is required for functions that use Pandas:

    pip install --upgrade google-cloud-bigquery[pandas]
    
  2. Follow the installation instructions in the Jupyter Documentation to install Jupyter.

  3. Follow the instructions in the Getting Started with Authentication page to set up Application Default Credentials. You set up authentication by creating a service account and setting an environment variable.

Overview: Jupyter Notebooks

A notebook provides an environment to author and execute code. A notebook is essentially a source artifact, saved as a .ipynb file — it can contain descriptive text content, executable code blocks, and associated results (rendered as interactive HTML). Structurally, a notebook is a sequence of cells.

A cell is a block of input text that is evaluated to produce results. Cells can be of two types:

  • Code cells — contain code to evaluate. Any outputs or results from executing the code are rendered immediately below the input code.

  • Markdown cells — contain markdown text that is converted to HTML to produce headers, lists, and formatted text.

The screenshot below shows a Markdown cell followed by a Python code cell. Note that the output of the Python cell is shown immediately below the code.

Jupyter markdown and code cells

Each opened notebook is associated with a running session. In IPython, this is also referred to as a kernel. This session executes all the code entered within the notebook, and manages the state (variables, their values, functions and classes, and any existing Python modules you load).

Querying and visualizing BigQuery data

In this section of the tutorial, you create a Google Cloud Datalab notebook used to query and visualize data in BigQuery. You create visualizations using the data in the natality sample table. All queries in this tutorial are in standard SQL syntax.

To query and visualize BigQuery data using a Jupyter notebook:

  1. If you haven't already started Jupyter, run the following command in your terminal:

    jupyter notebook
    
  2. Jupyter should now be running and open in a browser window. In the Jupyter window, click the New button and select Python 2 or Python 3 to create a new Python notebook.

    Jupyter new Python 3 notebook

  3. At the top of the page, click Untitled.

  4. In the Rename notebook dialog, type a new name such as BigQuery tutorial, and then click Rename.

  5. The BigQuery Python client library provides a magic command that allows you to run queries with minimal code. To load the magic commands from the client library, paste the following code into the first cell of the notebook.

    %load_ext google.cloud.bigquery

  6. Run the command by clicking the Run button or with SHIFT + ENTER.

  7. The BigQuery client library provides a cell magic, %%bigquery, which runs a SQL query and returns the results as a Pandas DataFrame. Enter the following into the next cell to return total births by year.

    %%bigquery
    SELECT
        source_year AS year,
        COUNT(is_male) AS birth_count
    FROM `bigquery-public-data.samples.natality`
    GROUP BY year
    ORDER BY year DESC
    LIMIT 15

  8. Click Run.

  9. The query results appear below the code cell.

    Births by year table

  10. In the next cell block, enter the following command to run the same query, but this time save the results to a new variable total_births, which is given as an argument to the %%bigquery. The results can then be used for further analysis and visualization.

    %%bigquery total_births
    SELECT
        source_year AS year,
        COUNT(is_male) AS birth_count
    FROM `bigquery-public-data.samples.natality`
    GROUP BY year
    ORDER BY year DESC
    LIMIT 15

  11. Click Run.

  12. Now you have a Pandas DataFrame saved to variable total_births, which is ready to plot. To prepare for plotting the query results, paste the following built-in magic command in the next cell to activate matplotlib, which is the library used by Pandas for plotting.

    %matplotlib inline

  13. Click Run.

  14. In the next cell, enter the following code to use the Pandas DataFrame.plot() method to visualize the query results as a bar chart. See the Pandas documentation to learn more about data visualization with Pandas.

    total_births.plot(kind='bar', x='year', y='birth_count');

  15. Click Run.

  16. The chart appears below the code block.

    Births by year bar chart

  17. Next, paste the following query into the next cell to retrieve the number of births by weekday.

    %%bigquery births_by_weekday
    SELECT
        wday,
        SUM(CASE WHEN is_male THEN 1 ELSE 0 END) AS male_births,
        SUM(CASE WHEN is_male THEN 0 ELSE 1 END) AS female_births
    FROM `bigquery-public-data.samples.natality`
    WHERE wday IS NOT NULL
    GROUP BY wday
    ORDER BY wday ASC

    Because the wday (weekday) field allows null values, the query excludes records where wday is null.

  18. Click Run.

  19. In the next cell, enter the following code to visualize the query results using a line chart.

    births_by_weekday.plot(x='wday');

  20. Click Run.

  21. The chart appears below the code block. Notice the number of births dramatically decreases on Sunday (1) and Saturday (7).

    Births by weekday line chart

  22. Click File > Save and Checkpoint or click the save icon in the toolbar. Creating a checkpoint allows you to roll the notebook back to a previous state.

Pandas DataFrames

Magic commands allow you to use minimal syntax to interact with BigQuery. Behind the scenes, %%bigquery uses the BigQuery Python client library to run the given query, convert the results to a Pandas Dataframe, optionally save the results to a variable, and finally display the results. Using the BigQuery Python client library directly instead of through magic commands gives you more control over your queries and allows for more complex configurations. The library's integrations with Pandas enable you to combine the power of declarative SQL with imperative code (Python) to perform interesting data analysis, visualization, and transformation tasks.

Querying and visualizing BigQuery data using pandas DataFrames

In this section of the tutorial, you query and visualize data in BigQuery using pandas DataFrames. You use the BigQuery Python client library to query BigQuery data. You use the Pandas library to analyze data using DataFrames.

  1. Type the following Python code into the next cell to import the BigQuery Python client library and initialize a client. The BigQuery client is used to send and receive messages from the BigQuery API.

    from google.cloud import bigquery
    client = bigquery.Client()

  2. Click Run.

  3. Use the Client.query() method to run a query. In the next cell, enter the following code to run a query to retrieve the annual count of plural births by plurality (2 for twins, 3 for triplets, etc.).

    sql = """
    SELECT
        plurality,
        COUNT(1) AS count,
        year
    FROM
        `bigquery-public-data.samples.natality`
    WHERE
        NOT IS_NAN(plurality) AND plurality > 1
    GROUP BY
        plurality, year
    ORDER BY
        count DESC
    """
    df = client.query(sql).to_dataframe()
    df.head()

  4. Click Run.

  5. To chart the query results in your DataFrame, insert the following code into the next cell to pivot the data and create a stacked bar chart of the count of plural births over time.

    pivot_table = df.pivot(index='year', columns='plurality', values='count')
    pivot_table.plot(kind='bar', stacked=True, figsize=(15, 7));

  6. Click Run.

  7. The chart appears below the code block.

    Birth plurality by year stacked bar chart

  8. In the next cell, enter the following query to retrieve the count of births by the number of gestation weeks.

    sql = """
    SELECT
        gestation_weeks,
        COUNT(1) AS count
    FROM
        `bigquery-public-data.samples.natality`
    WHERE
        NOT IS_NAN(gestation_weeks) AND gestation_weeks <> 99
    GROUP BY
        gestation_weeks
    ORDER BY
        gestation_weeks
    """
    df = client.query(sql).to_dataframe()

  9. Click Run.

  10. To chart the query results in your DataFrame, paste the following code in the next cell.

    ax = df.plot(kind='bar', x='gestation_weeks', y='count', figsize=(15,7))
    ax.set_title('Count of Births by Gestation Weeks')
    ax.set_xlabel('Gestation Weeks')
    ax.set_ylabel('Count');

  11. Click Run.

  12. The bar chart appears below the code block.

    Average weight by gestation weeks chart

What's next

  • Learn more about writing queries for BigQueryQuerying Data in the BigQuery documentation explains how to run queries, create user-defined functions (UDFs), and more.

  • Explore BigQuery syntax — The preferred dialect for SQL queries in BigQuery is standard SQL, which is described in the SQL Reference. BigQuery's legacy SQL-like syntax is described in the Query Reference (legacy SQL).

Was this page helpful? Let us know how we did:

Send feedback about...