Применение преобразования SQL

Выполняет запрос SQLite на получение входных наборов данных для преобразования данных.

Категория: Преобразование или управление данными

Примечание

Применимо к: машинное обучение Studio (классическая модель)

Это содержимое относится только к Studio (классическая модель). Аналогичные модули перетаскивания были добавлены в конструктор Машинное обучение Azure. Дополнительные сведения см. в статье сравнение двух версий.

Обзор модуля

В этой статье описывается, как использовать модуль Apply SQL преобразование в машинное обучение Azure Studio (классическая модель) для указания SQL-запроса к входному набору данных или DataSets.

SQL удобен в том случае, если необходимо изменить данные сложными способами или сохранить данные для использования в других средах. Например, с помощью модуля Apply SQL преобразование можно:

  • создать таблицу для результатов, а затем сохранить наборы данных в переносимой базе данных;

  • выполнять пользовательские преобразования типов данных или создавать статистические выражения;

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

Важно!

Ядро SQL, используемое в этом модуле, — SQLite. Если вы не знакомы с синтаксисом SQLite, ознакомьтесь с примерами в разделе синтаксис и использование этой статьи.

Что такое SQLite?

SQLite — это система управления реляционными базами данных в общедоступном домене, которая содержится в библиотеке программирования на языке C. SQLite — это популярная встраиваемая база данных для локального хранения в браузерах.

SQLite изначально был разработан в 2000 г. для ВМФ США для поддержки бессерверных транзакций. Это автономный модуль для работы с базами данных без системы управления, не требующий настройки или администрирования.

Настройка преобразования «применение SQL»

В модуле может содержаться до трех наборов в качестве входных данных. При ссылке на наборы данных, подключенные к каждому порту ввода, необходимо использовать имена t1 , t2 и t3 . Номер таблицы соответствует индексу входного порта.

Оставшийся параметр — SQL-запрос, в котором используется синтаксис SQLite. Этот модуль поддерживает все стандартные операторы синтаксиса SQLite. Список неподдерживаемых инструкций см. в разделе Технические примечания .

Общий синтаксис и использование

  • При вводе нескольких строк в текстовом поле скрипт SQL используйте точку с запятой для завершения каждой инструкции. В противном случае разрывы строки преобразуются в пробелы.

    Например, следующие инструкции эквивалентны.

    SELECT   
    *   
    from   
    t1;  
    
    SELECT * from t1;  
    
  • Вы можете добавлять комментарии либо с помощью -- в начале каждой строки, либо заключая текст в /* */.

    Например, допустима следующая инструкция:

    SELECT * from t1  
    /*WHERE ItemID BETWEEN 1 AND 100*/;  
    
  • Если имя столбца дублирует имя зарезервированного ключевого слова, к тексту в текстовом поле скрипта SQL применяется выделение синтаксиса. Чтобы избежать путаницы, имена столбцов следует заключать в квадратные скобки (для соблюдения соглашения Transact-SQL), а также обратные или двойные кавычки (соглашение ANSI SQL).

    Например, в следующем запросе набора данных о пожертвовании крови указано допустимое имя столбца, но оно также является зарезервированным ключевым словом.

    SELECT Recency, Frequency, Monetary, Time, Class  
    FROM t1  
    WHERE Time between 3 and 20;  
    

    Если выполнить запрос как есть, запрос может вернуть правильные результаты, но в зависимости от набора данных он может вернуть ошибку. Ниже приведены некоторые примеры того, как избежать проблемы.

    -- Transact-SQL  
    SELECT [Recency], [Frequency], [Monetary], [Time], [Class]  
    FROM t1  
    WHERE [Time] between 3 and 20;  
    -- ANSI SQL  
    SELECT "Recency", "Frequency", "Monetary", "Time", "Class"  
    FROM t1  
    WHERE `Time` between 3 and 20;  
    

    Примечание

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

  • SQLite не учитывает регистр, за исключением нескольких команд с различными значениями, учитывающими регистр (стандартная маска и стандартная маска).

