Consumo de uma API com SQL Server - Parte-2

Prévia do material em texto

Web Service Consumindo API/JSon/SQL Server 1
 
O objetivo desse exercício é mostrar a possibilidade de consumir uma API dentro do SQL 
Service, para poder fazer uma integração rápida com WebService, pega a informação que pode 
ser gravada em tabelas que podem ser temporárias e utiliza as informações sem ter que 
necessariamente desenvolver uma rotina em linguagem específica, não precisa compilar. 
 
Reforçando o termo Web Service  é uma solução utilizada na integração de sistemas 
e na comunicação entre aplicações diferentes. Com esta tecnologia é possível que novas 
aplicações possam interagir com aquelas que já existem e que sistemas desenvolvidos em 
plataformas diferentes sejam compatíveis. 
 
Reforçando o termo API Interface de Programação de Aplicações ou tambem 
traduzido como Interface de Programação de Aplicação, cuja sigla API provém do Inglês 
Application Programming Interface, é um conjunto de rotinas e padrões estabelecidos por um 
software para a utilização das suas funcionalidades por aplicativos que não pretendem envolver-
se em detalhes da implementação do software, mas apenas usar seus serviços. 
 
Vai ser o nosso guia de teste para consumir um WebService dentro do SQL Server, com 
utilização de APIs de ambientes conhecidos, como Google, vamos passar um endereço, será 
devolvida como resposta em um JSON, dentro desse JSON terá informações por exemplo de 
latitude e Longitude, que será digitada no navegador Google Maps e será retornada uma posição 
dentro do Google Maps. 
 
O que é Google Maps  é um serviço de pesquisa e visualização de mapas e imagens 
de satélite da Terra gratuito na web fornecido e desenvolvido pela empresa estadunidense 
Google. Atualmente, o serviço disponibiliza mapas e rotas para qualquer ponto nos Estados 
Unidos, Canadá, na União Europeia, Austrália e Brasil, entre outros. 
 
Reforçando o conceito de JSON  Em computação, JSON, um acrônimo de JavaScript 
Object Notation, é um formato compacto, de padrão aberto independente, de troca de dados 
simples e rápida entre sistemas, especificado em 2000, que utiliza texto legível a humanos, no 
formato atributo-valor. 
 
 Abrir o 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 2
 
Aplicando o Código 1 
 
Cod1.txt - Vamos abrir um script e iniciar habilitando o OLE Automation, para utilizar as 
procedures. 
 
O SQL Server aceita os seguintes procedimentos armazenados de sistema que permitem 
o uso de objetos de automação OLE em um lote do Transact-SQL. Por padrão, o SQL Server 
impede o acesso a procedimentos armazenados de automação OLE, pois este componente está 
desligado como parte da configuração de segurança do servidor. Um administrador de sistema 
pode ativar o acesso a procedimentos de automação OLE usando sp_configure. 
 
Se não for habilitado o OLE o SQL Server emitira uma mensagem de erro que não é 
possível executar as rotinas que serão apresentadas para consumir a API. O erro é de que não 
tem permissão. 
 
 
-- enable Ole Automation Stored Procedures -- 
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1; 
GO 
RECONFIGURE; 
GO 
 
 
 
 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 3
 
Antes de seguir em frente com o consumo da API, algumas alterações podem fazer diferença 
na questão de confiabilidade e desempenho: 
 
 Exec sys.sp_configure N’max degree of parallelism’, N’8; 
 
 Nessa linha teremos a importância, principalmente em ambiente Virtuais “ bare metal 
“ que é muito utilizado, atualmente o assistente de instalação do SQL Server configura 
isso automaticamente, mas em uma Máquina Virtual é muito menos intuitivo. 
 
Exemplificando: O seu Servidor SQL tem oito núcleos, olhe para o processador fatiado 
(núcleos). Quantas consultas você deseja executar? Ao mesmo tempo, você pode deixar 
o máximo grau de paralelismo em 8 ou 0(ilimitado), mas não são a mesma coisa em 
termos de resultados, se você deixar em oito, ele poderá fazer uso de todos os 8 núcleos, 
a idéia é não ter apenas uma consulta executada ao mesmo tempo, geralmente a 
preocupação é com muitas consultas feitas sendo executadas ao mesmo tempo. 
 
 Na prática, pegue a quantidade de núcleos que o seu processador tem (e que a VM 
reconhece) e faça uma divisão simples, exemplo: Se for fazer duas consultas ao mesmo 
tempo, então, cada consulta usaria 4 núcleos na especificação de “max degree of 
parallelism” em 4. 
 
