Azure Cosmos DB NoSQL Set Up for DSR Automation

Just like any other database integration, with Transcend's Azure Cosmos DB integration, you can automate the execution of Data Subject Requests on an Azure Cosmos database instance by preparing SQL statements. You can also automate query execution during preflight enrichment as well using the Database Enricher.

However, unlike other database integrations, preparing SQL statements for NoSQL instances of Azure Cosmos DB require a stricter format.

This doc outlines the SQL format you must follow in order for your Azure Cosmos DB integration to execute successful queries.

Transcend's Azure Cosmos DB integration supports 3 database types — Postgres, MongoDB, and NoSQL. If you're connecting an Azure Cosmos DB Postgres instance, follow this guide on preparing SQL statements for database integrations. If you're connecting an Azure Cosmos DB MongoDB instance, follow this guide on preparing JSON payloads for MongoDB integrations.

Unlike Postgres and MongoDB, Transcend uses the required Azure Cosmos DB Node SDK to execute queries for NoSQL instances.

This SDK has specific functions for executing different operations. For example, the SDK has its own upsert() function to upsert items, delete() function to delete items, and .query().fetchAll() function to retrieve items from SELECT queries.

Because of this limitation, Transcend requires a stricter format for the SQL queries you prepare, so that Transcend can successfully transform your query to a format the SDK can digest.

There are 3 types of queries Transcend supports for NoSQL instances in Transcend's Azure Cosmos DB integration — SELECT, INSERT INTO, and DELETE FROM. Below are guidelines you should follow to ensure successful query execution:

Similar to how you'd write a query in the Azure Cosmos DB application for NoSQL instances, you must specify the container name, but without any mention of the database.

Example:

SELECT my_container.personId as personId, my_container.email as email
FROM my_container
WHERE email = {{identifier}}

The SQL statement for upserting data into a container must contain an INSERT INTO clause, a VALUES clause, and the container name. Column names and values must be defined within paranthesis, as per SQL standards. The format should look like this: INSERT INTO <container> (field1, field2) VALUES (value1, value2).

Example:

INSERT INTO my_container (personId, consent)
VALUES ({{identifier}}, 'OPT_OUT')

The SQL statement for deleting data from a container must contain a DELETE FROM clause, WHERE clause, and the container name. The format should look like this: DELETE FROM <container> WHERE <condition>.

Example:

DELETE FROM my_container
WHERE personId = {{identifier}}