DataSpell 2021.3 Help

Connect to BigQuery

BigQuery is a REST-based web service that allows you to run complex analytical SQL-based queries under large data sets.

To connect to BigQuery, you can use the following approaches:

You can jump to these sections if you already have a configured service account with the necessary permissions. Otherwise, consider reading Configuring the Google Cloud project.

Configuring the Google Cloud project

Step 1. Create or select a project

  • To create a project, select IAM & Admin | Create a project in the Navigation menu of the Google Cloud Platform. Alternatively, click the Create a project list on the Dashboard page and click New project.

  • To select an existing project, click a project that you want to use in the Select a project list on the Dashboard page.

    Create or select a project

Google user account

When you use authorization with the Google user account, you need to receive the authorization code in a web browser.

Step 1. Connect to BigQuery from DataSpell

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. On the Data Sources tab in the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select BigQuery.

  3. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataSpell downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

    You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  4. From the Authentication list, select Google User Account.

  5. In the Project ID field, type the project identifier.

    Usually, it is a part of the service account email identifier that goes after the at sign (@). In our case, it is bigqueryproject-322409.

  6. To ensure that the connection to the data source is successful, click the Test Connection link.

  7. From the Authorization Code Required dialog, cut the URL, paste it into the address bar of your web browser and press Enter to follow the URL.

  8. Authorize access to your BigQuery application in your Google account.

  9. Copy the authorization code received from Google, paste it in the Authorization Code Required dialog, and click OK.

  10. To ensure that the connection to the data source is successful, click the Test Connection link.

Application Default Credentials

For authorization, the BigQuery driver uses data from a special file. By default, the path to the file is ~/.config/gcloud/application_default_credentials.json.

In DataSpell 2021.3.3 and later, you can set a custom location for the credentials file by using the GOOGLE_APPLICATION_CREDENTIALS environment variable.

Step 1. Create a service account

  1. In the Navigation menu of the Google Cloud Platform, select IAM & Admin | Service accounts.

  2. On the Service accounts page, click Create service account.

    Create service account
  3. On the Service account details step in the wizard, type a project name and description.

    Service account details
  4. On the Grant this service account access to the project step in the wizard, select roles for this service account.

    For example, for read-only access, select BigQuery Data Viewer, BigQuery Job User, and BigQuery User from the BigQuery menu. Alternatively, select BigQuery | BigQuery Admin for access to all resources within the project. For more information about roles and permissions, see Predefined roles and permissions at cloud.google.com.

    Grant this service account access to the project

Step 2. Generating service account keys

  1. On the Service accounts page (IAM & Admin | Service accounts), click the configuration icon in the Actions column and select Manage keys.

    Manage keys
  2. Click the Add key list and select Create new key.

    Create new key
  3. In the Create private key dialog, select JSON and click Create.

    The private key download is started automatically.

    JSON

Step 3. Connect to BigQuery from DataSpell

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. On the Data Sources tab in the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select BigQuery.

  3. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataSpell downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

    You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  4. From the Authentication list, select Application Default Credentials.

  5. Click the Advanced tab.

  6. In the VM environment field, click the Edit environment variables icon.

  7. In the Environment Variables dialog, click the Add button (the Add button) and add the following variable: GOOGLE_APPLICATION_CREDENTIALS.

    GOOGLE_APPLICATION_CREDENTIALS
  8. To ensure that the connection to the data source is successful, click the Test Connection link.

Connecting with Application Default Credentials

Google service account

You need to create a service account that is going to be used by your application. For more information about service accounts, see Service accounts at cloud.google.com.

Step 1. Create a service account

  1. In the Navigation menu of the Google Cloud Platform, select IAM & Admin | Service accounts.

  2. On the Service accounts page, click Create service account.

    Create service account
  3. On the Service account details step in the wizard, type a project name and description.

    Service account details
  4. On the Grant this service account access to the project step in the wizard, select roles for this service account.

    For example, for read-only access, select BigQuery Data Viewer, BigQuery Job User, and BigQuery User from the BigQuery menu. Alternatively, select BigQuery | BigQuery Admin for access to all resources within the project. For more information about roles and permissions, see Predefined roles and permissions at cloud.google.com.

    Grant this service account access to the project

Step 2. Generating service account keys

  1. On the Service accounts page (IAM & Admin | Service accounts), click the configuration icon in the Actions column and select Manage keys.

    Manage keys
  2. Click the Add key list and select Create new key.

    Create new key
  3. In the Create private key dialog, select JSON and click Create.

    The private key download is started automatically.

    JSON

Step 3. Connecting to BigQuery from DataSpell

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. On the Data Sources tab in the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select BigQuery.

  3. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataSpell downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

    You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  4. From the Authentication list, select Google Service Account.

  5. In the Service account email field, type the service account email identifier.

    You can get the identifier on the Service accounts page (IAM & Admin | Service accounts) on the Google Cloud Platform (see Generating service account keys). In our case, it is intellij-ide@bigqueryproject-322409.iam.gserviceaccount.com.

  6. In the Project ID field, type the project identifier.

    Usually, it is a part of the service account email identifier that goes after the at sign (@). In our case, it is bigqueryproject-322409.

  7. In the Key file field, type the full path to the JSON key file that you downloaded on Step 3. Generating service account keys.

  8. To ensure that the connection to the data source is successful, click the Test Connection link.

Connecting with a Google Service Account

Access and refresh tokens (OAuth 2.0)

When you use OAuth 2.0 for authentication, your users are authenticated after they agree to terms that are presented to them on a user consent screen. The OAuth consent screen is a dialog that displays a notification about who requests access to the user's data and a type of this data.

Before you generate a client ID and a client secret, you need to configure the OAuth consent screen.

  1. In the Navigation menu on the Google cloud platform, click APIs and services | OAuth consent screen.

    OAuth consent screen
  2. Follow the wizard and complete the configuration. For more information about user consent, see the User consent section at support.google.com.

Step 2. Getting a client ID and a client secret

  1. In the Navigation menu on the Google cloud platform, click APIs and services | Credentials.

  2. On the Credentials page, click Create credentials | OAuth client ID.

  3. From the Application type list, select Web application.

  4. In the Authorised redirect URIs section, click Add URI and paste the following URI:

    https://developers.google.com/oauthplayground
  5. Click Create.

    In the OAuth client created window, you will see your client ID and client secret. Alternatively, you can click the created OAuth client ID configuration and get your credentials here.

    OAuth client created

Having a client ID and a client secret, you can generate authorization code and tokens.

Step 3. Generating access and refresh tokens at OAuth 2.0 Playground

  1. Open the OAuth 2.0 Playground in a separate browser tab.

  2. Click the OAuth 2.0 Configuration icon, select the Use your own OAuth credentials checkbox.

  3. Paste your client ID and client secret in OAuth Client ID and OAuth Client secret fields.

    OAuth 2.0 Configuration
  4. Click Close in the OAuth 2.0 Configuration window.

  5. In the wizard on Step 1. Select & authorize APIs, select BigQuery API v2 | https://www.googleapis.com/auth/bigquery.

  6. Click Authorize APIs.

  7. Follow the wizard in a browser and give permissions to the application.

    give permissions to the application
  8. On Step 2 Exchange authorization code for tokens, click Exchange authorization code for tokens.

  9. Copy received refresh and access tokens.

    refresh and access tokens

Step 4. Connect to BigQuery from DataSpell

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. On the Data Sources tab in the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select BigQuery.

  3. At the bottom of the data source settings area, click the Download missing driver files link. As you click this link, DataSpell downloads drivers that are required to interact with a database. The IDE does not include bundled drivers in order to have a smaller size of the installation package and to keep driver versions up-to-date for each IDE version.

    You can specify your drivers for the data source if you do not want to download the provided drivers. For more information about creating a database connection with your driver, see Add a user driver to an existing connection.

  4. From the Authentication list, select Access and Refresh Tokens.

  5. In the ProjectId field, type your project name.

    Usually, it is a part of the service account email identifier that goes after the at sign (@). In our case, it is bigqueryproject-322409.

  6. In the Access token field, paste your access token.

  7. In the Refresh token field, paste your refresh token.

  8. In the Client ID field, paste your client ID.

  9. In the Client secret field, paste your client secret.

  10. To ensure that the connection to the data source is successful, click the Test Connection link.

connecting with tokens
Last modified: 19 March 2022