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

Важно!

Поддержка Студии машинного обучения (классической) будет прекращена 31 августа 2024 г. До этой даты рекомендуется перейти на Машинное обучение Azure.

Начиная с 1 декабря 2021 года вы не сможете создавать новые ресурсы Студии машинного обучения (классической). Существующие ресурсы Студии машинного обучения (классическая версия) можно будет использовать до 31 августа 2024 г.

• См. сведения о перемещении проектов машинного обучения из ML Studio (классической) в Машинное обучение Azure.
• См. дополнительные сведения о Машинном обучении Azure.

Поддержка документации по ML Studio (классической) прекращается, а сама документация может не обновляться в будущем.

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

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

Примечание

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

Подобные модули перетаскивания доступны в конструкторе машинного обучения Azure.

Обзор модуля

в этой статье описывается, как использовать модуль преобразования «применение SQL » в Машинное обучение Studio (классическая модель) для указания SQL запроса к входному набору данных или datasets.

SQL удобно использовать, если необходимо изменить данные сложными способами или сохранить данные для использования в других средах. например, с помощью модуля преобразования «применение 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), а также обратные или двойные кавычки (соглашение SQL ANSI).

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

  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 для всех значений.

однако, несмотря на то, что оператор не поддерживается для этого типа данных, в Машинное обучение не возникает ошибок. Не забудьте проверить результаты модуля Применение преобразования 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

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

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

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

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

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

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

Исключения

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

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

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

См. также раздел

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