Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will cover retrieving data from MongoDB with PolyBase; PolyBase allows us to retrieve data from multiple sources and combine them into a single database.
In this article, I will explain how to query a table in Mongo using polybase on SQL.
If you have not created a master key, you need to create one.
1 | CREATE MASTER KEY ENCRYPTION BY PASSWORD ='farukerdem.com' |
After creating our master key, we need to enter the user in the mongodb database.
1 2 | CREATE DATABASE SCOPED CREDENTIAL mongo_credential WITH IDENTITY = 'polybaseuser', SECRET = 'farukerdem.com'; |
We have created a credential in our database and are creating a data source for our mongodb connection.
1 2 3 4 5 6 | CREATE EXTERNAL DATA SOURCE Mongodb_ds WITH ( LOCATION = 'mongodb://20.56.93.246:27017', -- PUSHDOWN = ON | OFF, CREDENTIAL = mongo_credential,CONNECTION_OPTIONS='ssl=false;' ); |
We have introduced the connection and user information to the SQL server and now let’s see the data we want to pull on Mongo.
As seen above, there is a collection (table) with three columns.
In order to see our table on Mongodb on SQL Server, we need to create an external table as follows.
1 2 3 4 5 | CREATE EXTERNAL TABLE mongo_ex_table ( _id nvarchar(25) , name nvarchar(max), [birthday] DATETIME2(6)) WITH (LOCATION = 'frkd1.test', DATA_SOURCE=Mongodb_ds) |
We created our external table. Let’s check it by pulling our data.
