Sintaxis SQL de Google BigQuery: Una guía completa

¿Qué es Google BigQuery? ¿Qué puede hacer por su organización? ¿Es BigQuery SQL una habilidad valiosa? ¿Dónde se puede aprender y dónde se puede practicar?

Hoy en día, muy pocas organizaciones no están informatizadas. Muchos procesos utilizan ahora el IoT (Internet de las cosas), donde todo tipo de dispositivos están conectados en red y alimentan continuamente datos en tiempo real a los sistemas informáticos. El resultado es una enorme cantidad de datos disponibles para la toma de decisiones. Esto suele denominarse lago de datos.

Los principales retos que plantea este flujo de datos son cómo y dónde almacenarlos y cuál es la mejor manera de ponerlos a disposición de los responsables de la toma de decisiones. BigQuery de Google es sin duda una de las mejores respuestas a estos retos.

Los conocimientos de BigQuery SQL son un complemento importante para tu cartera. Si quieres aprender BigQuery, ¿por dónde deberías empezar? BigQuery es compatible con los estándares ANSI SQL, por lo que el primer paso es adquirir conocimientos básicos de SQL.

Si aún no sabe SQL, el curso SQL para principiantes de LearnSQL.es le pondrá al día rápidamente. Aprenderás conceptos importantes de bases de datos; al final del curso serás capaz de extraer, agregar y analizar datos de una o más tablas para obtener información significativa. El curso dura unas 10 horas e incluye 129 ejercicios interactivos que te enseñarán a resolver problemas reales con SQL.

¿Qué es Google BigQuery?

Google BigQuery es una solución de almacenamiento de datos altamente escalable y sin servidor. Puede analizar terabytes de datos en segundos y petabytes de datos en minutos. Pero, ¿qué significa eso en realidad?

Almacén de datos

Los datos suelen almacenarse con uno de estos dos fines:

• Para apoyar las operaciones cotidianas de una empresa
• Apoyar la toma de decisiones.

El primero de ellos se conoce como datos operativos, y a menudo se almacenan en varios sistemas informáticos diferentes, ya que llevan a cabo diversas tareas necesarias para la organización. Los segundos se denominan datos analíticos. Lo ideal es que estos datos se almacenen en un lugar central al que pueda acceder cualquiera que necesite información.

Este lugar central se conoce como almacén de datos, y debe estar optimizado para buscar y analizar grandes volúmenes de datos con rapidez. Se utilizaría un proceso conocido como ETL (Extraer, Transformar, Cargar) para transferir los datos operativos al almacén.

Como almacén de datos, BigQuery tiene varias ventajas:

• Dispone de excelentes herramientas ETL.
• Utiliza el sistema de archivos Colossus, que está diseñado para "grandes"; se puede añadir más espacio fácilmente cuando sea necesario.
• Utiliza SQL como lenguaje de consulta, por lo que es compatible con las mejores herramientas de inteligencia empresarial (BI), como
• Google Data Studio permite presentar los datos de muchas formas distintas.

Búsquedas rápidas

BigQuery utiliza el motor de búsqueda Dremel para procesar rápidamente grandes volúmenes de datos. Como he mencionado antes, BigQuery puede escanear petabytes (un petabyte equivale aproximadamente a un millón de gigabytes) en cuestión de minutos. ¿Cómo lo consigue?

Cada vez que se ejecuta una búsqueda en BigQuery, miles de hilos de trabajo se reparten la tarea, lo que permite escanear enormes cantidades de datos simultáneamente. A continuación, los resultados se amalgaman y se devuelven al usuario.

Arquitectura sin servidor

Si utiliza BigQuery, no tiene que contratar un servidor. Esto significa que no tiene que gestionar la infraestructura, preocuparse por las copias de seguridad y la protección, o averiguar si necesita más discos duros. Sus datos pasan a formar parte del gran lago de datos, ¡o océano!

Este océano digital de datos se reparte entre innumerables servidores de todo el mundo. Si se necesita más espacio, Google lo asigna y lo gestiona. Sólo pagas por lo que utilizas.

¿Y la capacidad de procesamiento necesaria para realizar los cálculos? Google asigna la potencia de procesamiento en función de las necesidades. El software de red de alta velocidad de Google, Jupiter, está especialmente diseñado para la comunicación rápida entre subprocesos; sólo pagas por las ranuras de procesamiento que utilizas.

