Compartilhar via

Tutorial: Explorar e analisar data lakes com o pool de SQL sem servidor

  • Artigo

Neste tutorial, você aprenderá a executar a análise de dados exploratória. Você combina diferentes Azure Open Datasets usando um pool de SQL sem servidor. Depois, você visualizará os resultados no Synapse Studio para o Azure Synapse Analytics.

A função OPENROWSET(BULK...) permite que você acesse arquivos no Armazenamento do Azure. [OPENROWSET](develop-openrowset.md) lê o conteúdo de uma fonte de dados remota, como um arquivo, e retorna o conteúdo como um conjunto de linhas.

Inferência de esquema automática

Como os dados são armazenados no formato de arquivo parquet, a inferência de esquema automática está disponível. Você pode consultar os dados sem a necessidade de listar os tipos de dados de todas as colunas nos arquivos. Você também pode utilizar o mecanismo de coluna virtual e a função filepath para filtrar um determinado subconjunto de arquivos.

Observação

O agrupamento padrão é SQL_Latin1_General_CP1_CI_ASIf. Para uma ordenação não padrão, leve em conta a diferenciação de maiúsculas e minúsculas.

Se você criar um banco de dados com uma ordenação com diferenciação de maiúsculas e minúsculas, use o nome correto da coluna ao especificar as colunas.

Um nome de coluna tpepPickupDateTime estaria correto enquanto tpeppickupdatetime não funcionasse em um agrupamento não padrão.

Este tutorial usa um conjunto de dados sobre o Táxi de Nova Iorque (NYC):

  • Datas e horas da retirada e entrega
  • Localizações da retirada e entrega
  • Distâncias das viagens
  • Tarifas discriminadas
  • Tipos de taxa
  • Tipos de pagamento
  • Contagens de passageiro relatadas pelos motoristas

Para familiarizar-se com os dados de táxis de NYC, execute a seguinte consulta:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

Da mesma forma, podemos consultar o conjunto de dados de feriados públicos usando a seguinte consulta:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

Você também pode consultar o conjunto de dados meteorológicos usando a seguinte consulta:

SELECT
    TOP 100 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

Você pode saber mais sobre o significado das colunas individuais nas descrições dos conjuntos de dados:

Análise de série temporal, sazonalidade e exceções

Você pode resumir o número anual de corridas de táxi usando a seguinte consulta:

SELECT
    YEAR(tpepPickupDateTime) AS current_year,
    COUNT(*) AS rides_per_year
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC

O seguinte snippet mostra o resultado do número anual de corridas de táxi:

A captura de tela mostra uma tabela de número anual de corridas de táxi.

Os dados podem ser visualizados no Synapse Studio, alternando da exibição de Tabela para a exibição de gráfico. Você pode escolher entre diferentes tipos de gráfico, tais como: área, barras, colunas, linha, pizza e dispersão. Neste caso, plote o gráfico de colunas com a coluna Categoria definida com o valor current_year:

A captura de tela mostra um gráfico de colunas que exibe corridas por ano.

Nessa visualização, veja uma tendência de diminuição dos números de corridas ao longo dos anos. Supostamente, essa queda se deve ao recente aumento da popularidade das empresas de compartilhamento de caronas.

Observação

No momento da elaboração deste tutorial, os dados de 2019 ainda estavam incompletos. Como resultado, há uma enorme queda no número de corridas desse ano.

Você pode concentrar a análise em um ano específico, por exemplo: 2016. A seguinte consulta retorna o número diário de corridas durante aquele ano:

SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC

O seguinte snippet mostra o resultado dessa consulta:

A captura de tela mostra uma tabela do número diário de corridas para o resultado de 2016.

Novamente, você pode visualizar os dados plotando o gráfico de colunas com a coluna Categoria definida com o valor current_day e a coluna Legend (series) definida com o valor rides_per_day.

A captura de tela mostra um gráfico de colunas que exibe o número diário de corridas de 2016.

No gráfico de plotagem, você pode ver que há um padrão semanal, com pico diário aos sábados. Durante os meses de verão, há menos corridas de táxi devido ao período de férias. Há também algumas quedas significativas no número de corridas de táxi sem um padrão claro de quando e por que elas ocorrem.

Em seguida, veja a queda nas corridas se correlaciona com feriados públicos. Verifique se há uma correlação unindo o conjunto de dados de corridas de Táxi de NYC com o conjunto de dados de feriados públicos:

WITH taxi_rides AS (
SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS (
SELECT
    holidayname as holiday,
    date
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]
WHERE countryorregion = 'United States' AND YEAR(date) = 2016
),
joined_data AS (
SELECT
    *
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
)

SELECT 
    *,
    holiday_rides = 
    CASE   
      WHEN holiday is null THEN 0   
      WHEN holiday is not null THEN rides_per_day
    END   
FROM joined_data
ORDER BY current_day ASC

A captura de tela mostra uma tabela de corridas de táxi de NYC e os resultados dos conjuntos de dados de feriados públicos.

Destaque o número de corridas de táxi durante os feriados públicos. Para isso, escolha current_day para a coluna Categoria e rides_per_day e holiday_rides como as colunas Legend (series).

Captura de tela que mostra o número de corridas de táxi durante feriados públicos como um gráfico de caixa.

No gráfico de plotagem, é possível ver que, durante os feriados públicos, o número de corridas de táxi é menor. No entanto, ainda há uma grande queda não explicada no dia 23 de janeiro. Vamos examinar o clima em NYC naquele dia consultando o conjunto de dados meteorológicos:

SELECT
    AVG(windspeed) AS avg_windspeed,
    MIN(windspeed) AS min_windspeed,
    MAX(windspeed) AS max_windspeed,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(sealvlpressure) AS avg_sealvlpressure,
    MIN(sealvlpressure) AS min_sealvlpressure,
    MAX(sealvlpressure) AS max_sealvlpressure,
    AVG(precipdepth) AS avg_precipdepth,
    MIN(precipdepth) AS min_precipdepth,
    MAX(precipdepth) AS max_precipdepth,
    AVG(snowdepth) AS avg_snowdepth,
    MIN(snowdepth) AS min_snowdepth,
    MAX(snowdepth) AS max_snowdepth
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'

A captura de tela mostra uma visualização do resultado do conjunto de dados meteorológicos.

Os resultados da consulta indicam que a queda no número de corridas de táxis ocorreu porque:

  • houve uma nevasca de alta intensidade (aprox. 30 cm de espessura) naquele dia em NYC.
  • Fez muito frio (a temperatura ficou abaixo de zero graus Celsius).
  • Vento muito (aproximadamente 10 m/s).

Este tutorial mostrou como um analista de dados pode executar rapidamente uma análise exploratória de dados. Você pode combinar conjuntos de dados diferentes usando o pool de SQL sem servidor e visualizar os resultados usando o Azure Synapse Studio.

Próximas etapas

Para aprender como conectar o pool de SQL sem servidor ao Power BI Desktop e criar relatórios, confira o artigo Conectar o pool de SQL sem servidor ao Power BI Desktop e criar relatórios.

Para saber como usar tabelas externas no pool de SQL sem servidor, confira Usar tabelas externas com o SQL do Synapse