KB485362: "Unable to authenticate with Google BigQuery Storage API. Check your account permissions" error is returned when upgrading from MicroStrategy 2020 Update 4 to 2020 Update 6, or version 2021

Quality Engineer, Principal • MicroStrategy

This article addressees the query execution error that is returned when running a report or dossier against Google BigQuery after upgrading to MicroStrategy 2020 Update 4 or 2021. This article provides solutions to avoid this error.

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description

When running reports or dossiers against Google BigQuery after upgrading Strategy from version 2020 Update 4 to 2020 Update 6 or 2021, you may encounter the following error message.
Failed to execute query.
(Error in accessing information from the external data source (Database instance: (DataImport)[0], Error:FetchScroll failed. Error type: ODBC error. Odbc operation attempted: SQLFetchScroll. [HY000:131: on SQLHANDLE][Strategy][BigQuery](131) Unable to authenticate with Google BigQuery Storage API. Check your account permissions.))

Why is this happening?

You may have set the following parameters in your DSN or JDBC connection string in the pre-upgrade environment:

```
EnableHTAPI=0 
```
```
HTAPI_MinResultsSize=1000 
```
```
HTAPI_MinActivationRatio=0
```

The BigQuery Simba driver used prior to Strategy 2020 Update 6 is 2.2.2.1007, and the current Simba driver shipped with MicroStratgey 2020 Update 6 or version 2021 Update 4 is 2.3.5.1009. According to our Magnitude vendor, the

EnableHTAPI

setting on the driver has since been obsolete and no longer controls the ON/OFF behavior of High-Throughput API. The driver now listens to the the following four settings to determine whether the High-Throughput API is enabled:

```
HTAPI_MinResultsSize=1000
```
```
HTAPI_MinActivationRatio=3
```
```
RowsFetchedPerBlock=100000
```
```
UseSystemTrustStore=0
```

From the example settings above, any report exceeding 1000 rows trigger the High-Throughput API. However, if the BigQuery users are not given the following permissions to access High-Throughput API (Storage API), the error is triggered.

```
bigquery.readsessions.create
```
– Creates a new read session via the BigQuery Storage API
```
bigquery.readsessions.getData
```
– Reads data from a read session via the BigQuery Storage API
```
bigquery.readsessions.update
```
– Updates a read session via the BigQuery Storage API

Solution

Refer to the following two solutions to avoid this error.

Grant the BigQuery users the necessary permissions to use High-Throughput API (Storage API) if you wish to use this capability. See Access control with IAM for more information.
Disable the usage of High-Throughput API via the following settings. The example below only triggers High-throughput API if both conditions are met:

The number of table rows in your query results exceeds a very large number, for example, 10000000; AND
The number of pages in the results exceeds 3 (the default value when creating a connection).
```
HTAPI_MinResultsSize=10000000
```
```
HTAPI_MinActivationRatio=3
```

The following are settings and their definitions. See the Installation and Configuration Guide for the Magnitude Simba Google BigQuery ODBC Data Connector for more information.

EnableHTAPI - This setting specifies whether the connector uses BigQuery High-Throughput API for large data sets.
The default value is 0.
```
EnableHTAPI = 0
```
is ignored starting in Strategy 2020 Update 6 and 2021 Update 4 version due to a driver update (version 2.3.0 [GAUSS-1070] High-throughput API for anonymous tables. See the SIMBA/Google Release notes.
HTAPI_ MinResultsSize - When the number of table rows in your query results exceeds this number, and the number of pages in the results exceeds the Ratio of Results to Rows per Block (
```
HTAPI_MinActivationRatio
```
) value, the connector switches to using the BigQuery High-Throughput API.
The default value is 1000.
HTAPI_ MinActivationRatio - The minimum ratio of total rows to rows in the first page needed to activate reading through the High-Throughput API. If this value is set to 0, then the connector uses the High-Throughput API for all query results that meet the minimum results size specified by Minimum Query Results Size for HTAPI (
```
HTAPI_MinResultsSize
```
).
The default value is 3.
RowsFetchedPerBlock - The maximum number of rows that the connector can fetch for each data request.
The default value is 10000.

Comment

0 comments

Details

Knowledge Article

Published:

February 11, 2022

Last Updated:

March 21, 2024

Description

Why is this happening?

You may have set the following parameters in your DSN or JDBC connection string in the pre-upgrade environment:

EnableHTAPI=0

HTAPI_MinResultsSize=1000

HTAPI_MinActivationRatio=0

EnableHTAPI

HTAPI_MinResultsSize=1000

HTAPI_MinActivationRatio=3

RowsFetchedPerBlock=100000

UseSystemTrustStore=0

bigquery.readsessions.create

– Creates a new read session via the BigQuery Storage API

bigquery.readsessions.getData

– Reads data from a read session via the BigQuery Storage API

bigquery.readsessions.update

– Updates a read session via the BigQuery Storage API

Solution

Refer to the following two solutions to avoid this error.

Grant the BigQuery users the necessary permissions to use High-Throughput API (Storage API) if you wish to use this capability. See Access control with IAM for more information.

Disable the usage of High-Throughput API via the following settings. The example below only triggers High-throughput API if both conditions are met:

The number of table rows in your query results exceeds a very large number, for example, 10000000; AND
The number of pages in the results exceeds 3 (the default value when creating a connection).
```
HTAPI_MinResultsSize=10000000
```
```
HTAPI_MinActivationRatio=3
```

The following are settings and their definitions. See the Installation and Configuration Guide for the Magnitude Simba Google BigQuery ODBC Data Connector for more information.

EnableHTAPI - This setting specifies whether the connector uses BigQuery High-Throughput API for large data sets.
The default value is 0.

EnableHTAPI = 0

is ignored starting in Strategy 2020 Update 6 and 2021 Update 4 version due to a driver update (version 2.3.0 [GAUSS-1070] High-throughput API for anonymous tables. See the SIMBA/Google Release notes.

HTAPI_ MinResultsSize - When the number of table rows in your query results exceeds this number, and the number of pages in the results exceeds the Ratio of Results to Rows per Block (

HTAPI_MinActivationRatio

) value, the connector switches to using the BigQuery High-Throughput API.
The default value is 1000.

HTAPI_ MinActivationRatio - The minimum ratio of total rows to rows in the first page needed to activate reading through the High-Throughput API. If this value is set to 0, then the connector uses the High-Throughput API for all query results that meet the minimum results size specified by Minimum Query Results Size for HTAPI (

HTAPI_MinResultsSize

).
The default value is 3.

RowsFetchedPerBlock - The maximum number of rows that the connector can fetch for each data request.
The default value is 10000.