question

Pittrecon08127 avatar image
0 Votes"
Pittrecon08127 asked Pittrecon08127 edited

Query Cosmos DB from Azure SQL

we store some data in Cosmos DB and Azure SQL, how can I establish a connection to Cosmos DB from Azure SQL as an external resource? so I can query Cosmos from Azure SQL and use joining to merge data together. TIA.

azure-cosmos-db
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SudiptaChakraborty-1767 avatar image
0 Votes"
SudiptaChakraborty-1767 answered Pittrecon08127 commented

@Pittrecon08127 :

You can use SQL Linked Servers for achieving your requirement.
This solution only works for SQL Server on VM/IaaS – and is not supported for Azure SQL DB (ASDB) – mainly as ASDB doesn’t support SQL Linked Servers.

Setting Up the CosmosDB ODBC System DSN
To connect SQL Server to CosmosDB you need an ODBC driver installed onto the VM. Its a little fiddly the first time you set it up, but once done you often dont need to revisit it.
You can get the ODBC driver for free here – https://docs.microsoft.com/en-us/azure/cosmos-db/odbc-driver#install
The one you want is most likely the 64-bit MSI for 64-bit Windows.
Once installed, when you open the ODBC client tool you should see the driver installed
Under the System DSN tab, click ADD a new entry for the Driver and you will see something like this below. This one is already setup for my own Azure CosmosDB.

The values you want to enter are:
• Data Source Name: ACDB (or whatever – but keep it short as we’ll use this in SQL)
• Host: https://COSMOSDBNAME.documents.azure.com:443/
• Access Key: COSMOSDBKEY
You can get your COSMOSDBNAME and COSMOSDBKEY from the Azure Portal under the Keys section of your Azure CosmosDB Account.
TEST the connection to ensure its working OK.
Setting Up the CosmosDB ODBC Schema
To do this we need to use the Schema Editor installed along with the Azure CosmosDB ODBC driver. You can read more about the process to do that here – https://docs.microsoft.com/en-us/azure/cosmos-db/odbc-driver#a-idcollection-mappingastep-3-create-a-schema-definition-using-the-collection-mapping-method
Essentially – what this tool does is help you automatically define the schema you want to use for the JSON documents stored in your Azure CosmosDB. It is this schema we will see when querying CosmosDB from SQL Server.
When I run the Schema Creator Tool (using the Create New + Sample Method) across my own Azure CosmosDB database, it auto-creates a flattened schema that looks as below. At a glance, you can see a close match to the fields in the JSON document sample I showed at the start of this blog post.
Any columns you see named “_<column>” are standard Azure CosmosDB system generated.
The generated schema has 2 tables in a parent/child structure; [Customers]–>[Orders]
Lastly, but absolutely and definitely not least, you MUST ensure that you attach that saved schema format file to your ODBC DSN. You do this via clicking on the Advanced Options button and selecting the schema format file you just created.

Setting up the Azure CosmosDB SQL Linked Server
Once the ODBC driver and System DSN is configured, then all we need to do is to create a standard SQL Server Linked Server to reference the ODBC System DSN!
Open SSMS, create a new connection to your SQL Server 2016 instance and run the code.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ACDB', @srvproduct=N'ACDB', @provider=N'MSDASQL', @datasrc=N'ACDB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ACDB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'use remote collation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ACDB', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO


Querying CosmosDB from SQL Server
To reference the SQL linked server we need to use OPENQUERY, which executes the specified pass-through query on the linked server. Directly referencing the table via the 4-part naming does not work (ie server.database.owner.table will fail).
Here’s some examples of the types of SQL queries we can run across the ODBC driver.
Customers Rowcount
SELECT
FROM OPENQUERY
(
ACDB,
'
SELECT COUNT(
) AS ROWCNT
FROM PerfTest.CustomerOrders.CustomerOrders
'
)
GO


Orders Rowcount
SELECT
FROM OPENQUERY
(
ACDB,
'
SELECT COUNT(
) AS ROWCNT
FROM PerfTest.CustomerOrders."CustomerOrders_Orders[]"
'
)
GO

Top 10 Customers Where Name = X
SELECT
FROM OPENQUERY
(
ACDB,
'
SELECT TOP 10

FROM PerfTest.CustomerOrders.CustomerOrders
WHERE Name_First = ''Regina''
'
)


Orders Where Amount > X
SELECT
FROM OPENQUERY
(
ACDB,
'
SELECT

FROM PerfTest.CustomerOrders."CustomerOrders_Orders[]"
WHERE "CustomerOrders_Orders[]_Amount" > 50000
'
)


Customers Join Orders Where ID Between X and Y Order By Last_Name
SELECT
FROM OPENQUERY
(
ACDB,
'
SELECT c.
,
o.id AS Order_Customer_ID,
o."CustomerOrders_Orders[]_OrderID",
o."CustomerOrders_Orders[]_Amount"
FROM PerfTest.CustomerOrders.CustomerOrders AS c
INNER JOIN PerfTest.CustomerOrders."CustomerOrders_Orders[]" AS o
ON c.id = o.id
WHERE c.id BETWEEN 29000 and 29100
ORDER BY c.Name_Last
'
)


CosmosDB Customers Join SQL Server AdventureWorksDW Customers
SELECT oq.,
dc.

FROM OPENQUERY
(
ACDB,
'
SELECT TOP 10 *
FROM PerfTest.CustomerOrders.CustomerOrders
WHERE Name_First = ''Regina''
'
) as oq
INNER JOIN [AdventureWorksDW2016].[dbo].[DimCustomer] dc
on oq.id = dc.CustomerKey
GO


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for the info. Unfortunately, our Azure SQL is managed by Azure and not able to create linked server.

0 Votes 0 ·