¿Y qué hay de la seguridad? Puedes crear vistas para tus datos y especificar exactamente quién puede ver qué. Como los datos están encriptados, tanto cuando se almacenan como cuando están en tránsito, están a salvo de intrusos.

Practicar BigQuery

Si ya tiene conocimientos de SQL, puede probar BigQuery ahora mismo. Si no, puede probar algunos de los ejemplos de la siguiente sección para hacerse una idea de lo que puede hacer. Pero para sacar el máximo provecho de BigQuery, vale la pena mejorar tus conocimientos de SQL inscribiéndote en un curso completo de SQL. LearnSQL.es La ruta de aprendizaje de BigQuery en Curso completo de SQL incluye 7 cursos que le llevarán desde principiante a experto.

¿Dónde y cómo se pueden practicar estos conocimientos en BigQuery? Cualquiera puede crear un proyecto en BigQuery de forma gratuita. Sólo se cobra cuando se desea cargar y procesar grandes cantidades de datos. El sandbox de BigQuery te permite insertar tus propios datos de prueba. O puedes acceder a una amplia gama de bases de datos públicas con las que jugar.

Para empezar, visita la página BigQuery de Google Cloud Console. Si aún no has iniciado sesión en una cuenta de Google, es posible que se te pida que lo hagas. La primera vez que accedas a esta consola, verás un aviso como este:

Haz clic en CREAR PROYECTO y dale un nombre a tu proyecto o acepta la sugerencia de Google.

Ahora necesitarás algunos datos para practicar. Afortunadamente, BigQuery tiene muchos datos públicos disponibles. Para acceder a ellos, tendrás que añadir los conjuntos de datos públicos a tu espacio de trabajo SQL. Verás esto en la parte superior izquierda de la página:

Haga clic en AÑADIR. Aparecerá una pantalla emergente con varias opciones. Haz clic en ésta:

Se te pedirá el nombre del proyecto. Introduzca bigquery-public-data y haga clic en 'STAR'. Deberías ver este proyecto listado en el explorador de proyectos a la izquierda de la pantalla. Y ahora ya estás listo para empezar a explorar. Si haces clic en la flecha junto a bigquery-public-data, verás una lista de todas las bases de datos de este proyecto. Y si haces clic en una base de datos, verás una lista de sus tablas. Los ejemplos de la siguiente sección de este artículo están tomados de la base de datos census_bureau_international.

Si ya tienes experiencia con SQL, eso es todo lo que necesitas para empezar. Sin embargo, si eres un novato en SQL, puede que te apetezca practicar un poco en un entorno en el que haya ayuda disponible y te guíen para encontrar soluciones a los problemas. En ese caso, te recomiendo el curso práctico de LearnSQL.es, donde tendrás la oportunidad de resolver más de 80 problemas reales.

Sintaxis de BigQuery

Ahora que ya sabes cómo configurarte con BigQuery, echemos un vistazo a la sintaxis de BigQuery. Pero antes, como ya sabrás, SQL tiene muchos dialectos diferentes, exclusivos de los distintos productos de bases de datos. Puede obtener más información sobre los diferentes dialectos de SQL aquí.

El American National Standards Institute (ANSI) define los estándares recomendados para SQL; cada dialecto puede o no implementar todos estos estándares. Muchos de ellos tienen sus propias adiciones al lenguaje para permitir una mayor funcionalidad.

BigQuery disponía originalmente de una versión de SQL muy poco estándar y exclusiva para sus necesidades. Ahora se llama "Legacy SQL" y sólo necesitarías aprenderla si quisieras trabajar para una empresa que todavía la utiliza. BigQuery utiliza ahora Google SQL, que se ciñe mucho a la norma ANSI. Si ya estás familiarizado con otro dialecto (como MS SQL Server o MySQL), encontrarás muy pocas diferencias al utilizar BigQuery.

La principal diferencia es que BigQuery tiene algunas funciones estadísticas adicionales y admite estructuras de datos complejas como JSON y matrices.

Veamos algunos ejemplos de ejecución de consultas en la Cloud Console. Si nunca ha utilizado SQL y le resulta difícil seguir los ejemplos, puede que este artículo sobre los comandos SQL más importantes le resulte útil.

