Tutoriel : Explorer et analyser des lacs de données avec un pool SQL serverless
Dans ce tutoriel, vous allez apprendre à effectuer des analyses de données exploratoires. Vous combinez différents jeux de données Azure Open Datasets à l’aide d’un pool SQL serverless. Vous visualisez ensuite les résultats dans Synapse Studio pour Azure Synapse Analytics.
La fonction OPENROWSET(BULK...) vous permet d’accéder à des fichiers dans Stockage Azure. [OPENROWSET](develop-openrowset.md) lit le contenu d’une source de données distante (par exemple, un fichier) et retourne le contenu sous la forme d’un ensemble de lignes.
Inférence de schéma automatique
Les données étant stockées au format de fichier Parquet, l’inférence de schéma automatique est disponible. Il est donc possible d’interroger les données sans lister les types de données de toutes les colonnes dans les fichiers. Vous pouvez aussi utiliser le mécanisme de colonne virtuelle et la fonction filepath pour filtrer un certain sous-ensemble de fichiers.
Notes
Le classement par défaut est SQL_Latin1_General_CP1_CI_ASIf. Pour un classement non par défaut, prenez en compte la sensibilité de la casse.
Si vous créez une base de données avec un classement respectant la casse, quand vous spécifiez des colonnes, veillez à utiliser le nom correct de ces dernières.
Un nom de colonne tpepPickupDateTime serait correct, alors que tpeppickupdatetime ne fonctionnerait pas dans un classement autre que celui par défaut.
Ce tutoriel utilise un jeu de données sur New York City (NYC) Taxi :
- Dates et heures de début et de fin de trajet
- Emplacements de début et de fin de trajet
- Distance des trajets
- Tarifs détaillés
- Types de tarifs
- Types de paiement
- Nombre de passagers indiqué par le chauffeur
Pour vous familiariser avec les données NYC Taxi, exécutez la requête suivante :
SELECT TOP 100 * FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
FORMAT='PARQUET'
) AS [nyc]
De même, vous pouvez interroger le jeu de données sur les jours fériés à l’aide de la requête suivante :
SELECT TOP 100 * FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
FORMAT='PARQUET'
) AS [holidays]
Vous pouvez également interroger le jeu de données sur les données météorologiques à l’aide de la requête suivante :
SELECT
TOP 100 *
FROM
OPENROWSET(
BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
FORMAT='PARQUET'
) AS [weather]
Vous pouvez en savoir plus sur la signification des différentes colonnes dans les descriptions des jeux de données :
Analyse des séries chronologiques, de la saisonnalité et des valeurs hors norme
Vous pouvez totaliser le nombre annuel de courses de taxi à l’aide de la requête suivante :
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
L’extrait de code suivant présente le résultat pour le nombre annuel de courses de taxi :
Vous pouvez visualiser les données dans Synapse Studio en passant de la vue Table à la vue Graphique. Vous pouvez choisir parmi différents types de graphiques, par exemple Aires, Barres, Histogramme, Courbes, Secteurs et Nuages de points. Dans le cas présent, tracez l’histogramme avec la colonne Category définie sur current_year :
À partir de cette visualisation, vous pouvez voir une tendance à la baisse du nombre de courses au fil des ans. Cette baisse est vraisemblablement due à une augmentation récente du nombre d’entreprises de covoiturage.
Notes
Au moment de la rédaction de ce tutoriel, les données pour 2019 sont incomplètes. En conséquence, il y a une forte baisse du nombre de courses pour cette année.
Vous pouvez concentrer l’analyse sur une seule année, par exemple, 2016. La requête suivante retourne le nombre quotidien de courses au cours de cette année :
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
L’extrait de code suivant présente le résultat de cette requête :
Là encore, vous pouvez visualiser les données en traçant l’histogramme avec la colonne Category définie sur current_day et la colonne Legend (series) définie sur rides_per_day.
Dans le graphique en nuage de points, vous pouvez voir qu’il existe un modèle hebdomadaire, avec les samedis comme jour de pointe. Pendant les mois d’été, il y a moins de courses de taxi en raison des vacances. Notez également des baisses importantes du nombre de courses de taxi, sans schéma clair de quand et pourquoi elles se produisent.
Ensuite, voyons s’il existe une corrélation entre la baisse du nombre de courses et les jours fériés. Vérifiez s’il existe une corrélation en joignant le jeu de données sur les courses de taxi de la ville de New York au jeu de données sur les jours fériés :
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
Mettez en surbrillance le nombre de courses de taxi pendant les jours fériés. Dans ce but, choisissez current_day pour la colonne Category, et rides_per_day et holiday_rides pour les colonnes Legend (series).
À partir du graphique en nuage de points, vous pouvez voir que pendant les jours fériés, le nombre de courses de taxi est plus faible. Il existe toujours une forte chute inexpliquée le 23 janvier. Vérifions la météo de ce jour-là à New York en interrogeant le jeu de données sur les données météorologiques :
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'
Les résultats de la requête indiquent que la chute du nombre de courses de taxi était liée aux raisons suivantes :
- Il y avait du blizzard ce jour-là à New-York, avec de fortes chutes de neige (environ 30 cm).
- Il faisait froid (la température était inférieure à zéro degré Celsius).
- Il y avait du vent (environ 10 m/s).
Ce tutoriel a montré comment un analyste de données peut rapidement effectuer une analyse exploratoire des données. Vous pouvez combiner différents jeux de données à l’aide d’un pool SQL serverless et visualiser les résultats à l’aide d’Azure Synapse Studio.
Étapes suivantes
Pour savoir comment connecter un pool SQL serverless à Power BI Desktop et créer des rapports, consultez Connecter un pool SQL serverless à Power BI Desktop et créer des rapports.
Pour découvrir comment utiliser des tables externes dans un pool SQL serverless, consultez Utiliser des tables externes avec Synapse SQL.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour