BigQuery admite dos dialectos de SQL: SQL estándar y SQL heredado. En este tema se explican las diferencias entre ambos, se incluye la sintaxis, las funciones y la semántica y se brindan ejemplos de algunos de los aspectos destacados de SQL estándar.
Comparación entre SQL estándar y SQL heredado
Antes, BigQuery ejecutaba consultas con un dialecto de SQL no estándar conocido como BigQuery SQL. Con BigQuery 2.0, se lanzó la compatibilidad con SQL estándar y se cambió el nombre de BigQuery SQL a SQL heredado. SQL estándar es el dialecto de SQL preferido para consultar datos almacenados en BigQuery.
¿Tengo que migrar a SQL estándar?
Recomendamos migrar de SQL heredado a SQL estándar, pero no es obligatorio. Por ejemplo, supongamos que ejecutas muchas consultas que usan SQL heredado, pero deseas aprovechar una característica de SQL estándar para una consulta nueva. Puedes crear consultas nuevas con SQL estándar que se ejecutan junto con las consultas que usan SQL heredado.
Habilita SQL estándar
Cuando ejecutas una consulta, tienes la opción de usar SQL heredado o SQL estándar. Para obtener información sobre cómo cambiar entre dialectos SQL, consulta Cambia los dialectos de SQL.
Ventajas de SQL estándar
SQL estándar cumple con el estándar de SQL 2011 y tiene extensiones que admiten consultas de datos anidados y repetidos. Las siguientes son algunas de las ventajas que tiene sobre SQL heredado:
- Capacidad de composición mediante cláusulas
WITHy funciones SQL - Subconsultas en la lista
SELECTy la cláusulaWHERE - Subconsultas correlacionadas
- Tipos de datos
ARRAYySTRUCT - Instrucciones de inserción, actualización y eliminación
COUNT(DISTINCT <expr>)es exacto y escalable, lo que proporciona la precisión deEXACT_COUNT_DISTINCTsin sus limitaciones- Despliegue automático de predicados mediante
JOIN - Predicados de
JOINcomplejos que incluyen expresiones arbitrarias
Para ver ejemplos que demuestran algunas de estas funciones, consulta Aspectos destacados de SQL estándar.
Diferencias de tipos
Los tipos de SQL heredado tienen un equivalente en SQL estándar y viceversa. En algunos casos, el tipo tiene un nombre diferente. En la siguiente tabla, se enumera cada tipo de datos de SQL heredado y su equivalente en SQL estándar.
| SQL heredado | SQL estándar | Notas |
|---|---|---|
BOOL |
BOOL |
|
INTEGER |
INT64 |
|
FLOAT |
FLOAT64 |
|
STRING |
STRING |
|
BYTES |
BYTES |
|
RECORD |
STRUCT |
|
REPEATED |
ARRAY |
|
TIMESTAMP |
TIMESTAMP |
Consulta diferencias de TIMESTAMP. |
DATE |
DATE |
SQL heredado tiene compatibilidad limitada con DATE. |
TIME |
TIME |
SQL heredado tiene compatibilidad limitada con TIME |
DATETIME |
DATETIME |
SQL heredado tiene compatibilidad limitada con DATETIME |
Para obtener más información sobre el sistema de tipos de SQL estándar, consulta la referencia de tipos de datos de SQL estándar. Para obtener más información sobre los tipos de datos de BigQuery, consulta la referencia de tipos de datos de BigQuery.
Diferencias de TIMESTAMP
SQL estándar tiene un rango más estricto de valores TIMESTAMP válidos que SQL heredado. En SQL estándar, los valores válidos de TIMESTAMP están en el rango de 0001-01-01 00:00:00.000000 a 9999-12-31 23:59:59.999999 Por ejemplo, puedes seleccionar el valor mínimo y el máximo de TIMESTAMP con SQL estándar:
#standardSQL
SELECT
min_timestamp,
max_timestamp,
UNIX_MICROS(min_timestamp) AS min_unix_micros,
UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
SELECT
TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);
Esta consulta muestra -62135596800000000 como min_unix_micros y 253402300799999999 como max_unix_micros.
Si seleccionas una columna que contiene valores de marca de tiempo fuera de este rango, recibirás un error como el que se muestra a continuación:
#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;
Esta consulta muestra el siguiente error:
Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]
Para corregir el error, puedes definir y usar una función definida por el usuario a fin de filtrar las marcas de tiempo no válidas:
#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
t >= TIMESTAMP('0001-01-01 00:00:00') AND
t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);
También puedes usar la función SAFE_CAST con la columna de marca de tiempo. Por ejemplo:
#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;
Esta consulta muestra NULL en lugar de una string de marca de tiempo para valores de marca de tiempo no válidos.
Diferencias de sintaxis
Descarta identificadores no válidos y palabras clave reservadas
En SQL heredado, se usan corchetes [] para descartar las palabras clave reservadas y los identificadores que contienen caracteres no válidos, como un espacio o guion -.
En SQL estándar, se usan acentos graves ` para descartar identificadores y palabras clave. Por ejemplo:
#standardSQL
SELECT
word,
SUM(word_count) AS word_count
FROM
`bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;
SQL heredado permite palabras clave reservadas en algunos lugares en los que SQL estándar no las permite.
Por ejemplo, la siguiente consulta falla debido a un Syntax error si se usa SQL estándar:
#standardSQL
SELECT
COUNT(*) AS rows
FROM
`bigquery-public-data.samples.shakespeare`;
Para corregir el error, descarta el alias rows con acentos graves:
#standardSQL
SELECT
COUNT(*) AS `rows`
FROM
`bigquery-public-data.samples.shakespeare`;
Para obtener una lista de palabras clave reservadas y cuáles son los identificadores válidos, consulta Estructura léxica.
Nombres de tablas calificados para el proyecto
En SQL heredado, si deseas consultar una tabla con un nombre calificado para el proyecto, debes usar dos puntos : como separador. Por ejemplo:
#legacySQL
SELECT
word
FROM
[bigquery-public-data:samples.shakespeare]
LIMIT 1;
En SQL estándar, se usa un punto, ., en su lugar. Por ejemplo:
#standardSQL
SELECT
word
FROM
`bigquery-public-data.samples.shakespeare`
LIMIT 1;
Si el nombre de tu proyecto incluye un dominio, como example.com:myproject, debes usar example.com:myproject como el nombre de proyecto, incluidos los dos puntos (:).
Decoradores de tabla
SQL estándar no admite decoradores de tablas.
Puedes conseguir la semántica de los decoradores de instantáneas mediante la cláusula FOR SYSTEM_TIME AS OF, que hace referencia a la versión histórica de una tabla en una marca de tiempo especificada. Para obtener más información, consulta Cómo acceder a datos históricos con viajes en el tiempo.
No hay un equivalente exacto a los decoradores de rangos en SQL estándar. Puedes conseguir una semántica similar si creas una tabla particionada por tiempo y utilizas un filtro de partición cuando consultas datos. Para obtener más información, visita Consulta tablas particionadas.
Otra opción es crear tablas fragmentadas por fecha y filtrar en la seudocolumna _TABLE_SUFFIX. Para obtener más información, consulta Tablas comodín.
Funciones comodín
SQL estándar no admite las funciones TABLE_DATE_RANGE, TABLE_DATE_RANGE_STRICT o TABLE_QUERY.
Puedes obtener la misma semántica de TABLE_DATE_RANGE y TABLE_QUERY con un filtro en la seudocolumna _TABLE_SUFFIX. Por ejemplo, considera la siguiente consulta de SQL heredado, que cuenta el número de filas durante 2010 y 2011 en las tablas del GSOD (resumen global del día) de la Oficina Nacional de Administración Oceánica y Atmosférica:
#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
'table_id IN ("gsod2010", "gsod2011")');
A continuación, se muestra una consulta equivalente con SQL estándar:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");
Para obtener más información, incluidos ejemplos que usen TABLE_DATE_RANGE, consulta Migra funciones comodín de tablas de SQL heredado.
Operador de coma con tablas
En SQL heredado, el operador de coma (,) tiene el significado no estándar de UNION ALL cuando se aplica a las tablas. En SQL estándar, el operador de coma tiene el significado estándar de JOIN. Por ejemplo, considera la siguiente consulta de SQL heredado:
#legacySQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y),
(SELECT 2 AS x, "bar" AS y);
Esto equivale a la siguiente consulta de SQL estándar:
#standardSQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y UNION ALL
SELECT 2 AS x, "bar" AS y);
Además, ten en cuenta que UNION ALL asocia las columnas por posición y no por nombre en SQL estándar. La consulta anterior es equivalente a esta:
#standardSQL
SELECT
x,
y
FROM
(SELECT 1 AS x, "foo" AS y UNION ALL
SELECT 2, "bar");
Un uso común del operador de coma en SQL estándar es usar JOIN con un arreglo.
Por ejemplo:
#standardSQL
WITH T AS (
SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
SELECT 1, [4, 5])
SELECT
x,
y
FROM
T,
UNNEST(arr) AS y;
Esto muestra el producto cruzado de la tabla T con los elementos de arr. También puedes expresar la consulta en SQL estándar de la siguiente forma:
#standardSQL
WITH T AS (
SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
SELECT 1, [4, 5])
SELECT
x,
y
FROM
T
JOIN
UNNEST(arr) AS y;
En esta consulta, JOIN tiene el mismo significado que el operador de coma (,) que separa T y UNNEST(arr) AS y en el ejemplo anterior.
Vistas lógicas
No puedes usar SQL estándar para consultar una vista lógica definida con SQL heredado y viceversa, debido a las diferencias de sintaxis y semántica entre los dialectos. En su lugar, deberías crear una vista nueva que use SQL estándar, quizás con un nombre diferente, para reemplazar una vista que usa SQL heredado.
Como ejemplo, supongamos que la vista V se define con SQL heredado de la siguiente forma:
#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;
Supongamos que la vista W se define con SQL heredado de la siguiente forma:
#legacySQL
SELECT user, action, day
FROM V;
Supongamos que ejecutas la siguiente consulta de SQL heredado a diario, pero deseas migrarla para usar SQL estándar:
#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;
Una ruta de migración posible es crear vistas nuevas con nombres diferentes. A continuación, se detallan los pasos que debes seguir:
Crea una vista llamada V2 mediante SQL estándar con el siguiente contenido:
#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;
Crea una vista llamada W2 mediante SQL estándar con el siguiente contenido:
#standardSQL
SELECT user, action, day
FROM V2;
Cambia la consulta que se ejecuta a diario para que use SQL estándar y haga referencia a W2 en su lugar:
#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;
También puedes borrar las vistas V y W, y recrearlas mediante SQL estándar con los mismos nombres. Sin embargo, con esta opción, tendrías que migrar al mismo tiempo todas tus consultas que hagan referencia a V o W para que usen SQL estándar.
Comparación de funciones
A continuación, se presenta una lista parcial de las funciones de SQL heredado y sus equivalentes en SQL estándar.
Para obtener más información sobre las funciones de SQL estándar, consulta Funciones y operadores.
Comparación de funciones COUNT
SQL heredado y SQL estándar tienen una función COUNT. Sin embargo, cada función se comporta de manera diferente según el dialecto de SQL que uses.
En SQL heredado, COUNT(DISTINCT x) muestra un recuento aproximado. En SQL estándar, muestra un recuento exacto. Para realizar un recuento aproximado de valores distintos que se ejecute más rápido y necesite menos recursos, usa APPROX_COUNT_DISTINCT.
Comparación de funciones de URL
SQL heredado y SQL estándar contienen funciones para analizar las URL. En SQL heredado, las funciones son HOST(url), TLD(url) y DOMAIN(url). En SQL estándar, estas funciones son NET.HOST(url), NET.PUBLIC_SUFFIX(url) y NET.REG_DOMAIN(url).
Mejoras respecto a las funciones de SQL heredado
- Las funciones URL de SQL estándar pueden analizar las URL que comienzan con “//”.
- Cuando la entrada no cumple con RFC 3986 o no es una URL (por ejemplo, “mailto:?to=&subject=&body=”), se la analiza con reglas distintas. En particular, las funciones URL de SQL estándar pueden analizar entradas no estándar sin “//”, como “www.google.com”. Para obtener los mejores resultados, se recomienda asegurarse de que las entradas sean URL y cumplan con RFC 3986.
NET.PUBLIC_SUFFIXmuestra resultados sin los puntos iniciales. Por ejemplo, muestra “com” en lugar de “.com”. Esto cumple con el formato de la lista de sufijos públicos.NET.PUBLIC_SUFFIXyNET.REG_DOMAINadmiten letras mayúsculas y nombres de dominio internacionalizados.TLDyDOMAINno las admiten (pueden mostrar resultados inesperados).
Diferencias menores en casos extremos
- Si la entrada no contiene ningún sufijo en la lista de sufijos públicos,
NET.PUBLIC_SUFFIXyNET.REG_DOMAINmuestran NULL, mientras queTLDyDOMAINmuestran valores distintos de NULL como estimaciones aproximadas. - Si la entrada solo contiene un sufijo público sin una etiqueta al principio (por ejemplo, “http://com”),
NET.PUBLIC_SUFFIXmuestra el sufijo público, mientras queTLDmuestra una string vacía. De forma similar,NET.REG_DOMAINmuestra NULL, mientras queDOMAINmuestra el sufijo público. - Para las entradas con hosts IPv6,
NET.HOSTno quita los corchetes del resultado, como se especifica en RFC 3986. - Para las entradas con hosts IPv4,
NET.REG_DOMAINmuestra NULL, mientras queDOMAINmuestra los primeros 3 octetos.
Ejemplos
En la tabla siguiente, el color de texto gris indica que los resultados son los mismos entre SQL heredado y SQL estándar.
| URL (descripción) | HOST | NET.HOST | TLD | NET.PUBLIC _SUFFIX | DOMINIO | NET.REG_DOMAIN |
|---|---|---|---|---|---|---|
| “//google.com” (comienza con “//”) |
NULL | “google.com” | NULL | “com” | NULL | “google.com” |
| “google.com” (no estándar; sin “//”) |
NULL | “google.com” | NULL | “com” | NULL | “google.com” |
| “http://user:pass@word@x.com” (no estándar con varios “@”) |
“word@x.com” | “x.com” | “.com” | “com” | “word@x.com” | “x.com” |
| “http://foo.com:1:2” (no estándar con varias “:”) |
"foo.com:1" | “foo.com” | “.com:1” | “com” | “foo.com” | “foo.com” |
| “http://x.Co.uk” (letras mayúsculas) |
“x.Co.uk” | “x.Co.uk” | “.uk” | “Co.uk” | “Co.uk” | “x.Co.uk” |
| “http://a.b” (sufijo público no encontrado) |
“a.b” | “a.b” | “.b” | NULL | “a.b” | NULL |
| “http://com” (el host solo contiene un sufijo público) |
“com” | “com” | “” | “com” | “com” | NULL |
| “http://[::1]” (host IPv6; sin sufijo público) |
“::1” | “[::1]” | “” | NULL | “::1” | NULL |
| “http://1.2.3.4” (host IPv4; sin sufijo público) |
“1.2.3.4” | “1.2.3.4” | “” | NULL | "1.2.3" | NULL |
Diferencias en el manejo de campos repetidos
Un tipo REPEATED en SQL heredado es equivalente a un ARRAY de ese tipo en SQL estándar. Por ejemplo, REPEATED INTEGER es equivalente a ARRAY<INT64> en SQL estándar. En la siguiente sección, se tratan algunas de las diferencias en las operaciones en campos repetidos entre SQL heredado y SQL estándar.
Elementos NULL y arreglos NULL
SQL estándar admite elementos de arreglo NULL, pero genera un error si hay un elemento de arreglo NULL en el resultado de la consulta. Si hay una columna de arreglos NULL en el resultado de la consulta, SQL estándar la almacena como un arreglo vacío.
Selecciona campos de hoja repetidos anidados
Cuando usas SQL heredado, puedes “puntear” en un campo repetido anidado sin necesidad de tener en cuenta dónde ocurre la repetición. En SQL estándar, tratar de “puntear” en un campo repetido anidado genera un error. Por ejemplo:
#standardSQL
SELECT
repository.url,
payload.pages.page_name
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Si tratas de ejecutar esta consulta, se muestra lo siguiente:
Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>
Para corregir el error y mostrar un arreglo de page_name en el resultado, usa una subconsulta de ARRAY en su lugar. Por ejemplo:
#standardSQL
SELECT
repository.url,
ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Para obtener más información sobre arreglos y subconsultas ARRAY, consulta Trabaja con arreglos.
Filtra campos repetidos
Cuando usas SQL heredado, puedes filtrar campos repetidos de forma directa con una cláusula WHERE. En SQL estándar, puedes expresar una lógica similar con un operador de coma JOIN seguido de un filtro. Por ejemplo, considera la siguiente consulta de SQL heredado:
#legacySQL
SELECT
payload.pages.title
FROM
[bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');
Esta consulta muestra todos los title de las páginas en las que page_name es db_jobskill o Profession. Puedes expresar una consulta similar en SQL estándar de la siguiente forma:
#standardSQL
SELECT
page.title
FROM
`bigquery-public-data.samples.github_nested`,
UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');
Una diferencia entre las consultas de SQL heredado y de SQL estándar anteriores es que si desactivas la opción Acoplar resultados y ejecutas la consulta de SQL heredado, payload.pages.title aparece como REPEATED en el resultado de la consulta. A fin de lograr la misma semántica en SQL estándar y mostrar un arreglo para la columna title, usa una subconsulta ARRAY en su lugar:
#standardSQL
SELECT
title
FROM (
SELECT
ARRAY(SELECT title FROM UNNEST(payload.pages)
WHERE page_name IN ('db_jobskill', 'Profession')) AS title
FROM
`bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;
Esta consulta crea un arreglo de titles, en el que el page_name es 'db_jobskill' o 'Profession' y, luego, filtra las filas en las que el arreglo no cumpla con esa condición mediante ARRAY_LENGTH(title) > 0.
Para obtener más información sobre los arreglos, consulta Trabaja con arreglos.
Estructura de campos de hoja anidados seleccionados
SQL heredado conserva la estructura de los campos de hoja anidados en la lista SELECT cuando la opción Acoplar resultados está desactivada, mientras que SQL estándar no lo hace. Por ejemplo, considera la siguiente consulta de SQL heredado:
#legacySQL
SELECT
repository.url,
repository.has_downloads
FROM
[bigquery-public-data.samples.github_nested]
LIMIT 5;
Esta consulta muestra url y has_downloads dentro de un registro llamado repository cuando se desactiva la opción Acoplar resultados. Ahora considera la siguiente consulta de SQL estándar:
#standardSQL
SELECT
repository.url,
repository.has_downloads
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Esta consulta muestra url y has_downloads como columnas de nivel superior. No son parte de un struct o registro repository. Para mostrarlas como parte de un struct, usa el operador STRUCT:
#standardSQL
SELECT
STRUCT(
repository.url,
repository.has_downloads) AS repository
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Quita repeticiones con FLATTEN
SQL estándar no tiene una función FLATTEN como SQL heredado, pero puedes lograr una semántica similar con el operador JOIN (coma). Por ejemplo, considera la siguiente consulta de SQL heredado:
#legacySQL
SELECT
repository.url,
payload.pages.page_name
FROM
FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;
Puedes expresar una consulta similar en SQL estándar de la siguiente manera:
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`,
UNNEST(payload.pages) AS page
LIMIT 5;
También puedes usar JOIN en lugar del operador de coma (,):
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`
JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Una diferencia importante es que la consulta de SQL heredado muestra una fila en la que payload.pages.page_name es NULL si payload.pages está vacío. Sin embargo, la consulta de SQL estándar no muestra ninguna fila si payload.pages está vacío. Para lograr una semántica exacta, debes usar un LEFT JOIN o LEFT OUTER JOIN. Por ejemplo:
#standardSQL
SELECT
repository.url,
page.page_name
FROM
`bigquery-public-data.samples.github_nested`
LEFT JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Para obtener más información sobre los arreglos, consulta Trabaja con arreglos. Para obtener más información sobre UNNEST, consulta el tema UNNEST.
Filtra filas con OMIT RECORD IF
La cláusula OMIT IF de SQL heredado te permite filtrar filas en función de una condición que se puede aplicar a campos repetidos. En SQL estándar puedes modelar una cláusula OMIT IF con una cláusula EXISTS o IN, o un filtro simple.
Por ejemplo, considera la siguiente consulta de SQL heredado:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF
EVERY(payload.pages.page_name != 'db_jobskill'
AND payload.pages.page_name != 'Profession');
A continuación, se muestra la consulta de SQL estándar análoga:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE EXISTS (
SELECT 1 FROM UNNEST(payload.pages)
WHERE page_name = 'db_jobskill'
OR page_name = 'Profession');
Aquí la cláusula EXISTS se evalúa como true si hay al menos un elemento de payload.pages en el que el nombre de la página sea 'db_jobskill' o 'Profession'.
De forma alternativa, supongamos que la consulta de SQL heredado usa IN:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));
En SQL estándar, puedes expresar la consulta mediante una cláusula EXISTS con IN:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE EXISTS (
SELECT 1 FROM UNNEST(payload.pages)
WHERE page_name IN ('db_jobskill', 'Profession'));
Considera la siguiente consulta de SQL heredado que filtra registros de 80 páginas o menos:
#legacySQL
SELECT
repository.url,
FROM
[bigquery-public-data:samples.github_nested]
OMIT RECORD IF
COUNT(payload.pages.page_name) <= 80;
En este caso, puedes usar un filtro con ARRAY_LENGTH en SQL estándar:
#standardSQL
SELECT
repository.url
FROM
`bigquery-public-data.samples.github_nested`
WHERE
ARRAY_LENGTH(payload.pages) > 80;
Ten en cuenta que la función ARRAY_LENGTH se aplica al campo repetido payload.pages de forma directa en lugar del campo anidado payload.pages.page_name como en la consulta de SQL heredado.
Para obtener más información sobre arreglos y subconsultas ARRAY, consulta Trabaja con arreglos.
Diferencias semánticas
La semántica de algunas operaciones difiere entre SQL heredado y SQL estándar.
Coerciones automáticas de tipos de datos
SQL heredado y SQL estándar admiten coerciones (conversiones automáticas) entre ciertos tipos de datos. Por ejemplo, BigQuery convierte un valor de tipo INT64 en FLOAT64 si la consulta lo pasa a una función que requiere FLOAT64 como entrada.
SQL estándar no admite las siguientes coerciones que admite SQL heredado.
En su lugar, debes usar un CAST explícito.
- Literal de
INT64aTIMESTAMP. En su lugar, usaTIMESTAMP_MICROS(micros_value). - Literal de
STRINGaINT64,FLOAT64oBOOL. En su lugar, usaCAST(str AS INT64),CAST(str AS FLOAT64)oCAST(str AS BOOL). STRINGaBYTES. En su lugar, usaCAST(str AS BYTES).
Errores de entorno de ejecución
Algunas funciones de SQL heredado muestran NULL para entradas no válidas, lo que podría enmascarar problemas en consultas o datos. Por lo general, SQL estándar es más estricto y genera un error si una entrada no es válida.
- Para todos los operadores y funciones matemáticas, SQL heredado no verifica si hay desbordamientos. SQL estándar agrega controles de desbordamiento, además de generar un error si un cálculo genera un desbordamiento. Esto incluye los operadores
+,-y*, y las funciones de agregaciónSUM,AVGySTDDEV, entre otros. - SQL estándar genera un error si se intenta dividir por cero, mientras que SQL heredado muestra
NULL. Si deseas que se muestreNULLpara la división por cero en SQL estándar, usaSAFE_DIVIDE. - SQL estándar genera un error para
CASTs en los que el formato de entrada no es válido o está fuera del rango del tipo de destino, mientras que SQL heredado muestraNULL. Si deseas evitar que se genere un error de una conversión no válida en SQL estándar, usaSAFE_CAST.
Resultados repetidos anidados
Las consultas que se ejecutan con SQL estándar conservan el anidamiento y la repetición de las columnas en el resultado, y la opción Acoplar resultados no tiene efecto. Si quieres que se muestren columnas de nivel superior para campos anidados, usa el operador .* en las columnas de struct. Por ejemplo:
#standardSQL
SELECT
repository.*
FROM
`bigquery-public-data.samples.github_nested`
LIMIT 5;
Si quieres que se muestren columnas de nivel superior para campos anidados repetidos (ARRAY de STRUCT), usa JOIN a fin de tomar el producto cruzado de las filas de la tabla y los elementos del campo anidado repetido. Por ejemplo:
#standardSQL
SELECT
repository.url,
page.*
FROM
`bigquery-public-data.samples.github_nested`
JOIN
UNNEST(payload.pages) AS page
LIMIT 5;
Para obtener más información sobre arreglos y subconsultas ARRAY, consulta Trabaja con arreglos.
NULL y condiciones NOT IN
SQL heredado no cumple con el estándar de SQL en su manejo de NULL con condiciones NOT IN, mientras que SQL estándar sí lo hace. Considera la siguiente consulta de SQL heredado, que busca la cantidad de palabras que no aparecen en la tabla de muestra de GitHub como ubicaciones:
#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
SELECT actor_attributes.location
FROM [bigquery-public-data.samples.github_nested]
);
Esta consulta muestra un recuento de 163,716, lo que indica que hay 163,716 palabras que no aparecen como ubicaciones en la tabla de GitHub. Ahora considera la siguiente consulta de SQL estándar:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
SELECT actor_attributes.location
FROM `bigquery-public-data.samples.github_nested`
);
Esta consulta muestra un recuento de 0. La diferencia se debe a la semántica de NOT IN con SQL estándar, que muestra NULL si cualquiera de los valores del lado derecho es NULL. Para lograr los mismos resultados que obtienes con la consulta de SQL heredado, usa una cláusula WHERE a fin de excluir los valores NULL:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
SELECT actor_attributes.location
FROM `bigquery-public-data.samples.github_nested`
WHERE actor_attributes.location IS NOT NULL
);
Esta consulta muestra un recuento de 163,716. Como alternativa, puedes usar una condición NOT EXISTS:
#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
SELECT 1
FROM `bigquery-public-data.samples.github_nested`
WHERE t.word = actor_attributes.location
);
Esta consulta también muestra un recuento de 163,716. Para obtener más información, consulta los operadores de comparación de la documentación, en la que se explica la semántica de IN, NOT IN, EXISTS, y otros operadores de comparación.
Diferencias entre las funciones de JavaScript definidas por el usuario
En el tema Funciones definidas por el usuario, se describe cómo usar las funciones definidas por el usuario de JavaScript con SQL estándar. En esta sección se explican algunas de las diferencias principales entre las funciones definidas por el usuario en SQL heredado y SQL estándar.
Funciones en el texto de consulta
Con SQL estándar, se usa CREATE TEMPORARY FUNCTION como parte del cuerpo de la consulta en lugar de especificar por separado las funciones definidas por el usuario. Algunos ejemplos de la definición de funciones por separado son el uso del Editor de UDF en Cloud Console o el uso de la marca --udf_resource en la herramienta de línea de comandos de bq.
Considera la siguiente consulta de SQL estándar:
#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
-- n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";
WITH T AS (
SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;
Esta consulta define una función de JavaScript llamada HarmonicMean y, luego, la aplica a la columna de arreglos arr desde T.
Para obtener más información sobre las funciones definidas por el usuario, consulta el tema Funciones definidas por el usuario.
Las funciones operan en valores, no en filas
En SQL heredado, las funciones de JavaScript operan sobre las filas de una tabla. En SQL estándar, como en el ejemplo anterior, las funciones de JavaScript operan sobre valores. Para pasar un valor de fila a una función de JavaScript con SQL estándar, define una función que tome un struct del mismo tipo de fila que la tabla. Por ejemplo:
#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
new_struct.foo = 'bar';
} else {
new_struct.foo = 'baz';
}
return new_struct;
""";
WITH T AS (
SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;
Esta consulta define una función de JavaScript que toma un struct con el mismo tipo de fila que T y crea uno nuevo con un campo adicional llamado foo. La declaración SELECT pasa la fila t como entrada a la función y usa .* para mostrar los campos del struct en el resultado.
Aspectos destacados de SQL estándar
En esta sección, se analizan algunos de los aspectos destacados de SQL estándar en comparación con SQL heredado.
Capacidad de integración mediante cláusulas WITH
En algunos de los ejemplos de SQL estándar de esta página, se usa una cláusula WITH, que permite extraer o volver a usar subconsultas con nombre. Por ejemplo:
#standardSQL
WITH T AS (
SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;
Esta consulta define una subconsulta con nombre T que contiene valores x de 1, 2, 3 y 4. Selecciona valores x desde T y los divide por la suma de todos los valores x en T. Esta consulta equivale a otra en la que el contenido de T está intercalado:
#standardSQL
SELECT
x / (SELECT SUM(x)
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);
Como otro ejemplo, considera esta consulta, que usa varias subconsultas con nombre:
#standardSQL
WITH T AS (
SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
SELECT x + 1 AS y
FROM T
),
TPlusOneTimesTwo AS (
SELECT y * 2 AS z
FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;
Esta consulta define una secuencia de transformaciones de los datos originales, seguida de una declaración SELECT sobre TPlusOneTimesTwo. Esta consulta equivale a la siguiente consulta, que intercala los cálculos:
#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);
Para obtener más información, consulta la cláusula WITH.
Capacidad de integración mediante funciones de SQL
SQL estándar admite funciones de SQL definidas por el usuario. Puedes usar funciones de SQL definidas por el usuario para definir expresiones comunes y, luego, hacer referencia a ellas desde la consulta. Por ejemplo:
#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
-- n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);
WITH T AS (
SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;
Esta consulta define una función de SQL llamada HarmonicMean y, luego, la aplica a la columna de arreglos arr desde T.
Subconsultas en más lugares
SQL estándar admite subconsultas en la lista SELECT, la cláusula WHERE y en cualquier otro lugar de la consulta en el que se espera una expresión. Por ejemplo, considera la siguiente consulta de SQL estándar que calcula la fracción de días cálidos en Seattle en 2015:
#standardSQL
WITH SeattleWeather AS (
SELECT *
FROM `bigquery-public-data.noaa_gsod.gsod2015`
WHERE stn = '994014'
)
SELECT
COUNTIF(max >= 70) /
(SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;
La estación meteorológica de Seattle tiene un ID de '994014'. La consulta calcula la cantidad de días cálidos en función de aquellos en los que la temperatura alcanzó los 70 grados Fahrenheit o alrededor de 21 grados Celsius, dividida por el número total de días registrados en esa estación en 2015.
Subconsultas correlacionadas
En SQL estándar, las subconsultas pueden hacer referencia a columnas correlacionadas, es decir, columnas que se originan a partir de la consulta externa. Por ejemplo, considera la siguiente consulta de SQL estándar:
#standardSQL
WITH WashingtonStations AS (
SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
FROM `bigquery-public-data.noaa_gsod.stations` AS station
INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
ON station.usaf = weather.stn
WHERE station.state = 'WA' AND station.usaf != '999999'
GROUP BY station_id
)
SELECT washington_stations.name,
(SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
WHERE washington_stations.station_id = weather.stn
AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;
Esta consulta calcula los nombres de las estaciones meteorológicas en el estado de Washington y la cantidad de días del año 2015 en los que la temperatura alcanzó los 70 grados Fahrenheit o alrededor de 21 grados Celsius. Observa que hay una subconsulta en la lista SELECT que hace referencia a washington_stations.station_id desde el alcance externo, es decir, FROM WashingtonStations AS washington_stations.
Arreglos y struct
ARRAY y STRUCT son conceptos muy importantes en SQL estándar. Como ejemplo que usa ambos, considera la siguiente consulta, que calcula los dos artículos principales de cada día en el conjunto de datos de HackerNews:
#standardSQL
WITH TitlesAndScores AS (
SELECT
ARRAY_AGG(STRUCT(title, score)) AS titles,
EXTRACT(DATE FROM time_ts) AS date
FROM `bigquery-public-data.hacker_news.stories`
WHERE score IS NOT NULL AND title IS NOT NULL
GROUP BY date)
SELECT date,
ARRAY(SELECT AS STRUCT title, score
FROM UNNEST(titles)
ORDER BY score DESC
LIMIT 2)
AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;
La cláusula WITH define TitlesAndScores, que contiene dos columnas. La primera es un arreglo de struct, en el que un campo es el título de un artículo y la segunda es una puntuación. La expresión ARRAY_AGG muestra un arreglo de estos struct para cada día.
La declaración SELECT que sigue a la cláusula WITH usa una subconsulta de ARRAY para ordenar y mostrar los dos artículos principales dentro de cada arreglo de acuerdo con score. Luego, muestra los resultados en orden descendente por fecha.
Para obtener más información sobre arreglos y subconsultas ARRAY, consulta Trabaja con arreglos. También consulta las referencias sobre los tipos ARRAY y STRUCT.