Importando dados JSON da Premier League com SQL Server 2016

Quando pensamos em projetos de BI e Big Data, um dos principais pontos que temos que planejar são as fontes de dados que iremos utilizar, estas que contém os dados que precisamos consumir para gerar as análises e os relatórios. E cada vez mais temos que trabalhar com formatos diferentes, devido às novas maneiras de armazenar as informações que existem.

O SQL Server 2016, visando se adaptar à esta realidade, traz nesta versão diversas funcionalidades e recursos novos, entre eles a possibilidade de manipular arquivos JSON. Podemos não somente importar arquivos, como também escrever consultas para retornar informações destes arquivos JSON e até gerar saídas neste formato, parecido com o que já existe com o formato XML.

Para exemplificar esta funcionalidade, realizei uma consulta à API do Fantasy Premier League 15/16, que pode ser acessada através da URL a seguir, e que gera um arquivo JSON para cada jogador (no exemplo abaixo, dados do jogador de id 170):

http://fantasy.premierleague.com/web/api/elements/170 *

Para quem não conhece, o Fantasy da Premier League é um game onde o desempenho dos jogadores de futebol que jogam no Campeonato Inglês valem pontos, ou seja, eles marcam pontos de acordo com as suas estatísticas. E são exatamente estas estatísticas que nós vamos importar para o SQL Server.

Utilizei o script abaixo para realizar a importação:

—Criando banco de dados de exemplo

CREATE DATABASE Sports

GO

USE Sports

GO

—Criando tabela com coluna DOC no formato JSON

CREATE TABLE Player

(id INT IDENTITY CONSTRAINT PK_JSON PRIMARY KEY,

DOC NVARCHAR(MAX) constraint [Properly formatted JSON] CHECK (ISJSON(DOC)>0));

—Gerando loop para carregar todos os arquivos para o SQL Server

declare @i int = 1

declare @file nvarchar(max)

declare @json nvarchar(MAX)

declare @sql nvarchar(max)

WHILE(@i<724)

BEGIN

SET @file = ”’c:\json\’ + cast(@i as varchar(5)) + ‘.json”’;

SET @sql = ‘INSERT INTO Player

SELECT BulkColumn

FROM OPENROWSET (BULK ‘+@file+‘, SINGLE_CLOB) as j’

SET @i = @i +1;

EXEC sp_executesql @sql

END

Assim, conseguimos ter os arquivos JSON de todos os jogadores dentro do nosso banco de dados do SQL Server 2016!

Podemos, por exemplo, realizando a consulta abaixo, retornar os 10 jogadores do Leicester City que mais fizeram gols no campeonato:

—Consulta para retornar os 10 jogadores do Leicester City que mais marcaram gols

SELECT

TOP 10

JSON_VALUE(doc,‘$.web_name’) AS player_name,

JSON_VALUE(doc,‘$.goals_scored’) AS goals_scored

FROM Player p

WHERE JSON_VALUE(doc,‘$.team_name’) = ‘Leicester’

ORDER BY cast(JSON_VALUE(doc,‘$.goals_scored’)as int) DESC

Resultado (imagem abaixo):

gols_leicester_json_query

Para facilitar, disponibilizei os códigos e os arquivos utilizados no seguinte diretório do GitHub:

GitHub – Premier League Datasets

* Atualização: houve uma mudança na URL da API do Fantasy Premier League para a temporada 16/17. Em vez da URL antiga (http://fantasy.premierleague.com/web/api/elements/170), utilizar a nova (https://fantasy.premierleague.com/drf/element-summary/170). Para reproduzir o script, utilizar arquivos .json que se encontram no GitHub.

Referência:

Importing JSON files into SQL Server using OPENROWSET (BULK)

Sugestão de leitura relacionada:

JSON support in SQL Server 2016

Anúncios

2 pensamentos sobre “Importando dados JSON da Premier League com SQL Server 2016

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s