Applies to: 
Kyvos Enterprise Kyvos Cloud (SaaS on AWS) 
Kyvos AWS Marketplace
...
Kyvos supports both ROLAP and HOLAP on BigQuery connections. You can use this connection for querying data from your BigQuery data warehouse.
BigQuery warehouse connection with Spark
Points to know
From Kyvos 2024.2 onwards, Kyvos now supports Column Level Security defined in Big Query. When creating a Semantic Model with Big Query as the data source, any column level security applied in Big Query will be reflected in Kyvos when accessing data from the Semantic Model.
The email ID of the user on Kyvos should be the same as the email ID in Google Cloud.
The user should have Fine Grained Reader access for accessing secured columns in Kyvos and BigQuery.
To read CLS columns in Kyvos when accessing data from the semantic model, the following permissions must be set on Connection’s service account to successfully call GCP APIs:
o bigquery.tables.get
o cloudasset.assets.analyzeIamPolicy
o cloudasset.assets.searchAllIamPolicies
o cloudasset.assets.searchAllResources
o datacatalog.taxonomies.get
o serviceusage.services.use
o iam.roles.get
If you implement a filter on a date column with >= or <= and explicit type cast, you must add the kyvos.rf.sqlparser.enabled propertyand set its value to true at the Hadoop connection.
You can create multiple BigQuery connections for raw data querying. The connections are available on the Dataset designer page, where you can select the connection to be used for a particular semantic model.
All Spark-supported functions must be mapped according to supported BigQuery functions. You must update the SQL and datasets with supported BigQuery functions. Otherwise, the incompatible functions would fail.
You can connect to your BigQuery data warehouse for reading data from both GCP and EMR-based AWS clusters.
You can create multiple read connections using different BigQuery data warehouse accounts from different projects. You can then use datasets from all these BigQuery warehouses in a single relationship and hence a single semantic model. If you intend to create multiple datasets from different warehouses in different projects, ensure all the Materialization Datasets specified when configuring the Connections belong to the same region.
Setting up or view a BigQuery warehouse connection
To set up or view a BigQuery warehouse connection, perform the following steps.
From the Toolbox, click Connections.
From the Actions menu ( ⋮ ) click Add Connection.
Enter a name or select it from the Connection list.
After you have entered the parameters described in the table below, click the Test button from the top left to validate the connection settings.
If the connection is valid, click the Save button.
To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.
| Aura tab collection |
|---|
| params | JTdCJTIyZ2VuZXJhbCUyMiUzQSU3QiUyMnRhYlNwYWNpbmclMjIlM0EwJTJDJTIydGFiV2lkdGglMjIlM0ExMDAlMkMlMjJ0YWJIZWlnaHQlMjIlM0E1MCUyQyUyMmRpcmVjdGlvbiUyMiUzQSUyMmhvcml6b250YWwlMjIlN0QlMkMlMjJjb250ZW50JTIyJTNBJTdCJTIyYmFja2dyb3VuZENvbG9yJTIyJTNBJTdCJTIyY29sb3IlMjIlM0ElMjIlMjNmZmYlMjIlN0QlMkMlMjJib3JkZXIlMjIlM0ElN0IlMjJzdHlsZSUyMiUzQSUyMnNvbGlkJTIyJTJDJTIyd2lkdGglMjIlM0ExJTJDJTIydG9wJTIyJTNBdHJ1ZSUyQyUyMmJvdHRvbSUyMiUzQXRydWUlMkMlMjJsZWZ0JTIyJTNBdHJ1ZSUyQyUyMnJpZ2h0JTIyJTNBdHJ1ZSUyQyUyMmNvbG9yJTIyJTNBJTdCJTIybGlnaHQlMjIlM0ElMjIlMjNjY2NlY2YlMjIlN0QlN0QlMkMlMjJwYWRkaW5nJTIyJTNBJTdCJTIydG9wJTIyJTNBMTAlMkMlMjJyaWdodCUyMiUzQTEwJTJDJTIyYm90dG9tJTIyJTNBMTAlMkMlMjJsZWZ0JTIyJTNBMTAlN0QlN0QlMkMlMjJhY3RpdmUlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzZjU4MjI3JTIyJTdEJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzMDAwMDAwJTIyJTdEJTJDJTIydGV4dEFsaWduJTIyJTNBJTIybGVmdCUyMiUyQyUyMmZvbnRXZWlnaHQlMjIlM0ElMjJib2xkJTIyJTdEJTdEJTJDJTIyaG92ZXIlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSUyMiUyM2RmZTFlNiUyMiU3RCUyQyUyMnRleHQlMjIlM0ElN0IlMjJmb250U2l6ZSUyMiUzQTE4JTJDJTIyY29sb3IlMjIlM0ElMjIlMjM1ZTZjODQlMjIlMkMlMjJ0ZXh0QWxpZ24lMjIlM0ElMjJsZWZ0JTIyJTJDJTIyZm9udFdlaWdodCUyMiUzQSUyMmxpZ2h0ZXIlMjIlN0QlN0QlMkMlMjJpbmFjdGl2ZSUyMiUzQSU3QiUyMmJhY2tncm91bmRDb2xvciUyMiUzQSU3QiUyMmNvbG9yJTIyJTNBJTIyJTIzZjRmNWY3JTIyJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSUyMiUyMzVlNmM4NCUyMiUyQyUyMnRleHRBbGlnbiUyMiUzQSUyMmxlZnQlMjIlMkMlMjJmb250V2VpZ2h0JTIyJTNBJTIybGlnaHRlciUyMiU3RCU3RCU3RA== |
|---|
|
| Aura tab |
|---|
| summary | Application Default Credentials Authentication |
|---|
| params | JTdCJTIydGl0bGUlMjIlM0ElMjJBcHBsaWNhdGlvbiUyMERlZmF1bHQlMjBDcmVkZW50aWFscyUyMEF1dGhlbnRpY2F0aW9uJTIwJTIyJTdE |
|---|
|
From the Toolbox, click Setup, then Connections. From the Actions menu ( ⋮ ) click Add Connection. Enter a name or select it from the Connection list. Select Warehouse from the Category List.
There may be more than one warehouse connection. For Providers, select BigQuery. Specify the Server to access Google APIs. For example, https://www.googleapis.com. Specify the Port to use. Enter your Project ID for the Google Cloud Platform. To generate Temporary Views in Separate Dataset when performing the validation/preview operation from Kyvos on Google BigQuery, provide the Materialization Project and Materialization Dataset names. From the Authentication Type, select Application Default Credentials option. By default, Use as Source checkbox is selected as this connection can only be used to read data (creating datasets) on which the semantic model will be created. Select Is Default SQL Engine checkbox to use the default connection. Enter the JDBC URL for Big Query connection. Click the Test button from the top left to validate the connection settings. If the connection is valid, click the Save button.
| Aura tab |
|---|
| summary | Google Service Account Authentication |
|---|
| params | JTdCJTIydGl0bGUlMjIlM0ElMjJHb29nbGUlMjBTZXJ2aWNlJTIwQWNjb3VudCUyMEF1dGhlbnRpY2F0aW9uJTIwJTIyJTdE |
|---|
|
| Panel |
|---|
| panelIconId | atlassian-note |
|---|
| panelIcon | :note: |
|---|
| bgColor | #DEEBFF |
|---|
| Note Before you proceed to create a connection, ensure that the Private key JSON file must be placed at olapengine/bin of the BI Server node.
To create Service account private key, refer to Google documentation. |
From the Toolbox, click Setup, then Connections. From the Actions menu ( ⋮ ) click Add Connection. Enter a name or select it from the Connection list. Select Warehouse from the Category List.
There may be more than one warehouse connection. For Providers, select BigQuery. Specify the Server to access Google APIs. For example, https://www.googleapis.com. Specify the Port to use. Enter your Google Project ID for the Google Cloud Platform. To generate Temporary Views in Separate Dataset when performing the validation/preview operation from Kyvos on Google BigQuery, provide the Materialization Project and Materialization Dataset names. From the Authentication Type, select Application Default Credentials or Google Service Account option and provide: Service Account Email: Provide the email of the service account to be used for authentication. Service Account Private Key Path: Provide the name of the private key JSON file to be used for authentication. Ensure that the Private key JSON file must be placed at olapengine/bin of the BI Server node.
By default, Use as Source checkbox is selected as this connection can only be used to read data (creating datasets) on which the semantic models will be created. Select Is Default SQL Engine checkbox to use the default connection. Enter the JDBC URL for Big Query connection. Click the Test button from the top left to validate the connection settings. If the connection is valid, click the Save button. Go to the Datalake Connection and search for spark.yarn.dist.files in the connection Properties. Provide a comma-separated list of the name of service account JSON files.
NOTE: DO NOT delete any existing paths available in this field. Restart the BI Server.
 Image Added
