Поделиться через

Общие сведения о программируемости U-SQL

Это важно

Azure Data Lake Analytics вышел из эксплуатации 29 февраля 2024 года. Дополнительные сведения см. в этом объявлении.

Для аналитики данных ваша организация может использовать Azure Synapse Analytics или Microsoft Fabric.

U-SQL — это язык запросов, предназначенный для типов больших данных рабочих нагрузок. Одной из уникальных функций U-SQL является сочетание декларативного языка SQL с расширяемостью и программируемостью, предоставляемой C#. В этом руководстве мы сосредоточимся на расширяемости и программируемости языка U-SQL, обеспечиваемых C#.

Требования

Скачайте и установите инструменты Azure Data Lake для Visual Studio.

Начало работы с U-SQL

Ознакомьтесь со следующим скриптом U-SQL:

@a  =
  SELECT * FROM
    (VALUES
       ("Contoso",   1500.0, "2017-03-39"),
       ("Woodgrove", 2700.0, "2017-04-10")
    ) AS D( customer, amount, date );

@results =
  SELECT
    customer,
    amount,
    date
  FROM @a;

Этот скрипт определяет два набора строк: @a и @results. RowSet @results определяется из @a.

Типы и выражения C# в скрипте U-SQL

Выражение U-SQL — это выражение C# в сочетании с логическими операциями U-SQL, такими AND, ORи NOT. Выражения U-SQL можно использовать с SELECT, EXTRACT, WHERE, HAVING, GROUP BY и DECLARE. Например, следующий скрипт анализирует строку как значение типа DateTime.

@results =
  SELECT
    customer,
    amount,
    DateTime.Parse(date) AS date
  FROM @a;

Следующий фрагмент кода анализирует строку как значение DateTime в инструкции DECLARE.

DECLARE @d = DateTime.Parse("2016/01/01");

Использование выражений C# для преобразования типов данных

В следующем примере показано, как можно преобразовать данные datetime с помощью выражений C#. В этом конкретном сценарии данные даты и времени в виде строки преобразуются в стандартный формат даты и времени с отметкой времени полуночи 00:00:00.

DECLARE @dt = "2016-07-06 10:23:15";

@rs1 =
  SELECT
    Convert.ToDateTime(Convert.ToDateTime(@dt).ToString("yyyy-MM-dd")) AS dt,
    dt AS olddt
  FROM @rs0;

OUTPUT @rs1
  TO @output_file
  USING Outputters.Text();

Использование выражений C# для сегодняшней даты

Чтобы извлечь текущую дату, мы можем использовать следующее выражение C#: DateTime.Now.ToString("M/d/yyyy")

Ниже приведен пример использования этого выражения в скрипте:

@rs1 =
  SELECT
    MAX(guid) AS start_id,
    MIN(dt) AS start_time,
    MIN(Convert.ToDateTime(Convert.ToDateTime(dt<@default_dt?@default_dt:dt).ToString("yyyy-MM-dd"))) AS start_zero_time,
    MIN(USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)) AS start_fiscalperiod,
    DateTime.Now.ToString("M/d/yyyy") AS Nowdate,
    user,
    des
  FROM @rs0
  GROUP BY user, des;

Использование сборок .NET

Модель расширяемости U-SQL сильно зависит от возможности добавления пользовательского кода из сборок .NET.

Зарегистрировать сборку .NET

Используйте инструкцию CREATE ASSEMBLY, чтобы поместить сборку .NET в базу данных U-SQL. После этого скрипты U-SQL могут использовать эти сборки с помощью инструкции REFERENCE ASSEMBLY.

В следующем коде показано, как зарегистрировать сборку:

CREATE ASSEMBLY MyDB.[MyAssembly]
   FROM "/myassembly.dll";

В следующем коде показано, как ссылаться на сборку:

REFERENCE ASSEMBLY MyDB.[MyAssembly];

Ознакомьтесь с инструкциями по регистрации сборки , которые подробно рассматривают этот вопрос.

Используйте управление версиями сборок

В настоящее время U-SQL использует .NET Framework версии 4.7.2. Поэтому убедитесь, что собственные сборки совместимы с этой версией среды выполнения.

Как упоминалось ранее, U-SQL выполняет код в 64-разрядном формате (x64). Поэтому убедитесь, что код компилируется для запуска в x64. В противном случае вы получите ошибку неправильного формата, показанную ранее.

Каждая загруженная библиотека DLL сборки и файл ресурсов, например другая среда выполнения, собственная сборка или файл конфигурации, не должна превышать 400 МБ. Общий размер развернутых ресурсов с помощью DEPLOY RESOURCE или с помощью ссылок на сборки и другие файлы не может превышать 3 ГБ.

