Access Data in cosmodb using mongodb API with SQL Server PolyBase fails with error

Mohammad Sufian 1

I am trying to access data in my azure cosmosdb using SQL Server PolyBase (cosmosdb was created using "Azure Cosmos DB for MongoDB API)

Alert me|Edit|Change type
Question
You cannot vote on your own post
0
Hi All,

I am trying to access data in my azure cosmosdb using SQL Server PolyBase (cosmosdb was created using "Azure Cosmos DB for MongoDB API)

I am able to connect and access documents using mongo shell. However when trying to create external table, getting below error.

Searched though PolyBase logs and could not find any useful information which can help troubleshoot the issue.

Hence posting it here.

SQL Server version : Microsoft SQL Server 2019 (RTM-CU4) (KB4548597)

2 answers

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-07-21T01:16:26.05+00:00

@mohammadsufian-9623

When we researched old cases raised with support we were able to find the cause of this which is: "SQL Server was not able to handle the MongoDB 3.6 URI format of mongo.cosmos.com properly."

There were many things tried but what worked was to change the 2 lines "Polybase ODBC Driver for MongoDb.ini” file of the SQL Server files with location C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Polybase\ODBC Drivers\PolyBase ODBC Driver for MongoDb.ini

The code that needs to be changed:
a. Driver=PolyBase ODBC Driver for MongoDb\MongoDBODBC_sb64.dll
b. Setup=PolyBase ODBC Driver for MongoDb\MongoDBODBC_sb64.dll

You can also reference a similar issue in this Stack overflow post.

Hope this helps.

Regards
Navtej S
Please sign in to rate this answer.
Mohammad Sufian 1 Reputation point

2020-07-21T02:14:34.347+00:00

Hi Navtej,

I looked into the MongoDb.ini file and the values mentioned are same as you mentioned in your response. Still i am getting the error

[PolyBase ODBC Driver for MongoDb]
Description=PolyBase ODBC Driver for MongoDb
Driver=PolyBase ODBC Driver for MongoDb\MongoDBODBC_sb64.dll
Setup=PolyBase ODBC Driver for MongoDb\MongoDBODBC_sb64.dll
CPTimeout=60

Mohammad Sufian 1 Reputation point

2020-07-22T03:02:58.087+00:00

HI Navtej,

Any pointers to solve this issue.

Mohammad Sufian 1 Reputation point

2020-07-22T15:17:27.98+00:00

HI Navtej,

II tried debugging the issue but could not find anything which can point to the issue. Your help is greatly appreciated.

Regards
Sufian

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-07-22T15:54:32.147+00:00

@mohmmadSufian-9623

Thanks for getting back. I am checking this further from my end but the correct way forward to unblock you would be to create a support ticket for this where engineers can get access to your resources and check what is going on.

So I strongly advise you to create the support ticket and I am checking this from my end.

Regards
Navtej S

Mohammad Sufian 1 Reputation point

2020-07-25T08:01:01.73+00:00

HI Navtej,

I am working on a POC with free account which doesn't allow to create support ticket for technical issues. Do you have any update?

Regards
Sufian

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-07-27T22:20:48.067+00:00

@mohmmadsufian-9623

We advise(but don't recommend) for faster resolution to get a support plan. On my front I have directly raised it with product group but it may take some time to get a response and MS documentation for this.

I hope you have seen the external document for this issue. I appreciate your patience in this regard.

Thanks
Navtej S

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-07-28T01:34:31.62+00:00

@mohammadsufian-9623

Please also share your subscription id on email - AzCommunity@microsoft.com so we can work on enabling support for you in this instance.

Please do mention the title of the issue and my id in the message so I can take action regarding the same.

Thanks
Navtej S

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-07-28T18:11:14.81+00:00

@Mohammad Sufian

Please convey if you have already sent the email as we have not received any email regarding this.

Thanks
Navtej S

Mohammad Sufian 1 Reputation point

2020-08-03T11:02:02.237+00:00

Hi Navtej,

Sent the email today. Please confirm once you have received it

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-08-04T17:29:46.917+00:00

@Mohammad Sufian

I have enabled the free support for you and sent info on how to create the ticket.

Please share the ticket number once you create it so that we can get visibility into the same and get traction if needed.

Thanks
Navtej S

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-08-04T21:55:37.107+00:00

Also there is a gated preview going on for - https://learn.microsoft.com/en-us/azure/cosmos-db/configure-synapse-link for using Synapse Link instead?

The odbc way of connecting to CosmosDB might not be the best way.

Thanks
Navtej S

Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-08-05T16:29:45.697+00:00

@Mohammad Sufian Have you raised the Support ticket. please do share the ticket number here so that we can track it.

Thanks
Navtej S

Mohammad Sufian 1 Reputation point

2020-08-06T02:19:03.127+00:00

HI Navtej

I have created the support ticket : Support request ID 120080623000247

Regards
Sufian
Sign in to comment
Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-08-17T18:36:28.013+00:00

@Mohammad Sufian

We have checked the ticket raised by you and we got to know the workaround provided by the team has been working for you.

The workaround mentioned is : To use v3.2 format endpoints for v3.6 account. For example in below scripts, change original endpoint in “LOCATION” from *.mongo.cosmos.azure.com to *.documents.azure.com:

Support Team will still look into this as it might be a bug for Cosmos DB v3.6 account, so they have engaged our product team to investigate and confirm.

Thanks
Navtej S
Please sign in to rate this answer.
Navtej Singh Saini 4,216 Reputation points Microsoft Employee

2020-08-18T16:50:11.047+00:00

Also As conveyed the fix has been identified by our PG team for this and will be rolled out in September.
Sign in to comment