I have been working on developing different Machine Learning models along with custom algorithm using Jupyter Notebook for a while where I mostly worked on data files (CSV, JSON etc.) on my laptops windows 10 environment. While I am working on Google Cloud Platform for Data Analytics using different GCP services like BigTable, BigQuery, DataProc for majorly developing Data pipeline and exploratory data analytics.

GCP though having very intuitive UI for writing all BigQuery queries and also provide Jupyter Notebook integration with BigQuery in GCP console, working on your local Jupyter Notebook environment has different comfort feeling. Many industry experts prefer to use local Jupyter notebook rather working on any cloud service. Hence, I thought to write this article to provide details “How to configure Google BigQuery with Jupyter Notebook on your windows Laptop”

What you already need

Python Setup

You must be having windows OS preferably Windows-10 installed. You should have Python 3.5 or higher and pip already installed on your windows laptop. (Refer to python and pip installation steps for windows : https://www.python.org/downloads/windows/ )

GCP Setup

Make sure you should have account configure with Google Cloud Platform. (Refer to GCP account setup steps : https://cloud.google.com/gcp/getting-started/ )

Let’s Play on playground

Configuring GCP BigQuery

Launch GCP Console : https://console.cloud.google.com/home/

Enable BigQuery API

First you need to activate BigQuery API. Enabling a BigQuery API requires you to accept the Terms of Service and billing responsibility for the BigQuery API. You need proper permissions on the project and the API to enable it. To enable a BigQuery API for a project using the console: Go to the GCP Console BigQuery API Library

GCP Console

Check whether BigQuery API already enabled by checking the list

Listing of all API available on GCP

If not, use Enable API and Services options and search for BigQuery API to enable it

Click on Enable API and Services

Select BigQuery API to enable it for you account

Generating Credentials

To allow your application code to use a Cloud API, you will need to set up the proper credentials for your application to authenticate its identity to the service and to obtain authorization to perform tasks. (These credential-related mechanisms are known as auth schemes.)

Go to Credentials options in API & Services

Provide necessary privilege to your service account to access BigQuery

In the “Create Credentials” drop down, select service account key

Now we are creating new Service Account Key

1.Select “New Service Account” from Service Account Dropdown

2. Type unique name for your service account name in “Service account name” field

3. In the Role drop down select BigQuery -> BigQuery Admin. This gives you full administrator privileges while working with BigQuery.

4. Keep “Key type” as “JSON” only.

5. And click Create.

It will take few moments to create a new Key. After creating the service account and key, following message will appear

And at the same time the key JSON file downloaded on your local computer.

You will find details on Credentials page about your key and service account.

Configuring your windows machine

Installing BigQuery Libraries for Python

The google BigQuery API client python libraries includes the functions you need to connect your Jupyter Notebook to the BigQuery

Open command promo (aka Dos prompt) on windows and type following command. (Refer to Python Setup for PIP installation)

pip install google-cloud-bigquery

Refer following link for details: https://pypi.org/project/google-cloud-bigquery/

Setting up environment variable for Credentials

Open System Properties of your windows system and click on environment variables

Environment Variable configuration on Windows 10

Create new environment variable as “GOOGLE_APPLICATION_CREDENTIALS” and assigned the location of your credential JSON file which downloaded when you created Service Account (refer Generating Credentials)

Configure environment variable for Google Application Credentials

Verify BigQuery working with your Jupyter Notebook

Perform a query

Create a new Jupyter Notebook in your folder for your project, and look at the example code to see how it works.

(Note: Use your own BigQuery to try)

Happy Coding

Now you are able to connect to Google Cloud Platform BigQuery and can directly work with your local Windows 10 Jupyter Notebook.

Hope this is helpful. Have fun on Data engineering using BigQuery on local machine.

Happy Data Engineering!!! Enjoy!!!