Aplicar Transformação SQL

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)

Este conteúdo diz respeito apenas ao Studio (clássico). Módulos semelhantes de arrasto e queda foram adicionados ao designer de Aprendizagem automática Azure. Saiba mais neste artigo comparando as duas versões.

Visão geral do módulo

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

O SQL é útil quando precisa modificar os seus dados de forma complexa, ou persistir os dados para utilização em outros ambientes. Por exemplo, utilizando o módulo de transformação de SQL Aplicar, 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 declarações de consulta SQL para filtrar ou alterar dados e devolva os resultados da consulta como uma tabela de dados.

Importante

O motor SQL utilizado neste módulo é 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 A Transformação SQL

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 t2 , e 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 sql Script, 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 divulgação de texto /* */ .

    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 do Script SQL. Para evitar confusões, deve incluir nomes de colunas com suportes quadrados (para seguir a convenção Transact-SQL) ou backticks ou marcas de aspas duplas (convenção ANSI SQL).

    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 SELECT declaração, os 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 t1 em , 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 t1 a , 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 através da concatenação de 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 na Aprendizagem automática Azure. 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 ter valores mutuamente exclusivos. Se não for encontrada nenhuma bandeira, a corda "nenhuma" será 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] END

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 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 a transformação DO SQL: Utiliza as classificações de restaurantes, características do restaurante e conjunto de dados de clientes de restaurante 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 coluna 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 o SQL como entendido pela SQLite. Além disso, esteja ciente das seguintes restrições ao criar declarações SQL:

  • A SQLite utiliza 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 a 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 , e ALTER COLUMN ADD CONSTRAINT .

  • Pode criar uma vista dentro do SQLite, mas depois as vistas são apenas para leitura. Não pode executar uma DELETE declaração ou uma declaração sobre uma INSERT UPDATE vista. No entanto, pode criar um gatilho que dispara numa tentativa DELETE INSERT de, ou 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 - SQL não suportado

Entradas esperadas

Nome Tipo Descrição
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 Descrição
Roteiro de consulta SQL qualquer StreamReader Declaraçã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 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 códigos de erro de aprendizagem automática.

Para obter uma lista de exceções da API, consulte códigos de erro da API de aprendizagem automática.

Ver também

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