Configure PolyBase to access external data in MongoDB
Applies to:
SQL Server
The article explains how to use PolyBase on a SQL Server instance to query external data in MongoDB.
Prerequisites
If you haven't installed PolyBase, see PolyBase installation.
Before you create a database scoped credential, the database must have a master key to protect the credential. For more information, see CREATE MASTER KEY.
Configure a MongoDB external data source
To query the data from a MongoDB data source, you must create external tables to reference the external data. This section provides sample code to create these external tables.
The following Transact-SQL commands are used in this section:
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL DATA SOURCE (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- CREATE STATISTICS (Transact-SQL)
Create a database scoped credential for accessing the MongoDB source.
The following script creates a database scoped credential. Before you run the script update it for your environment:
- Replace
<credential_name>
with a name for the credential. - Replace
<username>
with the user name for the external source. - Replace
<password>
with the appropriate password.
CREATE DATABASE SCOPED CREDENTIAL [<credential_name>] WITH IDENTITY = '<username>', Secret = '<password>';
Important
The MongoDB ODBC Connector for PolyBase supports only basic authentication, not Kerberos authentication.
- Replace
Create an external data source.
The following script creates the external data source. For reference, see CREATE EXTERNAL DATA SOURCE. Before you run the script update it for your environment:
- Update the location. Set the
<server>
and<port>
for your environment. - Replace
<credential_name>
with the name of the credential you created in the previous step. - Optionally you can specify
PUSHDOWN = ON
orPUSHDOWN = OFF
if you want to specify pushdown computation to the external source.
CREATE EXTERNAL DATA SOURCE external_data_source_name WITH (LOCATION = '<mongodb://<server>[:<port>]>' [ [ , ] CREDENTIAL = <credential_name> ] [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]] [ [ , ] PUSHDOWN = { ON | OFF } ]) [ ; ]
- Update the location. Set the
Query the external schema in MongoDB.
You can use the Data Virtualization extension for Azure Data Studio to connect to and generate a CREATE EXTERNAL TABLE statement based on the schema detected by the PolyBase ODBC Driver for MongoDB driver. You can also manually customize a script based on the output of the system stored procedure sp_data_source_objects (Transact-SQL). The Data Virtualization extension for Azure Data Studio and
sp_data_source_table_columns
use the same internal stored procedures to query the external schema schema.To create external tables to MongoDB collections that contain arrays, the recommendation is to use Data Virtualization extension for Azure Data Studio. The flattening actions are performed automatically by the driver. The
sp_data_source_table_columns
stored procedure also automatically performs the flattening via the PolyBase ODBC Driver for MongoDB driver.Create an external table.
If you use the Data Virtualization extension for Azure Data Studio, you can skip this step, as the CREATE EXTERNAL TABLE statement is generated for you. To provide the schema manually, consider the following sample script to create an external table. For reference, see CREATE EXTERNAL TABLE.
Before you run the script, update it for your environment:
- Update the fields with their name, collation, and if they are collections then specify the collection name and the field name. In the example,
friends
is a custom data type. - Update the location. Set the database name and the table name. Note three-part names are not allowed, so you can't create it for the
system.profile
table. Also you can't specify a view because it can't obtain the metadata from it. - Update the data source with the name of the one you created in the previous step.
CREATE EXTERNAL TABLE [MongoDbRandomData]( [_id] NVARCHAR(24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RandomData_friends_id] INT, [RandomData_tags] NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS) WITH ( LOCATION='MyDb.RandomData', DATA_SOURCE=[MongoDb])
- Update the fields with their name, collation, and if they are collections then specify the collection name and the field name. In the example,
Optional: Create statistics on an external table.
We recommend creating statistics on external table columns, especially the ones used for joins, filters and aggregates, for optimal query performance.
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;
Important
Once you have created an external data source, you can use the CREATE EXTERNAL TABLE command to create a query-able table over that source.
For an example, see Create an external table for MongoDB.
MongoDB connection options
For information about MongoDB connection options, see MongoDB documentation: Connection String URI Format.
Flattening
Flattening is enabled for nested and repeated data from MongoDB document collections. User is required to enable create an external table
and explicitly specify a relational schema over MongoDB document collections that may have nested and/or repeated data.
JSON nested/repeated data types will be flattened as follows
Object: unordered key/value collection enclosed in curly braces (nested)
SQL Server creates a table column for each object key
- Column Name: objectname_keyname
Array: ordered values, separated by commas, enclosed in square brackets (repeated)
SQL Server adds a new table row for each array item
SQL Server creates a column per array to store the array item index
Column Name: arrayname_index
Data Type: bigint
There are several potential issues with this technique, two of them being:
An empty repeated field will effectively mask the data contained in the flat fields of the same record
The presence of multiple repeated fields can result in an explosion of the number of produced rows
As an example, SQL Server evaluates the MongoDB sample dataset restaurant collection stored in non-relational JSON format. Each restaurant has a nested address field and an array of grades it was assigned on different days. The figure below illustrates a typical restaurant with nested address and nested-repeated grades.
Object address will be flattened as below:
- Nested field
restaurant.address.building
becomesrestaurant.address_building
- Nested field
restaurant.address.coord
becomesrestaurant.address_coord
- Nested field
restaurant.address.street
becomesrestaurant.address_street
- Nested field
restaurant.address.zipcode
becomesrestaurant.address_zipcode
Array grades will be flattened as below:
grades_date | grades_grade | games_score |
---|---|---|
1393804800000 | A | 2 |
1378857600000 | A | 6 |
135898560000 | A | 10 |
1322006400000 | A | 9 |
1299715200000 | B | 14 |
Cosmos DB Connection
Using the Cosmos DB Mongo API and the Mongo DB PolyBase connector you can create an external table of a Cosmos DB instance. This accomplished by following the same steps listed above. Make sure the Database scoped credential, Server address, port, and location string reflect that of the Cosmos DB server.
Examples
The following example creates an external data source with the following parameters:
Parameter | Value |
---|---|
Name | external_data_source_name |
Service | mongodb0.example.com |
Instance | 27017 |
Replica set | myRepl |
TLS | true |
Pushdown computation | On |
CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = 'mongodb://mongodb0.example.com:27017',
CONNECTION_OPTIONS = 'replicaSet=myRepl; tls=true',
PUSHDOWN = ON ,
CREDENTIAL = credential_name);
Next steps
For more tutorials on creating external data sources and external tables to a variety of data sources, see PolyBase Transact-SQL reference.
To learn more about PolyBase, see Overview of SQL Server PolyBase.
Обратна връзка
https://aka.ms/ContentUserFeedback.
Очаквайте скоро: През цялата 2024 г. постепенно ще отстраняваме проблемите в GitHub като механизъм за обратна връзка за съдържание и ще го заменим с нова система за обратна връзка. За повече информация вижте:Подаване и преглед на обратна връзка за