Model Data in Google BigQuery

Connect to a Google BigQuery database so that you can model and visualize data from Google BigQuery. You can model Google BigQuery data in Semantic Modeler or Model Administration Tool. These tasks describes the process using Model Administration Tool.

Topics

Create an Oracle Analytics Connection to Google BigQuery

You can create a system connection to a Google BigQuery database and use the connection to model the Google BigQuery project.

Before you start, download the Service Account Private Key (in JSON format) for your Google BigQuery service.
  1. On the Oracle Analytics home page, click Create, and then click Connection.
  2. Click BigQuery.
  3. Enter the connection details.
    • In Connection Name, specify a user-friendly name to identify the connection details in Oracle Analytics.
    • In Project, specify the BigQuery project name in lowercase of the project that you want to analyze.
    • In Service Account Private Key, click Select and upload the Service Account Private Key (JSON format) for your BigQuery service. The Service Account Email is populated from the uploaded key details.
    • In System connection, select this option.
      Description of googlebq1.png follows
      Description of the illustration googlebq1.png

  4. Save the details.

Download and Set Up BigQuery ODBC Driver

Install the ODBC driver required for connection to Google BigQuery, and configure it in Model Administration Tool to enable you to model the project.

  1. Download the Simba BigQuery ODBC driver from Google.
    For example, download it from the Google reference site.
  2. Install the downloaded driver on the machine where Oracle Analytics Client Tools is installed.
  3. Configure the ODBC driver using the DSN Setup dialog.
    Description of googlebq2.png follows
    Description of the illustration googlebq2.png

  4. Click Test to test the connection.
    Description of googlebq3.png follows
    Description of the illustration googlebq3.png

  5. Save the details.

Build a Data Model from Google BigQuery Data Source

You build a data model for your Google BigQuery database so that you can deploy it to visualize data in a BigQuery project.

To build a data model, you need permissions in the BigQuery key. If the BigQuery key grants access to the dataset level, simply perform Import Metadata using the BigQuery ODBC driver by following the steps below. If the BigQuery key grants access to only specific tables or views, follow the steps below to create a physical schema.
  1. In Model Administration tool, create a database in the repository and set the Database type to ODBC Basic.
    Description of googlebq4.png follows
    Description of the illustration googlebq4.png

  2. In the Connection Pools dialog, create a connection pool in the database.
    • In Call interface, select "Default (ODBC 2.0)".

    • In Data source name field, select the BigQuery ODBC driver that you created earlier.

  3. Create a physical schema in the database using the same name as the BigQuery dataset.
    BigQuery SQL requires that the dataset name prepend the table name, dataset.table. The dataset name is equivalent to a physical schema object in the repository file.
  4. Right-click the connection pool and select Import Metadata.
  5. On the Select Data Source dialog, select either ODBC 2.0 or ODBC 3.5 for the connection type, and select the BigQuery ODBC driver.
    Description of googlebq7.png follows
    Description of the illustration googlebq7.png

  6. On the Select Metadata Types dialog, select Views and any other types you want to use for which your BigQuery key has permissions.
    Description of googlebq8.png follows
    Description of the illustration googlebq8.png

  7. On the Select Metadata Types dialog, select the individual tables and then click Import Selected. This imports the BigQuery database and the underlying structures.
    Description of googlebq10.png follows
    Description of the illustration googlebq10.png

    If you click Import All, you import only the database. If this happens, select Import All a second time to import the tables.
  8. Click Finish.
  9. Drag imported tables into physical schema.
  10. Edit the physical database and change the database type to BigQuery.

    When changing the physical database, you see a message that states that the database type doesn't match the call interface set in the connection pool. Click Yes.

  11. In the Connection Pool dialog, configure these settings:
    • In Call interface, change the call interface to JDBC (Direct Driver).
    • Select Require fully qualified table names.
    • Select Use Data Connection.
    • In Oracle Analytics, inspect the BigQuery connection and copy the Object ID. BigQuery is case-sensitive. To ensure that the data connection syntax is correct, use the Copy button.
      Description of googlebq14.png follows
      Description of the illustration googlebq14.png

    • In the Connection Pool dialog, paste the copied Object ID into the Object ID field.
    • Set Maximum connections to 100.
  12. Save the details.
Model the metadata in the repository and upload the repository file (RPD) to Oracle Analytics.

Troubleshoot Repository Connection Issues for Google BigQuery

Here're some issues that you might encounter when connecting to Google BigQuery and work-arounds for them.

If 'Require fully qualified table names' isn't selected and a physical schema isn't part of the generated SQL, then queries fail with a message similar to 'Failed to read data from Java Datasource server'.

If the query is run against BigQuery using nqcmd or another SQL entry tool, the actual error message is displayed:

WITH SAWITH0 AS (select distinct T4.PROP_CD as c1 from FINOPS_RM_OCC_ACT T4) select 0 as c1, D1.c1 as c2 from SAWITH0 D1 order by c2
[Simba][BigQuery] (70) Invalid query: Table "FINOPS_RM_OCC_ACT" must be qualified with a dataset (e.g. dataset.table).
Statement preparation failed

The way to qualify the query with a dataset is to use a physical schema in the repository file.

If the Oracle Analytics connection uses an uppercase project name, the connection is created successfully.

You might see two different problems.

1. Queries fail with a 404 Not Found message about a masked URL:

[2022-03-17T01:13:44.105+00:00] [OBIS] [TRACE:2] [USER-34] [] [ecid: d6382db0-1e63-427e-893b-18bc00c0424e-0000de96,0:2:1:5] [sik: bootstrap] [tid: 856a6700] [messageId: USER-34] [requestid: 6358001e] [sessionid: 63580000] [username: Testuser] -------------------- Query Status: [nQSError: 46164] HTTP Server returned 404 (Not Found) for URL [masked_url]. [[
[nQSError: 46281] Failed to download metadata for dataset ‘system’.‘BigQuery Test’.
[nQSError: 43119] Query Failed:

2. Within Oracle Analytics, you see datasets but the underlying tables aren't available.
Description of googlebq15.png follows
Description of the illustration googlebq15.png

In both cases, you can modify the connection so that the project name is lowercase.

When troubleshooting BigQuery connections in Oracle Analytics Cloud, use a third-party JDBC client to try to connect to BigQuery using the same Service Account Key.

If the connection still fails, there's a problem with the Service Account Key.

If the connection is successful, there's a problem with Oracle Analytics and you should contact Oracle Support.

This test is helpful in cases where the Service Account Key is not verified through ODBC.