Инструкция SELECT

В SELECT инструкции имена столбцов, которые содержат пробелы или другие символы, запрещенные в идентификаторах, должны быть заключены в двойные кавычки, квадратные скобки или символы обратного апострофа (').

Например, этот запрос ссылается на Two-Class набор данных IRI в t1 , но одно имя столбца содержит запрещенный символ, поэтому имя столбца заключено в кавычки.

SELECT class, "sepal-length" FROM t1;  

Можно добавить WHERE предложение для фильтрации значений в наборе данных.

SELECT class, "sepal-length" FROM t1 WHERE "sepal-length" >5.0;  

Синтаксис SQLite не поддерживает TOP ключевое слово, которое используется в Transact-SQL. Вместо этого можно использовать LIMIT ключевое слово или FETCH оператор.

Например, Сравните эти запросы с набором данных об аренде велосипедов.

-- unsupported in SQLite  
SELECT  TOP 100 [dteday] FROM t1 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100   
SELECT  [dteday] FROM t1 LIMIT 100 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100. Note that FETCH is on a new line.  
SELECT  [dteday] FROM t1 - ;  
FETCH FIRST 100 rows ONLY;  
ORDER BY [dteday] DESC;  

Соединения

В следующих примерах используется набор данных оценок ресторанов на входном порте, соответствующем t1, и набор данных характеристик ресторана на входном порте, соответствующем t2.

Следующая инструкция соединяет две таблицы для создания набора данных, в котором указанные характеристики ресторана объединены со средними оценками каждого ресторана.

SELECT DISTINCT(t2.placeid),    
t2.name, t2.city, t2.state, t2.price, t2.alcohol,  
AVG(rating)  AS 'AvgRating'   
FROM t1   
JOIN t2  
ON t1.placeID = t2.placeID  
GROUP BY t2.placeid;  

Агрегатные функции

В этом разделе приводятся основные примеры некоторых общих агрегатных функций SQL с помощью SQLite.

В настоящее время поддерживаются следующие агрегатные функции: AVG , COUNT , MAX , MIN , SUM , TOTAL .

Следующий запрос возвращает набор данных, содержащий идентификатор ресторана и его среднюю оценку.

SELECT DISTINCT placeid,  
AVG(rating) AS ‘AvgRating’,  
FROM t1  
GROUP BY placeid  

Работа со строками

SQLite поддерживает оператор двойной вертикальной черты для объединения строк.

Следующая инструкция создает новый столбец путем объединения двух текстовых столбцов.

SELECT placeID, name,   
(city || '-' || state) AS 'Target Region',   
FROM t1  

Предупреждение

Оператор объединения строк Transact-SQL не поддерживается: + (объединение строк). Например, выражение ('city + '-' + state) AS 'Target Region' в примере запроса возвращает 0 для всех значений.

Хотя этот оператор не поддерживается для этого типа данных, в Машинном обучении Azure ошибок не возникает. Не забудьте проверить результаты модуля Применение преобразования SQL перед использованием результирующего набора данных в эксперименте.

COALESCE и CASE

COALESCE вычисляет несколько аргументов по порядку и возвращает значение первого выражения, которое не вычисляется как NULL.

Например, этот запрос к набору данных Steel Annealing Multi-Class возвращает первый ненулевой флаг из списка столбцов, которые имеют взаимоисключающие значения. Если флаг не найден, возвращается строка "none".

SELECT classes, family, [product-type],  
COALESCE(bt,bc,bf,[bw/me],bl, "none") AS TemperType  
FROM t1;  

CASEОператор полезен для проверки значений и возврата нового значения на основе вычисленных результатов. SQLite поддерживает следующий синтаксис для CASE операторов:

  • CASE WHEN [условие] THEN [выражение] ELSE [выражение] END

  • CASE [выражение] WHEN [значение] THEN [выражение] ELSE [выражение] END

Например, предположим, что ранее использовался модуль преобразовать в значения индикатора , чтобы создать набор столбцов, содержащих значения true-false. Следующий запрос сворачивает значения в нескольких столбцах функций в один многозначный столбец.

SELECT userID, [smoker-0], [smoker-1],  
CASE  
WHEN [smoker-0]= '1' THEN 'smoker'   
WHEN [smoker-1]= '1' THEN 'nonsmoker'   
ELSE 'unknown'  
END AS newLabel  
FROM t1;  

Примеры

Пример использования этого модуля в экспериментах машинного обучения см. в этом примере в Коллекция решений ии Azure:

  • Применение преобразования SQL: в используются функции "Оценка ресторана", "Ресторан" и "набор данных клиентов ресторана" для иллюстрации простых соединений, инструкций SELECT и агрегатных функций.

Технические примечания

В этом разделе содержатся сведения о реализации, советы и ответы на часто задаваемые вопросы.

  • Входные данные всегда требуются для порта 1.

  • Если входной набор данных содержит имена столбцов, столбцы в результирующем наборе данных будут использовать имена столбцов из входного набора данных.

    Если у входного набора данных нет имен столбцов, имена столбцов в таблице автоматически создаются с использованием следующего соглашения об именовании: T1COL1, T1COL2, T1COL3 и т. д., где числа указывают индекс каждого столбца во входном наборе данных.

  • Для идентификаторов столбцов, содержащих пробелы или другие специальные символы, всегда заключите идентификатор столбца в квадратные или двойные кавычки при ссылке на столбец в SELECT WHERE предложениях или.

Неподдерживаемые инструкции

Хотя SQLite поддерживает большую часть стандарта ANSI SQL, в него не включены несколько функций, поддерживаемых коммерческими системами реляционных баз данных. Дополнительные сведения см. в разделе SQL как понятное для SQLite. Кроме того, при создании инструкций SQL учитывайте следующие ограничения.

  • SQLite использует динамическую типизацию значений, а не присвоение типа столбцу, как в большинстве систем реляционных баз данных. Кроме того, для нее характерна слабая типизация. Система также позволяет выполнять неявное преобразование типов.

  • LEFT OUTER JOIN реализован, но не RIGHT OUTER JOIN или FULL OUTER JOIN .

  • Можно использовать инструкции RENAME TABLE и ADD COLUMN с командой ALTER TABLE, но другие предложения не поддерживаются, в том числе DROP COLUMN, ALTER COLUMN и ADD CONSTRAINT.

  • Вы можете создать VIEW в SQLite, но последующие представления будут доступны только для чтения. Невозможно выполнить инструкцию DELETE, INSERT или UPDATE для представления. Но вы можете создать триггер, срабатывающий при попытке выполнить инструкцию DELETE, INSERT или UPDATE для представления, и выполнить другие операции в теле триггера.

Помимо списка неподдерживаемых функций, предоставляемых на официальном веб-сайте SQLite, следующий вики-сайт предоставляет список других неподдерживаемых функций: SQLite — неподдерживаемый SQL

Ожидаемые входные данные

Имя Type Описание
Table1 Таблица данных Input dataset1
Table2 Таблица данных Input dataset2
Table3 Таблица данных Input dataset3

Параметры модуля

Имя Диапазон Тип По умолчанию Описание
Сценарий SQL-запроса any StreamReader Инструкция SQL-запроса

Выходные данные

Имя Type Описание
Набор данных результатов Таблица данных Выходной набор данных

Исключения

Исключение Описание
Ошибка 0001 Исключение возникает, если не удалось найти один или несколько столбцов указанного набора данных.
Ошибка 0003 Исключение возникает, если один или несколько входных наборов данных имеют значение NULL или пусты.
Ошибка 0069 Логическая ошибка SQL или отсутствует база данных

Список ошибок, относящихся к модулям студии (классическая версия), см. в разделе машинное обучение коды ошибок.

Список исключений API см. в разделе Машинное обучение REST API коды ошибок.

См. также

Управлять
Преобразование данных
Список модулей в алфавитном порядке