Olá, pessoal! Estou trazendo esse assunto pois pode ser muito útil para algumas pessoas assim como foi para mim, os vários posts do Dirceu Resende em seu blog sobre a possibilidade de extrair dados via API, diretamente do SQL SERVER sem a necessidade de uma ferramenta de ETL para isso. Isso mesmo! Direto do SQL Server, é possível utilizar as “OLE AUTOMATION PROCEDURES” e não só realizar requisições web (GET, POST, PUT..) como também manipular arquivos e muitas outras coisas.
Como dito antes, a primeira vez que eu li algo sobre OLE Automation foi no blog do Dirceu e, obviamente quem sou eu para reinventar a roda ou “chover no molhado”. Por isso, vou deixar abaixo o link dos posts que o Dirceu fez sobre isso.
A ideia é reorganizar ainda mais o uso dessa funcionalidade com o objetivo de construir suas próprias procedures para consumir dados via API. Vou tentar aumentar o post aos poucos com novos cenários e, por isso começarei com o simples.
Vale lembrar que parafraseando o tio Ben “com grandes poderes vêm grandes responsabilidades” e portanto, preciso te alertar que a ativação desse recurso na instância pode representar uma falha na segurança do seu servidor. Por que? Pois com essa funcionalidade ativa, quem tem acesso a ela pode facilmente manipular arquivos do servidor e até mesmo acessar dados que estiverem disponíveis em outros locais acessíveis via rede. A recomendação é conversar com o time DBA para contornar isso, promovendo a revisão dos mecanismos de segurança da sua instância.
Então, vamos colocar a mão na massa!
No contexto do SQL Server, as procedures OLE Automation permitem que o SQL Server interaja com objetos COM (Component Object Model) do Windows, usando T-SQL. Isso é feito por meio de um conjunto de stored procedures chamadas de OLE Automation Stored Procedures, que vêm desativadas por padrão, justamento por motivos de segurança como comentei anteriormente. Para ativá-las, podemos rodar o script abaixo:
|
1 2 3 4 5 6 7 8 9 10 |
-- Primeiro vamos habilitar as opções avançadas do servidor sp_configure 'show advanced options', 1; GO RECONFIGURE; GO -- Agora vamos setar e habilitar o recurso na instância sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO |
Para verificar se funcionou ou se já está habilitado na sua instância pode rodar o script abaixo (lembrando que talvez seja necessário rodar primeiro o comando para mostrar as opções avançadas):
|
1 2 3 |
-- Verificando o Status da OLE AUTOMATION PROCEDURES na instância EXEC sp_configure 'Ole Automation Procedures'; GO |
Abaixo um resumo das procedures que são liberadas após a ativação na instância com os links das respectivas documentações da Microsoft:
Para efetivamente começar a brincadeira, precisamos escolher uma API que gostaríamos de extrair os dados. Atualmente, temos várias APIs abertas para testes e com finalidades educacionais. Para esse nosso exemplo, vou trazer a API de moedas da AwesomeAPI (https://docs.awesomeapi.com.br/api-de-moedas). A API é aberta e podemos encontrar o resultado colocando o endpoint escolhido no navegado (https://economia.awesomeapi.com.br/last/USD-BRL,EUR-BRL,BTC-BRL) mas, como de prache, vamos primeiro enxergar o resultado no Postman:
Agora que sabemos como esperamos o resultado da API, vamos lá no SQL começar os trabalhos. O primeiro passo para o nosso objetivo é criar a “instancia do objeto COM (Component Object Model)” que no nosso caso vamos utilizar o objeto MSXML2.ServerXMLHTTP. Para isso, vamos utilizar a procedure sp_OACreate. Essa procedure basicamente vai dizer pro SQL acionar a API do Windows e criar a instância do objeto que vamos passar como parâmetro. Essa procedure retorna um valor inteiro que vamos armazenar em uma variável de saída (@objecttoken). Essa varíavel será passada como parâmetro em todas as outras procedures.
Outra procedure que já vou comentar é a sp_OADestroy que é chamada passando como parâmetro o @objecttoken que foi retornado na primeira.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @objecttoken INT; --INSTANCIANDO O OBJETO EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @objecttoken OUT; -- PRINTANDO O TOKEN DO OBJETO PRINT(@objecttoken); --LIBERANDO O OBJETO DA MEMÓRIA EXEC sp_OADestroy @objecttoken; |
Entre a “create” e a “destroy” é que vai acontecer a brincadeira para configurarmos os parâmetros da requisição. O que eu quero dizer é que é dessa forma que começa e termina a abertura de um objeto OLE no SQL e o que acontece setamos de configurações entre essas duas é que fará a mágica acontecer.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @objecttoken INT; EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @objecttoken OUT; PRINT(@objecttoken); -- Invocando o método "open", dizendo que faremos uma requisição do tipoe GET e passando a URL como parâmetro. EXEC sp_OAMethod @objecttoken, 'open', NULL, 'GET', 'https://economia.awesomeapi.com.br/last/USD-BRL,EUR-BRL,BTC-BRL', false; -- Invocando o método "send" e fazendo a requisição. EXEC sp_OAMethod @objecttoken, 'send', NULL; -- Se tudo ocorreu, a requisição já foi feita. Mas para recuperarmos o resultado vamos precisar de chamar outra procedure. -- Também vamos precisar de uma outra variável para armazenar o resultado. DECLARE @responseText VARCHAR(8000); EXEC sp_OAGetProperty @objecttoken, 'responseText', @responseText OUT; -- Vamos visualizar o resultado print(@responseText) EXEC sp_OADestroy @objecttoken; |
Dessa forma, você conseguirá visualizar o JSON completo da mesma forma que retornou no Postman.
Observe que tem pouca informação sobre os parâmetros dessas procedures na documentação. Isso ocorre pois elas são genéricas, isto é, servem para abrir dezenas de objetos COM. Deve-se portanto, minerar a informação correta do objeto correspondente na documentação da Microsoft. Por isso, blogs como o do Dirceu Resende, o site stack overflow e agora o meu blog são úteis. Já teve alguém com um determinado problema, que se deu o trabalho de parar, organizar e divulgar para comunidade, fazendo o conhecimento se dissipar.
Abaixo, deixo alguns métodos e propriedades que comumente utilizamos quando estamos trabalhando com esse tipo de objeto COM:
- open Method https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms757849(v=vs.85)
- send Method https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms763706(v=vs.85)
- getProperty Method https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms757029(v=vs.85)
- getResponseHeader Method https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms757006(v=vs.85)
- responseText Property https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms762275(v=vs.85)
- responseXML Property https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms757055(v=vs.85)
- status Property https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms767681(v=vs.85)
Vamos agora interpretar esse JSON recebido em formato tabular? Para isso, vou utilizar a cláusula OPENJSON do SQL Server que está disponível a partir do SQL SERVER 2016.
Link documentação OPENJSON: https://learn.microsoft.com/pt-br/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @objecttoken INT; DECLARE @responseText VARCHAR(8000); EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @objecttoken OUT; EXEC sp_OAMethod @objecttoken, 'open', NULL, 'GET', 'https://economia.awesomeapi.com.br/last/USD-BRL,EUR-BRL,BTC-BRL', false; EXEC sp_OAMethod @objecttoken, 'send', NULL; EXEC sp_OAGetProperty @objecttoken, 'responseText', @responseText OUT; EXEC sp_OADestroy @objecttoken; --Utilizei o CROSS APPLY para fazer o parse de cada moeda SELECT * FROM OPENJSON (@responseText) A CROSS APPLY OPENJSON (A.[value]) WITH ( [code] VARCHAR(255) '$.code', [codein] VARCHAR(255) '$.codein', [name] VARCHAR(255) '$.name', [high] VARCHAR(255) '$.high', [low] VARCHAR(255) '$.low', [varBid] VARCHAR(255) '$.varBid', [pctChange] VARCHAR(255) '$.pctChange', [bid] VARCHAR(255) '$.bid', [ask] VARCHAR(255) '$.ask', [timestamp] VARCHAR(255) '$.timestamp', [create_date] VARCHAR(255) '$.create_date' ) B |