Puedes encontrar la sintaxis completa de todos los comandos SQL de Google aquí.

Ejecución de comandos en la consola de BigQuery Cloud

A la derecha del explorador de proyectos en la consola de BigQuery Cloud, verás una ventana donde puedes ejecutar comandos de Google SQL. Su aspecto es el siguiente:

Escriba su consulta en la ventana de consulta y, a continuación, haga clic en 'RUN' en la barra de acciones de la parte superior. Si has escrito un comando SQL válido, verás los datos solicitados en la ventana de resultados. En caso contrario, aparecerá un mensaje de error.

Comandos SQL sencillos

Para extraer y analizar datos sólo se utiliza un comando SQL: la sentencia SELECT. En su forma más simple, el comando es SELECT * FROM tablename, donde tablename es el nombre de una de las tablas de su base de datos. El asterisco (*) le indica que devuelva todas las columnas de la tabla.

En Google SQL, a menos que los datos procedan de tu propio proyecto, antepones al nombre de la tabla el nombre del proyecto y de la base de datos. En los ejemplos, voy a acceder a datos de la base de datos census_bureau_international en el proyecto bigquery-public-data. Una de las tablas que utilizaré se llama midyear_population. Necesitaré referirme a esta tabla como:

bigquery-public-data.census_bureau_international.midyear_population

Para mayor comodidad, puedes darle un alias a este nombre bastante largo para no tener que escribirlo continuamente. En el ejemplo siguiente, le doy el alias midyear.

bigquery-public-data.census_bureau_international.midyear_population AS midyear

Cuando realice pruebas en tablas que pueden ser muy grandes, es una buena idea poner un límite al número de filas que se devolverán. De lo contrario, puede superar los límites de la versión gratuita de BigQuery.

Para ver todo lo que hay en las 10 primeras filas de esta tabla, escriba este comando en la ventana de consulta y haga clic en RUN:

SELECT * FROM 
bigquery-public-data.census_bureau_international.midyear_population 
AS midyear
LIMIT 10

Pruébelo usted mismo. A menos que hayas cometido un error tipográfico, tu pantalla debería tener este aspecto:

Puede utilizar la barra de desplazamiento para desplazarse por los resultados.

Veamos ahora algunas de las funciones opcionales de la declaración SELECT.

• La cláusula WHERE permite seleccionar sólo las filas que cumplen un determinado criterio.
• En lugar de mostrar todas las columnas (es decir, utilizando el asterisco), puede enumerar los nombres de las columnas que desea ver. En la imagen anterior, los nombres de las columnas (country_code, country_name, year, y midyear_population) se muestran en la parte superior de cada columna de la ventana de resultados.
• Puede especificar el orden de las filas en el conjunto de resultados utilizando el botón ORDER BY

Pruebe a ejecutar esta consulta para ver sólo los nombres de los países y las poblaciones de 1975, ordenados por nombre de país.

SELECT
midyear.country_name, midyear_population
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Si has hecho todo correctamente, deberías ver esto:

SQL tiene muchas más funciones. Dos de las más útiles son:

• El uso de funciones de agregado, como SUM() para hallar totales, AVG() para hallar promedios, y MIN() y MAX() para hallar los valores más pequeños/más grandes. Los agregados suelen utilizarse junto con la cláusula GROUP BY, que define los grupos a los que se aplican las funciones de agregado. Sin agrupar los datos, verá los agregados para el conjunto de datos en su totalidad.
• Recuperación de datos de dos o más tablas, siempre que tengan una o más columnas en común que puedan utilizarse para unirlas.

Intentemos algunas consultas de ejemplo. Primero:

SELECT
midyear.country_name, AVG(midyear.midyear_population)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
GROUP BY country_name
ORDER BY country_name

Esta consulta muestra la población media a mitad de año de cada país a lo largo de todo el periodo.

La siguiente consulta toma datos de dos tablas: la tabla midyear_population y la tabla birth_death_growth_rates. A continuación, podrás ver la tasa de crecimiento junto con la población.

Tendrás que unirlas en dos columnas coincidentes: el país y el año. He aquí la consulta, seguida de sus resultados:

