Connect and query to Azure database for PostgresSql from Synapse Analytics

Sathya Sai Cheetirala 0

Hi,

Is there a way to connect and query Azure Database for PostgresSql from Synapse Analytics.We need near real time data from postgres database to join the CosmosDB data in Synapse Analytics.

I would really appreciate for the input.

Thanks!

Sathya Sai Cheetirala 0 Reputation points

2024-06-13T12:40:16.7933333+00:00

currently we are using serverless sql pool for querying the Azure cosmosDB using synapse link,

we want to join the data from potgres and cosmos db for analytics
Harishga 5,990 Reputation points Microsoft Vendor

2024-06-14T08:18:26.6833333+00:00
Hi @Sathya Sai Cheetirala
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To connect and query Azure Database for PostgreSQL from Synapse Analytics and join it with CosmosDB data for analytics, you can follow these steps:

Create a Linked Service to Azure Database for PostgreSQL:

Navigate to the Manage tab in your Azure Data Factory or Synapse workspace.

Select Linked Services, then click New.

Search for PostgreSQL and select the Azure database for PostgreSQL connector.

Configure the service details, test the connection, and create the new linked service.

Use Copy Activity in Azure Data Factory and Synapse Analytics Pipelines:

Utilize the Copy Activity to copy data from and to Azure Database for PostgreSQL.

Use Data Flow to transform data in Azure Database for PostgreSQL.

Query Azure Cosmos DB Data Using Serverless SQL Pool:

Use the serverless SQL pool in Azure Synapse Analytics to query data from Azure Cosmos DB containers enabled with Azure Synapse Link.

The full SELECT surface area is supported through the OPENROWSET function, which includes the majority of SQL functions and operators.

Join Data from PostgreSQL and CosmosDB:

Create SQL views to join data in the analytical stores across multiple Azure Cosmos DB containers.

Organize your data in a semantic layer that will accelerate your data exploration and reporting workloads.

Reference:
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-database-for-postgresql?tabs=data-factory

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?tabs=openrowset-key

https://devblogs.microsoft.com/cosmosdb/synapse-sql-serverless-preview/

I hope this information helps you. Let me know if you have any further questions or concerns.
Harishga 5,990 Reputation points Microsoft Vendor

2024-06-17T04:44:29.6133333+00:00

@Sathya Sai Cheetirala
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.

1 answer

Andrey Prokopenko 0 Reputation points

2024-07-26T20:20:09.1366667+00:00

Your statement is not correct. Neither plain PosgreSQL instance nor Azure CosmosDB for PosgreSQL are supported as a linked resource.

Currently only Azure CosmosDB for MongoDB and Azure CosmosDB for NoSQL dababase types are supported.

See screenshot below from "connect to external data" dialog from Azure Synapse for a proof with complete list of external resources available for linking.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

Connect and query to Azure database for PostgresSql from Synapse Analytics

1 answer

Your answer