Esercitazione: Esplorare e analizzare data lake con un pool SQL serverless

  • Articolo

Questa esercitazione illustra come eseguire un'analisi esplorativa dei dati. Per combinare set di dati aperti di Azure diversi, è possibile usare il pool SQL serverless. È quindi possibile visualizzare i risultati in Synapse Studio per Azure Synapse Analytics.

La funzione OPENROWSET(BULK...) consente di accedere ai file di Archiviazione di Azure. [OPENROWSET](develop-openrowset.md) legge il contenuto di un'origine dati remota, ad esempio un file, e lo restituisce come set di righe.

Inferenza automatica dello schema

Poiché i dati vengono archiviati nel formato di file Parquet, è disponibile l'inferenza automatica dello schema. È possibile eseguire query sui dati senza elencare i tipi di dati di tutte le colonne nei file. È anche possibile usare il meccanismo delle colonne virtuali e la funzione filepath per filtrare un determinato sottoinsieme di file.

Nota

Le regole di confronto predefinite sono SQL_Latin1_General_CP1_CI_ASIf. Per le regole di confronto non predefinite, tenere in considerazione la distinzione tra maiuscole e minuscole.

Se si crea un database con regole di confronto con distinzione tra maiuscole e minuscole quando si specificano le colonne, assicurarsi di usare il nome corretto della colonna.

Un nome di colonna tpepPickupDateTime è corretto, mentre tpeppickupdatetime non funziona in regole di confronto non predefinite.

In questa esercitazione si usa un set di dati relativo alle corse dei taxi di New York City (NYC):

  • Date e ore di inizio e fine corsa
  • Luoghi di inizio e fine corsa
  • Distanze delle corse
  • Tariffe dettagliate
  • Tipi di tariffa
  • Tipi di pagamento
  • Numero di passeggeri segnalato dall'autista

Per acquisire familiarità con i dati dei taxi di NYC, eseguire la query seguente:

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

Allo stesso modo, è possibile eseguire una query sul set di dati di festività pubbliche con la query seguente:

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

È anche possibile eseguire una query sul set di dati relativi al meteo con la query seguente:

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

Altre informazioni sul significato delle singole colonne sono disponibili nelle descrizioni dei set di dati:

Analisi di outlier, stagionalità e serie temporali

È possibile riepilogare il numero annuale di corse di taxi con la query seguente:

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

Il frammento seguente mostra il risultato per il numero annuale di corse di taxi:

Screenshot shows a table of yearly number of taxi rides.

I dati possono essere visualizzati in Synapse Studio passando da una visualizzazione Tabella a una visualizzazione Grafico. È possibile scegliere tra diversi tipi di grafico, ad esempio ad area, a barre, istogramma, a linee, a torta e a dispersione. In questo caso, verrà tracciato un istogramma con la colonna Categoria impostata su current_year:

Screenshot shows a column chart that displays rides per year.

In questa visualizzazione è riportata una tendenza di numeri di corse in diminuzione nel corso degli anni. Presumibilmente, questo decremento è dovuto al recente aumento di popolarità delle aziende di ride-sharing.

Nota

Al momento della stesura di questa esercitazione, i dati per 2019 sono incompleti. Di conseguenza, è presente un enorme calo nel numero di corse per quell'anno.

È possibile incentrare l'analisi su un singolo anno, ad esempio il 2016. La query seguente restituisce il numero giornaliero di corse durante l'anno:

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

Il frammento seguente mostra il risultato di questa query:

Screenshot shows a table of the daily number of rides for 2016 result.

Anche in questo caso, è possibile visualizzare i dati tracciando l'istogramma con la colonna Categoria impostata su current_day e la colonna Legenda (serie) su rides_per_day.

Screenshot shows a column chart that displays the daily number of rides for 2016.

Dal grafico del tracciato si evince un modello settimanale, con il sabato come giorno di punta. Durante i mesi estivi, il numero di corse di taxi è inferiore a causa delle vacanze. Sono anche visibili anche alcuni cali significativi nel numero di corse di taxi senza un modello chiaro che evidenzi quando e perché si verificano.

Verrà ora illustrata la correlazione tra il calo del numero di corse e le festività pubbliche. Verificare se esiste una correlazione incrociando il set di dati delle corse dei taxi di NYC con quello delle festività pubbliche:

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

Screenshot shows a table of N Y C Taxi rides and Public Holidays datasets result.

Evidenziare il numero di corse di taxi durante le festività pubbliche. A tale scopo, scegliere current_day per la colonna Categoria e rides_per_day e holiday_rides come colonne Legenda (serie).

Screenshot shows the number of taxi rides during public holidays as a plot chart.

Dal tracciato, si osserva che durante le festività pubbliche il numero di corse di taxi è inferiore. Il 23 gennaio si registra un importante calo inspiegabile. A questo punto, verrà controllato il meteo di New York per il giorno in questione tramite una query sul set di dati meteo:

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'

Screenshot shows a Weather Data dataset result visualization.

I risultati della query indicano che il calo del numero di corse di taxi è stato dovuto a:

  • Una bufera di neve su New York, con una pesante nevicata (circa 30 cm).
  • Una temperatura particolarmente fredda, inferiore a zero gradi Celsius.
  • Un vento forte (circa 10 m/s).

Questa esercitazione ha illustrato in che modo un analista di dati può eseguire rapidamente analisi esplorative dei dati. È possibile combinare set di dati diversi usando il pool SQL serverless e visualizzare i risultati con Azure Synapse Studio.

Passaggi successivi

Per informazioni su come connettere il pool SQL serverless a Power BI Desktop e creare report, vedere l'articolo Connettere un pool SQL serverless a Power BI Desktop e creare report.

Per informazioni su come usare tabelle esterne nel pool SQL serverless, vedere Usare tabelle esterne con Synapse SQL