Spotfire Server: JDBC connection to Google BigQuery fails with "Error fetching metadata" "java.lang.NullPointerException: Cannot invoke java.lang.Boolean.booleanValue() because the return value of getUseLegacySql() is null"

Description:
When connecting to Google BigQuery from Spotfire using Information Link, users may encounter an error:

Error fetching metadata. Message: Cannot invoke 'java.lang.Boolean.booleanValue()' because the return value of 'com.google.api.services.bigquery.model.TableList$Tables$View.getUseLegacySql()' is null.

This issue occurs because Spotfire calls the JDBC driver's getTables() function when expanding catalog metadata in BigQuery. When this function encounters tables/views with NULL values for 'use_legacy_sql', it throws a null pointer exception. To bypass the issue, getSchemas() function should be used instead of getTables().

Solution:
1. Add the following XML setting to the BigQuery's Data Source Template in Spotfire Server Configuration Tool:

<use-get-schemas>true</use-get-schemas>
 
Additional information on this setting is available here:
https://docs.tibco.com/pub/spotfire_server/latest/doc/html/TIB_sfire_server_tsas_admin_help/server/topics/xml_settings_for_data_source_templates.html
2. Save the updated configuration and restart all Spotfire Server services.

3. Open a new session of Spotfire Analyst client.

4. Re-save existing data sources from Information Designer to ensure they pick up the latest changes from the data source template.

This configuration forces the JDBC driver to use the getSchemas() function instead of getTables() when expanding the catalog, avoiding the null pointer exception. Note that the getSchemas() function is only available for JDBC versions 4.1 and later, but this is compatible with the Google BigQuery JDBC driver.

Alternative Solutions:
If the above solution doesn't resolve the issue, two alternative approaches can be tried:

1. Update or recreate problematic BigQuery views with the 'use_legacy_sql' option explicitly set:
  CREATE OR REPLACE VIEW `project.dataset.view_name`
  OPTIONS (use_legacy_sql = false)
  AS
  SELECT * FROM `project.dataset.table_name`;

2. Modify the BigQuery data source URL connection string in Information Designer to use standard SQL by adding "QueryDialect=SQL", for example:
  jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<id>;OAuthType=0;...QueryDialect=SQL