Se fosse fazer muitas consultas ao mesmo tempo, oito ou mais, poderia então definir o 
grau máximo em apenas 1, para que qualquer um esteja executando. A maioria das 
aplicações (pacote de softwares) funcionam, como por exemplo o Microsoft 
Dynamics,ou Sharepoint, quando você instala tem a informação que permite fazer 
apenas uma consulta em execução que executa apenas um núcleo por vez. 
 
Então na análise de dados junto ao cliente deve ser discutida a necessidade de consultas 
esperadas, lembrando que como no exemplo desses pacotes seria impossível mudar a 
forma de consulta de limitado para ilimitado. É difícil explicar isso para o cliente, mas 
por exemplo, você não coloca um número ilimitado de pessoas em cima de uma 
motocicleta, e espera que essa motocicleta atinja uma velocidade máxima. Para uma 
máquina VM o número máximo pode ser especificada em 8, segundo análises de testes 
já executados. 
 
 Lembrete! Nesse exemplo de consultas consumindo API, o máximo considerado de 
volume de dados seria de 50 a 250 GB. 
 
 
 EXEC sys.sp_configure N’backup checksum default’, N’1’; /* 2014 
 
 Padrão de soma de verificação de backup: acontece para cada consulta individual, esse 
recurso foi introduzido no SQL 2014 ou mais recente, se for considere colocar essa linha, 
se não, apenas coloque um comentário como é o nosso caso em que estamos utilizando 
o SQL Server 2008 nesse exemplo em aula. Lembrando que optei por essa versão devido 
a espaço disponível em memória e em disco ficando assim mais leve para fazermos estes 
de teorias na prática. 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 4
 
 /* Set max server memory to 90% of server memory */ 
 
 Aqui você considera a memória máxima do servidor em 90% de utilização de memória 
RAM em um servidor, na prática o SQL Server irá consumir cada byte disponível e isso 
pode causar problemas na área de trabalho se você estiver executando outros 
aplicativos ou se existirem outros serviços instalados nessa VM. 
 