Наконец, каждая база данных U-SQL может содержать только одну версию любой конкретной сборки. Например, если вам нужна как версия 7, так и версия 8 библиотеки NewtonSoft Json.NET, необходимо зарегистрировать их в двух разных базах данных. Кроме того, каждый скрипт может ссылаться только на одну версию заданной библиотеки DLL сборки. В этом отношении U-SQL следует управлению сборками C# и семантике управления версиями.

Использование определяемых пользователем функций: UDF

Определяемые пользователем функции U-SQL или UDF— это подпрограммы программирования, которые принимают параметры, выполняют действие (например, сложный расчет) и возвращают результат этого действия в качестве значения. Возвращаемое значение UDF может быть только одним скалярным. U-SQL UDF можно вызывать в базовом скрипте U-SQL, как любая другая скалярная функция C#.

Рекомендуется инициализировать определяемые пользователем функции U-SQL как общедоступные и статические.

public static string MyFunction(string param1)
{
    return "my result";
}

Сначала рассмотрим простой пример создания UDF.

В этом сценарии использования необходимо определить финансовый период, включая финансовый квартал и финансовый месяц первого входа для конкретного пользователя. Первый финансовый месяц года в нашем сценарии — июнь.

Чтобы вычислить финансовый период, мы введем следующую функцию C#:

public static string GetFiscalPeriod(DateTime dt)
{
    int FiscalMonth=0;
    if (dt.Month < 7)
    {
        FiscalMonth = dt.Month + 6;
    }
    else
    {
        FiscalMonth = dt.Month - 6;
    }

    int FiscalQuarter=0;
    if (FiscalMonth >=1 && FiscalMonth<=3)
    {
        FiscalQuarter = 1;
    }
    if (FiscalMonth >= 4 && FiscalMonth <= 6)
    {
        FiscalQuarter = 2;
    }
    if (FiscalMonth >= 7 && FiscalMonth <= 9)
    {
        FiscalQuarter = 3;
    }
    if (FiscalMonth >= 10 && FiscalMonth <= 12)
    {
        FiscalQuarter = 4;
    }

    return "Q" + FiscalQuarter.ToString() + ":P" + FiscalMonth.ToString();
}

Он просто вычисляет финансовый месяц и квартал и возвращает строковое значение. В июне первый месяц первого финансового квартала мы используем "Q1:P1". В июле мы используем "Q1:P2" и т. д.

Это обычная функция C#, которую мы будем использовать в нашем проекте U-SQL.

Вот как выглядит раздел программной части в этом сценарии:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace USQL_Programmability
{
    public class CustomFunctions
    {
        public static string GetFiscalPeriod(DateTime dt)
        {
            int FiscalMonth=0;
            if (dt.Month < 7)
            {
                FiscalMonth = dt.Month + 6;
            }
            else
            {
                FiscalMonth = dt.Month - 6;
            }

            int FiscalQuarter=0;
            if (FiscalMonth >=1 && FiscalMonth<=3)
            {
                FiscalQuarter = 1;
            }
            if (FiscalMonth >= 4 && FiscalMonth <= 6)
            {
                FiscalQuarter = 2;
            }
            if (FiscalMonth >= 7 && FiscalMonth <= 9)
            {
                FiscalQuarter = 3;
            }
            if (FiscalMonth >= 10 && FiscalMonth <= 12)
            {
                FiscalQuarter = 4;
            }

            return "Q" + FiscalQuarter.ToString() + ":" + FiscalMonth.ToString();
        }
    }
}

Теперь мы будем вызывать эту функцию из базового скрипта U-SQL. Для этого необходимо указать полное имя функции, включая пространство имен, которое в данном случае — NameSpace.Class.Function(parameter).

USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)

Ниже приведен фактический базовый скрипт U-SQL:

DECLARE @input_file string = @"\usql-programmability\input_file.tsv";
DECLARE @output_file string = @"\usql-programmability\output_file.tsv";

@rs0 =
    EXTRACT
        guid Guid,
        dt DateTime,
        user String,
        des String
    FROM @input_file USING Extractors.Tsv();

DECLARE @default_dt DateTime = Convert.ToDateTime("06/01/2016");

