External Table between Docker SQL Server and Docker MongoDB

87 Views Asked by At

I have a container with SQL (following this example https://github.com/croblesm/mssql-polybase)

I have a container with MongoDB running as normal.

I need to create a external table to consume mongoDB data and create a report.

I did follow this instructions https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-mongodb?view=sql-server-ver16

but I'm getting an error on SQL

Msg 7320, Level 16, State 110, Line 2
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". External access operation failed because the location URI is invalid. Revise the URI and try again.

Here are the networkSettings of my Mongo Container:

 "NetworkSettings": {
            "Bridge": "",
            "SandboxID": "fd848692fbdb9ba61c5bbbe774d05955bef0cca850e2bb137e54323fede0b5c1",
            "HairpinMode": false,
            "LinkLocalIPv6Address": "",
            "LinkLocalIPv6PrefixLen": 0,
            "Ports": {
                "27017/tcp": [
                    {
                        "HostIp": "0.0.0.0",
                        "HostPort": "27017"
                    }
                ]
            },
            "SandboxKey": "/var/run/docker/netns/fd848692fbdb",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "EndpointID": "",
            "Gateway": "",
            "GlobalIPv6Address": "",
            "GlobalIPv6PrefixLen": 0,
            "IPAddress": "",
            "IPPrefixLen": 0,
            "IPv6Gateway": "",
            "MacAddress": "",
            "Networks": {
                "oms_dev_setup_default": {
                    "IPAMConfig": null,
                    "Links": null,
                    "Aliases": [
                        "mongodb",
                        "1eb3634590ea"
                    ],
                    "NetworkID": "0f740644bac8ee38bf6e237c31e718d524d1cb74932c4426744f1820aa51abb1",
                    "EndpointID": "349f1b5717f0fd8a401b518d62846c53166e4d729843e2fd162c05baec32b82a",
                    "Gateway": "172.18.0.1",
                    "IPAddress": "172.18.0.4",
                    "IPPrefixLen": 16,
                    "IPv6Gateway": "",
                    "GlobalIPv6Address": "",
                    "GlobalIPv6PrefixLen": 0,
                    "MacAddress": "02:42:ac:12:00:04",
                    "DriverOpts": null
                }
            }
        }

I think the problem is related with the configuration of the SQL EXTERNAL DATA SOURCE.

ALTER EXTERNAL DATA SOURCE MongoDBSource 
SET LOCATION = 'mongodb://172.18.0.1:27017'', --- Host CREDENTIAL = MongoDBConnection,CONNECTION_OPTIONS = "ssl=false;"';

I did try several combinations but nothing is working

0

There are 0 best solutions below