Support formation Microsoft Azure

Support formation Microsoft Azure

DP-203

Utiliser un pool SQL serverless Azure Synapse pour interroger des fichiers dans un lac de données

Gonzague Ducos

Technologies utilisée dans solutions de données est langage SQL, langage standard permettant d’interroger et de manipuler les données.

Comprendre les fonctionnalités des pools SQL serverless Azure Synapse et les cas d’usage

Pools SQL serverless dans Azure Synapse Analytics

Azure Synapse SQL : système de requête distribuée Azure Synapse Analytics2

2 types d’environnements d’exécution :

Pool SQL serverless : traitement des requêtes SQL à la demande, principalement utilisé pour les données d’un lac de données.

Pool SQL dédié : instances de base de données relationnelle à l’échelle de l’entreprise utilisées pour héberger des entrepôts de données où les données sont stockées dans des tables relationnelles.

Avantages

Transact-SQL pour interroger des données sur place sans avoir besoin de les copier ou de les charger dans un magasin spécialisé.

Connectivité intégrée issue d’un large éventail d’outils décisionnels et d’outils de requête ad hoc, y compris les pilotes les plus populaires.

Un traitement des requêtes distribuées conçu pour les données à grande échelle et les fonctions de calcul, ce qui améliore les performances de requête.

Une tolérance de panne pour l’exécution intégrée des requêtes, ce qui fournit des taux de réussite et de fiabilité élevés, même pour les requêtes longues qui impliquent des jeux de données volumineux.

Aucune infrastructure à configurer ni aucun cluster à gérer : un point de terminaison intégré fourni dans chaque espace de travail Azure Synapse.

Aucuns frais pour les ressources réservées. Seules les données traitées par les requêtes que vous exécutez vous sont facturées.

Quand utiliser les pools SQL serverless

Il vous suffit de pointer la requête vers les données qui se trouvent déjà dans le lac et de l’exécuter.

Modèle de ressource serverless Synapse SQL idéal pour charges de travail non planifiées à l’aide du point de terminaison SQL serverless AlwaysOn dans votre espace de travail Azure Synapse Analytics.

Utile pour connaître le coût exact de chaque requête exécutée afin de monitorer et attribuer les coûts.

Pool SQL serverless = système d’analytique. Pas pour :

Charges de travail OLTP telles que les bases de données utilisées par les applications pour stocker des données transactionnelles.

Charges de travail qui nécessitent des temps de réponse de l’ordre de quelques millisecondes

Charges de travail qui cherchent à identifier une seule ligne dans un jeu de données

Cas d’usages courants pour les pools SQL serverless

Exploration des données : parcourir le lac de données pour obtenir de premiers insights sur les données. Facilement dans Azure Synapse Studio. Parcourir les fichiers de votre stockage Data Lake associé et utiliser le pool SQL serverless intégré pour générer automatiquement un script SQL permettant de sélectionner les 100 premières lignes d’un fichier ou d’un dossier, comme vous le feriez avec une table dans SQL Server. Appliquer des projections, un filtrage, un regroupement et la plupart des opérations sur les données comme si ces dernières se trouvaient dans une table SQL Server standard.

Transformation des données : Azure Synapse Analytics fournit des fonctionnalités de transformation de données intéressantes avec Synapse Spark, certains ingénieurs peuvent trouver que la transformation des données est plus facilement réalisable avec SQL, que ce soit de manière interactive ou dans le cadre d’un pipeline de données automatisé.

Entrepôt de données logiques : définir des objets externes comme des tables et des vues dans une base de données SQL serverless. Les données restent stockées dans les fichiers Data Lake, mais sont abstraites par un schéma relationnel qui peut être utilisé par les applications clientes et les outils analytiques pour interroger les données comme ils le feraient dans une base de données relationnelle hébergée dans SQL Server.

Interroger des fichiers avec un pool SQL serverless

Interroger des CSV, JSON et Parquet. Même syntaxe pour tous les types de fichiers.

OPENROWSET génère un ensemble de lignes tabulaires à partir des données d’un ou plusieurs fichiers.

SELECT TOP 100 *

FROM OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',

FORMAT = 'csv') AS rows

⁠

Guide pratique pour utiliser OPENROWSET dans le pool SQL serverless - Azure Synapse Analytics⁠

