Bien démarrer avec U-SQL dans Azure Data Lake Analytics

Important

Azure Data Lake Analytics mis hors service le 29 février 2024. Découvrez-en plus avec cette annonce.

Pour l’analytique des données, votre organization peut utiliser Azure Synapse Analytics ou Microsoft Fabric.

U-SQL est un langage qui combine le langage SQL déclaratif avec le langage C# impératif pour vous permettre de traiter des données quelle que soit l’échelle. La fonctionnalité évolutive de requête distribuée d’U-SQL vous permet d’analyser efficacement les données entre magasins relationnels comme Azure SQL Database. Avec U-SQL, vous pouvez traiter des données non structurées en appliquant des schémas de lecture et en insérant une logique personnalisée et des fonctions définies par l'utilisateur. En outre, U-SQL comprend l’extensibilité qui vous donne un contrôle précis sur l’exécution à l’échelle.

Ressources d’apprentissage

• Le Didacticiel U-SQL fournit une procédure pas à pas pour la plupart du langage U-SQL. La lecture de ce document est recommandée pour tous les développeurs qui veulent apprendre le langage U-SQL.
• Pour plus d’informations sur la syntaxe du langage U-SQL, consultez la Référence du langage U-SQL.
• Pour comprendre la philosophie de conception d’U-SQL, consultez l’article de blog Visual Studio Présentation d’U-SQL – Un langage qui facilite le traitement du Big Data.

Créons des données

Le script U-SQL suivant est simple et nous permet de créer un exemple de fichier de données que nous pouvons référencer dans nos autres scripts U-SQL. Nous aborderons les concepts clés qui sous-tendent ce script dans la section suivante.

