Connect to a BigQuery instance

To access a Google BigQuery project, Hackolade Studio currently supports access using two methods, Application Default Credentials and Service account.

Choosing the most appropriate method depends on the use case.

If you want to connect from your local workstation, then you should favor using Application Default Credentials method because it allows for better auditing and access management, as you will be authenticated as a user (e.g. yourself) to Google Cloud Platform.

While if your use case implies running the Hackolade Studio Command-Line Interface on servers (for instance using Docker), then you should favor using the Service Account because this method is tailored for Machine-to-Machine use cases.

The Hackolade process for reverse-engineering of Google BigQuery datasets includes the execution of SQL statements to discover datasets, tables and views, columns and their data types. 

 

Application Default Credentials

ADC are created by the gcloud CLI client on your workstation.

Instructions to setup ADC on your local workstation

As stated by Google Cloud Platform's documentation:

1. Install gcloud CLI client on your workstation
2. If not performed by the last step of the installer, go to a terminal prompt and initialize gcloud: gcloud init
3. And finally authenticate yourself to GCP: gcloud auth application-default login

You should be getting this confirmation:

 

Gcloud creates an application_default_credentials.json credentials file in the following location depending on your operating system:

• Windows: %userprofile%\Appdata\Roaming\gcloud\legacy_credentials\<your GCP identifier>\adc.json

or in %userprofile%\Appdata\Roaming\gcloud\application_default_credentials.json

• Mac: ${HOME}/.config/gcloud/application_default_credentials.json
• Linux: ${HOME}/.config/gcloud/application_default_credentials.json

Note that on Mac and Linux, this location is in a hidden folder. On Mac to be able to select a hidden folder in a file picker, you might need to use the CMD + SHIFT + . keyboard shortcut for MacOS to show them in the picker first. On Linux all folders are displayed.

 

Create the Hackolade Studio connection settings

Give a meaningful name to the connection, and provide the mandatory Project ID.

Also reference the path and name of the credentials path and file generated by gcloud.

 

 

Service Account

Instructions to create a Service Account key file

Credentials are loaded from a file created in the Google IAM & Admin console by your administrator.

 

Here are the steps to follow:

1) go to the IAM console, and choose Service Accounts in the menu

2) select an existing project, and press the button + Create a Service Account

3) provide the service account details, and create

4) grant the following service account access to the project:

    - BigQuery Data Viewer: in order to be able to perform reverse-engineering

and

    - BigQuery Data Editor: in order to be able to forward-engineer (apply SQL script to instance)

    - BigQuery Job User: in order to be able to forward-engineer (apply SQL script to instance)

 

then save the role

5) select the action Manage Keys for the service account:

 

and create a new key with JSON type.  The .json file gets downloaded in the browser.

 

 

Create the Hackolade Studio connection settings

Give a meaningful name to the connection, optionally provide a specific project id, and reference the path and name of the key file provided by your administrator.

 

The key file must be referenced in the connection settings dialog.