@rs1 =
    SELECT
        MAX(guid) AS start_id,
        MIN(dt) AS start_time,
        MIN(Convert.ToDateTime(Convert.ToDateTime(dt<@default_dt?@default_dt:dt).ToString("yyyy-MM-dd"))) AS start_zero_time,
        MIN(USQL_Programmability.CustomFunctions.GetFiscalPeriod(dt)) AS start_fiscalperiod,
        user,
        des
    FROM @rs0
    GROUP BY user, des;

OUTPUT @rs1
    TO @output_file
    USING Outputters.Text();

Ниже приведен выходной файл выполнения скрипта:

0d8b9630-d5ca-11e5-8329-251efa3a2941,2016-02-11T07:04:17.2630000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User1",""

20843640-d771-11e5-b87b-8b7265c75a44,2016-02-11T07:04:17.2630000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User2",""

301f23d2-d690-11e5-9a98-4b4f60a1836f,2016-02-11T09:01:33.9720000-08:00,2016-06-01T00:00:00.0000000,"Q3:8","User3",""

В этом примере демонстрируется простое использование встроенной UDF в U-SQL.

Сохранение состояния между вызовами UDF

Объекты программирования U-SQL C# могут быть более сложными, используя интерактивность через глобальные переменные в коде. Рассмотрим следующий сценарий бизнес-кейса.

В крупных организациях пользователи могут переключаться между разновидностями внутренних приложений. К ним относятся Microsoft Dynamics CRM, Power BI и т. д. Клиентам может потребоваться применить анализ телеметрии о том, как пользователи переключаются между различными приложениями, что такое тенденции использования и т. д. Цель бизнеса — оптимизировать использование приложений. Кроме того, может потребоваться объединить различные приложения или определенные процедуры входа.

Для достижения этой цели необходимо определить идентификаторы сеансов и время задержки между последним сеансом.

Нам нужно найти предыдущий вход, а затем назначить этот вход всем сеансам, созданным в одном приложении. Первая проблема заключается в том, что базовый скрипт U-SQL не позволяет применять вычисления по уже вычисляемым столбцам с функцией LAG. Второй проблемой является то, что мы должны сохранить конкретный сеанс для всех сеансов в течение одного и того же периода времени.

Для решения этой проблемы мы используем глобальную переменную в разделе «code-behind»: static public string globalSession;.

Эта глобальная переменная применяется ко всему набору строк во время выполнения скрипта.

Ниже приведен раздел программной части программы U-SQL:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace USQLApplication21
{
    public class UserSession
    {
        static public string globalSession;
        static public string StampUserSession(string eventTime, string PreviousRow, string Session)
        {

            if (!string.IsNullOrEmpty(PreviousRow))
            {
                double timeGap = Convert.ToDateTime(eventTime).Subtract(Convert.ToDateTime(PreviousRow)).TotalMinutes;
                if (timeGap <= 60) {return Session;}
                else {return Guid.NewGuid().ToString();}
            }
            else {return Guid.NewGuid().ToString();}

        }

        static public string getStampUserSession(string Session)
        {
            if (Session != globalSession && !string.IsNullOrEmpty(Session)) { globalSession = Session; }
            return globalSession;
        }

    }
}

В этом примере показана глобальная переменная static public string globalSession;, используемая внутри функции getStampUserSession и повторно инициализируется при каждом изменении параметра Сеанса.

Базовый скрипт U-SQL выглядит следующим образом:

DECLARE @in string = @"\UserSession\test1.tsv";
DECLARE @out1 string = @"\UserSession\Out1.csv";
DECLARE @out2 string = @"\UserSession\Out2.csv";
DECLARE @out3 string = @"\UserSession\Out3.csv";

@records =
    EXTRACT DataId string,
            EventDateTime string,
            UserName string,
            UserSessionTimestamp string

    FROM @in
    USING Extractors.Tsv();

@rs1 =
    SELECT
        EventDateTime,
        UserName,
        LAG(EventDateTime, 1)
            OVER(PARTITION BY UserName ORDER BY EventDateTime ASC) AS prevDateTime,
        string.IsNullOrEmpty(LAG(EventDateTime, 1)
            OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)) AS Flag,
        USQLApplication21.UserSession.StampUserSession
           (
                EventDateTime,
                LAG(EventDateTime, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC),
                LAG(UserSessionTimestamp, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)
           ) AS UserSessionTimestamp
    FROM @records;

@rs2 =
    SELECT
        EventDateTime,
        UserName,
        LAG(EventDateTime, 1)
        OVER(PARTITION BY UserName ORDER BY EventDateTime ASC) AS prevDateTime,
        string.IsNullOrEmpty( LAG(EventDateTime, 1) OVER(PARTITION BY UserName ORDER BY EventDateTime ASC)) AS Flag,
        USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) AS UserSessionTimestamp
    FROM @rs1
    WHERE UserName != "UserName";