⁠

Paramètre BULK : URL complète. Fichier ou dossier avec une expression générique qui permet de filtrer les types de fichiers qui doivent être inclus :

files/file1.csv : uniquement le fichier

files/file*.csv : tous les fichiers du dossier commençant par “file”

files/* : tous les fichiers du dossier

files/** : tous les fichiers et récursivement ses sous-dossiers

, : plusieurs chemins

Paramètre FORMAT spécifie le type des données interrogées

SELECT TOP 100 *

FROM OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',

FORMAT = 'csv',

PARSER_VERSION = '2.0',

FIRSTROW = 2) AS rows

PARSER_VERSION : v 2 = moins de format d’encodage mais plus performant.

FIRSTROW : ignore les x-1 lignes en haut du fichier.

FIELDTERMINATOR : caractère pour séparer les valeurs de champ sur chaque ligne

ROWTERMINATOR : caractère pour la fin d’une ligne de données

FIELDQUOTE : caractère pour placer les valeurs de chaîne entre guillemets

Spécification du schéma de l’ensemble de lignes

HEADER_ROW = TRUE (si PARSER_VERSION en v2 ) : types de données SQL Server déduits de manière appropriée. Sinon spécifié avec WITH

SELECT TOP 100 *

FROM OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',

FORMAT = 'csv',

PARSER_VERSION = '2.0')

WITH (

product_id INT,

product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,

list_price DECIMAL(5,2)

) AS rows

Interrogation des fichiers JSON

SELECT JSON_VALUE(doc, '$.product_name') AS product,

JSON_VALUE(doc, '$.list_price') AS price

FROM

OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',

FORMAT = 'csv',

FIELDTERMINATOR ='0x0b',

FIELDQUOTE = '0x0b',

ROWTERMINATOR = '0x0b'

) WITH (doc NVARCHAR(MAX)) as rows

Pas de format OPENROWSET spécifique JSON. Tout le JSON dans une seule colonne doc puis parsée avec JSON_VALUEJSON_VALUE.

Interrogation des fichiers Parquet

SELECT TOP 100 *

FROM OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',

FORMAT = 'parquet') AS rows

Interroger des données partitionnées

Partitionner des données en les répartissant dans plusieurs fichiers situés dans des sous-dossiers qui reflètent les critères de partitionnement.

/orders

/year=2020

/month=1

/01012020.parquet

/02012020.parquet

...

/month=2

/01022020.parquet

/02022020.parquet

...

/year=2021

/month=1

/01012021.parquet

/02012021.parquet

...

Uniquement Janvier et Février 2020

SELECT *

FROM OPENROWSET(

BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',

FORMAT = 'parquet') AS orders

WHERE orders.filepath(1) = '2020'

AND orders.filepath(2) IN ('1','2');

Le * de year=* sera remplacé par orders.filepath(1).

Le * de month=* sera remplacé par orders.filepath(2).

Créer des objets de base de données externes

OPENROWSET s’exécutent dans la base de données master par défaut du pool SQL serverless.

Besoin de créer une base de données personnalisée contenant certains objets qui facilitent l’utilisation des données externes dans le lac de données.

Création d’une base de données

CREATE DATABASE SalesDB

COLLATE Latin1_General_100_BIN2_UTF8

Création d’une source de données externe

Pour simplifier la syntaxe de OPENROWSET.

CREATE EXTERNAL DATA SOURCE files

WITH (

LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'

)

Puis :

SELECT *

FROM

OPENROWSET(

BULK 'orders/*.csv',

DATA_SOURCE = 'files',

FORMAT = 'csv',

        PARSER_VERSION = '2.0'

) AS orders

Autre avantage : passer des informations d’authentification pour autoriser l’accès au stockage sous-jacent, sans autoriser les utilisateurs à accéder directement aux données du compte de stockage. Exemple ici avec une signature d’accès partagé (SAS) :

CREATE DATABASE SCOPED CREDENTIAL sqlcred

WITH

IDENTITY='SHARED ACCESS SIGNATURE',

SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles

WITH (

LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'

CREDENTIAL = sqlcred

);

⁠

Contrôler l’accès au compte de stockage pour le pool SQL serverless - Azure Synapse Analytics⁠

⁠