Aplicación de transformaciones de SQL

• 05/06/2019
• Tiempo de lectura: 9 minutos
• • x
  • o

Ejecuta una consulta de SQLite en conjuntos de datos de entrada para transformar los datos

Categoría: transformación y manipulación de datos

Información general sobre el módulo

En este artículo se describe cómo usar el módulo aplicar transformación de SQL en Azure machine learning Studio (clásico) para especificar una consulta SQL en un conjunto de datos de entrada o en conjuntos de datos.

SQL es útil cuando necesita modificar los datos de maneras complejas o conservar los datos para su uso en otros entornos. Por ejemplo, mediante el módulo aplicar transformación de SQL , puede:

• Crear tablas para los resultados y guarde los conjuntos de datos en una base de datos portátil.

• Realizar transformaciones personalizadas en tipos de datos o crear agregados.

• Ejecutar instrucciones de consulta SQL para filtrar o modificar datos y devolver los resultados de la consulta como una tabla de datos.

¿Qué es SQLite?

SQLite es un sistema de administración de bases de datos relacionales de dominio público que se encuentra en una biblioteca de programación de C. SQLite es una opción muy habitual de base de datos incrustada para el almacenamiento local en exploradores web.

SQLite se diseñó originalmente en 2000 para la Marina estadounidense, para realizar las transacciones sin servidor. Es un motor de base de datos independiente que no tiene ningún sistema de administración y, por tanto, no requiere ninguna configuración o administración.

Configuración del módulo de aplicación de transformaciones de SQL

El módulo puede tardar hasta tres conjuntos de datos como entradas. Al hacer referencia a los conjuntos de datos conectados a cada puerto de entrada, debe utilizar los nombres t1, t2 y t3. El número de la tabla indica el índice del puerto de entrada.

El parámetro restante es una consulta SQL, que utiliza la sintaxis de SQLite. Este módulo admite todas las instrucciones estándar de la sintaxis de SQLite. Para obtener una lista de instrucciones no admitidas, consulte la sección Notas técnicas.

Sintaxis y uso generales

• Al escribir varias líneas en el cuadro de texto SQL Script (Script de SQL), utilice un punto y coma para terminar cada instrucción. De lo contrario, los saltos de línea se convierten en espacios.

  Por ejemplo, las siguientes instrucciones son equivalentes:

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• Puede agregar comentarios usando -- al principio de cada línea o encerrando el texto entre /* */.

  Por ejemplo, esta instrucción es válida:

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

• Si un nombre de columna duplica el nombre de una palabra clave reservada, el resaltado de sintaxis se aplica al texto dentro del cuadro de texto script de SQL . Para evitar confusiones, debe incluir los nombres de columna entre corchetes (para seguir la Convención de Transact-SQL) o los acentos o las comillas dobles (Convención de ANSI SQL).

  Por ejemplo, en la siguiente consulta del conjunto de claves de la donación de sangre, Time es un nombre de columna válido, pero también es una palabra clave reservada.

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

  Si ejecuta la consulta como está, es posible que la consulta devuelva los resultados correctos, pero, dependiendo del conjunto de resultados, podría devolver un error. Estos son algunos ejemplos de cómo evitar el 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;  
  

  Nota

  El resaltado de sintaxis permanece en la palabra clave incluso después de encerrarse entre comillas o corchetes.

• SQLite no distingue entre mayúsculas y minúsculas, salvo por algunos comandos que tienen variantes que distinguen mayúsculas de minúsculas y tienen diferentes significados (Glob frente a Glob).

Instrucción SELECT

