In this article, you’ll learn the approach to integrate MongoDB data source using data virtualization technique in SQL Server 2019. In this article, you can see how SQL Server 2019 provides a platform to create a modern enterprise data hub using data virtualization technology and the PolyBase technique.
- Discuss Data Virtualization
- Pre-requisite to setup MongoDB
- Set up MongoDB external connection in SQL Server 2019
- Many more…
Introduction
The advent of Data virtualization in SQL Server 2019 allows us to solve modern and complex data challenges. Data virtualization with PolyBase in SQL Server 2019 is used as a data hub, and you can directly query the data from several heterogeneous data sources. These data sources include Azure Managed Instance, Oracle, Teradata, SAP HANA, MongoDB, Hadoop clusters, Cosmos DB, and SQL Server. We can query the data source using T-SQL and without separately installing driver software.
The data virtualization in SQL Server 2019 is an improvised solution to the ETL process. The other advantage of Data virtualization is that it allows the integration of data from different sources such as Azure MI, SQL Server, MongoDB, Oracle, DB2, Cosmos DB, and Hadoop-Distributed-File-System (HDFS) without the much data movement around the source and destination. This process is possible with the advent of PolyBase connectors.
- Note:
- Using T-SQL, we can query heterogeneous data sources using PolyBase connectors. This provides the bridge to query the data from external data sources such as SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables
- It also supports the UTF-8 encoding format
Get started:
In this section, you will learn how to create secure data access from the underlying data source.
In this case, PolyBase uses the security model of the MongoDB model to access the data. In most cases, we need permission to read the data. However, the credentials used to read the data and it is stored inside the PolyBase data hub.
To set-up data virtualization, follow the below steps:
- Setup database master key
- Create database-scoped credentials
- PolyBase external tables
- Configure External data sources
To configure database virtualization, select the database. Right-click the database and select Create External Table that starts the data virtualization wizard.
- Select a data source
- Create a database master key
In this section, we will see how to create a database master key. The master key is created inside the SQL Server database and it acts as a data hub.
The master key is providing a secure way to read data using the credentials in the external data source. It is always recommended to choose a complex password for the master key. In addition, use the BACKUP MASTER KEY command to back up the master key.
- Note:
- You can have one master key in a database instance
- Use a secure location to backup the master key
- If you already have the master key created in your SQL instance—re-use it. The same master key is used to secure data access for data virtualization
-
To see whether the master key is already created run the following query
1SELECT * from sys.symmetric_keys
Syntax to create a database master key is as follows:
|
1 |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mongodb@123'; |
Database scoped credentials
Database scoped credentials provide the abstraction layer to secure the model to access the data from the data source. SQL Server 2019 supports the following security models when using PolyBase for data virtualization:
- Proxy authentication
- Kerberos authentication
- Storage access key—shared secret key
The following syntax is used to provide SQL Server with the security context for a connection to a MongoDB external data source. In addition, you will see a shared secret key in use to create a database scoped credential.
|
1 2 |
CREATE DATABASE SCOPED CREDENTIAL MyMongoDBDemo2019 WITH IDENTITY = 'admin', Secret = 'mongodb'; |
- Note: The identity refers to the username of the MongoDB external data source
To validate database scoped credentials in the datahub run the following T-SQL:
|
1 |
SELECT * FROM sys.database_scoped_credentials |
Configure External data sources
External data sources provide a path to virtualize data from other data sources. It collects all the meta-data to access data from the single database object.
Each external data source provides the below options required to access the external source:
- Location
- port
- credential
- connectivity
|
1 2 3 4 5 |
CREATE EXTERNAL DATA SOURCE MyMongoDBSource WITH ( LOCATION = 'mongodb://127.0.0.1:27017', CREDENTIAL = MyMongoDBDemo2019 --specify credential created in step 2 ); |
The LOCATION parameter defines the communication protocol, server name and port for connectivity.
In this case, the communication protocol is mongodb and server is 127.0.0.1(Localhost) and port is 27017
The CREDENTIAL argument uses the database scoped credential MyMongoDBDemo2019. The external data source will use this database-scoped credential to query the target data sources
- Note: The Connection_options and Pushdown parameters will be explained later in the article
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021
To validate external data sources run the following T-SQL:
|
1 |
SELECT * FROM sys.external_data_sources |
How to map an external data table
In this section, we will see how to create external views. On selecting the parent tables, the child tables are also referred by default. In addition, you have the mapping table where you modify the columns to match the target data types. This also gives you the option to modify the name of the external table.
Let us look at the details of the MongoDB instance. Create the user using the following commands. I will not go in-depth on MongoDB concepts.
|
1 2 3 4 5 6 7 |
> use admin switched to db admin db.createUser( { user: "Prashanth2020", pwd: "MyMongoDemp2019", roles:[{role: "userAdminAnyDatabase" , db:"admin"}]}) > |
PolyBase external tables
External tables provide SQL Server with the schema to reason over data in the external data source.
|
1 2 3 4 5 6 7 8 9 |
CREATE EXTERNAL TABLE Employee( [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [name] nvarchar(max) , age float(53), status nvarchar(max)COLLATE SQL_Latin1_General_CP1_CI_AS) WITH ( LOCATION='employee.Employee', DATA_SOURCE= MyMongoDBSource ); |
Error Message:
The T-SQL create external table statement errors out with the following details. You can see that the TLS handshake failed error reported. This can be handled in two ways. You can manage it at the Mongo DB startup parameters or you can turn off the SSL parameter or you can turn off the SSL=false in the T-SQL statement.
“Msg 105082, Level 16, State 1, Line 16
105082;Generic ODBC error: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client:
No suitable servers found (serverSelectionTryOnce set):
[TLS handshake failed: error:00000000:lib(0):func(0):reason(0) calling ismaster on ‘127.0.0.1:27017’]
(Error Code: 13053) Additional error <2>:
ErrorMsg: [Microsoft][MongoDBODBC] (110) Error from MongoDB Client: No suitable servers found
(serverSelectionTryOnce set)
: [TLS handshake failed: error:00000000:lib(0):func(0):reason(0) calling ismaster on ‘127.0.0.1:27017’]
(Error Code: 13053), SqlState: HY000, NativeError: 110 “
You may receive this error due to the SSL issue. Now, the CONNECTION_OPTIONS parameter plays a role to connect MongoDB data source.
In this case, set the connection_options to ssl=false and then run the create external statement.
|
1 2 3 4 5 |
CREATE EXTERNAL DATA SOURCE MyMongoDBSource WITH ( LOCATION = 'mongodb://127.0.0.1:27017', CREDENTIAL = MyMongoDBDemo2019,CONNECTION_OPTIONS = 'ssl=false;' ); |
The CONNECTION_OPTION setting suppresses the connection over SSL, which is a default requirement for communicating with many other data sources. I will discuss more about it in the next article.
Let us run create external table statement again:
|
1 2 3 4 5 6 7 8 9 |
CREATE EXTERNAL TABLE Employee( [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [name] nvarchar(max) , age float(53), status nvarchar(max)COLLATE SQL_Latin1_General_CP1_CI_AS) WITH ( LOCATION='employee.Employee', DATA_SOURCE= MyMongoDBSource ); |
We can see in the below snippet, the external table created successfully.
Let us validate the external table using the following T-SQL:
|
1 |
SELECT * FROM sys.external_tables |
Now, Let us browse object explorer to validate the existence of the External Tables and External Data Sources.
Finally, query the external table
|
1 2 3 4 5 6 |
SELECT TOP (1000) [_id] ,[name] ,[age] ,[status] FROM [MongoDB].[dbo].[Employee] |
Summary
In this article, we discussed how to set up a PolyBase in SQL Server for MongoDB external data source.
To summarize the steps real quick:
The data virtualization framework provides a platform to eliminate the use of redundant data copies. To maintain the physical copy always requires additional storage and hardware resources. It incurs to increase an additional overhead on costs. Using the data virtualization technique, organizations can create an abstract layer to create a virtual data copy from a heterogeneous or homogeneous data repositories. The virtual copies define the soft external table links to the physical data repository. As I mentioned, the data virtualization technique not only avoids extra storage but it provides strong consistency between the source and the target in the data retrieval process. In addition, it addresses the data issues which in turn improves the data quality and improves the data query performance.
That’s all for now. I hope you like this article. Stay tuned for more updates…
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram