Aplicar transformação de SQL

• 05/06/2019
• 9 minutos para o fim da leitura
• • x

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

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

Visão geral do módulo

Este artigo descreve como usar o módulo aplicar transformação de SQL no Azure Machine Learning Studio (clássico) para especificar uma consulta SQL em um conjunto de dados de entrada ou DataSets.

O SQL é útil quando você precisa modificar seus dados de maneiras complexas ou manter os dados para uso em outros ambientes. Por exemplo, usando o módulo aplicar transformação SQL , 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.

O que é 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 SQL

O módulo pode ter até três conjuntos de dados como entradas. Ao fazer referência aos conjuntos de dados conectados a cada porta de entrada, você deve 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, consulte a seção observações técnicas .

Sintaxe e uso geral

• 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 colocando o texto entre /* */.

  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 caixa de texto script SQL . Para evitar confusão, você deve colocar os nomes de coluna com colchetes (para seguir a Convenção Transact-SQL) ou backtiques ou aspas duplas (a Convenção SQL ANSI).

  Por exemplo, na consulta a seguir no conjunto de colunas de doação de sangue, time é 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 conjunto de um, 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 colocado entre aspas ou colchetes.

• O SQLite não diferencia maiúsculas de minúsculas, exceto para alguns comandos que têm variantes com diferenciação de maiúsculas e minúsculas com significados diferentes (glob versus glob).

Instrução SELECT

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

Por exemplo, essa consulta faz referência a Two-Class conjunto de t1 espaço íris em, mas um nome de coluna contém um caractere proibido, portanto, o nome da coluna é colocado entre aspas.

SELECT class, "sepal-length" FROM t1;  

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

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

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

Por exemplo, Compare essas consultas no conjunto de consulta de 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 de agregação SQL comuns, usando o SQLite.

Atualmente, as funções de agregação 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  

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 CASE instruções:

• 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 para valores de indicador para criar um conjunto de colunas de recursos que contenham valores true-false. A consulta a seguir recolhe os valores em várias colunas de recurso em uma única coluna com 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 obter 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 transformação SQL: usa as classificações do restaurante, os recursos do restaurante e o conjunto de os clientes do restaurante para ilustrar junções simples, instruções SELECT e funções de agregação.

Notas técnicas

Esta 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 conjunto de dados de entrada não tiver nomes de coluna, os nomes de coluna na tabela serão criados automaticamente usando a seguinte convenção de nomenclatura: T1COL1, T1COL2, T1COL3 e assim por diante, em que os números indicam o índice de cada coluna no conjunto 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 SELECT WHERE cláusulas ou.

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, consulte SQL como compreendido pelo SQLite. Além disso, esteja ciente 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 não suportadas fornecida no site do SQLite oficial, o seguinte wiki fornece uma lista de outros recursos sem suporte: SQLite-SQL sem suporte

Entradas esperadas

Parâmetros do módulo

Saídas

Exceções

Para obter uma lista de erros específicos para módulos do Studio (clássicos), consulte Machine Learning códigos de erro.

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

Confira também

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