How to Onboard Free Public BigQuery Data on Magic Data

Intro

Google's BigQuery data warehouse provides a treasure trove of free public datasets that you can use to super charge your own data operations or simply to give Magic Data a spin!

In this tutorial we'll walk you through how to onboard a public dataset in minutes, generate a data catalog, and start using brainstorming to generate queries against the data.

Keep in mind that these same steps apply if you are simply onboarding your BigQuery Data!

Step by Step

Let's breakdown exactly how step by step:

0. In this tutorial, we will be using a Service Account JSON file to authenticate to BigQuery in our Google Cloud Platform project. This is our recommended form of authentication to Google Cloud resources whenever possible.

Before getting started please make sure the following APIs are enabled in your GCP environment:

• The BigQuery API

• The Cloud Resource Manager API

• The Google Drive API (if any of your datasets are linked to external Drive files)

• The Google Sheets API (if any of your datasets are linked to Google Sheets)

Then make sure the following permission roles are enabled on your Service Account JSON:

• BigQuery Job User

• BigQuery Data Viewer

• Driver Reader (if available) or Editor (if Google Drive and Google Sheets files are linked)

And finally, if your datasets do have links to external Google Drive or Google Sheets files, those files will need to shared with your created Service Account's email address (usually of the format <service-account-name>@<your-project-id>.iam.gserviceaccount.com).

If you have any trouble, please don't hesitate to email us at hello@makemagic.io and we will walk you through it.

Now let's jump into it!

1. Navigate to Your Landscape and click "Add Resource"

2. Select "BigQuery" and click "Next"

3. Navigate over to BigQuery in your Google Cloud Console and click "Add data".

4. Click "Public Datasets"

5. Search and open a public dataset that you would like to explore.

6. Copy the text in the "Overview" section.

7. Come back to Magic Data and drop the overview text into the Description field. Then click "Next"

8. Authenticate to your GCP project using your desired method and click "Connect". Here we uploaded a service account JSON file with a "BigQuery Job User" role.

We STRONGLY recommend using Service Accounts that are permissioned for the least required permission roles and have the needed datasets permissioned for access. Avoid using admin level credentials or credentials that have access to data outside the scope of your use case. The credentials used must have the ability to create and run jobs on BigQuery in your project.

9. When you land on the Choose Datasets screen you will see all the datasets within your project as well as all the available Public BigQuery datasets available.

In this case we only want to onboard the Births Data so we click our internal data schemas until it shows "None Selected".

10. Then hop over to the Public Datasets tab and open the "bigquery-public-data" schema.

11. Find and select the one or more datasets you'd like to onboard.

12. Magic Data will now start an automated multi-agent process to explore your data, discovery entity relationships and generate a comprehensive data catalog!

13. Once complete, take a look around by clicking "1. View Docs" or "2. Start Brainstorming".

If you start a Brainstorm you can ask any open ended questions you would like about the data, generate queries and execute them right inside the Brainstorm.

Happy data hunting!