Fala pessoal, tudo bem? Hoje vamos ver como fazer para pegar dados do MongoDB e levá-los para o SQL Server através de uma simples REST API em NodeJS.
Desde a versão 2016 o SQL Server tem suporte a JSON (JSON é vida!), isso ampliou suas formas de comunicação com aplicações e outros bancos de dados, como por exemplo o MongoDB. Mesmo sendo possível a comunicação com o MongoDB através de ODBC, uma API REST deixa essa comunicação mais fluida, porque uma vez publicada, o SQL Server se conecta a ela e os dados chegam de forma padronizada.
O intuito desse artigo é demonstrar de forma simples a construção de uma API que se conecta ao MongoDB e fazer com que o SQL Server faça requests a ela para obter os dados. Não vou abordar detalhes sobre a construção da API, o foco é a comunicação entre os dois bancos.
Este artigo é baseado num post da equipe do Studio 3T e pode ser encontrado nesse link.
Limitações e pré-requisitos
Durante os testes para fazer esse post, testei alguns cenários e encontrei algumas limitações:
- Por requerer parâmetros de configurações, digamos mais administrativas, não é possível utilizar o Azure SQL Databases;
- Bem como o SQL Server for Linux, por conta de algumas DLLs, que obviamente estão disponíveis somente para Windows;
Utilizei a versão SQL Server 2017 Developer com Windows Server 2016 na Azure.
Você também precisará do NodeJS e de alguns módulos que podem ser instalados através do NPM.
Do lado do MongoDB, criei uma instância M0 no MongoDB Atlas que é free também na Azure. Se você não conhece o MongoDB Atlas, recomendo a leitura deste artigo que escrevi sobre ele!
O Dataset
Como exemplo, baixei do Kaggle um dataset pequeno sobre carros (download aqui), utilizei apenas alguns campos e nosso documento no MongoDB ficou assim:
{
_id: ObjectId(5c6ef4d6aaeaf27b7cdb76d4),
brand: "alfa-romero",
fuelType: "gas",
numOfDoors: "two",
bodyStyle: "convertible",
engineLocation: "front",
numberOfCylinders: "four",
horsepower: 111
}A API
Novamente, o foco não é uma API perfeita, logo, o código abaixo pode (e deve) ser muito melhorado, mas para efeitos de estudo ela nos atende bem:
const express = require("express"),
app = express(),
bodyParser = require("body-parser"),
cors = require("cors"),
MongoClient = require('mongodb').MongoClient;
const port = 8000;
var mongodbURI = "mongodb+srv://user:pass@seu_servidor_mongodb/test?retryWrites=true"
app.use(cors())
app.use(bodyParser.json())
app.use(bodyParser.urlencoded({ extended: true }))
MongoClient.connect(mongodbURI, {useNewUrlParser: true}, function(err, client){
if(err) throw err;
app.get('/', function(req, res){
res.json({
"message": "Welcome to Cars Sample API",
"Find Brand Name": "curl -X GET http://server.com/brand/",
})
});
app.get('/brand/:name', function(req, res){
const collection = client.db('newCars').collection('cars');
collection
.find({
brand: {
$regex: '\\N*' + req.params.name + '\\N*',
$options: "si"
}
})
.project({
_id: 0
})
.sort({
brand: 1
})
.toArray(function(err, result){
if(err) throw err;
res.setHeader("Content-Type", "application/json");
res.json({
result
});
})
});
})
app.listen(port, () => {
console.log(`listening on port ${port}`)
})Note que temos dois pontos de entrada em nossa API:
- O / apenas nos exibe uma mensagem de boas vindas
- O /brand/:name recebe o nome da marca de automóveis que queremos listar, esse é o parâmetro de entrada para nossa consulta no MongoDB. Note também que utilizei uma busca utilizando o $regex para possibilitar uma busca mais abrangente, sem nos preocuparmos de que forma o nome da marca está sendo inputado. O retorno é um array de documentos:
Fazendo a mágica no SQL Server
Agora, que temos nossa API retornando os dados corretamente, vamos para o SQL Server e fazer os requests a partir dele. Vamos utilizar ‘Ole Automation Procedures’ que por default e para dificultar um ataque por SQL Injection está desabilitado. Para habilitar utilizei os seguintes comandos:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
Agora, vamos criar uma procedure para executar serviços REST no SQL Server. Esse código é todo do artigo original e não precisei fazer nenhuma modificação:
GO
IF Object_Id('dbo.GetWebService','P') IS NOT NULL
DROP procedure dbo.GetWebService
GO
CREATE PROCEDURE dbo.GetWebService
@TheURL VARCHAR(255),-- the url of the web service
@TheResponse NVARCHAR(4000) OUTPUT --the resulting JSON
AS
BEGIN
DECLARE @obj INT, @hr INT, @status INT, @message VARCHAR(255);
/**
Summary: >
This is intended for using web services that
utilize JavaScript Object Notation (JSON). You pass it the link to
a webservice and it returns the JSON string
Note: >
OLE Automation objects can be used within a Transact-SQL batch, but
SQL Server blocks access to OLE Automation stored procedures because
this component is turned off as part of the security configuration.
Author: PhilFactor
Date: 26/10/2017
Database: PhilFactor
Examples:
- >
DECLARE @response NVARCHAR(MAX)
EXECUTE dbo.GetWebService 'http://headers.jsontest.com/', @response OUTPUT
SELECT @response
Returns: >
nothing
**/
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;
SET @message = 'sp_OAMethod Open failed';
IF @hr = 0 EXEC @hr = sp_OAMethod @obj, 'open', NULL, 'GET', @TheURL, false;
SET @message = 'sp_OAMethod setRequestHeader failed';
IF @hr = 0
EXEC @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type',
'application/x-www-form-urlencoded';
SET @message = 'sp_OAMethod Send failed';
IF @hr = 0 EXEC @hr = sp_OAMethod @obj, send, NULL, '';
SET @message = 'sp_OAMethod read status failed';
IF @hr = 0 EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT;
IF @status <> 200 BEGIN
SELECT @message = 'sp_OAMethod http status ' + Str(@status), @hr = -1;
END;
SET @message = 'sp_OAMethod read response failed';
IF @hr = 0
BEGIN
EXEC @hr = sp_OAGetProperty @obj, 'responseText', @Theresponse OUT;
END;
EXEC sp_OADestroy @obj;
IF @hr <> 0 RAISERROR(@message, 16, 1);
END;Criamos então uma procedure chamada GetWebService que recebe como parâmetro de entrada uma URL e como resultado o JSON retornado.
Executamos da seguinte maneira, somente alterando a URL para o seu servidor:
E temos como resultado os dados tabulados, prontos para serem persistidos no SQL Server:
Legal, né? O exemplo é simples, mas pode ser facilmente modificado e gerar uma variedade de possibilidades para a comunicação entre o MongoDB e o SQL Server. Um exemplo é que em determinados cenários podemos eliminar ETLs para inserir dados no SQL Server a partir do MongoDB. Por outro lado vemos também uma maior abertura por parte do SQL Server utilizando JSON e isso é muito bacana!
Bem, espero que tenham gostado. Deixe seu like e compartilhe à vontade!
Ah! Se você quiser saber mais sobre MongoDB, dê uma olhada nas próximas turmas de treinamento, serão online e ao vivo, presenciais em São Paulo e Brasília!
Até a próxima!