OUTPUT @rs2
    TO @out2
    ORDER BY UserName, EventDateTime ASC
    USING Outputters.Csv();

Функция USQLApplication21.UserSession.getStampUserSession(UserSessionTimestamp) вызывается здесь во время вычисления второго набора строк памяти. Он передает столбец UserSessionTimestamp и возвращает значение до тех пор, пока UserSessionTimestamp не изменится.

Выходной файл выглядит следующим образом:

"2016-02-19T07:32:36.8420000-08:00","User1",,True,"72a0660e-22df-428e-b672-e0977007177f"
"2016-02-17T11:52:43.6350000-08:00","User2",,True,"4a0cd19a-6e67-4d95-a119-4eda590226ba"
"2016-02-17T11:59:08.8320000-08:00","User2","2016-02-17T11:52:43.6350000-08:00",False,"4a0cd19a-6e67-4d95-a119-4eda590226ba"
"2016-02-11T07:04:17.2630000-08:00","User3",,True,"51860a7a-1610-4f74-a9ea-69d5eef7cd9c"
"2016-02-11T07:10:33.9720000-08:00","User3","2016-02-11T07:04:17.2630000-08:00",False,"51860a7a-1610-4f74-a9ea-69d5eef7cd9c"
"2016-02-15T21:27:41.8210000-08:00","User3","2016-02-11T07:10:33.9720000-08:00",False,"4d2bc48d-bdf3-4591-a9c1-7b15ceb8e074"
"2016-02-16T05:48:49.6360000-08:00","User3","2016-02-15T21:27:41.8210000-08:00",False,"dd3006d0-2dcd-42d0-b3a2-bc03dd77c8b9"
"2016-02-16T06:22:43.6390000-08:00","User3","2016-02-16T05:48:49.6360000-08:00",False,"dd3006d0-2dcd-42d0-b3a2-bc03dd77c8b9"
"2016-02-17T16:29:53.2280000-08:00","User3","2016-02-16T06:22:43.6390000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-17T16:39:07.2430000-08:00","User3","2016-02-17T16:29:53.2280000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-17T17:20:39.3220000-08:00","User3","2016-02-17T16:39:07.2430000-08:00",False,"2fa899c7-eecf-4b1b-a8cd-30c5357b4f3a"
"2016-02-19T05:23:54.5710000-08:00","User3","2016-02-17T17:20:39.3220000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T05:48:37.7510000-08:00","User3","2016-02-19T05:23:54.5710000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T06:40:27.4830000-08:00","User3","2016-02-19T05:48:37.7510000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T07:27:37.7550000-08:00","User3","2016-02-19T06:40:27.4830000-08:00",False,"6ca7ed80-c149-4c22-b24b-94ff5b0d824d"
"2016-02-19T19:35:40.9450000-08:00","User3","2016-02-19T07:27:37.7550000-08:00",False,"3f385f0b-3e68-4456-ac74-ff6cef093674"
"2016-02-20T00:07:37.8250000-08:00","User3","2016-02-19T19:35:40.9450000-08:00",False,"685f76d5-ca48-4c58-b77d-bd3a9ddb33da"
"2016-02-11T09:01:33.9720000-08:00","User4",,True,"9f0cf696-c8ba-449a-8d5f-1ca6ed8f2ee8"
"2016-02-17T06:30:38.6210000-08:00","User4","2016-02-11T09:01:33.9720000-08:00",False,"8b11fd2a-01bf-4a5e-a9af-3c92c4e4382a"
"2016-02-17T22:15:26.4020000-08:00","User4","2016-02-17T06:30:38.6210000-08:00",False,"4e1cb707-3b5f-49c1-90c7-9b33b86ca1f4"
"2016-02-18T14:37:27.6560000-08:00","User4","2016-02-17T22:15:26.4020000-08:00",False,"f4e44400-e837-40ed-8dfd-2ea264d4e338"
"2016-02-19T01:20:31.4800000-08:00","User4","2016-02-18T14:37:27.6560000-08:00",False,"2136f4cf-7c7d-43c1-8ae2-08f4ad6a6e08"

В этом примере демонстрируется более сложный сценарий использования, в котором мы используем глобальную переменную в разделе программной части, применяемой ко всему набору строк памяти.

Дальнейшие действия

  • Руководство по программированию на U-SQL — UDT и UDAGG
  • Руководство по программированию U-SQL — UDO
  • Last updated on