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 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.