Aplicación de transformaciones de SQL

Artículo
05/06/2019

Importante

El soporte técnico de Machine Learning Studio (clásico) finalizará el 31 de agosto de 2024. Se recomienda realizar la transición a Azure Machine Learning antes de esa fecha.

A partir del 1 de diciembre de 2021 no se podrán crear recursos de Machine Learning Studio (clásico). Hasta el 31 de agosto de 2024, puede seguir usando los recursos de Machine Learning Studio (clásico) existentes.

Consulte la información acerca de traslado de proyectos de aprendizaje automático de ML Studio (clásico) a Azure Machine Learning.
Más información sobre Azure Machine Learning.

La documentación de ML Studio (clásico) se está retirando y es posible que no se actualice en el futuro.

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

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

Nota:

Solo se aplica a: Machine Learning Studio (clásico)

Hay módulos para arrastrar y colocar similares en el diseñador de Azure Machine Learning.

Información general sobre el módulo

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

SQL es útil cuando necesita modificar los datos de maneras complejas o conservar los datos para usarlos en otros entornos. Por ejemplo, mediante el módulo Aplicar SQL transformación, 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.

Importante

El motor de SQL que se usa en este módulo es SQLite. Si no está familiarizado con la sintaxis de SQLite, asegúrese de leer la sección sintaxis y uso de este artículo para obtener ejemplos.

¿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.

Uso y sintaxis general

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 mediante al -- principio de cada línea o si incluye texto mediante /* */.

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 SQL de texto Script. Para evitar confusiones, debe incluir nombres de columna entre corchetes (para seguir la convención de Transact-SQL) o comillas simples o comillas dobles (la convención ansi SQL).

Por ejemplo, en la siguiente consulta del conjunto de datos Desa 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 tal y como está, la consulta podría devolver los resultados correctos, pero en función del conjunto de datos, 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 incluirse entre comillas o corchetes.
SQLite no distingue mayúsculas de minúsculas, excepto para algunos comandos que tienen variantes que distinguen mayúsculas de minúsculas con significados diferentes (GLOB frente a glob).

Instrucción SELECT

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

Por ejemplo, esta consulta hace referencia al conjunto de datos Two-Class Iris t1en , pero un nombre de columna contiene un carácter prohibido, por lo que el nombre de columna se incluye entre comillas.

SELECT class, "sepal-length" FROM t1;

Puede agregar una cláusula para WHERE filtrar los valores del conjunto de datos.

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

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

Por ejemplo, compare estas consultas en el conjunto de datos Bike Rental.

-- 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 SQL de agregado, mediante SQLite.

Las funciones de agregado admitidas 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

Advertencia

No se admite el operador de concatenación de cadenas Transact-SQL: + (Concatenación de cadenas). Por ejemplo, la expresión ('city + '-' + state) AS 'Target Region' en la consulta de ejemplo devolvería 0 para todos los valores.

Sin embargo, aunque el operador no se admite para este tipo de datos, no se produce ningún error en Machine Learning. Asegúrese de comprobar los resultados de Aplicar transformación de SQL antes de utilizar el conjunto de datos resultante en un experimento.

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 en función de los resultados evaluados. SQLite admite la siguiente sintaxis para 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 ha usado el módulo Convertir en valores de indicador para crear columnas de características establecidas que contienen valores true-false. La consulta siguiente contrae los valores de varias columnas de características en una sola columna de varios 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;

Ejemplos

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

Aplicar SQL transformación: usa el conjunto de datos Restaurant Ratings, Restaurant Features y Restaurant Customers 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 mediante la siguiente convención de nomenclatura: T1COL1, T1COL2, T1COL3, y así sucesivamente, donde los números indican el índice de cada columna en el 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

Nombre	Tipo	Descripción
Table1	Tabla de datos	Conjunto de datos de entrada1
Tabla 2	Tabla de datos	Conjunto de datos de entrada2
Tabla3	Tabla de datos	Conjunto de datos de entrada3

Parámetros del módulo

Nombre	Intervalo	Tipo	Valor predeterminado	Descripción
Script de consulta SQL	cualquiera	StreamReader		Instrucción de consulta SQL

Salidas

Nombre	Tipo	Descripción
Conjunto de datos de resultados	Tabla de datos	Conjunto de datos de salida

Excepciones

Excepción	Descripción
Error 0001	Se produce una excepción si no se encuentran una o varias de las columnas especificadas del conjunto de datos.
Error 0003	Se produce una excepción si uno o más de los conjuntos de datos de entrada es nulo o está vacío.
Error 0069	SQL error lógico o falta la base de datos

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

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

Consulte también

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