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

U-SQL est un langage qui combine sql déclaratif avec C# impératif pour vous permettre de traiter les données à n’importe quelle échelle. Grâce à la fonctionnalité de requête distribuée évolutive de U-SQL, vous pouvez analyser efficacement les données issues de magasins relationnels tels qu'Azure SQL Database. Avec U-SQL, vous pouvez traiter des données non structurées en appliquant un schéma lors de la lecture et en insérant une logique personnalisée et des fonctions définies par l’utilisateur. En outre, U-SQL inclut l’extensibilité qui vous donne un contrôle précis sur la façon d’exécuter à grande échelle.

Ressources d’apprentissage

• Le didacticiel U-SQL fournit une procédure pas à pas guidée de la plupart du langage U-SQL. Ce document est recommandé de lire pour tous les développeurs souhaitant apprendre 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 U-SQL , consultez le billet de blog Visual Studio Présentation de U-SQL – Langage qui facilite le traitement de Big Data.

Nous allons créer 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 allons aborder les concepts clés qui permettent à ce script de fonctionner 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 des données à partir d’un fichier

Maintenant que nous avons des données, nous allons la 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 ne comporte aucune étape de transformation. Il lit à partir du fichier source appelé SearchLog.tsv, le schématise et écrit l’ensemble de lignes dans un fichier appelé SearchLog-first-u-sql.csv.

Notez le point d’interrogation en regard du type de données dans le champ Duration. Cela signifie que le champ Duration peut être null.

Concepts clés

• variables d’ensemble de lignes: chaque expression de requête qui produit un ensemble de lignes peut être affecté à une variable. U-SQL suit le modèle de nommage des variables 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 en lecture. Extractors.Tsv est un extracteur U-SQL intégré pour les fichiers de valeurs séparées par des onglets. Vous pouvez développer des extracteurs personnalisés.
• La OUTPUT écrit des données d’un ensemble de lignes dans un fichier. 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

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

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

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

Le chemin de fichier 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 être écrit 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’expression C# pour effectuer vos propres expressions et fonctions. Vous pouvez même effectuer un filtrage plus complexe en les combinant avec des combinaisons 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();

Agréger des ensembles de lignes

U-SQL vous donne les instructions ORDER BY, GROUP BY et agrégations familières.

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 commander une sortie, 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 nécessite l’utilisation de la clause FETCH dans une expression SELECT.

La clause U-SQL HAVING peut être utilisée pour restreindre la sortie aux groupes qui répondent à 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 pour fonctions d’agrégation, d’analyse 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