Tutorial: Explorar e Analisar data lakes com o conjunto de SQL sem servidor

  • Artigo

Neste tutorial, vai aprender a realizar análises de dados exploratórias. Pode combinar conjuntos de dados do Azure Open Datasets diferentes com o conjunto de SQL sem servidor. Em seguida, visualize os resultados no Synapse Studio para o Azure Synapse Analytics.

A OPENROWSET(BULK...) função permite-lhe aceder a ficheiros no Armazenamento do Azure. [OPENROWSET](develop-openrowset.md) lê o conteúdo de uma origem de dados remota, como um ficheiro, e devolve o conteúdo como um conjunto de linhas.

Inferência automática do esquema

Uma vez que os dados são armazenados no formato de ficheiro Parquet, a inferência automática de esquemas está disponível. Pode consultar os dados sem listar os tipos de dados de todas as colunas nos ficheiros. Também pode utilizar o mecanismo de coluna virtual e a filepath função para filtrar um determinado subconjunto de ficheiros.

Nota

O agrupamento predefinido é SQL_Latin1_General_CP1_CI_ASIf. Para um agrupamento não predefinido, tenha em conta a sensibilidade a maiúsculas e minúsculas.

Se criar uma base de dados com agrupamento sensível a maiúsculas e minúsculas quando especificar colunas, certifique-se de que utiliza o nome correto da coluna.

Um nome tpepPickupDateTime de coluna estaria correto e tpeppickupdatetime não funcionaria num agrupamento não predefinido.

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

  • Datas e horas de recolha e entrega
  • Localizações de recolha e entrega
  • Distâncias de viagem
  • Tarifas itemizadas
  • Tipos de taxa
  • Tipos de pagamento
  • Contagens de passageiros reportadas pelo condutor

Para se familiarizar com os dados do Táxi 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, pode consultar o conjunto de dados Feriados Públicos com a seguinte consulta:

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

Também pode consultar o conjunto de dados de Dados Meteorológicos com a seguinte consulta:

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

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

Série temporal, sazonalidade e análise atípica

Pode resumir o número anual de viagens de táxi com 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 fragmento seguinte mostra o resultado do número anual de viagens de táxi:

Captura de ecrã a mostrar uma tabela do número anual de viagens de táxi.

Os dados podem ser visualizados em Synapse Studio ao mudar da Tabela para a vista Gráfico. Pode escolher entre diferentes tipos de gráficos, como Área, Barra, Coluna, Linha, Circular e Dispersão. Neste caso, desenhe o Gráfico de colunas com a coluna Categoria definida como current_year:

Captura de ecrã a mostrar um gráfico de colunas que apresenta os passeios por ano.

Nesta visualização, pode ver uma tendência de diminuição do número de passeios ao longo dos anos. Presumivelmente, esta diminuição deve-se ao recente aumento da popularidade das empresas de partilha de boleias.

Nota

No momento da escrita deste tutorial, os dados de 2019 estão incompletos. Como resultado, há uma enorme queda no número de passeios para esse ano.

Pode concentrar a análise num único ano, por exemplo, 2016. A consulta seguinte devolve o número diário de viagens durante esse 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 fragmento seguinte mostra o resultado desta consulta:

Captura de ecrã a mostrar uma tabela do número diário de viagens para o resultado de 2016.

Mais uma vez, pode visualizar os dados ao desenhar o Gráfico de colunas com a coluna Categoria definida como current_day e a coluna Legenda (série) definida como rides_per_day.

Captura de ecrã a mostrar um gráfico de colunas que apresenta o número diário de viagens para 2016.

No gráfico de gráficos, pode ver que existe um padrão semanal, com os sábados como o dia de pico. Durante os meses de verão, há menos passeios de táxi por causa das férias. Além disso, observe algumas quedas significativas no número de passeios de táxi sem um padrão claro de quando e por que ocorrem.

Em seguida, veja se a queda nos passeios está relacionada com os feriados públicos. Verifique se existe uma correlação ao associar o conjunto de dados Passeios de táxi de NYC com o conjunto de dados 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

Captura de ecrã a mostrar uma tabela de passeios de táxi N Y C e de conjuntos de dados de Feriados Públicos.

Realce o número de passeios de táxi durante os feriados públicos. Para essa finalidade, selecione current_day para a coluna Categoria e rides_per_day e holiday_rides como as colunas Legenda (série ).

Captura de ecrã a mostrar o número de viagens de táxi durante os feriados públicos como gráfico de desenho.

No gráfico de gráficos, pode ver que durante os feriados públicos o número de viagens de táxi é menor. Ainda há uma grande queda inexplicável no dia 23 de Janeiro. Vamos verificar a meteorologia em NOVA IORQUE nesse dia ao consultar o conjunto de dados 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'

Captura de ecrã a mostrar uma visualização do resultado do conjunto de dados Dados meteorológicos.

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

  • Houve uma tempestade naquele dia em NYC com neve pesada (~30 cm).
  • Estava frio (a temperatura estava abaixo de zero graus Celsius).
  • Estava ventoso (~10 m/s).

Este tutorial mostrou como um analista de dados pode realizar rapidamente análises de dados exploratórias. Pode combinar conjuntos de dados diferentes com o conjunto de SQL sem servidor e visualizar os resultados com o Azure Synapse Studio.

Passos seguintes

Para saber como ligar o conjunto de SQL sem servidor ao Power BI Desktop e criar relatórios, veja Ligar o conjunto de SQL sem servidor para Power BI Desktop e criar relatórios.

Para saber como utilizar tabelas externas no conjunto de SQL sem servidor, veja Utilizar tabelas externas com o Synapse SQL