Working with a BigQuery Warehouse Connection

Kyvos supports Column Level Security defined in BigQuery. When you create a semantic model with BigQuery as the data source, any column-level security applied in BigQuery is reflected in Kyvos when data is accessed from the semantic model.

• The user's email ID in Kyvos must match their email ID in Google Cloud.

• The user must have the Fine-Grained Reader role to access secured columns in both Kyvos and BigQuery.

Required service account permissions

To read CLS columns when accessing data from the semantic model, grant the following permissions to the connection's service account so that Kyvos can successfully call GCP APIs:

• Multiple connections: You can create multiple BigQuery connections for raw data querying. These appear on the Dataset Designer page, where you can select the connection to use for a particular semantic model.

• Cross-cloud reads: You can connect to your BigQuery data warehouse to read data from both GCP and EMR-based AWS clusters.

• Date filters with type casting: If you filter a date column using >= or <= with an explicit type cast, add the property kyvos.rf.sqlparser.enabled and set it to true on the Hadoop connection.

• Function mapping: All Spark-supported functions must be mapped to their supported BigQuery equivalents. Update your SQL and datasets accordingly; otherwise, incompatible functions will fail.

Based on the selected authentication type, grant the roles and permissions described in section 2.5 to the appropriate service account on the BigQuery project.

2.5.2 Required Roles at Project and Dataset Levels

Add the following roles to the applicable service account at project and dataset levels:

Source Data Project

To perform Kyvos Dataset Preview operation, add the following pre-defined roles.

Materialization Project

Create a Kyvos custom role and add the following permissions to the role.

Grant the dataset level access to the applicable service account by assigning it the custom role containing the required permissions.

This enhancement enables exporting data from BigQuery to a Google Cloud Storage (GCS) bucket located in a different GCP project (customer project). With this configuration, BigQuery writes the exported data directly to the customer-owned bucket, while Kyvos manages the temporary and export files by applying the required IAM permissions.

1. Create or identify a GCS bucket in the target customer project:

   gs://<cross-project-bucket>/<path>/temp/

2. Grant the following permissions: storage.objects.get, storage.objects.create, storage.objects.list, and storage.objects.delete.

   1. If using application's default credentials:

      1. Grant all the above permissions to Kyvos' service account.

   2. If using Google Service Account:

      1. Grant storage.objects.get, storage.objects.create, and storage.objects.list to the BigQuery service account.

      2. Grant storage.objects.get, storage.objects.list, and storage.objects.delete to the Kyvos' service account.

3. In the BigQuery connection, add the kyvos.connection.data.export.dir property and set the value to gs://<cross-project-bucket>/<path>

   This directory should point to the GCS bucket location where the user needs to export BigQuery data.

To set up or view a BigQuery warehouse connection with no-spark, perform the following steps. 

1. From the Toolbox, click Connections.

2. From the Actions menu ( ⋮ ) click Add Connection.

3. Enter a name or select it from the Connection list.

4. After you have entered the parameters described in the table below, click the Test button from the top left to validate the connection settings.

5. If the connection is valid, click the Save button.

6. To refresh connections, click the Actions menu ( ⋮ ) at the top of the Connections column and select Refresh.