Google BigQuery as Analytical Storage

Google BigQuery is a fully managed cloud data warehouse for analytics. While Google BigQuery works in conjunction with Google Storage for interactive analysis of massively large data sets it can scan terabytes in seconds and petabytes in minutes.

Google BigQuery as Analytical Storage

SQL

CALL SYSADMIN.createConnection('dwh','bigquery','projectId=XXXX,user-name=XXXX@developer.gserviceaccount.com,password=$${jboss.server.config.dir}/../deployments/bigquery.p12') ;;
CALL  SYSADMIN.createDatasource('dwh','bigquery','importer.schemaPattern=dataset_name,importer.defaultSchema=dataset_name,importer.useFullSchemaName=false,importer.useCatalogName=false','supportsNativeQueries=true,uploadMode=CLOUDSTORAGE,bucketName=dv_upload_bucket,useDdl=false') ;;
CALL SYSADMIN.setCurrentDWH('dwh', 'dataset_name') ;;

Please note that Google BigQuery does not support recommended indexes.

Template name: bigquery

Appropriate translator name: bigquery

Properties:

projectId (required)
transformQuery (default: TRUE); (obsolete)
credentialFactory (default: com.datavirtuality.dv.core.oauth.credential.BigQueryOAuthCredentialFactory)
allowLargeResults (obsolete)
largeResultsDefaultDataset
tableMaxResults
fetchSize
refreshToken
accessToken
expirationTimeMilliseconds
region
authCode
redirectUri
user-name (required)
password (a path to a key file or a Base64 string presentation of the key file; see note below)
ClientId
ClientSecret
driver (default: bigquery)
driver-class (default: com.datavirtuality.jdbc.bq.BQDriver)
storageProjectId (default: empty)
storageUser (default: empty)
storagePassword (default: empty)
new-connection-sql
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
readTimeout (default: 20000, 0 for an infinite, a negative for the default)
connectTimeout (default: 20000, 0 for an infinite, a negative for the default)

The password property is stored in the SYSADMIN.Connections table as a Base64 string, even when set as a path to a key file. In the import script, it will also be presented as a Base64 string, therefore the key file does not need to be backed up for migration.

The password property supports Base64 string values since v25.3