BigQuery

Automate the fulfillment of data subject access, deletion, opt-out, and identifier retrieval requests in Google BigQuery.

Configuration

Create a Connection object with a BigQuery ConnectorType to instantiate a connection. Once the object has been created and the credentials have been stored, place the object in the RMAgentConfig connections array.

Example Configuration

{
    "name": "Metrics",
    "uuid": "e3c7dca2-eb9d-4482-be76-77c77db8d8a5",
    "capabilities": ["privacy/access","privacy/delete", "privacy/optout", "privacy/identifiers"],
    "mode": "live",
    "connector_type": "BigQuery",
    "queries": {
    "identifiers": {
            "phone_number": [
                "CALL metrics.get_phone_number(%(email)s)"
            ]
        },
        "access": ["CALL metrics.dsr_operation('access', %(email)s)"],
        "delete": ["CALL metrics.dsr_operation('delete', %(email)s)"],
        "optout": ["CALL metrics.dsr_operation('optout', %(email)s)"]
    },
    "credentials_location":"bigquery-metrics-project"
}

Credential Creation

The BigQuery connection uses role-based access controls to grant the Agent service access to specific resources. To determine the appropriate BigQuery IAM roles and permissions, reference Google's documentation.

Create a new secret denoting the project ID of the data warehouse in JSON format in your preferred credentials manager with the following key/value pairs:

{
    "project_id": "<project ID>"
}

Labels, replication, and other settings, please set as necessary.

Copy the name of the secret and insert it in as the value of the credentials_location parameter of the connection.

Query Syntax and Parameter Binding

The Queries object allows query strings to be dynamically formatted with identifiers. Identifiers are passed individually to queries and are bound to the variables in the operation. Variables are specified using the pyformat parameter style, e.g. ...WHERE email=%(email)s.

Best Practices

For ease of maintainability and readability, it is recommended that the various queries be stored procedures. This allows for the underlying queries to be modified in BigQuery without needing to modify the Agent configuration, and for the query lists to be easily readable, especially in the case of complex joins.