Nesse momento estamos em uma situação parecida pois estou com o SQL Management 
Studio aberto, a utilização de memória começa a ficar sobre pressão e alguns processos 
podem ser desfeitos e a máquina local e a VM apresentam lentidão. 
 
 + CAST(CAST(PHYSICAL_MEMORY_KB / 1024 * .9 AS  se achar melhor balancear 
a utilização pode ser feito aqui onde no lugar de “.9” poderia colocar “.8” para 80%, 
verifique a situação local de disponibilidade de memória RAM. 
 
 EXEC sys.sp_configure N'show advanced options', N'1'; 
GO 
RECONFIGURE 
 
 Alguns comentários sobre opções avançadas quando você instala o SQL Server pela 
primeira vez, são algumas configurações de “sp.”, caso queira fazer alguma alteração 
após instalação. Temos que ligar para depois fazer as alterações, por isso temos o “EXEC 
e depois o RECONFIGURE”. – LIGANDO E EXECUTANDO O RECONFIGURE – 
 
 EXEC sys.sp_configure N'cost threshold for parallelism', N'50'; 
EXEC sys.sp_configure N'remote admin connections', N'1'; 
EXEC sys.sp_configure N'backup compression default', N'1'; 
 
 Por padrão o SQL Server fará backup de dados corrompidos e sempre lançando avisos, 
e se perceber um evento em um campo de data é uma data válida irá enviar para o 
backup e termina a transação. 
 
 Seria mais produtivo se o SQL verificasse com mais detalhes, sempre, para ter certeza 
de que o dado não está corrompido. Então por padrão ele não verifica se há dados 
corrompidos durante os backups. Talvez esteja relacionado a desempenho, assimse 
descuidando um pouco da segurança. “N'cost threshold for parallelism'” – limite de 
custo para o paralelismo – 
 
 Conexões de Administrador Remoto – para ter certeza da autoridade e conexão. 
N'remote admin connections' 
 
 E como padrão a compressão de Backup – “ N'backup compression default' “ 
 
 Então, essas são as configurações a serem alteradas depois que o seu SQL estiver pronto 
para começar a produzir. 
 
 Obs... A edição Standard não pode criar backups compactados. É um recurso exclusivo 
para empresas no SQL 2008 (está na Standard Edition no SQL 2008 R2), por esse motivo 
vamos deixar a linha comentada em nosso exemplo. 
 
 
Web Service Consumindo API/JSon/SQL Server 5
 
Abra uma nova consulta, copie e cole o código abaixo, depois clique em 
 
/* cod2.txt */ 
EXEC sys.sp_configure N'show advanced options', N'1'; 
GO 
RECONFIGURE 
GO 
EXEC sys.sp_configure N'max degree of parallelism', N'2'; 
 
/* EXEC sys.sp_configure N'backup checksum default', N'1'; /* 2014 & newer only */ */ 
 
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'; 
EXEC sys.sp_configure N'remote admin connections', N'1'; 
/* EXEC sys.sp_configure N'backup compression default', N'1'; */ 
 
/* Set max server memory to 90% of server memory */ 
DECLARE @StringToExecute NVARCHAR(400); 
SELECT @StringToExecute = N'EXEC sys.sp_configure N''max server memory (MB)'', N''' 
 + CAST(CAST(physical_memory_in_bytes / 1024 * 0.4 AS INT) AS NVARCHAR(20)) + N''';' 
 FROM sys.dm_os_sys_info; 
EXEC(@StringToExecute); 
GO 
RECONFIGURE; 
GO 
 
 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 6
 
Resultados finais das alterações avançadas pós instalação 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 7
 
 
Iniciando a rotina de consumo da API da Google para obter alguns dados que serão 
posteriormente aplicados no Google MAPS, lembrando que os dados serão devolvidos em 
JSON. 
 
Declarando as variáveis: 
 
DECLARE 
/* Aqui foi declarado um toquem quando for criado a OLE será devolvido um objeto ID, esse ID 
com esse token é o OLE Object que foi criado nesse momento para ser utilizado. */ 
 @intToken INT, 
/* O endereço que será procurado, passa esse endereço para chamar a Webservice do Google 
e será devolvida a Latitude e Longitude. */ 
 @vchEndereco VARCHAR(MAX), 
/*É a URL da API do Google onde será feita a chamada para que devolva o JSON. */ 
 @vchURL AS VARCHAR(MAX), 
/* Aqui será guardada a API de Retorno, quando houver a devolução será colocada nessa 
variável. */ 
 @vchJSON AS VARCHAR(8000), 
/* Depois que estiver com o resultado do JSON será testado o status se esta ok */ 
 @vchStatus AS VARCHAR(MAX), 
/* Para checar a quantidade de resultados, as vezes o endereço pode ser comum e vão chegar 
vários resultados, no nosso exemplo vamos pegar o TOP 1 */ 
 @intQtdeResultados AS INT, 
 
/* è a parte de mapear o JSON, que por sua vez devolve “Results” uma geometria que por sua 
vez tem uma localização (dentro) e terá como conteúdo a latitude e a Longitude. */ 
 --Mapear JSON 
 @vchJSONResults AS VARCHAR(MAX), 
 @vchJSONResultsGeometry AS VARCHAR(MAX), 
 @vchJSONResultsLocation AS VARCHAR(MAX), 
 @fltLatitude FLOAT, 
 @fltLongitude FLOAT; 
/* obs. As variáveis são para facilitar o entendimento os eventos da consulta */ 
 
/* iniciando, setar as variáveis /* 
/* Aqui indica um endereço, poderia ser a base de dados ai especificaria uma string */ 
SET @vchEndereco = 'Avenida Pereira Barreto, 600 - Baeta Neves, São Bernardo do Campo - SP 
'; 
 
/* URL é a do WebService, em nosso exemplo é o da Google, passando o address no final da 
linha */ 
 SET @vchURL = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + 
@vchEndereco; 
 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 8
 
/* Procedure, vai ser executado “EXEC”, a procedure sp-OACreate, possibilita criar um Objeto 
OLE Automation, aqui é criado o objeto e é devolvido o ID para que possa ser utilizado nas 
próximas Procedures, tem que passar o tipo que nesse caso é 'MSXML2.XMLHTTP' ( Você pode 
usar o objeto ServerXMLHTTP do Microsoft XML (MSXML) para executar uma requisição HTTP 
(POST,GET e PUT).) */ 
/*OLE Automation é mecanismo para a comunicação entre processos baseado em Component 
Object Model (COM) */ 
/* Is the returned object token, and must be a local variable of data type int. This object token 
identifies the created OLE object and is used in calls to the other OLE Automation stored 
procedures. */ 
/* obs... a variável @intToken é OUT */ 
 EXEC sp_OACreate 'MSXML2.XMLHTTP', @intToken OUT; 
 
/* nessa linha abaixo então terá o token do objeto OLE, aqui será executada uma outra 
procedure onde é passado o ID do objeto, e é feita a chamada no método “open” que é abrir e 
será utilizado o método gateway, ai passa a URL da API da Webservice “que é a da Google” já 
com o endereço na frente */ 
/* Chamada para método OPEN */ 
 EXEC sp_OAMethod @intToken, 'open', NULL, 'get', @vchURL, 'false'; 
 
/* Em seguida será executado o método “send”, Webservice */ 
/* Chamada para método SEND */ 
 EXEC sp_OAMethod @intToken, 'send'; 
 
/* e por último o método responde text, aqui será consumida e colocada dentro da variável 
“comando OUTPUT”, aqui vai carregar o JSON com o resultado encontrado */ 
/* Chamada para método RESPONSE TEXT */ 
 EXEC sp_OAMethod @intToken, 'responseText', @vchJSON OUTPUT; 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Web Service Consumindo API/JSon/SQL Server 9
 
Como dar tratamento ao mapeamento do JSON 
 
--Site para visualizar JSON http://jsonviewer.stack.hu/ 
 SELECT @vchJSON; 
 
--Mapear o JSON. 
 
--Checar se é um JSON válido 
 IF (ISJSON(@vchJSON) = 1) 
 BEGIN 
--Verificar se o status é OK (sucesso), ou seja, se a chamada foi realizada com sucesso 
 SET @vchStatus = (SELECT TOP 1 [value] FROM OPENJSON(@vchJSON) WHERE [key] = 
'status'); 
 
 IF (@vchStatus = 'OK') 
 BEGIN 
 
 --Verificar a quantidade de resultados dentro do JSON. 
 SET @intQtdeResultados = (SELECT COUNT([key]) FROM 
OPENJSON(@vchJSON, '$.results')); 
 
 IF (@intQtdeResultados = 1) 
 BEGIN 
 
 SET @vchJSONResults = (SELECT TOP 1 [value] FROM 
OPENJSON(@vchJSON, '$.results')); 
 
 SET @vchJSONResultsGeometry = (SELECT TOP 1 [value] FROM 
OPENJSON(@vchJSONResults) WHERE [key] = 'geometry'); 
 
 SET @vchJSONResultsLocation = (SELECT TOP 1 [value] FROM 
OPENJSON(@vchJSONResultsGeometry) WHERE [key] = 'location'); 
 
 SET @fltLatitude = (SELECT top 1 [value] FROM 
OPENJSON(@vchJSONResultsLocation) WHERE [key] = 'lat'); 
 
 SET @fltLongitude = (SELECT top 1 [value] FROM 
OPENJSON(@vchJSONResultsLocation) WHERE [key] = 'lng'); 
 
 SELECT 
 @fltLatitude AS Latitude, 
 @fltLongitude AS Longitude, 
 
 'https://www.google.com/maps/search/?api=1&query=' + CAST(@fltLatitude AS 
VARCHAR) + ',' + CAST(@fltLongitude AS VARCHAR); 
 END 
 
 END 
 
 END 
 
 EXEC sp_OADestroy @intToken; 
Web Service Consumindo API/JSon/SQL Server 10