En la SELECT instrucción, los nombres de columna que incluyen espacios u otros caracteres prohibidos en identificadores deben ir entre comillas dobles, corchetes o caracteres de acento grave (').

Por ejemplo, esta consulta hace referencia al conjunto de Two-Class iris en t1 , pero un nombre de columna contiene un carácter prohibido, por lo que el nombre de la columna está entre comillas.

SELECT class, "sepal-length" FROM t1;  

Puede Agregar una WHERE cláusula para filtrar los valores del conjunto de DataSet.

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

La sintaxis de SQLite no admite la TOP palabra clave, que se usa en Transact-SQL. En su lugar, puede usar la LIMIT palabra clave o una FETCH instrucción.

Por ejemplo, Compare estas consultas en el conjunto de conjuntos Bike.

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

Combinaciones

Los ejemplos siguientes usan el conjunto de datos de las clasificaciones de restaurantes en el puerto de entrada correspondiente a t1, mientras que el conjunto de datos de las características de los restaurantes se usan en el puerto de entrada que corresponde a t2.

La instrucción siguiente une las dos tablas para crear un conjunto de datos que combina las características del restaurante especificado con un promedio de clasificaciones 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;  

Funciones de agregado

En esta sección se proporcionan ejemplos básicos de algunas funciones comunes de agregado de SQL, con SQLite.

Las funciones de agregado que se admiten actualmente son: AVG , COUNT , MAX , MIN , SUM , TOTAL .

La consulta siguiente devuelve un conjunto de datos que contiene el identificador del restaurante, junto con el promedio de clasificación para el restaurante.

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

Trabajo con cadenas

SQLite es compatible con el operador de canalización doble para concatenar cadenas.

La siguiente instrucción crea una nueva columna mediante la concatenación de dos columnas de texto.

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

COALESCE y CASE

COALESCE evalúa varios argumentos, en orden, y devuelve el valor de la primera expresión que no se evalúa como NULL.

Por ejemplo, si aplicamos esta consulta al conjunto de datos sobre las distintas clases de recocido de acero, se devuelve el primer indicador que no sea nulo de una lista de columnas que se supone que tienen valores mutuamente excluyentes. Si no se encuentra ninguna marca, se devuelve la cadena "none".

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

La CASE instrucción es útil para probar valores y devolver un nuevo valor basándose en los resultados evaluados. SQLite admite la siguiente sintaxis para las CASE instrucciones:

• CASE WHEN [condición] THEN [expresión] ELSE [expresión] END

• CASE [expresión] WHEN [valor] THEN [expresión] ELSE [expresión] END

Por ejemplo, supongamos que anteriormente usó el módulo convertir en valores de indicador para crear columnas de características de conjunto que contengan valores true-false. La siguiente consulta contrae los valores de varias columnas de características en una sola columna multivalor.

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;  

Ejemplos

Para obtener un ejemplo de cómo se puede usar este módulo en experimentos de aprendizaje automático, vea este ejemplo en el Azure AI Gallery:

• Aplicar transformación de SQL: utiliza el conjunto de elementos de la clasificación de restaurante, las características de restaurante y los clientes de restaurante para ilustrar combinaciones simples, instrucciones SELECT y funciones de agregado.

Notas técnicas

Esta sección contiene detalles de implementación, sugerencias y respuestas a las preguntas más frecuentes.

• Siempre se requiere una entrada en el puerto 1.

• Si el conjunto de datos de entrada tiene nombres de columna, las columnas del conjunto de datos de salida utilizarán los nombres de columna del conjunto de datos de entrada.

  Si el conjunto de datos de entrada no tiene nombres de columna, los nombres de columna de la tabla se crean automáticamente con la Convención de nomenclatura siguiente: T1COL1, T1COL2, T1COL3, etc., donde los números indican el índice de cada columna del conjunto de datos de entrada.

• En el caso de los identificadores de columna que contienen un espacio u otros caracteres especiales, incluya siempre el identificador de columna entre corchetes o comillas dobles al hacer referencia a la columna en las cláusulas SELECT o WHERE.

Instrucciones no admitidas

Aunque SQLite admite gran parte del estándar ANSI SQL, no incluye muchas características compatibles con los sistemas de bases de datos relacionales comerciales. Para más información, consulte SQL como lo entiende SQLite. Además, tenga en cuenta las siguientes restricciones al crear instrucciones SQL:

• SQLite usa la escritura dinámica para los valores, en lugar de asignar un tipo a una columna como en la mayoría de los sistemas de bases de datos relacionales. Tiene un establecimiento flexible de tipos y permite la conversión implícita de tipos.

• LEFT OUTER JOIN está implementado, pero no RIGHT OUTER JOIN ni FULL OUTER JOIN.

• Puede utilizar las instrucciones RENAME TABLE y ADD COLUMN con el comando ALTER TABLE, pero no se admiten otras cláusulas, como DROP COLUMN, ALTER COLUMN y ADD CONSTRAINT.

• Puede crear una vista dentro de SQLite, pero las vistas posteriores son de solo lectura. No se puede ejecutar una instrucción DELETE, INSERT o UPDATE en una vista. Sin embargo, puede crear un desencadenador que se active al intentar DELETE, INSERT o UPDATE en una vista y realizar otras operaciones en el cuerpo del desencadenador.

Además de la lista de funciones no admitidas que se proporcionan en el sitio oficial de SQLite, la siguiente wiki proporciona una lista de otras características no admitidas: SQLite: SQL no admitido

Entradas esperadas

Parámetros del módulo

Salidas

Excepciones

Para obtener una lista de los errores específicos de los módulos de Studio (clásico), consulte Machine Learning de los códigos de error.

Para obtener una lista de excepciones de API, consulte códigos de error de la API de REST de machine learning.

Consulte también

Manipula
Transformación de datos
Lista de módulos A-Z