SELECT
midyear.country_name, midyear.midyear_population, 
growth.growth_rate
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
JOIN
bigquery-public-data.census_bureau_international.birth_death_growth_rates
AS growth
ON growth.country_name = midyear.country_name
AND growth.year = midyear.year
WHERE midyear.year = 1975
ORDER BY country_name

Para todas estas consultas, BigQuery utiliza la sintaxis estándar ANSI; las consultas funcionarían igual de bien en cualquier dialecto SQL.

Algunos comandos de SQL avanzado

Profundizar en la sintaxis de las sentencias SQL complejas está fuera del alcance de este artículo, pero me gustaría incluir un par de consultas avanzadas que tienen la misma sintaxis en Google SQL que en otros dialectos (como MS SQL Server).

Las funciones de ventana se utilizan para ver filas individuales frente a agregados de todo el conjunto de datos. Este ejemplo ordena los países por población: el país con mayor población aparece en primer lugar.

SELECT
midyear.country_name, midyear.midyear_population, RANK() OVER (ORDER BY midyear_population DESC)
FROM
bigquery-public-data.census_bureau_international.midyear_population
AS midyear
WHERE
year = 1975
ORDER BY country_name

Los resultados deberían ser los siguientes:

Otras funciones avanzadas son las extensiones GROUP BY, que permiten mostrar agregados a más de un nivel. De nuevo, la sintaxis de BigQuery sigue los estándares ANSI; si ha aprendido estas funciones en MS SQL, podrá utilizarlas en BigQuery.

He aquí un ejemplo. Esto mostrará los totales en los niveles de edad y país.

SELECT
m.country_name, m.age, SUM(m.population)
FROM
bigquery-public-data.census_bureau_international.midyear_population_agespecific
AS m
WHERE m.year = 1975
GROUP BY ROLLUP (m.country_name, m.age)
ORDER BY m.country_name, m.age

Los resultados tienen este aspecto. Los nulos indican que esa fila de salida es un total.

Características adicionales de BigQuery

BigQuery tiene muchas características que no se encuentran en la mayoría de los dialectos. Éstas lo hacen especialmente útil para tratar con big data procedentes de muchas fuentes diferentes.

Dispone de varios tipos de datos adicionales:

• Geography - Almacena coordenadas geográficas y puede utilizarse para calcular la distancia entre dos ubicaciones.
• Interval - Almacena un intervalo de tiempo.
• JSON - Un formato utilizado por muchas aplicaciones.
• Struct - Un contenedor para contener campos ordenados.

BigQuery también permite trabajar con matrices.

Cuando se trabaja con conjuntos de datos muy grandes, resulta especialmente útil poder extraer muestras aleatorias de datos. He aquí un ejemplo de extracción de una muestra aleatoria de la tabla midyear_population.

SELECT
AVG (m.life_expectancy)
FROM
bigquery-public-data.census_bureau_international.mortality_life_expectancy
AS m
TABLESAMPLE SYSTEM (10 PERCENT)

En lugar de recorrer toda la tabla, que es grande, esto tomará una muestra estadística del 10% de la tabla. Como la muestra es aleatoria, es posible que obtenga resultados ligeramente diferentes cada vez que la ejecute. El resultado podría ser el siguiente:

Puede obtener más información sobre las funciones adicionales de BigQuery SQL en la documentación del producto.

Trabajar con Google BigQuery: El siguiente paso

El océano digital crece cada año, y cada vez son más los empleadores que buscan conocimientos de BigQuery. Merece la pena convertirse en un experto en esta área. ¿Cómo?

Mejora tus conocimientos de SQL realizando algunos cursos. LearnSQL.es cuenta con una amplia gama de cursos que abarcan desde temas para principiantes hasta temas avanzados. Puedes buscar cursos específicos o simplemente echar un vistazo a nuestra oferta.

LearnSQL.es's practice track te enseña a ser ágil en la resolución de todo tipo de problemas con SQL. También deberías dedicar mucho tiempo a experimentar con los conjuntos de datos públicos de Google Cloud Console. Si tienes tu propia empresa y quieres organizar un programa de formación en SQL para tu personal, también podemos ayudarte a hacerlo.

BigQuery está aquí para quedarse, ¡así que empieza a convertirte en un experto ahora mismo!