Aplicar Transformação SQL

Importante

O suporte para o Estúdio de ML (clássico) terminará a 31 de agosto de 2024. Recomendamos a transição para o Azure Machine Learning até essa data.

A partir de 1 de dezembro de 2021, não poderá criar novos recursos do Estúdio de ML (clássico). Até 31 de agosto de 2024, pode continuar a utilizar os recursos existentes do Estúdio de ML (clássico).

• Consulte informações sobre projetos de machine learning em movimento de ML Studio (clássico) para Azure Machine Learning.
• Saiba mais sobre Azure Machine Learning.

A documentação do Estúdio de ML (clássico) está a ser descontinuada e poderá não ser atualizada no futuro.

Executa uma consulta SQLite sobre conjuntos de dados de entrada para transformar os dados

Categoria: Transformação de Dados / Manipulação

Nota

Aplica-se a: Machine Learning Studio (clássico) apenas

Módulos semelhantes de arrasto e queda estão disponíveis em Azure Machine Learning designer.

Visão geral do módulo

Este artigo descreve como utilizar o módulo de transformação SQL em Machine Learning Studio (clássico), para especificar uma consulta SQL num conjunto de dados de entrada ou conjuntos de dados.

SQL é útil quando precisa modificar os seus dados de forma complexa, ou persistir os dados para uso em outros ambientes. Por exemplo, utilizando o módulo de transformação SQL, pode:

• Crie tabelas para obter resultados e guarde os conjuntos de dados numa base de dados portátil.

• Realize transformações personalizadas em tipos de dados ou crie agregados.

• Execute SQL declarações de consulta para filtrar ou alterar dados e devolver os resultados da consulta como uma tabela de dados.

Importante

O motor SQL utilizado neste módulo é o SQLite. Se não estiver familiarizado com a sintaxe SQLite, leia a sintaxe e a secção de utilização deste artigo por exemplo.

O que é SQLite?

SQLite é um sistema de gestão de bases de dados relacional de domínio público que está contido numa biblioteca de programação C. SQLite é uma escolha popular como uma base de dados incorporada para armazenamento local em navegadores web.

A SQLite foi originalmente projetada em 2000 para a Marinha dos EUA, para suportar transações sem servidor. É um motor de base de dados autossuficiente que não tem sistema de gestão e, portanto, não requer nenhuma configuração ou administração.

Como configurar Aplicar SQL Transformação

O módulo pode levar até três conjuntos de dados como entradas. Quando referenciar os conjuntos de dados ligados a cada porta de entrada, deve utilizar os nomes t1, t2e t3. . O número da tabela indica o índice da porta de entrada.

O parâmetro restante é uma consulta SQL, que utiliza a sintaxe SQLite. Este módulo suporta todas as declarações padrão da sintaxe SQLite. Para obter uma lista de declarações não apoiadas, consulte a secção Notas Técnicas .

Sintaxe geral e utilização

• Ao escrever várias linhas na caixa de texto do script SQL, utilize um ponto e vírgula para terminar cada declaração. Caso contrário, as quebras de linha são convertidas em espaços.

  Por exemplo, as seguintes declarações são equivalentes:

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• Pode adicionar comentários utilizando quer -- no início de cada linha, quer através da utilização de textos através da utilização de /* */textos .

  Por exemplo, esta afirmação é válida:

  SELECT * from t1  
  /*WHERE ItemID BETWEEN 1 AND 100*/;  
  

• Se um nome de coluna duplicar o nome de uma palavra-chave reservada, o destaque da sintaxe é aplicado no texto dentro da caixa de texto SQL Script. Para evitar confusões, deve incluir nomes de colunas com parênteses quadrados (para seguir a convenção Transact-SQL) ou retrocessos ou marcas de aspas duplas (convenção de SQL ANSI).

  Por exemplo, na seguinte consulta sobre o conjunto de dados de doação de sangue, o tempo é um nome de coluna válido, mas também é uma palavra-chave reservada.

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

  Se executar a consulta como está, a consulta pode devolver os resultados corretos, mas dependendo do conjunto de dados, pode devolver um erro. Aqui estão alguns exemplos de como evitar a questão:

  -- 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;  
  

  Nota

  O destaque da sintaxe permanece na palavra-chave mesmo depois de ser incluído em cotações ou suportes.