|
BigQuery warehouse connection with No-Spark
If you implement a filter on a date column with >= or <= and explicit type cast, you must add the kyvos.rf.sqlparser.enabled propertyand set its value to true at the Hadoop connection.
All Spark-supported functions must be mapped according to supported BigQuery functions. You must update the SQL and datasets with supported BigQuery functions. Otherwise, the incompatible functions would fail.
Setting up or view a BigQuery warehouse connection with no-Spark
To set up or view a BigQuery warehouse connection with no-spark, perform the following steps.
From the Toolbox, click Connections.
From the Actions menu ( ⋮ ) click Add Connection.
Enter a name or select it from the Connection list.
After you have entered the parameters described in the table below, click the Test button from the top left to validate the connection settings.
If the connection is valid, click the Save button.
To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.
| Aura tab collection |
|---|
| params | JTdCJTIyZ2VuZXJhbCUyMiUzQSU3QiUyMnRhYlNwYWNpbmclMjIlM0EwJTJDJTIydGFiV2lkdGglMjIlM0ExMDAlMkMlMjJ0YWJIZWlnaHQlMjIlM0E1MCUyQyUyMmRpcmVjdGlvbiUyMiUzQSUyMmhvcml6b250YWwlMjIlN0QlMkMlMjJjb250ZW50JTIyJTNBJTdCJTIyYmFja2dyb3VuZENvbG9yJTIyJTNBJTdCJTIyY29sb3IlMjIlM0ElMjIlMjNmZmYlMjIlN0QlMkMlMjJib3JkZXIlMjIlM0ElN0IlMjJzdHlsZSUyMiUzQSUyMnNvbGlkJTIyJTJDJTIyd2lkdGglMjIlM0ExJTJDJTIydG9wJTIyJTNBdHJ1ZSUyQyUyMmJvdHRvbSUyMiUzQXRydWUlMkMlMjJsZWZ0JTIyJTNBdHJ1ZSUyQyUyMnJpZ2h0JTIyJTNBdHJ1ZSUyQyUyMmNvbG9yJTIyJTNBJTdCJTIybGlnaHQlMjIlM0ElMjIlMjNjY2NlY2YlMjIlN0QlN0QlMkMlMjJwYWRkaW5nJTIyJTNBJTdCJTIydG9wJTIyJTNBMTAlMkMlMjJyaWdodCUyMiUzQTEwJTJDJTIyYm90dG9tJTIyJTNBMTAlMkMlMjJsZWZ0JTIyJTNBMTAlN0QlN0QlMkMlMjJhY3RpdmUlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzZjU4MjI3JTIyJTdEJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSU3QiUyMmxpZ2h0JTIyJTNBJTIyJTIzMDAwMDAwJTIyJTdEJTJDJTIydGV4dEFsaWduJTIyJTNBJTIybGVmdCUyMiUyQyUyMmZvbnRXZWlnaHQlMjIlM0ElMjJib2xkJTIyJTdEJTdEJTJDJTIyaG92ZXIlMjIlM0ElN0IlMjJiYWNrZ3JvdW5kQ29sb3IlMjIlM0ElN0IlMjJjb2xvciUyMiUzQSUyMiUyM2RmZTFlNiUyMiU3RCUyQyUyMnRleHQlMjIlM0ElN0IlMjJmb250U2l6ZSUyMiUzQTE4JTJDJTIyY29sb3IlMjIlM0ElMjIlMjM1ZTZjODQlMjIlMkMlMjJ0ZXh0QWxpZ24lMjIlM0ElMjJsZWZ0JTIyJTJDJTIyZm9udFdlaWdodCUyMiUzQSUyMmxpZ2h0ZXIlMjIlN0QlN0QlMkMlMjJpbmFjdGl2ZSUyMiUzQSU3QiUyMmJhY2tncm91bmRDb2xvciUyMiUzQSU3QiUyMmNvbG9yJTIyJTNBJTIyJTIzZjRmNWY3JTIyJTdEJTJDJTIydGV4dCUyMiUzQSU3QiUyMmZvbnRTaXplJTIyJTNBMTYlMkMlMjJjb2xvciUyMiUzQSUyMiUyMzVlNmM4NCUyMiUyQyUyMnRleHRBbGlnbiUyMiUzQSUyMmxlZnQlMjIlMkMlMjJmb250V2VpZ2h0JTIyJTNBJTIybGlnaHRlciUyMiU3RCU3RCU3RA== |
|---|
|
| Aura tab |
|---|
| summary | Application Default Credentials Authentication |
|---|
| params | JTdCJTIydGl0bGUlMjIlM0ElMjJBcHBsaWNhdGlvbiUyMERlZmF1bHQlMjBDcmVkZW50aWFscyUyMEF1dGhlbnRpY2F0aW9uJTIwJTIyJTdE |
|---|
|
From the Toolbox, click Setup, then Connections. From the Actions menu ( ⋮ ) click Add Connection. Enter a name or select it from the Connection list. Select Warehouse from the Category List.
There may be more than one warehouse connection. For Providers, select BigQuery. Specify the Server to access Google APIs. For example, https://www.googleapis.com. Specify the Port to use. Enter your Project ID for the Google Cloud Platform. To generate Temporary Views in Separate Dataset when performing the validation/preview operation from Kyvos on Google BigQuery, provide the Materialization Project and Materialization Dataset names. From the Authentication Type, select Application Default Credentials option. By default, Use as Source checkbox is selected as this connection can only be used to read data (creating datasets) on which the semantic model will be created. Select Is Default SQL Engine checkbox to use the default connection. Enter the JDBC URL for Big Query connection. Click the Test button from the top left to validate the connection settings. If the connection is valid, click the Save button. Image Added
| Aura tab |
|---|
| summary | Google Service Account Authentication |
|---|
| params | JTdCJTIydGl0bGUlMjIlM0ElMjJHb29nbGUlMjBTZXJ2aWNlJTIwQWNjb3VudCUyMEF1dGhlbnRpY2F0aW9uJTIwJTIyJTdE |
|---|
|
| Panel |
|---|
| panelIconId | atlassian-note |
|---|
| panelIcon | :note: |
|---|
| bgColor | #DEEBFF |
|---|
| Note Before you proceed to create a connection, ensure that the Private key JSON file must be placed at olapengine/bin of the BI Server node.
To create Service account private key, refer to Google documentation. Grant the Storage Object Creator role (to the service account) to the project where the storage bucket resides.
|
From the Toolbox, click Setup, then Connections. From the Actions menu ( ⋮ ) click Add Connection. Enter a name or select it from the Connection list. Select Warehouse from the Category List.
There may be more than one warehouse connection. For Providers, select BigQuery. Specify the Server to access Google APIs. For example, https://www.googleapis.com. Specify the Port to use. Enter your Google Project ID for the Google Cloud Platform. To generate Temporary Views in Separate Dataset when performing the validation/preview operation from Kyvos on Google BigQuery, provide the Materialization Project and Materialization Dataset names. From the Authentication Type, select Application Default Credentials or Google Service Account option and provide: Service Account Email: Provide the email of the service account to be used for authentication. Service Account Private Key Path: Provide the name of the private key JSON file to be used for authentication. Ensure that the Private key JSON file must be placed at olapengine/bin of the BI Server node.
By default, Use as Source checkbox is selected as this connection can only be used to read data (creating datasets) on which the semantic models will be created. Select Is Default SQL Engine checkbox to use the default connection. Enter the JDBC URL for Big Query connection. Click the Test button from the top left to validate the connection settings. If the connection is valid, click the Save button. Restart the BI Server.
|