USE DATABASE master;
USE SCHEMA dbo;
@a  = 
    SELECT * FROM 
        (VALUES
            (399266, "2/15/2012 11:53:16 AM", "en-us", "microsoft", 73, "microsoft.com;xbox.com", "NULL"),
			(382045, "2/15/2012 11:53:18 AM", "en-gb", "azure data lake analytics", 614, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(382045, "2/16/2012 11:53:20 AM", "en-gb", "usql", 74, "microsoft.com;github.com", "NULL"),
			(106479, "2/16/2012 11:53:50 AM", "en-ca", "xbox", 24, "xbox.com;xbox.com/xbox360", "xbox.com/xbox360"),
			(906441, "2/16/2012 11:54:01 AM", "en-us", "machine learning", 1213, "microsoft.com;github.com", "NULL"),
			(304305, "2/16/2012 11:54:03 AM", "en-us", "outlook", 60, "microsoft.com;office.com;live.com","microsoft.com"),
			(460748, "2/16/2012 11:54:04 AM", "en-us", "azure storage", 1270, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354841, "2/16/2012 11:59:01 AM", "en-us", "azure", 610, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(354068, "2/16/2012 12:00:33 PM", "en-mx", "key vault", 422, "microsoft.com;portal.azure.com", "portal.azure.com"),
			(347413, "2/16/2012 12:11:55 PM", "en-gr", "github", 305, "github.com", "NULL"),
			(840614, "2/16/2012 12:13:56 PM", "en-us", "surface", 1220, "microsoft.com", "NULL"),
			(656666, "2/16/2012 12:15:55 PM", "en-us", "visual studio", 691, "microsoft.com;code.visualstudio.com", "NULL"),
			(951513, "2/16/2012 12:17:00 PM", "en-us", "power bi", 63, "microsoft.com;app.powerbi.com", "powerbi.com"),
			(350350, "2/16/2012 12:18:17 PM", "en-us", "data factory", 30, "microsoft.com;portal.azure.com", "NULL"),
			(641615, "2/16/2012 12:19:55 PM", "en-us", "event hubs", 119, "microsoft.com;portal.azure.com", "NULL")
        ) AS 
              D( UserId, Start, Region, Query, Duration, Urls, ClickedUrls );
OUTPUT @a
    TO "Samples/Data/SearchLog.tsv"
    USING Outputters.Tsv();

Lire les données d’un fichier

Maintenant que nous avons des données, nous allons les lire à partir du fichier que nous avons créé.

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

OUTPUT @searchlog
    TO "/output/SearchLog-first-u-sql.csv"
    USING Outputters.Csv();

Ce script n'a aucune étape de transformation. Il lit le fichier source nommé SearchLog.tsv, le schématise et écrit l’ensemble de lignes dans un fichier nommé SearchLog-first-u-sql.csv.

Notez le point d'interrogation en regard du type de données dans le champ Duration. Il signifie que le champ Duration pourrait avoir la valeur Null.

Concepts clés

• Variables de l'ensemble de lignes: toute expression de requête qui produit un ensemble de lignes peut être affectée à une variable. U-SQL suit le modèle d’affectation de noms variable T-SQL (@searchlog, par exemple) dans le script.
• Le mot-clé EXTRACT lit les données d’un fichier et définit le schéma à la lecture. Extractors.Tsv est un extracteur U-SQL intégré pour les fichiers de valeurs séparées par des tabulations. Vous pouvez développer des extracteurs personnalisés.
• OUTPUT écrit les données dans un fichier à partir d’un ensemble de lignes. Outputters.Csv() est un générateur de sortie U-SQL intégré pour créer un fichier de valeurs séparées par des virgules. Vous pouvez développer des générateurs de sortie personnalisés.

Chemins d’accès des fichiers

Les instructions EXTRACT et OUTPUT utilisent des chemins d’accès. Les chemins d’accès peuvent être absolus ou relatifs :

Le chemin d’accès absolu suivant fait référence à un fichier dans un Data Lake Store nommé mystore :

adl://mystore.azuredatalakestore.net/Samples/Data/SearchLog.tsv

Le chemin d’accès suivant commence par "/". Il fait référence à un fichier dans le compte Data Lake Store par défaut :

/output/SearchLog-first-u-sql.csv

Utiliser des variables scalaires

Vous pouvez également utiliser des variables scalaires pour faciliter la maintenance de votre script. Le script U-SQL précédent peut également s'écrire comme suit :

DECLARE @in  string = "/Samples/Data/SearchLog.tsv";
DECLARE @out string = "/output/SearchLog-scalar-variables.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM @in
    USING Extractors.Tsv();
OUTPUT @searchlog
    TO @out
    USING Outputters.Csv();

Transformer des ensembles de lignes

Utilisez SELECT pour transformer des ensembles de lignes. Ce script sélectionne les colonnes Start, Region et Duration, et écrit ces lignes dans un fichier lorsque Region a la valeur « en-gb » :

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
OUTPUT @rs1
    TO "/output/SearchLog-transform-rowsets.csv"
    USING Outputters.Csv();

La clause WHERE utilise une expression booléenne C#. Vous pouvez utiliser le langage d'expressions C# pour faire vos propres expressions et fonctions. Vous pouvez même effectuer un filtrage plus complexe en les combinant avec des conjonctions logiques (AND) et des disjonctions (OR).

Le script suivant utilise la méthode DateTime.Parse() et une conjonction. colonnes Start, Region et Duration, où Region a la valeur « en-gb ». Ensuite, il vérifie les valeurs de la colonne Duration entre certaines dates et écrit ces valeurs dans un fichier :

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT Start, Region, Duration
    FROM @searchlog
WHERE Region == "en-gb";
@rs1 =
    SELECT Start, Region, Duration
    FROM @rs1
    WHERE Start >= DateTime.Parse("2012/02/16") AND Start <= DateTime.Parse("2012/02/17");
OUTPUT @rs1
    TO "/output/SearchLog-transform-datetime.csv"
    USING Outputters.Csv();

Notes

La deuxième requête fonctionne sur le résultat du premier ensemble de lignes, ce qui crée une combinaison des deux filtres. Vous pouvez également réutiliser un nom de variable, et les noms ont une portée lexicale.

Ensembles de lignes agrégés

U-SQL vous fournit les ORDER BY, GROUP BY et les agrégations que vous connaissez déjà.

La requête suivante recherche la durée totale par région, puis affiche les cinq premières durées dans l’ordre.

Les ensembles de lignes U-SQL ne conservent pas leur ordre pour la requête suivante. Par conséquent, pour ordonner un résultat, vous devez ajouter ORDER BY à l'instruction OUTPUT :

DECLARE @outpref string = "/output/Searchlog-aggregation";
DECLARE @out1    string = @outpref+"_agg.csv";
DECLARE @out2    string = @outpref+"_top5agg.csv";
@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@rs1 =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
GROUP BY Region;
@res =
    SELECT *
    FROM @rs1
    ORDER BY TotalDuration DESC
    FETCH 5 ROWS;
OUTPUT @rs1
    TO @out1
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();
OUTPUT @res
    TO @out2
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

La clause U-SQL ORDER BY exige l’utilisation de la clause FETCH dans une expression SELECT.

La clause U-SQL HAVING peut être utilisée pour restreindre le résultat aux groupes qui remplissent la condition HAVING :

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();
@res =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
    GROUP BY Region
    HAVING SUM(Duration) > 200;
OUTPUT @res
    TO "/output/Searchlog-having.csv"
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

Pour les scénarios d’agrégation avancés, consultez la documentation de référence U-SQL sur les fonctions d’agrégation, d’analytique et de référence.

Étapes suivantes

• Vue d'ensemble de Microsoft Azure Data Lake Analytics
• Développer des scripts U-SQL à l’aide des outils Data Lake pour Visual Studio