• SQLite é caso insensível, exceto por alguns comandos que têm variantes sensíveis a casos com significados diferentes (GLOB vs. glob).

Declaração SELECT

Na declaração, os SELECT nomes das colunas que incluam espaços ou outros caracteres proibidos nos identificadores devem ser incluídos em marcas duplas, suportes quadrados ou caracteres retrospetivos (').

Por exemplo, esta consulta refere o conjunto de dados Two-Class Iris em t1, mas um nome de coluna contém um carácter proibido, pelo que o nome da coluna é incluído em aspas.

SELECT class, "sepal-length" FROM t1;  

Pode adicionar uma WHERE cláusula para filtrar valores no conjunto de dados.

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

A sintaxe SQLite não suporta a TOP palavra-chave, que é utilizada em Transact-SQL. Em vez disso, pode usar a LIMIT palavra-chave ou uma FETCH declaração.

Por exemplo, compare estas consultas no conjunto de dados do Aluguer de Bicicletas.

-- 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;  

Associações

Os exemplos a seguir utilizam o conjunto de dados de Classificações de Restaurantes na porta de entrada correspondente a t1, e o conjunto de dados de Características do Restaurante na porta de entrada correspondente a t2.

A seguinte declaração junta-se às duas tabelas para criar um conjunto de dados que combina as características de restaurante especificadas com classificações médias para cada restaurante.

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;  

Funções de agregação

Esta secção fornece exemplos básicos de algumas funções agregadas SQL comuns, utilizando a SQLite.

As funções agregadas atualmente suportadas são: AVG, COUNT, MAX, MIN, SUM. . TOTAL

A seguinte consulta devolve um conjunto de dados contendo o ID do restaurante, juntamente com a classificação média para o restaurante.

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

Trabalhar com cadeias

A SQLite suporta o operador de tubo duplo para cordas de concatenação.

A seguinte declaração cria uma nova coluna, concatenando duas colunas de texto.

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

Aviso

O operador de concatenação de cordas Transact-SQL não é suportado: + (Concatenação de cordas). Por exemplo, a expressão ('city + '-' + state) AS 'Target Region'na consulta de exemplo devolveria 0 para todos os valores.

No entanto, embora o operador não seja suportado para este tipo de dados, não é levantado nenhum erro em Machine Learning. Certifique-se de verificar os resultados da Apply SQL Transformation antes de utilizar o conjunto de dados resultante numa experiência.

COALESCE E CASO

COALESCE avalia múltiplos argumentos, por ordem, e devolve o valor da primeira expressão que não avalia para NU.

Por exemplo, esta consulta sobre o conjunto de dados multi-classes Steel Annealing devolve a primeira bandeira não nula de uma lista de colunas assumidas como sendo valores mutuamente exclusivos. Se não for encontrada nenhuma bandeira, a corda "nenhuma" é devolvida.

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

A CASE declaração é útil para testar valores e devolver um novo valor com base nos resultados avaliados. A SQLite suporta a seguinte sintaxe para CASE declarações:

• CASO QUANDO [condição] ENTÃO [expressão] ELSE [expressão] FIM

• CASO [expressão] QUANDO [valor] THEN [expressão] ELSE [expressão] FIM

Por exemplo, suponha que já tinha usado o módulo Converte para Valores Indicadores para criar uma definição de colunas de funcionalidades que contenha valores verdadeiros e falsos. A seguinte consulta colapsa os valores em várias colunas de recurso numa única coluna multivalorizada.

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;  

Exemplos

Para um exemplo de como este módulo pode ser usado em experiências de machine learning, consulte esta amostra na Galeria Azure AI:

• Aplicar SQL Transformação: Utiliza o conjunto de dados de Restaurantes, Recursos de Restaurantes e Restaurantes para ilustrar juntas simples, declarações selecionadas e funções agregadas.

Notas técnicas

Esta secção contém detalhes de implementação, dicas e respostas a perguntas frequentes.

• É sempre necessária uma entrada na porta 1.

• Se o conjunto de dados de entrada tiver nomes de colunas, as colunas do conjunto de dados de saída utilizarão os nomes das colunas a partir do conjunto de dados de entrada.

  Se o conjunto de dados de entrada não tiver nomes de colunas, os nomes das colunas na tabela são automaticamente criados utilizando a seguinte convenção de nomeação: T1COL1, T1COL2, T1COL3, e assim por diante, onde os números indicam o índice de cada coluna no conjunto de dados de entrada.

• Para identificadores de colunas que contenham um espaço ou outros caracteres especiais, coloque sempre o identificador de colunas em suportes quadrados ou marcas de aspas duplas quando se refere à coluna nas SELECT cláusulas ou WHERE cláusulas.

Declarações não apoiadas

Embora a SQLite suporte grande parte da norma ansi SQL, não inclui muitas funcionalidades suportadas por sistemas de base de dados relacionais comerciais. Para mais informações, consulte SQL como entendido pela SQLite. Além disso, esteja ciente das seguintes restrições ao criar declarações SQL:

• A SQLite utiliza a dactilografia dinâmica para valores, em vez de atribuir um tipo a uma coluna como na maioria dos sistemas de base de dados relacionais. É fracamente dactilografado, e permite conversão implícita do tipo.

• LEFT OUTER JOIN é implementado, mas não RIGHT OUTER JOIN ou FULL OUTER JOIN. .

• Pode usar RENAME TABLE e ADD COLUMN declarações com o ALTER TABLE comando, mas outras cláusulas não são apoiadas, incluindo DROP COLUMN, ALTER COLUMNe ADD CONSTRAINT.

• Pode criar uma vista dentro do SQLite, mas depois as vistas são apenas para leitura. Não pode executar uma DELETEdeclaração INSERTou UPDATE uma declaração sobre uma vista. No entanto, pode criar um gatilho que dispara numa tentativa de DELETE, INSERTou UPDATE numa vista, e realizar outras operações no corpo do gatilho.

Além da lista de funções não suportadas fornecidas no site oficial da SQLite, o seguinte wiki fornece uma lista de outras funcionalidades não apoiadas: SQLite - Não suportada SQL

Entradas esperadas

Nome	Tipo	Description
Table1	Tabela de Dados	Conjunto de dados de entrada1
Mesa2	Tabela de Dados	Conjunto de dados de entrada2
Quadro3	Tabela de Dados	Conjunto de dados de entrada3

Parâmetros do módulo

Nome	Intervalo	Tipo	Predefinição	Description
Script de consulta de SQL	qualquer	StreamReader		declaração de consulta SQL

Saídas

Nome	Tipo	Description
Conjunto de dados de resultados	Tabela de Dados	Conjunto de dados de saída

Exceções

Exceção	Description
Erro 0001	Uma exceção ocorre se uma ou mais colunas especificadas do conjunto de dados não puderem ser encontradas.
Erro 0003	Uma exceção ocorre se um ou mais dos conjuntos de dados de entrada forem nulos ou vazios.
Erro 0069	SQL erro lógico ou base de dados em falta

Para obter uma lista de erros específicos dos módulos Studio (clássicos), consulte Machine Learning Códigos de Erro.

Para obter uma lista de exceções da API, consulte Machine Learning CÓDIGOs de Erro da API REST.

Ver também

Manipulação
Transformação de Dados
Lista de Módulos A-Z