Aplicar transformação de SQL

Artigo
05/06/2019

Importante

O suporte para o Machine Learning Studio (clássico) terminará em 31 de agosto de 2024. É recomendável fazer a transição para o Azure Machine Learning até essa data.

A partir de 1º de dezembro de 2021, você não poderá criar recursos do Machine Learning Studio (clássico). Até 31 de agosto de 2024, você pode continuar usando os recursos existentes do Machine Learning Studio (clássico).

Confira informações sobre como mover projetos de machine learning do ML Studio (clássico) para o Azure Machine Learning.
Saiba mais sobre o Azure Machine Learning.

A documentação do ML Studio (clássico) está sendo desativada e pode não ser atualizada no futuro.

Executa uma consulta do SQLite em conjuntos de dados de entrada para transformar os dados

Categoria: Transformação/manipulação de dados

Observação

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

Módulos semelhantes do tipo "arrastar e soltar" estão disponíveis no designer do Azure Machine Learning.

Visão geral do módulo

Este artigo descreve como usar o módulo Aplicar SQL Transformation no Machine Learning Studio (clássico) para especificar uma SQL em um conjuntos de dados de entrada ou conjuntos de dados.

SQL útil quando você precisa modificar seus dados de maneiras complexas ou persistir os dados para uso em outros ambientes. Por exemplo, usando o módulo Aplicar SQL Transformation, você pode:

Criar tabelas para resultados e salvar os conjuntos de dados em um banco de dados portátil.
Realizar transformações personalizadas em tipos de dados ou criar agregações.
Executar instruções de consulta SQL para filtrar ou alterar dados e retornar os resultados da consulta como uma tabela de dados.

Importante

O mecanismo SQL usado neste módulo é o SQLite. Se você não estiver familiarizado com a sintaxe SQLite, leia a seção sintaxe e uso deste artigo para ver exemplos.

O que é o SQLite?

SQLite é um sistema de gerenciamento de banco de dados relacional de domínio público que está contido em uma biblioteca de programação C. SQLite é uma opção popular como um banco de dados inserido para o armazenamento local em navegadores da Web.

SQLite foi originalmente projetado em 2000 para a Marinha dos EUA, para oferecer suporte a transações sem servidor. É um mecanismo de banco de dados independente que não possui um sistema de gerenciamento e portanto não requer configuração ou administração.

Como configurar Aplicar Transformação de SQL

O módulo pode ter até três conjuntos de dados como entradas. Quando você referencia os conjuntos de dados conectados a cada porta de entrada, é preciso usar os nomes t1, t2 e t3. O número da tabela indica o índice da porta de entrada.

O parâmetro restante é uma consulta SQL, que usa a sintaxe do SQLite. Este módulo oferece suporte a todas as instruções padrão da sintaxe do SQLite. Para obter uma lista de instruções sem suporte, veja a seção Observações técnicas.

Sintaxe e uso gerais

Ao digitar várias linhas na caixa de texto Script SQL, use um ponto e vírgula para encerrar cada instrução. Caso contrário, as quebras de linha serão convertidas em espaços.

Por exemplo, as instruções seguintes são equivalentes:
```
SELECT   
*   
from   
t1;  
```
```
SELECT * from t1;  
```
Você pode adicionar comentários usando no -- início de cada linha ou delimitando texto usando /* */.

Por exemplo, esta instruçã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 realce de sintaxe será aplicado ao texto dentro da SQL de texto Script. Para evitar confusão, você deve colocar nomes de coluna com colchetes (para seguir a convenção Transact-SQL) ou acionados ou aspas duplas (a convenção anSI SQL).

Por exemplo, na consulta a seguir no dataset de Exame de Sangue, Hora é 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 você executar a consulta como está, a consulta poderá retornar os resultados corretos, mas, dependendo do conjuntos de dados, ela poderá retornar um erro. Aqui estão alguns exemplos de como evitar o problema:
```
-- 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;  
```
Observação

O realce de sintaxe permanece na palavra-chave mesmo depois de ser entre aspas ou colchetes.
O SQLite não diferencia maiúsculas de minúsculas, exceto por alguns comandos que têm variantes que diferenciam maiúsculas de minúsculas com significados diferentes (GLOB versus glob).

Instrução SELECT

