Partilhar via

Tutorial: Explore e analise data lakes com pool SQL sem servidor

  • Artigo

Neste tutorial, você aprenderá a executar a análise exploratória de dados usando conjuntos de dados abertos existentes, sem necessidade de configuração de armazenamento. Você combina diferentes conjuntos de dados abertos do Azure usando o pool SQL sem servidor. Em seguida, você visualiza os resultados no Synapse Studio for Azure Synapse Analytics.

Neste tutorial:

  • Acesse o pool SQL interno sem servidor
  • Acessar Conjuntos de Dados Abertos do Azure para usar dados de tutorial
  • Executar análise básica de dados usando SQL

Acesse o pool SQL sem servidor

Cada espaço de trabalho vem com um pool SQL sem servidor pré-configurado para você usar chamado Built-in. Para acessá-lo:

  1. Abra seu espaço de trabalho e selecione o hub Revelar .
  2. Selecione o + botão Adicionar novo recurso .'
  3. Selecione Script SQL.

Você pode usar esse script para explorar seus dados sem ter que reservar capacidade SQL.

Se não tiver uma subscrição do Azure, crie uma conta gratuita antes de começar.

Acesse os dados do tutorial

Todos os dados que usamos neste tutorial estão alojados na conta de armazenamento azureopendatastorage, que contém os Conjuntos de Dados Abertos do Azure para uso aberto em tutoriais como este. Você pode executar todos os scripts como estão diretamente do seu espaço de trabalho, desde que ele possa acessar uma rede pública.

Este tutorial usa um conjunto de dados sobre o táxi da cidade de Nova York (NYC):

  • Datas e horários de recolha e entrega
  • Locais de recolha e entrega
  • Distâncias da viagem
  • Tarifas discriminadas
  • Tipos de taxa
  • Tipos de pagamento
  • Contagens de passageiros comunicadas pelo condutor

A OPENROWSET(BULK...) função 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.

Para se familiarizar com os dados do NYC Taxi, 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]

Outros conjuntos de dados acessíveis

Da mesma forma, você pode consultar o conjunto de dados de feriados 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:

Inferência automática de esquema

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

Nota

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

Se você criar um banco de dados com agrupamento que diferencia maiúsculas de minúsculas ao especificar colunas, certifique-se de usar o nome correto da coluna.

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

Séries temporais, sazonalidade e análise de valores atípicos

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 trecho a seguir mostra o resultado para o número anual de corridas de táxi:

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

Os dados podem ser visualizados no Synapse Studio alternando da visualização Tabela para a visualização Gráfico . Você pode escolher entre diferentes tipos de gráficos, como Área, Barra, Coluna, Linha, Torta e Dispersão. Nesse caso, plote o gráfico de colunas com a coluna Categoria definida como current_year:

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

A partir dessa visualização, é possível observar 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 viagens.

Nota

No momento em que escrevo este tutorial, os dados de 2019 estão incompletos. Como resultado, há uma enorme queda no número de viagens para esse ano.

Você pode concentrar a análise em um único ano, por exemplo, 2016. A consulta a seguir retorna 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 trecho a seguir mostra o resultado dessa consulta:

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

Novamente, você pode visualizar dados plotando o gráfico Coluna com a coluna Categoria definida como current_day e a coluna Legenda (série) definida como rides_per_day.

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

No gráfico do gráfico, você pode ver que há um padrão semanal, com os sábados como o dia de pico. Durante os meses de verão, há menos corridas de táxi por causa das férias. Além disso, observe 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 se a queda nas viagens está correlacionada com feriados. Verifique se há uma correlação juntando o conjunto de dados de corridas de táxi de Nova York com o conjunto de dados de feriados:

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 N Y C Corridas de táxi e resultados de conjuntos de dados de feriados.

Destaque o número de corridas de táxi durante os feriados. Para isso, escolha current_day para a coluna Categoria e rides_per_day e holiday_rides como as colunas Legenda (série).

A captura de tela mostra o número de corridas de táxi durante feriados como um gráfico de plotagem.

A partir do gráfico de enredo, você pode ver que durante feriados o número de corridas de táxi é menor. Ainda há uma grande queda inexplicável em 23 de janeiro. Vamos verificar o tempo em Nova York nesse 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áxi ocorreu porque:

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

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

Conteúdos relacionados

Para saber como conectar o pool SQL sem servidor ao Power BI Desktop e criar relatórios, consulte Conectar pool SQL sem servidor ao Power BI Desktop e criar relatórios.

Para saber como usar tabelas externas no pool SQL sem servidor, consulte Usar tabelas externas com Synapse SQL