Connecting to PAD using Python

Python is a powerful programming language that allows analysts and engineers to manipulate, explore, clean, and visualize data.

While the BigQuery console and Looker Studio provide lower-code interfaces to do this work, some PAD users may be more comfortable in Python or may need to perform more complicated scripting. Fortunately, Google offers several easy options to connect to your PAD project using Python: Colab (similar to Jupyter Notebook), Google Cloud Shell (a shell terminal directly in your browser), and the Google Cloud CLI (for connecting in your local environment). This article will explain how to authenticate and connect to your PAD project using your computer's Google Cloud CLI and Python client library. 

Installing and Initializing the Google Cloud CLI

1. Using Google’s installation guide, install the latest version of gcloud CLI according to your computer operating system.
   1. If using a Mac, it is important to also run the optional install.sh script to add gcloud CLI to your PATH
2. Once installed, use the gcloud init command to authenticate your credentials and connect to PAD

gcloud init

3. Select Y to log in using your CTA PAD Google account

To continue, you must log in. Would you like to log in (Y/n)? Y

4. In your browser, log in to your Google user account when prompted and click Allow to grant permission to access Google Cloud resources. 
   1. NOTE: Make sure you log in and grant access from your CTA PAD Google account rather than your personal or work account.

5. At the command prompt, select a Google Cloud project from the list of projects to which you have access. If you only have one project, gcloud init will automatically select it for you.

Pick cloud project to use:
[1] [my-project-1]
[2] [my-project-2]
...
Please enter your numeric choice:

6. gcloud init will then confirm that the connection was successful by showing you the following message. From here, you can explore all of the gcloud commands or proceed to installing the Python client library. 

Run `gcloud help config` to learn how to change individual settings

This gcloud configuration is called [default]. You can create additional configurations if you work with multiple accounts and/or projects.
Run `gcloud topic configurations` to learn more.

Some things to try next:

* Run `gcloud --help` to see the Cloud Platform services you can interact with. And run `gcloud help COMMAND` to get help on any gcloud command.
* Run `gcloud topic --help` to learn about advanced features of the SDK like arg files and output formatting
* Run `gcloud cheat-sheet` to see a roster of go-to `gcloud` commands.

7. If you want to change your configuration or re-authenticate using a different project, you can always do so by running gcloud init again.

Installing the Google Python client library

From your local or virtual environment install google-cloud-bigquery.

pip install google-cloud-bigquery

You can then use this package in your Python scripts! The library will use the default cloud configuration you set up using the gcloud cli above. You'll also need to set up application default credentials (more info on that here). For more information on this package and how to use it, you can check out Google’s help docs here.

Once everything is installed and authenticated you can run this sample code from Google to confirm everything is set up correctly.

from google.cloud import bigquery
client = bigquery.Client()
QUERY = (
    'SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` '
    'WHERE state = "TX" '
    'LIMIT 100')
query_job = client.query(QUERY)  # API request
rows = query_job.result()  # Waits for query to finish
for row in rows:
    print(row.name)

As always, if you have any questions, send us an email to help@techallies.org!