Na instrução SELECT , os nomes de coluna que incluem espaços ou outros caracteres proibidos em identificadores devem ser incluídos entre aspas duplas, colchetes ou caracteres de adição (').

Por exemplo, essa consulta faz referência ao Two-Class dados iris t1no , mas um nome de coluna contém um caractere proibido, portanto, o nome da coluna é entre aspas.

SELECT class, "sepal-length" FROM t1;

Você pode adicionar uma cláusula WHERE para filtrar valores no conjuntos de dados.

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

A sintaxe SQLite não dá suporte à palavra-chave TOP , que é usada em Transact-SQL. Em vez disso, você pode usar a palavra-chave LIMIT ou uma instrução FETCH .

Por exemplo, compare essas consultas no conjuntos de dados aluguel 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;

Junções

Os exemplos a seguir usam o conjunto de dados Classificações do restaurante na porta de entrada correspondente a t1 e o conjunto de dados Recursos do restaurante na porta de entrada correspondente a t2.

A instrução a seguir associa as duas tabelas para criar um conjunto de dados que combina os recursos especificados do restaurante às classificações médias de 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 seção fornece exemplos básicos de algumas funções SQL agregadas comuns, usando o SQLite.

As funções de agregação atualmente com suporte são: AVG, COUNT, MAX, MIN, SUM, TOTAL.

A consulta a seguir retorna um conjunto de dados que contém a ID do restaurante, junto com a classificação média do restaurante.

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

Trabalhando com cadeias de caracteres

SQLite suporta o operador de barra vertical dupla para concatenar cadeias de caracteres.

A instrução a seguir cria uma nova coluna, concatenando as duas colunas de texto.

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

Aviso

Não há suporte para o operador de concatenação SQL cadeia de caracteres Transact-SQL: + (Concatenação de cadeia de caracteres). Por exemplo, a expressão ('city + '-' + state) AS 'Target Region' na consulta de exemplo retornaria 0 para todos os valores.

No entanto, embora o operador não tenha suporte para esse tipo de dados, nenhum erro é gerado no Machine Learning. Certifique-se de verificar os resultados de Aplicar transformação de SQL antes de usar o conjunto de dados resultante em um experimento.

COALESCE e CASE

COALESCE avalia vários argumentos, em ordem, e retorna o valor da primeira expressão que não é avaliada como NULL.

Por exemplo, essa consulta no conjunto de dados Multiclasse do recozimento de aço retorna o primeiro sinalizador não nulo de uma lista de colunas para ter valores mutuamente exclusivos. Se nenhum sinalizador for encontrado, a cadeia de caracteres "nenhum" será retornado.

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

A CASE instrução é útil para testar valores e retornar um novo valor com base nos resultados avaliados. O SQLite dá suporte à seguinte sintaxe para instruções CASE :

CASE WHEN [condition] THEN [expression] ELSE [expression] END
CASE [expression] WHEN [value] THEN [expression] ELSE [expression] END

Por exemplo, suponha que você tenha usado anteriormente o módulo Converter em Valores de Indicador para criar um conjunto de colunas de recurso contendo valores true-false. A consulta a seguir colapsa os valores em várias colunas de recurso em uma única coluna de vários valores.

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 ver um exemplo de como esse módulo pode ser usado em experimentos de aprendizado de máquina, consulte este exemplo no Galeria de IA do Azure:

Aplicar SQL: usa o conjunto de dados Classificações do Restaurante, Recursos do Restaurante e Clientes do Restaurante para ilustrar junções simples, instruções de seleção e funções de agregação.

Observações técnicas

Essa seção contém detalhes de implementação, dicas e respostas para perguntas frequentes.

Uma entrada é sempre necessária na porta 1.
Se o conjunto de dados de entrada tiver nomes de coluna, as colunas do conjunto de dados de saída usarão os nomes das colunas do conjunto de dados de entrada.

Se o conjuntos de dados de entrada não tiver nomes de coluna, os nomes das colunas na tabela serão criados automaticamente usando a seguinte convenção de nomen entre eles: T1COL1, T1COL2, T1COL3 e assim por diante, em que os números indicam o índice de cada coluna no conjuntos de dados de entrada.
Para identificadores de coluna que contêm um espaço ou outros caracteres especiais, sempre coloque o identificador de coluna entre colchetes ou aspas duplas ao se referir à coluna nas cláusulas SELECT ou WHERE.

Instruções sem suporte

Embora SQLite suporte a maior parte do padrão ANSI SQL, ele não inclui muitos recursos com suporte nos sistemas de banco de dados relacional comercial. Para obter mais informações, veja SQL como compreendido pelo SQLite. Além disso, lembre-se das seguintes restrições ao criar instruções SQL:

O SQLite usa digitação dinâmica para valores, em vez de atribuir um tipo a uma coluna como na maioria dos sistemas de banco de dados relacional. Ele é digitado sem rigidez e permite a conversão implícita de tipos.
LEFT OUTER JOIN é implementado, mas não RIGHT OUTER JOIN ou FULL OUTER JOIN.
Você pode usar as instruções RENAME TABLE e ADD COLUMN com o comando ALTER TABLE, mas outras cláusulas não são suportadas, incluindo DROP COLUMN, ALTER COLUMN e ADD CONSTRAINT.
Você pode criar uma VIEW dentro do SQLite, mas depois disso as exibições serão somente leitura. Você não pode executar uma instrução DELETE, INSERT ou UPDATE em uma exibição. No entanto, você pode criar um disparador que é acionado em uma tentativa de DELETE, INSERT ou UPDATE em uma exibição e executar outras operações no corpo do gatilho.

Além da lista de funções sem suporte fornecida pelo site oficial do SQLite, o wiki a seguir fornece uma lista de outros recursos sem suporte: SQLite – SQL sem suporte

Entradas esperadas

Nome	Tipo	Descrição
Table1	Tabela de Dados	Conjunto de dados de entrada1
Tabela2	Tabela de Dados	Conjunto de dados de entrada2
Tabela3	Tabela de Dados	Conjunto de dados de entrada3

Parâmetros do módulo

Nome	Intervalo	Type	Padrão	Descrição
Script de consulta do SQL	any	StreamReader		Instrução de consulta SQL

Saídas

Nome	Tipo	Descrição
Conjunto de dados de resultados	Tabela de Dados	Conjunto de dados de saída

Exceções

Exceção	Descrição
Erro 0001	Ocorre uma exceção se uma ou mais colunas especificadas do conjunto de dados não podem ser encontradas.
Erro 0003	Ocorre uma exceção se um ou mais dos conjuntos de dados de entrada é nulo ou vazio.
Erro 0069	SQL lógico ou banco de dados ausente

Para ver uma lista de erros específicos dos módulos do Studio (clássico), consulte Machine Learning Códigos de erro.

Para ver uma lista de exceções de API, consulte Machine Learning códigos de erro da API REST.

Confira também

Manipulação
Transformação de dados
Lista de Módulo A-Z