How to query Cosmos DB (analytical store) in Data Factory using SQL (Change Data Capture)

Christopher Wilkinson 0

Hi,

I am trying to write a query to interrogate Cosmos DB in Data Factory using the Change Data Capture functionality but I am unsure of the type of SQL I need to do this for a query that unrolls an array. It seems that the variant of SQL is different for queries against the Analytical Store. For example, when writing a query that unrolls an array against the container in the transactional db, the SQL query in the data flow is the same as the variant used to query containers directly in Cosmos db:

SELECT c.id, r.type, r.description FROM c JOIN r in c.requirements

However, this query raises an error when used against the Analytical Store in a Data Flow. I tried using the SQL that is used in a serverless SQL pool but that also produces an error, i.e.:

SELECT c.id, r.type, r.description FROM c CROSS APPLY OPENJSON(c.requirements) WITH ( type INT '$.type', description VARCHAR(500) '$.description' ) r

My question is what is the syntax for a SQL query (in the Source Options section of a data flow) that unrolls an array and that queries the Analytical Store as is necessary for CDC. As can be seen from the queries above, the array is the requirements field which has type and description as fields within it.

Any help would be greatly appreciated.

Regards,

Chris

Ganesh Gurram 3,595 Reputation points Microsoft Vendor

2025-01-17T14:45:34.9+00:00
Hi @Christopher Wilkinson

Greetings! & welcome to the MS Q&A platform and thanks for the question.

To unroll an array in Cosmos DB's Analytical Store for Change Data Capture (CDC) in Azure Data Factory, you should use the JOIN syntax.

Here's the query:

SELECT c.id, r.type, r.description FROM c JOIN r IN c.requirements

JOIN r IN c.requirements - This is the correct Cosmos DB-specific syntax for unrolling the requirements array. Each item in the array becomes a separate row, and you can access properties like type and description for each item.

**r.type **and r.description - These are the fields you want to extract from each element in the requirements array.

c.id - The document ID for the row.

Ensure that the dataset in Data Factory is configured to query the Cosmos DB Analytical Store, not the transactional container.

In the Source Options section of your Data Flow, select the SQL Query option and paste the query provided above.

This approach will effectively unroll the requirements array and extract the required data from the Analytical Store for your CDC processing.

For more details refer to this: Change Data Capture in Azure Cosmos DB analytical store

Copy and transform data in Azure Cosmos DB analytical store by using Azure Data Factory

Hope this helps. Do let us know if you have any further queries.
Ganesh Gurram 3,595 Reputation points Microsoft Vendor

2025-01-20T05:01:50.93+00:00

@Christopher Wilkinson - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
ChrisWilkinson-5311 5 Reputation points

2025-01-20T08:00:21.7466667+00:00

Hi,

I have not found a resolution yet. The syntax that you provided is the same syntax that I mentioned in my original post.

SELECT c.id, r.type, r.description``JOIN r in c.requirements

does not work in a data flow with CDC when querying the Analytical Store:

The following is the error message that is returned:

at Source 'CosmosBookingCDC': an error occurred during snapshot metadata read phase - Syntax error at or near 'c'(line 1, pos 52) == SQL == SELECT c.id, r.type, r.description FROM c JOIN r in c.requirements ----------------------------------------------------^^^

I understand that the Analytical Store is a columnar database which is a different format to the JSON structure of a Cosmos db NoSQL container. I assume that this is why the above syntax does not work.

I also tried the syntax suggested by Sina Salam. This does not work, either.

Regards,

Chris Wilkinson
Ganesh Gurram 3,595 Reputation points Microsoft Vendor

2025-01-20T08:13:05.12+00:00

@ChrisWilkinson-5311 - If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? The Azure support team will review your request and provide assistance as soon as possible Azure support.
Thank you.

1 answer

Sina Salam 16,766 Reputation points

2025-01-17T14:46:14.55+00:00
Hello Christopher Wilkinson,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand with your SQL query to interrogate Cosmos DB in Data Factory using Change Data Capture (CDC) functionality to unroll an array in the Analytical Store.

To unroll an array in the Analytical Store using CDC in Data Factory - https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-change-data-capture you should use the following SQL syntax:

SELECT c.id, r.type, r.description FROM c CROSS APPLY OPENROWSET( 'CosmosDB', 'SELECT * FROM c.requirements' ) AS r

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.
Please sign in to rate this answer.
ChrisWilkinson-5311 5 Reputation points

2025-01-20T10:06:05.4266667+00:00

Hi,

Thanks you for your reply but it does not work in a data flow when query the Analytical Store, which is necessary for Change Data Capture. I received the following error message:

at Source 'CosmosBookingCDC': an error occurred during snapshot metadata read phase - Syntax error at or near 'APPLY': missing 'JOIN'(line 7, pos 6) == SQL == SELECT c.id, r.type, r.description FROM c CROSS APPLY ------^^^ OPENROWSET( 'CosmosDB', 'SELECT * FROM c.requirements' ) AS r

Regards,

Chris

Sina Salam 16,766 Reputation points

2025-01-20T12:59:04.5766667+00:00

Dear

Thank you for your feedback.

To query the Analytical Store in Data Factory and unroll an array, you should use the supported SQL syntax for the Analytical Store. This involves using the OPENJSON function directly within the query without CROSS APPLY.

SELECT c.id, r.value.type, r.value.description FROM c CROSS APPLY OPENJSON(c.requirements) WITH ( type INT '$.type', description VARCHAR(500) '$.description' ) AS r

This query should work in the Analytical Store within Data Factory's data flow. If you encounter any further issues, please provide the exact error message for more precise troubleshooting.

Alternatively, to query arrays in the Analytical Store using ADF Data Flow, you need to use the SQL variant supported by the Analytical Store, which differs from standard T-SQL. Specifically. Unrolling Arrays in the Analytical Store: Use the ARRAY_FLATTEN function in the query. Like syntax below:

SELECT c.id, r.value.type AS type, r.value.description AS description FROM c CROSS JOIN ARRAY_FLATTEN(c.requirements) AS r

In the ADF Data Flow Source transformation, ensure the following:

Connect to the Analytical Store.

Enter the above query in the Source Options section.

Validate CDC Settings by making sure that the Change Data Capture (CDC) functionality is correctly configured for the Analytical Store. Refer to the official documentation for CDC to ensure prerequisites like enabling the Analytical Store and CDC are met. https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-change-data-capture

If you still encounter issues, verify the structure of the requirements field in your data. It must be an array with the expected type and description fields.

Your Data Flow configuration, especially the connection settings and schema mapping.

Success
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to query Cosmos DB (analytical store) in Data Factory using SQL (Change Data Capture)

1 answer

Your answer