Capítulo 4. Cargar datos en BigQuery
Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com
En el capítulo anterior de, escribimos la siguiente consulta:
SELECT
state_name
FROM `bigquery-public-data`.utility_us.us_states_area
WHERE
ST_Contains(
state_geom,
ST_GeogPoint(-122.33, 47.61))
También nos enteramos de que la ciudad del lugar (-122.33, 47.61) está en el estado de Washington. ¿De dónde proceden los datos de state_name y state_geom?
Fíjate en la cláusula FROM de la consulta. Los propietarios del proyecto bigquery-public-data ya habían cargado la información sobre los límites estatales en una tabla llamada us_states_area en un conjunto de datos llamado utility_us. Como el equipo compartió el conjunto de datos utility_us con todos los usuarios autentificados de BigQuery (existen permisos más restrictivos), pudimos consultar la tabla us_states_area que se encuentra en ese conjunto de datos.
Pero, en primer lugar, ¿cómo se introducen los datos en BigQuery? En este capítulo veremos varias formas de cargar datos en BigQuery, empezando por lo más básico.
Lo básico
Los valores de los datos, como las fronteras de los estados de EE.UU., cambian raramente,1 y los cambios son lo suficientemente pequeños como para que la mayoría de las aplicaciones puedan permitirse ignorarlos. En la jerga del almacenamiento de datos, llamamos a esto una dimensión que cambia lentamente. En el momento de escribir esto, el último cambio de los límites de los estados de EEUU se produjo el 1 de enero de 2017, y afectó a 19 propietarios de viviendas y a una gasolinera.2
Los datos de los límites estatales son, por tanto, el tipo de datos que a menudo se cargan una sola vez. Los analistas consultan la tabla única e ignoran el hecho de que los datos podrían cambiar con el tiempo. Por ejemplo, puede que a una empresa minorista sólo le interese saber en qué estado se encuentra actualmente una vivienda para asegurarse de que se aplica el tipo impositivo correcto a las compras de esa vivienda. Por tanto, cuando se produzca un cambio, como por un tratado entre estados o debido a un cambio en el trazado de un canal fluvial, los propietarios del conjunto de datos podrían decidir sustituir la tabla por datos más actualizados. Se ignora el hecho de que las consultas podrían devolver resultados ligeramente distintos tras una actualización, en comparación con lo que se devolvía antes de la actualización.
Ignorar el impacto del tiempo en la corrección de los datos puede no ser siempre posible. Si los datos de los límites estatales van a ser utilizados por una empresa de títulos de propiedad que necesite hacer un seguimiento de la propiedad de las parcelas, o si una empresa auditora necesita validar el impuesto estatal pagado en envíos realizados en años diferentes, es importante que haya una forma de consultar los límites estatales tal y como existían en años pasados. Así que, aunque la primera parte de este capítulo trata de cómo hacer una carga única, considera detenidamente si te convendría planificar la actualización periódica de los datos y permitir que los usuarios de los datos conozcan la versión de los datos que están consultando.
Carga desde una fuente local
El gobierno estadounidense publica una "tarjeta de puntuación" de las universidades para ayudar a los consumidores a comparar el coste y el valor percibido de la educación superior. Carguemos estos datos en BigQuery a modo de ilustración. Los datos en bruto están disponibles en catalog.data.gov. Para mayor comodidad, también los tenemos disponibles como 04_load/college_scorecard.csv.gz en el repositorio de GitHub de este libro. El archivo de valores separados por comas (CSV) se descargó de data.gov y se comprimió utilizando la utilidad de software de código abierto gzip.
Consejo
¿Por qué comprimimos el archivo? El archivo sin comprimir ocupa unos 136 MB, mientras que el archivo comprimido sólo ocupa 18 MB. Como vamos a enviar el archivo a BigQuery por cable, tiene sentido optimizar el ancho de banda transferido. El comando de carga de BigQuery puede manejar archivos comprimidos con gzip, pero no puede cargar partes de un archivo comprimido con gzip en paralelo. Cargar sería mucho más rápido si entregáramos a BigQuery un archivo divisible, ya sea un archivo CSV sin comprimir que ya esté en el Almacenamiento en la Nube (de modo que se minimice la sobrecarga de transferencia de red) o datos en un formato como Avro, en el que cada bloque está comprimido internamente, pero el archivo en su conjunto puede dividirse entre los trabajadores.
Un archivo divisible puede ser cargado por diferentes trabajadores comenzando en diferentes partes del archivo, pero esto requiere que los trabajadores puedan "buscar" un punto predecible en medio del archivo sin tener que leerlo desde el principio. Comprimir todo el archivo utilizando gzip no permite esto, pero una compresión bloque a bloque como Avro sí. Por tanto, utilizar un formato comprimido y divisible como Avro es un bien sin paliativos. Sin embargo, si tienes archivos CSV o JSON que sólo son divisibles cuando no están comprimidos, debes medir si la mayor velocidad de transferencia por la red se ve contrarrestada por el mayor tiempo de carga.
Desde Cloud Shell, puedes consultar el archivo comprimido con zless:
zless college_scorecard.csv.gz
Nota
Aquí tienes los pasos detallados:
-
Abre Cloud Shell en tu navegador visitando https://console.cloud.google.com/cloudshell.
-
En la ventana del terminal, escribe:
git clone https://github.com/GoogleCloudPlatform/bigquery-oreilly-book. -
Navega hasta la carpeta que contiene el archivo del cuadro de mando del colegio:
cd bigquery-oreilly-book/04_load. -
Escribe el comando
zless college_scorecard.csv.gzy, a continuación, utiliza la barra espaciadora para hojear los datos. Escribe la letraqpara salir.
El fichero contiene una línea de encabezamiento con los nombres de las columnas. Cada una de las líneas que siguen a la cabecera contiene una fila de datos.
Para cargar los datos en BigQuery, primero crea un conjunto de datos llamado ch04 para contener los datos:
bq --location=US mk ch04
La herramienta de línea de comandos bq proporciona un cómodo punto de entrada para interactuar con el servicio BigQuery en Google Cloud Platform (GCP), aunque todo lo que hagas con bq también puedes hacerlo utilizando la API REST. Y puedes hacer la mayoría de las cosas utilizando la Consola en la Nube de GCP. Aquí le pedimos que cree (mk) un conjunto de datos llamado ch04.
Los conjuntos de datos en BigQuery funcionan como carpetas de nivel superior que se utilizan para organizar y controlar el acceso a tablas, vistas y modelos de aprendizaje automático. El conjunto de datos se crea en el proyecto actual,3 y es a este proyecto al que se facturarán los costes de almacenamiento de las tablas de este conjunto de datos (las consultas se cargan al proyecto del consultante).
También especificamos que el conjunto de datos debe crearse en la ubicación de EE.UU. (es la predeterminada, por lo que podríamos haberla omitido). Las opciones de ubicación incluyen ubicaciones multirregionales (como US, EU) y regiones específicas (por ejemplo, us-east4, europe-west2 y australia-southeast1).4 Ten cuidado al elegir una región para cargar los datos: en el momento de escribir esto, las consultas no pueden unir tablas que se encuentren en regiones diferentes. En este libro, utilizaremos la ubicación multirregión de EE.UU. para que nuestras consultas puedan unirse a tablas de los conjuntos de datos públicos que se encuentran en Estados Unidos.
A continuación, desde el directorio que contiene tu clon del repositorio de GitHub, carga los datos del archivo como una tabla en BigQuery:
bq --location=US \ load \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
En este caso, pedimos a bq que cargue el conjunto de datos, informando a la herramienta de que el formato de origen es CSV y que nos gustaría que la herramienta autodetectara el esquema (es decir, los tipos de datos de las columnas individuales). A continuación, especificamos que la tabla que se va a crear se llama college_scorecard en el conjunto de datos ch04 y que los datos se van a cargar desde college_scorecard.csv.gz en el directorio actual.
Sin embargo, cuando lo hicimos, nos encontramos con un problema:
Could not parse 'NULL' as int for field HBCU (position 26) starting at location 11945910
Esto hizo que el trabajo de carga fallara con el siguiente error:5
CSV table encountered too many errors, giving up. Rows: 591; errors: 1.
El problema es que, basándose en la mayoría de los datos del archivo CSV, la autodetección del esquema de BigQuery espera que la columna 26 (cuyo nombre es HBCU) sea un número entero, pero la fila 591 del archivo tiene el texto NULL en ese campo, lo que suele significar que la universidad en cuestión no respondió a la pregunta de la encuesta correspondiente a ese campo.6
Hay varias formas de solucionar este problema. Por ejemplo, podríamos editar el propio archivo de datos si supiéramos cuál debería ser el valor. Otra solución podría ser especificar explícitamente el esquema de cada columna y cambiar el tipo de la columna HBCU para que sea una cadena, de modo que NULL sea un valor aceptable. Otra posibilidad es pedir a BigQuery que ignore algunos registros erróneos especificando, por ejemplo, --max_bad_records=20. Por último, podríamos indicar al programa de carga de BigQuery que este archivo concreto utiliza la cadena NULL para marcar los nulos (la forma estándar en CSV es utilizar campos vacíos para representar los nulos).
Apliquemos el último método, porque parece el más adecuado:7
bq --location=US \ load --null_marker=NULL \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
Puedes encontrar la lista completa de opciones bq load escribiendo bq load --help. Por defecto, bq load se añadirá a una tabla. En este caso, quieres sustituir la tabla existente, por lo que debes añadir --replace:
bq --location=US \ load --null_marker=NULL --replace \ --source_format=CSV --autodetect \ ch04.college_scorecard \ ./college_scorecard.csv.gz
También puedes especificar --replace=false para añadir filas a una tabla existente.
Cabe señalar que puedes realizar cargas únicas desde la interfaz de usuario web (UI) de BigQuery. Haz clic en tu proyecto, y se te presentará un botón para crear un conjunto de datos (ch04, en nuestro caso); haz clic en el conjunto de datos, y se te presentará un botón para crear una tabla. A continuación, puedes seguir las instrucciones para cargar el archivo como una tabla BigQuery. Sin embargo, en el momento de escribir esto, el uso de la interfaz web para cargar datos desde un archivo local está limitado a datos cuyo tamaño sea inferior a 10 MB y 16.000 filas. Por lo tanto, no funcionaría con el conjunto de datos de la tarjeta de puntuación de la universidad a menos que lo hubiéramos almacenado primero en Google Cloud Storage.
Aunque no hayas (o no puedas) utilizar la interfaz web para cargar los datos, es una buena idea examinar la tabla creada utilizando la interfaz web para asegurarte de que los detalles sobre la tabla, así como el esquema autodetectado, son correctos. También es posible editar algunos detalles sobre la tabla incluso después de haberla creado. Por ejemplo, es posible especificar que la tabla caduque automáticamente tras un determinado número de días, añadir columnas o relajar un campo obligatorio para que sea anulable.
Nota
También puedes establecer una fecha de caducidad utilizando la declaración ALTER TABLE SET OPTIONS, por ejemplo:
ALTER TABLE ch04.college_scorecard
SET OPTIONS (
expiration_timestamp=
TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
description="College Scorecard table that expires
seven days from now"
)
Para más detalles, consulta https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_set_options_statement.
Independientemente de cómo se cargue la tabla, cualquiera que tenga permiso para acceder al conjunto de datos en el que se encuentra la tabla puede consultarla. Por defecto, un conjunto de datos recién creado sólo es visible para las personas con permisos de vista a nivel de proyecto. Sin embargo, puedes compartir el conjunto de datos9 con personas concretas (identificadas por su cuenta de Google), un dominio (por ejemplo, xyz.com) o un grupo de Google. En el Capítulo 10 hablaremos del uso de la Gestión de Identidades y Accesos (IAM) para compartir conjuntos de datos. Por ahora, sin embargo, cualquiera con acceso de visualización al proyecto que contiene el conjunto de datos puede consultarlo:
SELECT INSTNM , ADM_RATE_ALL , FIRST_GEN , MD_FAMINC , MD_EARN_WNE_P10 , SAT_AVG FROM ch04.college_scorecard WHERE SAFE_CAST(SAT_AVG AS FLOAT64) > 1300 AND SAFE_CAST(ADM_RATE_ALL AS FLOAT64) < 0.2 AND SAFE_CAST(FIRST_GEN AS FLOAT64) > 0.1 ORDER BY CAST(MD_FAMINC AS FLOAT64) ASC
Esta consulta extrae el nombre de la institución (INSTNM), la tasa de admisión y otra información de las universidades cuya puntuación media en el SAT es superior a 1300 y cuya tasa de admisión es inferior al 20%, lo que constituye una definición plausible de las universidades de "élite". También filtra por universidades que admiten a universitarios de primera generación en un porcentaje superior al 10% y las clasifica en orden ascendente según la renta familiar media, encontrando así universidades de élite que admiten a estudiantes cultural o económicamente desfavorecidos. La consulta también extrae los ingresos medios de los estudiantes 10 años después de su ingreso:
| Fila | INSTNM | ADM_RATE_ALL | PRIMER_GEN | MD_FAMINC |
MD_EARN _WNE_P10 |
SAT_AVG |
|---|---|---|---|---|---|---|
| 1 | Universidad de California-Berkeley | 0.1692687830816 | 0.3458005249 | 31227 | 64700 | 1422 |
| 2 | Universidad de Columbia en la Ciudad de Nueva York | 0.06825366802669 | 0.2504905167 | 31310.5 | 83300 | 1496 |
| 3 | Universidad de California-Los Ángeles | 0.17992627069775 | 0.3808913934 | 32613.5 | 60700 | 1334 |
| 4 | Universidad de Harvard | 0.05404574677902 | 0.25708061 | 33066 | 89700 | 1506 |
| 5 | Universidad de Princeton | 0.06521516568269 | 0.2773972603 | 37036 | 74700 | 1493 |
Mira, sin embargo, en la propia consulta. Observa cómo varias de las cláusulas WHERE necesitan un reparto:
SAFE_CAST(ADM_RATE_ALL AS FLOAT64)
Si no hubiéramos incluido el reparto, habríamos recibido un error:
No matching signature for operator > for argument types: STRING, INT64.
Si lo hubiéramos convertido simplemente en un flotador, habría fallado en una fila en la que el valor era una cadena (PrivacySuppressed) que no puede convertirse en un flotador:
Bad double value: PrivacySuppressed; while executing the filter ...
Esto se debe a que la detección automática del esquema no identificó la columna de tasa de admisión como numérica. En cambio, esa columna se está tratando como una cadena porque, en algunas de las filas, el valor se suprime por motivos de privacidad (por ejemplo, si el número de solicitudes es muy pequeño) y se sustituye por el texto PrivacySuppressed. De hecho, incluso la mediana de los ingresos familiares es una cadena (resulta que siempre es numérica para las universidades que cumplen los criterios de que hemos descrito), por lo que tenemos que moldearla antes de ordenarla.10
Especificar un esquema
Inevitablemente, en los conjuntos de datos del mundo real, necesitaremos hacer algunas limpiezas y transformaciones antes de cargar los datos en BigQuery. Aunque más adelante en este capítulo veremos cómo construir conductos de procesamiento de datos más sofisticados para hacerlo, una forma sencilla es utilizar las herramientas Unix de para sustituir los datos con privacidad suprimida por NULLs:
zless ./college_scorecard.csv.gz | \
sed 's/PrivacySuppressed/NULL/g' | \
gzip > /tmp/college_scorecard.csv.gz
Aquí, estamos utilizando un editor de cadenas (sed) para sustituir todas las apariciones de PrivacySuppressed por NULL, comprimiendo el resultado y escribiéndolo en una carpeta temporal. Ahora, en lugar de cargar el archivo original, podemos cargar el archivo más limpio.
Cuando se le presenta el archivo más limpio, BigQuery identifica correctamente muchas más de las columnas como enteros o flotantes, pero no SAT_AVG o ADM_RATE_ALL; esas columnas se siguen autodetectando como cadenas. Esto se debe a que el algoritmo para autodetectar el esquema no mira todas las filas del archivo, sino sólo una muestra de ellas. Como un gran número de filas tienen un SAT_AVG nulo (menos del 20% de las universidades informan de las puntuaciones SAT), el algoritmo no pudo deducir el tipo de campo. La opción segura es tratar como cadena cualquier columna de la que la herramienta no esté segura.
Por tanto, es una buena práctica no autodetectar el esquema de los archivos que recibas en producción: estarás a merced de los datos que se hayan muestreado. Para cargas de trabajo de producción, insiste en el tipo de datos de una columna especificándolo en el momento de la carga.
Puedes utilizar la función de autodetección para evitar empezar a escribir un esquema desde cero. Puedes mostrar el esquema de la tabla tal y como existe actualmente:
bq show --format prettyjson --schema ch04.college_scorecard
También puedes guardar el esquema en un archivo:
bq show --format prettyjson --schema ch04.college_scorecard > schema.json
Ahora, puedes abrir el archivo de esquema en tu editor de texto preferido (si no tienes ninguno, utiliza el icono del lápiz en Cloud Shell para abrir el editor predeterminado) y cambiar el tipo de las columnas que te interesen. En concreto, cambia las cuatro columnas de la cláusula WHERE (SAT_AVG, ADM_RATE_ALL, FIRST_GEN, y MD_FAMINC) por FLOAT64:
{
"mode": "NULLABLE",
"name": "FIRST_GEN",
"type": "FLOAT64"
},
Además, cambia también (por ahora) el T4APPROVALDATE para que sea una cadena, porque está en un formato de fecha no estándar:11
{
"mode": "NULLABLE",
"name": "T4APPROVALDATE",
"type": "STRING"
},
Con el esquema actualizado, podemos cargar los datos con este esquema en lugar de con la autodetección:
bq --location=US \ load --null_marker=NULL --replace \ --source_format=CSV \ --schema=schema.json --skip_leading_rows=1 \ ch04.college_scorecard \ ./college_scorecard.csv.gz
Como estamos proporcionando un esquema, tenemos que indicar a BigQuery que ignore la primera fila del archivo CSV (que contiene la información de cabecera).
Una vez cargada la tabla, podemos repetir la consulta del apartado anterior:
SELECT INSTNM , ADM_RATE_ALL , FIRST_GEN , MD_FAMINC , MD_EARN_WNE_P10 , SAT_AVG FROM ch04.college_scorecard WHERE SAT_AVG > 1300 AND ADM_RATE_ALL < 0.2 AND FIRST_GEN > 0.1 ORDER BY MD_FAMINC ASC
Observa que, como SAT_AVG, ADM_RATE_ALL, y los demás ya no son cadenas, nuestra consulta es mucho más limpia porque ya no necesitamos convertirlos en números de coma flotante. La razón por la que ya no son cadenas es que tomamos una decisión sobre cómo tratar los datos con privacidad suprimida (tratarlos como no disponibles) durante el proceso de extracción, transformación y carga (ETL) de .
Copiar en una nueva tabla
La tabla cargada contiene muchas columnas que no necesitamos. Es posible crear una tabla más limpia y útil a partir de la tabla original utilizando la sentencia CREATE TABLE y rellenando la nueva tabla sólo con las columnas que nos interesan:
CREATE OR REPLACE TABLE ch04.college_scorecard_etl AS
SELECT
INSTNM
, ADM_RATE_ALL
, FIRST_GEN
, MD_FAMINC
, SAT_AVG
, MD_EARN_WNE_P10
FROM ch04.college_scorecard
Al utilizar un proceso ETL robusto y tomar las decisiones con antelación, las consultas posteriores son más limpias y concisas. La contrapartida es que el proceso ETL conlleva trabajo extra (determinar los tipos de datos y especificar el esquema) y puede implicar decisiones irrevocables (por ejemplo, no hay forma de recuperar si un campo no está disponible porque no se recopiló, porque se suprimió por motivos de privacidad o porque se eliminó). Más adelante en este capítulo, hablaremos de cómo una canalización ELT en SQL puede ayudarnos a retrasar la toma de decisiones irrevocables.
Gestión de datos (DDL y DML)
¿Por qué tratar la gestión de datos de en un capítulo sobre la carga de datos? Porque cargar datos suele ser sólo una parte de la tarea de gestionarlos. Si los datos se cargan por error, puede que tengas que borrarlos. A veces tienes que borrar datos por motivos de normativa y cumplimiento.
Advertencia
Aunque normalmente queremos que pruebes todos los comandos y consultas de este libro, no pruebes los de esta sección, ¡porque perderás tus datos!
La forma más sencilla de eliminar una tabla (o vista) en su conjunto es desde la interfaz de usuario de BigQuery. También puedes llevar a cabo la eliminación desde la herramienta de línea de comandos bq:
bq rm ch04.college_scorecard bq rm -r -f ch04
La primera línea elimina una sola tabla, mientras que la segunda elimina recursivamente (-r) y sin preguntar (-f, por fuerza) el conjunto de datos ch04 y todas las tablas que contiene.
También puedes eliminar una tabla (o vista) mediante utilizando SQL:
DROP TABLE IF EXISTS ch04.college_scorecard_gcs
También es posible especificar que una tabla debe caducar en un momento determinado en el futuro. Puedes hacerlo con la sentencia ALTER TABLE SET OPTIONS:
ALTER TABLE ch04.college_scorecard
SET OPTIONS (
expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),
INTERVAL 7 DAY),
description="College Scorecard expires seven days from now"
)
Las sentencias DROP TABLE y ALTER TABLE, al igual que la sentencia CREATE TABLE, son ejemplos de sentencias del Lenguaje de Definición de Datos (DDL).
Es posible eliminar sólo determinadas filas de una tabla, por ejemplo:
DELETE FROM ch04.college_scorecard WHERE SAT_AVG IS NULL
Del mismo modo, también es posible INSERT filas en una tabla existente en lugar de sustituir toda la tabla. Por ejemplo, es posible insertar más valores en la tabla college_scorecard tabla utilizando lo siguiente:
INSERT ch04.college_scorecard
(INSTNM
, ADM_RATE_ALL
, FIRST_GEN
, MD_FAMINC
, SAT_AVG
, MD_EARN_WNE_P10
)
VALUES ('abc', 0.1, 0.3, 12345, 1234, 23456),
('def', 0.2, 0.2, 23451, 1232, 32456)
Es posible utilizar una subconsulta para extraer valores de una tabla y copiarlos en otra:
INSERT ch04.college_scorecard SELECT * FROM ch04.college_scorecard_etl WHERE SAT_AVG IS NULL
Las sentencias DELETE, INSERT, y MERGE son ejemplos de sentencias del Lenguaje de Manipulación de Datos (DML).
Consejo
En el momento de escribir esto, BigQuery no admite la sentencia SQL COPY. Para copiar tablas, utiliza bq cp para copiar una tabla en otra:
bq cp ch04.college_scorecard someds.college_scorecard_copy
No se te facturará por ejecutar una consulta, pero sí por el almacenamiento de la nueva tabla. El comando bq cp permite añadir (especifica -a o --append_table) y sustituir (especifica -noappend_table).
También puedes utilizar el método idiomático de SQL estándar de utilizar CREATE TABLE AS SELECT o INSERT VALUES, dependiendo de si el destino ya existe. Sin embargo, bq cp es más rápido (porque sólo copia los metadatos de la tabla) y no incurre en costes de consulta.
Carga eficiente de datos
Aunque BigQuery puede cargar datos de archivos CSV, los archivos CSV son ineficaces y poco expresivos (por ejemplo, no hay forma de representar matrices y structs en CSV). Si puedes elegir, deberías optar por exportar tus datos en un formato diferente. ¿Qué formato deberías elegir?
Un formato eficiente y expresivo de es Avro. Avro utiliza archivos binarios autodescriptivos que se dividen en bloques y pueden comprimirse bloque a bloque. Por ello, es posible paralelizar la carga de datos desde archivos Avro y la exportación de datos a archivos Avro. Como los bloques están comprimidos, el tamaño de los archivos también será menor de lo que podría indicar el tamaño de los datos. En términos de expresividad, el formato Avro es jerárquico y puede representar campos anidados y repetidos, algo que BigQuery admite pero que los archivos CSV no tienen una forma fácil de almacenar. Como los archivos Avro son autodescriptivos, nunca necesitas especificar un esquema.
Los archivos Avro tienen dos inconvenientes. Uno es que no son legibles para las personas. Si la legibilidad y la expresividad son importantes para ti, utiliza archivos JSON delimitados por nuevas líneas12 para almacenar tus datos. JSON admite la capacidad de almacenar datos jerárquicos, pero requiere que las columnas binarias estén codificadas en base-64. Sin embargo, los archivos JSON son más grandes que incluso los archivos CSV equivalentes, porque el nombre de cada campo se repite en cada línea. El segundo inconveniente es que los archivos Avro se almacenan fila a fila. Esto hace que los archivos Avro no sean tan eficientes para las consultas federadas.
El formato de archivo Parquet se inspiró en el formato Dremel ColumnIO original de Google,13 y, al igual que Avro, Parquet es binario, orientado a bloques, compacto y capaz de representar datos jerárquicos. Sin embargo, mientras que los archivos Avro se almacenan fila a fila, los archivos Parquet se almacenan columna a columna. Los archivos columnares están optimizados para leer un subconjunto de las columnas; cargar datos requiere leer todas las columnas, por lo que los formatos columnares son algo menos eficientes en la carga de datos. Sin embargo, el formato columnar hace que Parquet sea mejor opción que Avro para las consultas federadas, tema que trataremos en breve. Los archivos columnares de filas optimizadas (ORC) son otro formato de archivo columnar de código abierto. ORC es similar a Parquet en rendimiento y eficacia.
Por tanto, si puedes elegir entre varios formatos de archivo, te recomendamos Avro si piensas cargar los datos en BigQuery y descartar los archivos. Recomendamos Parquet si vas a conservar los archivos para consultas federadas. Utiliza JSON para archivos pequeños en los que la legibilidad humana sea importante.
Impacto de la compresión y la puesta en escena a través de Google Cloud Storage
Para formatos como CSV y JSON que no tienen compresión interna, debes considerar si debes comprimir los archivos utilizando gzip. Los archivos comprimidos se transmiten más rápidamente y ocupan menos espacio, pero se cargan más lentamente en BigQuery. Cuanto más lenta sea tu red, más deberías inclinarte por comprimir los datos.
Si estás en una red lenta o si tienes muchos archivos o archivos muy grandes, es posible configurar una subida multihilo de los datos utilizando gsutil cp. Una vez que todos los datos estén en Google Cloud Storage, puedes invocar bq load desde la ubicación de Cloud Storage:
gsutil -m cp *.csv gs://BUCKET/some/location bqload … gs://BUCKET/some/location/*.csv
Este experimento capta las distintas compensaciones que implica la compresión y el almacenamiento de los datos del cuadro de mando del colegio en el Almacenamiento en la Nube antes de invocar bq load. La Tabla 4-1 lo examina con más detalle. Tus resultados variarán, por supuesto, dependiendo de tu red y de los datos reales que estés cargando.14 Por tanto, deberías realizar una medición similar para tu trabajo de carga y elegir el método que te proporcione el mejor rendimiento en las medidas que te importan.
| Archivo comprimido | ¿Escenario en GCS? | Tamaño GCS | Tiempo de red (si está separado) | Tiempo de carga en BigQuery | Tiempo total |
|---|---|---|---|---|---|
| Sí | No | Ninguno | N/A | 105 segundos | 105 segundos |
| No | No | Ninguno | N/A | 255 segundos | 255 segundos |
| Sí | Sí | 16 MB | 47 seg. | 42 segundos | 89 segundos |
| No | Sí | 76 MB | 139 seg. | 28 segundos | 167 seg. |
Preparar el archivo en Google Cloud Storage implica pagar costes de almacenamiento al menos hasta que finalice el trabajo de carga de BigQuery. Sin embargo, los costes de almacenamiento suelen ser bastante bajos, por lo que, en este conjunto de datos y esta conexión de red (ver Tabla 4-1), la mejor opción es poner en escena los datos comprimidos en Cloud Storage y cargarlos desde allí. Aunque es más rápido cargar archivos sin comprimir en BigQuery, el tiempo de red para transferir los archivos empequeñece cualquier beneficio que obtendrías de una carga más rápida.
En el momento de escribir esto, la carga de archivos CSV y JSON comprimidos está limitada a archivos de menos de 4 GB en porque BigQuery tiene que descomprimir los archivos sobre la marcha en trabajadores cuya memoria es finita. Si tienes conjuntos de datos más grandes, divídelos en varios archivos CSV o JSON. Dividir los archivos tú mismo puede permitir cierto grado de paralelismo al realizar las cargas, pero dependiendo de cómo dimensiones los archivos, esto puede dar lugar a tamaños de archivo subóptimos en la tabla hasta que BigQuery decida optimizar el almacenamiento.
Precio y cuota
BigQuery no cobra por la carga de datos. La ingesta se produce en un conjunto de trabajadores distinto del clúster que proporciona las ranuras utilizadas para las consultas. Por lo tanto, tus consultas (incluso en la misma tabla en la que estás ingiriendo datos) no se ralentizan por el hecho de que se estén ingiriendo datos.
Las cargas de datos son atómicas. Las consultas sobre una tabla reflejarán la presencia de todos los datos que se carguen mediante la operación bq load o no reflejarán ninguno de ellos. No obtendrás resultados de consulta sobre una parte parcial de los datos.
El inconveniente de cargar datos utilizando un clúster "libre" es que los tiempos de carga pueden volverse impredecibles y verse atascados por trabajos preexistentes. En el momento de escribir esto, los trabajos de carga están limitados a 1.000 por tabla y 100.000 por proyecto al día. En el caso de archivos CSV y JSON, las celdas y filas están limitadas a 100 MB, mientras que en Avro, los bloques están limitados a 16 MB. El tamaño de los archivos no puede superar los 5 TB. Si tienes un conjunto de datos más grande, divídelo en varios archivos, cada uno de ellos de menos de 5 TB. Sin embargo, un único trabajo de carga puede enviar un máximo de 15 TB de datos divididos en un máximo de 10 millones de archivos. El trabajo de carga debe terminar de ejecutarse en menos de seis horas o será cancelado.
Consultas federadas y fuentes de datos externas
Puedes utilizar BigQuery sin cargar primero los datos. Es posible dejar los datos in situ, especificar la estructura de los datos, y utilizar BigQuery como mero motor de consulta. A diferencia de las consultas realizadas hasta ahora, para las que BigQuery consultaba su propio almacenamiento nativo, en esta sección tratamos el uso de "consultas federadas" para consultar "fuentes de datos externas" y explicamos cuándo puede interesarte utilizar este tipo de consultas.
Actualmente, admite fuentes de datos externas como Google Cloud Storage, Cloud Bigtable, Cloud SQL y Google Drive. Te darás cuenta de que todas estas fuentes son externas a BigQuery, pero están, no obstante, dentro del perímetro de Google Cloud. Esto es necesario porque, de lo contrario, la sobrecarga de la red y las consideraciones de seguridad harían que las consultas fueran lentas o inviables.
Cómo utilizar las consultas federadas
Hay tres pasos para consultar los datos de en una fuente de datos externa:
-
Crea una definición de tabla utilizando
bq mkdef. -
Crea una tabla utilizando
bq mk, pasando la definición de la tabla externa. -
Consulta la tabla normalmente.
Al igual que con la consulta de datos en el almacenamiento nativo, puedes hacerlo en la interfaz web o utilizando una interfaz programática. Para utilizar la interfaz web, sigue los pasos que acabamos de enumerar para crear una tabla, pero asegúrate de especificar que quieres una tabla externa, no nativa, como se muestra en la Figura 4-1.
Figura 4-1. Puedes crear una tabla externa desde la interfaz web siguiendo el flujo de trabajo "Crear tabla" pero especificando "Tabla externa" como tipo de tabla
Utilizando la interfaz de línea de comandos, crea una definición de tabla utilizando bq mkdef. Al igual que con bq load, tienes la opción de utilizar --autodetect:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_scorecard.csv
Esto imprime un archivo de definición de tabla en la salida estándar. Lo normal es redirigirlo a un archivo y utilizar esa definición de tabla para crear una tabla con bq mk:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_scorecard.csv \ > /tmp/mytable.json bq mk --external_table_definition=/tmp/mytable.json \ ch04.college_scorecard
Con estos dos pasos, puedes consultar la tabla college_scorecard como en la sección anterior, con la diferencia de que las consultas se realizarán sobre el archivo CSV almacenado en Google Cloud Storage; los datos no se ingieren en el almacenamiento nativo de BigQuery.
Comodines
Muchos marcos de big data como, como Apache Spark, Apache Beam y otros, fragmentan su salida en cientos de archivos con nombres como course_grades.csv-00095-de-00313. Al cargar estos archivos, sería conveniente evitar tener que listar cada archivo individualmente.
De hecho, es posible utilizar un comodín en la ruta a bq mkdef (y bq load) para que puedas hacer coincidir varios archivos:
bq mkdef --source_format=CSV \ --autodetect \ gs://bigquery-oreilly-book/college_* \ > /tmp/mytable.json
Esto crea una tabla que hace referencia a todos los archivos coincidentes con el patrón.
Tabla temporal
También es posible condensar los tres pasos (mkdef, mk, y query) pasando los parámetros de definición de la tabla junto con una consulta, garantizando así que la definición de la tabla sólo se utilizará mientras dure la consulta:
LOC="--location US"
INPUT=gs://bigquery-oreilly-book/college_scorecard.csv
SCHEMA=$(gsutil cat $INPUT | head -1 | awk -F, '{ORS=","}{for (i=1; i <= NF; i++){
print $i":STRING"; }}' | sed 's/,$//g'| cut -b 4- )
bq $LOC query \
--external_table_definition=cstable::${SCHEMA}@CSV=${INPUT} \
'SELECT SUM(IF(SAT_AVG != "NULL", 1, 0))/COUNT(SAT_AVG) FROM cstable'
En la consulta anterior, la definición de la tabla externa consta del nombre de la tabla temporal (cstable), dos dos puntos, la cadena del esquema, el símbolo @, el formato (CSV), un signo igual y la URL de Google Cloud Storage correspondiente al archivo o archivos de datos. Si ya tienes un archivo de definición de tabla, puedes especificarlo directamente:
--external_table_definition=cstable::${DEF}
Es posible especificar un archivo de esquema JSON, así como consultar JSON, Avro y otros formatos compatibles directamente desde Cloud Storage, Cloud Bigtable y otras fuentes de datos compatibles.
Aunque son innegablemente cómodas, las consultas federadas dejan mucho que desear en términos de rendimiento. Como los archivos CSV se almacenan por filas y las propias filas se almacenan en un orden arbitrario, se pierde gran parte de la eficacia que solemos asociar a BigQuery. Tampoco es posible que BigQuery estime cuántos datos va a necesitar escanear antes de ejecutar la consulta.
Carga y consulta de Parquet y ORC
Como se ha mencionado anteriormente en, Parquet y ORC son formatos de datos en columnas. Por lo tanto, la consulta federada de estos formatos proporcionará un mejor rendimiento de consulta que si los datos se almacenaran en formatos basados en filas, como CSV o JSON (no obstante, las consultas seguirán siendo más lentas que el almacenamiento nativo Capacitor de BigQuery).
Como Parquet y ORC son autodescriptivos (es decir, el esquema está implícito en los propios archivos), es posible crear definiciones de tablas sin especificar un esquema:
bq mkdef --source_format=PARQUET gs://bucket/dir/files* > table_def.json bq mk --external_table_definition=table_def.json <dataset>.<table>
Al igual que con la consulta de tablas externas creadas a partir de archivos CSV, la consulta de esta tabla funciona como la de cualquier otra tabla en BigQuery.
Aunque los archivos Parquet y ORC ofrecen un mejor rendimiento de consulta que los formatos de archivo basados en filas, siguen estando sujetos a las limitaciones de las tablas externas.
Carga y consulta de particiones Hive
Apache Hive permite leer, escribir y gestionar un almacén de datos basado en Apache Hadoop utilizando un lenguaje de consulta familiar similar a SQL. Cloud Dataproc, en Google Cloud, permite al software Hive trabajar con datos distribuidos almacenados en particiones Hive en Google Cloud Storage. Un patrón común de migración a la nube pública es que las cargas de trabajo Hive locales se trasladen a Cloud Dataproc y que las nuevas cargas de trabajo se escriban utilizando la capacidad de consulta federada de BigQuery. De este modo, las cargas de trabajo Hive actuales funcionan tal cual, mientras que las cargas de trabajo más recientes pueden aprovechar la capacidad de consulta a gran escala y sin servidor que proporciona BigQuery.
Puedes cargar particiones Hive en Google Cloud Storage especificando un modo de particionamiento Hive a bq load:
bq load --source_format=ORC --autodetect \ --hive_partitioning_mode=AUTO <dataset>.<table> <gcs_uri>
El URI de Almacenamiento en la Nube en el caso de las tablas Hive debe codificar el prefijo de la ruta de la tabla sin incluir ninguna clave de partición en el comodín. Así, si la clave de partición de una tabla Hive es un campo llamado datestamp, el URI de Almacenamiento en la Nube debe tener la siguiente forma:
gs://some-bucket/some-dir/some-table/*
Esto es así aunque los propios archivos empiecen todos por lo siguiente:
gs://some-bucket/some-dir/some-table/datestamp=
En el momento de escribir esto, el modo de partición AUTO puede detectar los siguientes tipos: STRING, INTEGER, DATE, y TIMESTAMP. También es posible solicitar que las claves de partición se detecten como cadenas (esto puede ser útil en el trabajo exploratorio):
bq load --source_format=ORC --autodetect \ --hive_partitioning_mode=STRINGS <dataset>.<table> <gcs_uri>
Al igual que con los archivos CSV de Google Cloud Storage, la consulta federada de particiones Hive requiere la creación de un archivo de definición de tabla, y las opciones reflejan fielmente las de carga:
bq mkdef --source_format=ORC --autodetect \
--hive_partitioning_mode=AUTO <gcs_uri> > table_def.json
Una vez creado el archivo de definición de tablas, la consulta es la misma tanto si el conjunto de datos externo subyacente está formado por archivos CSV como por particiones Hive.
Además de ORC, como se ha mostrado antes, también se admiten datos en otros formatos. Por ejemplo, para crear en una definición de tabla de datos almacenados en JSON delimitado por nuevas líneas, puedes utilizar esto:
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --autodetect -- hive_partitioning_mode=STRINGS <gcs_uri> <schema> > table_def.json
Observa que en el comando anterior se están autodetectando las claves de partición, pero no los tipos de datos de las claves de partición, porque especificamos explícitamente que deben tratarse como cadenas y no los tipos de datos de las demás columnas, ya que pasamos un esquema explícito.
Comenzamos esta sección diciendo que un caso de uso común para consultar particiones Hive es apoyar los esfuerzos de migración a la nube, donde ya existen importantes cargas de trabajo Hive, pero permiten que las futuras cargas de trabajo se implementen utilizando BigQuery. Aunque Apache Hive permite una gestión completa (lectura y escritura) de los datos, las tablas externas de BigQuery son de sólo lectura. Además, aunque BigQuery puede gestionar los datos que se modifican (por ejemplo, desde Hive) mientras se ejecuta una consulta federada, actualmente no admite conceptos como la lectura de datos en un momento determinado. Dado que las tablas externas en BigQuery tienen estas limitaciones, con el tiempo es mejor trasladar los datos al almacenamiento nativo de BigQuery y reescribir las cargas de trabajo de Hive en BigQuery. Cuando los datos están en el almacenamiento nativo de BigQuery, se hacen posibles funciones como DML, streaming, clustering, copias de tablas, etc.
Cuándo utilizar consultas federadas y fuentes de datos externas
La consulta de fuentes externas es más lenta que la consulta de datos que están de forma nativa en BigQuery, por lo que las consultas federadas no suelen recomendarse a largo plazo para los datos a los que se accede con frecuencia. Sin embargo, hay situaciones en las que las consultas federadas pueden ser ventajosas:
-
Realizar un trabajo exploratorio utilizando consultas federadas para determinar la mejor forma de transformar los datos brutos antes de cargarlos en BigQuery. Por ejemplo, las pruebas de las cargas de trabajo de análisis reales podrían dictar las transformaciones presentes en las tablas de producción. También podrías tratar las fuentes de datos originales y externas como staging, y utilizar consultas federadas para transformar los datos y escribirlos en las tablas de producción.
-
Mantener los datos en Google Sheets si la hoja de cálculo se va a editar interactivamente, y utilizar exclusivamente consultas federadas si los resultados de esas consultas tienen que reflejar los datos vivos de esa hoja.
-
Mantener los datos en una fuente de datos externa si la consulta SQL ad hoc de los datos es relativamente infrecuente. Por ejemplo, podrías mantener los datos en Cloud Bigtable si el uso predominante de esos datos es para la ingesta de streaming de baja latencia y gran volumen, y si la mayoría de las consultas sobre los datos pueden realizarse utilizando prefijos de clave.
Para conjuntos de datos grandes, relativamente estables y bien comprendidos, que se actualizarán periódicamente y se consultarán con frecuencia, el almacenamiento nativo de BigQuery es una mejor opción. En el resto de esta sección, examinamos los detalles de implementación de cada una de estas situaciones, comenzando con el trabajo exploratorio mediante consultas federadas.
Trabajo exploratorio mediante consultas federadas
La autodetección es una función cómoda que funciona tomando muestras de unas pocas filas (del orden de cientos) de los archivos de entrada para determinar el tipo de una columna. No es infalible, a menos que utilices formatos de archivo autodescriptivos, como Avro, Parquet u ORC. Para asegurarte de que tu canal ETL funciona correctamente, debes verificar el valor de cada fila para asegurarte de que el tipo de datos de cada columna es correcto. Por ejemplo, es posible que una columna contenga números enteros, salvo un puñado de filas que tengan flotantes. Si es así, es bastante probable que la autodetección detecte que la columna es un entero, porque la probabilidad de seleccionar una de las filas que contiene el valor de coma flotante es bastante baja. No te darás cuenta de que hay un problema hasta que realices una consulta que escanee la tabla con los valores de esta columna.
La mejor práctica es utilizar formatos de archivo autodescriptivos, en cuyo caso no tienes que preocuparte de cómo interpreta BigQuery los datos. Si necesitas utilizar CSV o JSON, te recomendamos que especifiques explícitamente un esquema. Aunque es posible especificar el esquema en un archivo JSON adjunto, también es posible pasar el esquema en la línea de comandos de bq mkdef creando una cadena con este formato:
FIELD1:DATATYPE1,FIELD2:DATATYPE2,...
Si no estás seguro de la calidad de tus datos, debes especificarlo todo como STRING. Ten en cuenta que éste es el tipo de datos por defecto, por lo que el comando de formateo se convierte simplemente en esto:
FIELD1,FIELD2,FIELD3,,...
¿Por qué tratarlo todo como una cadena? Aunque creas que algunos de los campos son enteros y otros flotantes, es mejor validar esta suposición. Define todo como una cadena y aprende qué transformaciones tienes que realizar a medida que consultas los datos y descubres errores.
Podemos extraer los nombres de las columnas utilizando la primera línea del archivo CSV para crear una cadena de esquema con el formato deseado:15
INPUT=gs://bigquery-oreilly-book/college_scorecard.csv SCHEMA=$(gsutil cat $INPUT | head -1 | cut -b 4- )
Si vamos a especificar el esquema, debemos pedir que se salte la primera fila y que la herramienta permita líneas vacías en el archivo. Podemos hacerlo pasando la definición de la tabla a través de sed, un editor de líneas:16
LOC="--location US" OUTPUT=/tmp/college_scorecard_def.json bq $LOC \ mkdef \ --source_format=CSV \ --noautodetect \ $INPUT \ $SCHEMA \ | sed 's/"skipLeadingRows": 0/"skipLeadingRows": 1/g' \ | sed 's/"allowJaggedRows": false/"allowJaggedRows": true/g' \ > $OUTPUT
Definimos que estamos operando en EE.UU. y que queremos guardar el resultado (la definición de la tabla) en la carpeta /tmp.
Llegados a este punto, tenemos una tabla que podemos consultar. Observa dos cosas: esta tabla está definida en una fuente de datos externa, por lo que podemos empezar a consultar los datos sin necesidad de esperar a que se ingieran; y todas las columnas son cadenas: no hemos hecho ningún cambio irreversible en los datos brutos.
Empecemos nuestra exploración de datos intentando hacer un reparto:
SELECT MAX(CAST(SAT_AVG AS FLOAT64)) AS MAX_SAT_AVG FROM `ch04.college_scorecard_gcs`
La consulta falla con el siguiente mensaje de error:
Bad double value: NULL
Esto indica que tenemos que tratar la forma no estándar en que se codifican los datos que faltan en el archivo. En la mayoría de los archivos CSV, los datos que faltan se codifican como una cadena vacía, pero en éste, se codifican como la cadena NULL.
Podríamos solucionar este problema comprobándolo antes de hacer el reparto:
WITH etl_data AS ( SELECT SAFE_CAST(SAT_AVG AS FLOAT64) AS SAT_AVG FROM `ch04.college_scorecard_gcs` ) SELECT MAX(SAT_AVG) AS MAX_SAT_AVG FROM etl_data
Observa que hemos iniciado una cláusula WITH que contiene todas las operaciones ETL que hay que realizar en el conjunto de datos. De hecho, a medida que vamos explorando el conjunto de datos y culminamos con la consulta de la sección anterior, aprendemos que necesitamos una función reutilizable para limpiar los datos numéricos:
CREATE TEMP FUNCTION cleanup_numeric(x STRING) AS
(
IF ( x != 'NULL' AND x != 'PrivacySuppressed',
CAST(x as FLOAT64),
NULL )
);
WITH etl_data AS (
SELECT
INSTNM
, cleanup_numeric(ADM_RATE_ALL) AS ADM_RATE_ALL
, cleanup_numeric(FIRST_GEN) AS FIRST_GEN
, cleanup_numeric(MD_FAMINC) AS MD_FAMINC
, cleanup_numeric(SAT_AVG) AS SAT_AVG
, cleanup_numeric(MD_EARN_WNE_P10) AS MD_EARN_WNE_P10
FROM
`ch04.college_scorecard_gcs`
)
SELECT
*
FROM
etl_data
WHERE
SAT_AVG > 1300
AND ADM_RATE_ALL < 0.2
AND FIRST_GEN > 0.1
ORDER BY
MD_FAMINC ASC
LIMIT 10
Llegados a este punto, podemos exportar los datos depurados (fíjate en SELECT *) a una nueva tabla (fíjate en CREATE TABLE) sólo para las columnas de interés ejecutando la siguiente consulta:
CREATE TEMP FUNCTION cleanup_numeric(x STRING) AS
(
IF ( x != 'NULL' AND x != 'PrivacySuppressed',
CAST(x as FLOAT64),
NULL )
);
CREATE TABLE ch04.college_scorecard_etl
OPTIONS(description="Cleaned up college scorecard data") AS
WITH etl_data AS (
SELECT
INSTNM
, cleanup_numeric(ADM_RATE_ALL) AS ADM_RATE_ALL
, cleanup_numeric(FIRST_GEN) AS FIRST_GEN
, cleanup_numeric(MD_FAMINC) AS MD_FAMINC
, cleanup_numeric(SAT_AVG) AS SAT_AVG
, cleanup_numeric(MD_EARN_WNE_P10) AS MD_EARN_WNE_P10
FROM
`ch04.college_scorecard_gcs`
)
SELECT * FROM etl_data
También es posible hacer un guión eliminando la declaración CREATE TABLE de la consulta anterior, invocando bq query y pasando una --destination_table.
ELT en SQL para experimentación
En muchas organizaciones, hay muchos más analistas de datos que ingenieros. Por tanto, las necesidades de los equipos de análisis de datos suelen superar con creces lo que pueden ofrecer los ingenieros de datos. En tales casos, puede ser útil que los propios analistas de datos puedan crear un conjunto de datos experimental en BigQuery y empezar con las tareas de análisis.
A continuación, la organización puede utilizar las pruebas de las cargas de trabajo analíticas reales para priorizar en qué se centran los ingenieros de datos. Por ejemplo, como ingeniero de datos, puede que aún no sepas qué campos necesitas extraer de un archivo de registro. Así que puedes configurar una fuente de datos externa como experimento y permitir que los analistas de datos consulten directamente los datos sin procesar en Google Cloud Storage.
Si los archivos de registro en bruto están en formato JSON, y cada una de las filas tiene una estructura diferente porque los registros proceden de aplicaciones distintas, los analistas podrían definir todo el mensaje de registro como una única columna de cadena de BigQuery y utilizar JSON_EXTRACT y funciones de manipulación de cadenas para extraer los datos necesarios. Al final de un mes, podrías analizar los registros de consulta de BigQuery para saber a qué campos accedieron realmente y cómo lo hicieron, y luego construir una canalización para cargar rutinariamente esos campos en BigQuery.
Por ejemplo, puedes exportar los registros de auditoría de BigQuery desde Stackdriver en formato JSON con el mensaje de registro completo en una columna anidada llamada protopayload_auditlog.metadataJson. Aquí tienes una consulta para contar los mensajes de registro con el elemento raíz tableDataRead y utilizar el recuento para clasificar los conjuntos de datos en función del número de veces que se accede a cada conjunto de datos:
SELECT
REGEXP_EXTRACT(protopayload_auditlog.resourceName,
'^projects/[^/]+/datasets/([^/]+)/tables') AS datasetRef,
COUNTIF(JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead")
IS NOT NULL) AS dataReadEvents,
FROM `ch04.cloudaudit_googleapis_com_data_access_2019*`
WHERE
JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead")
IS NOT NULL
GROUP BY datasetRef
ORDER BY dataReadEvents DESC
LIMIT 5
El método JSON_EXTRACT toma el nombre de la columna (protopayload_auditlog.metadataJson) como primer parámetro y un JSONPath17 como segundo parámetro.
Si los datos originales están en un sistema de gestión de bases de datos relacionales (RDBMS), es posible exportar los datos periódicamente como un archivo de valores separados por tabulaciones (TSV) a Google Cloud Storage. Por ejemplo, si utilizas MySQL con una base de datos denominada somedb, el comando correspondiente sería el siguiente:
mysql somedb < select_data.sql | \
gsutil cp - gs://BUCKET/data_$(date -u "+%F-%T").tsv
El select_data.sql contendría una consulta para extraer sólo los registros más recientes (aquí, los de los 10 días anteriores):
select * from my_table where transaction_date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
Con estos archivos exportados periódicamente, es sencillo para un analista empezar a consultar los datos mediante consultas federadas. Una vez demostrado el valor del conjunto de datos, éstos pueden cargarse de forma rutinaria y/o en tiempo real mediante una canalización de datos.
La razón por la que esto no siempre es adecuado para la operativa es que no maneja el caso de las mutaciones de la base de datos. Si se actualizan datos con más de 10 días de antigüedad, los volcados separados por tabulaciones no se sincronizarán. Siendo realistas, los volcados a archivos TSV sólo funcionan para conjuntos de datos pequeños (del orden de unos pocos gigabytes) en los que los propios campos originales de la base de datos no necesitan ser transformados o corregidos antes de ser utilizados para consultas analíticas.
Si quieres hacer operativa la sincronización desde una base de datos operativa a BigQuery, hay varias empresas de terceros que colaboran con Google, cada una con un menú de conectores y opciones de transformación.18 Estas herramientas pueden realizar la captura de datos de cambios (CDC) para permitirte transmitir los cambios de una base de datos a una tabla de BigQuery.
Consulta externa en Cloud SQL
BigQuery admite consultas externas, no sólo consultas federadas. Mientras que una consulta federada te permite consultar una fuente de datos externa utilizando BigQuery, una consulta externa te permite ejecutar la consulta en la base de datos externa y unir sin problemas los resultados con los datos en BigQuery. En el momento de escribir esto, se admiten las bases de datos MySQL y PostgresSQL en Cloud SQL (el servicio de base de datos relacional gestionado en Google Cloud).
Hay una configuración inicial única para crear un recurso de conexión en BigQuery y conceder a los usuarios permiso para utilizar este recurso de conexión. Una vez configurado este recurso de conexión, se puede utilizar desde EXTERNAL_QUERY de la siguiente manera:
SELECT * FROM EXTERNAL_QUERY(connection_id, cloud_sql_query);
En este ejemplo, connection_id es el nombre del recurso de conexión a la base de datos que creaste en BigQuery mediante la interfaz web, una API REST o la herramienta de línea de comandos.
El rendimiento de la consulta externa depende de la velocidad de la base de datos externa y, debido a que implica una tabla temporal intermedia, normalmente será más lenta que las consultas que se realizan puramente en Cloud SQL o puramente en BigQuery. Aún así, es muy beneficioso poder consultar datos que residen en un RDBMS en tiempo real sin tener que mover los datos de un lado a otro, evitando así la ETL, la programación y la orquestación innecesarias.
Por ejemplo, supongamos que deseamos crear un informe de tarjetas regalo pertenecientes a clientes que no han realizado ninguna compra reciente. La fecha del último pedido de cada cliente está disponible en Cloud SQL y se actualiza en tiempo real. Sin embargo, el saldo asociado a cada tarjeta regalo que haya emitido nuestra tienda está disponible en BigQuery. Podemos unir el resultado de una consulta externa de los datos de los pedidos en Cloud SQL con los datos del saldo de la tarjeta regalo en BigQuery para crear un informe actualizado sin tener que mover ningún dato:
SELECT
c.customer_id
, c.gift_card_balance
, rq.latest_order_date
FROM ch04.gift_cards AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
'connection_id',
'''SELECT customer_id, MAX(order_date) AS latest_order_date
FROM orders
GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
WHERE c.gift_card_balance > 100
ORDER BY rq.latest_order_date ASC;
Exploración y consulta interactivas de datos en Google Sheets
Google Sheets forma parte de G Suite, un conjunto de herramientas de productividad y de colaboración de Google Cloud. Proporciona los medios para crear, visualizar, editar y publicar hojas de cálculo. Una hoja de cálculo contiene valores tabulares en celdas individuales; algunos de estos valores son datos y otros son el resultado de cálculos realizados sobre los valores de otras celdas. Las Hojas de cálculo de Google llevan las hojas de cálculo a Internet: varias personas pueden editar en colaboración una hoja de cálculo, y puedes acceder a ella desde diversos dispositivos.
Cargar datos de Google Sheets en BigQuery
Google Sheets es una fuente externa, por lo que cargar y consultar una hoja de cálculo de Google Sheets es una consulta federada ; funciona de forma similar a consultar un archivo CSV desde Google Cloud Storage. Creamos una definición de tabla en BigQuery para que apunte a los datos de Google Sheets, y luego podemos consultar esa tabla como si fuera una tabla nativa de BigQuery.
Empecemos creando una hoja de cálculo de Google Sheets que podamos consultar. Abre un navegador web y, en la barra de navegación de URL, escribe https://sheets.new --. Al visitar esta URL se abre una hoja de cálculo en blanco.
Introduce los siguientes datos (o descarga el archivo CSV correspondiente de GitHub y haz un Archivo > Importar los datos a Google Sheets):
| Estudiante | Estado de origen | Puntuación SAT |
|---|---|---|
| Aarti | KS | 1111 |
| Billy | LA | 1222 |
| Cao | MT | 1333 |
| Dalia | NE | 1444 |
A continuación, navega a la sección BigQuery de la Consola en la Nube de GCP, crea un conjunto de datos (si es necesario) y crea una tabla, especificando que el origen de la tabla está en Drive y su URL, y que se trata de una Hoja de Google. Pide que se autodetecte el esquema, como se muestra en la Figura 4-2.
Figura 4-2. El cuadro de diálogo "Crear tabla" te permite especificar que la fuente de datos externa es Google Sheets
Una vez hecho esto, puedes consultar la hoja de cálculo como cualquier otra tabla de BigQuery:
SELECT * from advdata.students
Prueba a cambiar la hoja de cálculo y comprueba que los resultados devueltos reflejan el estado actual de la tabla (los resultados de las consultas federadas sobre conjuntos de datos externos no se almacenan en caché).
Aunque es posible consultar una hoja de cálculo utilizando SQL de este modo, es poco probable que quieras hacerlo, porque suele ser más cómodo utilizar las opciones interactivas de filtrado y ordenación integradas en las Hojas de cálculo de Google. Por ejemplo, puedes hacer clic en el botón Explorar y escribir la consulta en lenguaje natural "nota media SAT de los estudiantes en KS", que devuelve los resultados mostrados en la Figura 4-3.
Figura 4-3. Consulta en lenguaje natural en Google Sheets
Existen varios casos de uso generales para el vínculo entre Google Sheets y BigQuery:
-
Rellenar una hoja de cálculo con datos de BigQuery
-
Explorar las tablas de BigQuery mediante hojas de cálculo
-
Consulta de datos de Hojas mediante SQL
Veamos estos tres casos.
Rellenar una hoja de cálculo de Google Sheets con datos de BigQuery
El conector de datos de BigQuery en Google Sheets te permite consultar tablas de BigQuery19 y utilizar los resultados para rellenar una hoja de cálculo. Esto puede ser extremadamente útil cuando se comparten datos con usuarios no técnicos. En la mayoría de las empresas, casi todos los oficinistas saben leer/interpretar hojas de cálculo. No necesitan tener nada que ver con BigQuery o SQL para poder utilizar Google Sheets y trabajar con los datos de la hoja.
Desde Hojas de cálculo de Google, haz clic en Datos > Conectores de datos > BigQuery, selecciona tu proyecto y escribe una consulta para rellenar la hoja de cálculo a partir de la tabla de BigQuery con los datos del cuadro de mando de la universidad:
SELECT * FROM ch04.college_scorecard_etl
Explorar las tablas de BigQuery mediante hojas de cálculo
Una de las razones por las que podrías querer rellenar una hoja de cálculo de Google Sheets con datos de una tabla BigQuery de es que Sheets es una interfaz familiar para los usuarios empresariales que crean gráficos, fórmulas y tablas dinámicas. Por ejemplo, a partir de los datos del cuadro de mando de las universidades en Sheets, es bastante sencillo crear una fórmula para clasificar las universidades según el aumento de los ingresos medios experimentado por sus graduados:
-
En una nueva columna, introduce la siguiente fórmula:
=ArrayFormula(IF(ISBLANK(D2:D), 0, F2:F/D2:D))
Observa que ahora la hoja de cálculo se ha rellenado con la relación entre el valor de la columna F y el valor de la columna D, es decir, por el aumento de los ingresos.
-
En el menú Datos, crea un filtro en la columna recién creada y desactiva los espacios en blanco y los ceros.
-
Ordena la hoja de cálculo de la Z a la A basándote en esta columna.
Seleccionando las primeras filas de la hoja, podemos crear rápidamente un gráfico para mostrar las mejores universidades en términos de mejora económica del alumnado, como se ilustra en la Figura 4-4.
Figura 4-4. Gráfico que muestra las universidades que ofrecen mayores mejoras económicas a sus titulados
Además de crear interactivamente los gráficos que desees, puedes utilizar las funciones de aprendizaje automático de Google Sheets para explorar más a fondo tus datos.
En Google Sheets, haz clic en el botón Explorar y fíjate en los gráficos que se crean automáticamente mediante aprendizaje automático.20 Por ejemplo, la visión generada automáticamente que se muestra en la Figura 4-5 capta una desigualdad sorprendente.
Figura 4-5. Google Sheets genera automáticamente la idea de que las universidades que atienden a estudiantes universitarios de primera generación también tienen un alumnado más pobre; por cada 10% de aumento de estudiantes universitarios de primera generación, la renta familiar media disminuye en 11.400 $.
La Figura 4-6 muestra un gráfico posterior creado automáticamente que pone en contexto la página SAT_AVG.
Figura 4-6. Las universidades que atienden a estudiantes universitarios de primera generación tienden a tener promedios SAT más bajos
Incluso podemos pedir gráficos concretos utilizando el lenguaje natural. Si escribes "histograma de sat_avg donde first_gen más de 0,5" en el cuadro "Haz una pregunta", obtendrás la respuesta que se muestra en la Figura 4-7.
Figura 4-7. Obtener los gráficos que queremos simplemente pidiéndolos en Google Sheets
Explorar tablas BigQuery como una hoja de datos en Google Sheets
En la sección anterior, cargamos toda la tabla BigQuery en Google Sheets, pero esto sólo fue posible porque nuestro conjunto de datos de puntuación universitaria era lo suficientemente pequeño. Cargar toda la tabla BigQuery en Google Sheets obviamente no es factible para tablas BigQuery más grandes.
Google Sheets te permite acceder, analizar, visualizar y compartir incluso grandes conjuntos de datos BigQuery como una Hoja de Datos BigQuery. Para probarlo, inicia un nuevo documento de Google Sheets y navega a través del menú haciendo clic en Datos > Conectores de datos > Hoja de datos de BigQuery.
Elige tu proyecto en la Nube (que debería estar facturado), y navega por el menú hasta la tabla que quieras cargar en la Hoja de Datos haciendo clic en bigquery-public-data > usa_names > usa_1910_current > Conectar. Esta tabla contiene casi seis millones de filas y es demasiado grande para cargarla en su totalidad. En su lugar, BigQuery actúa como un backend en la nube para los datos mostrados en las Hojas.
A diferencia de cuando se carga toda la tabla en Hojas (como en la sección anterior), en la IU sólo se cargan las primeras 500 filas de una Hoja de Datos. La mejor forma de considerar estas 500 filas es como una vista previa del conjunto de datos completo. Otra diferencia está en la edición: si se carga toda la tabla, Google Sheets guarda una copia de los datos; por tanto, puedes editar las celdas y guardar la hoja de cálculo modificada. En cambio, si BigQuery actúa como backend en la nube, las celdas no son editables: los usuarios pueden filtrar y pivotar la hoja de datos de BigQuery, pero no pueden editar los datos. Cuando los usuarios filtran y pivotan, estas acciones ocurren en toda la tabla BigQuery, no sólo en la vista previa que se muestra en las Hojas.
Como ejemplo de el tipo de análisis que es posible, vamos a crear una tabla dinámica haciendo clic en el botón Tabla dinámica. En el editor de la tabla dinámica, elige state como Rows, y year como Columns. Para Values, elige number, y pide a Sheets que resuma por COUNTUNIQUE y muestre como Default, como se muestra en la Figura 4-8.
Figura 4-8. Crear una tabla dinámica a partir de una hoja de datos BigQuery
Como ilustra la Figura 4-8, obtenemos una tabla del número de nombres de bebé únicos en cada estado, desglosados por año.
Unir datos de Hojas con un gran conjunto de datos en BigQuery
Tanto BigQuery como Google Sheets son capaces de almacenar datos tabulares y proporcionar acceso a ellos. Sin embargo, BigQuery es principalmente un almacén de datos analíticos, mientras que Google Sheets es principalmente un documento interactivo. Como vimos en las secciones anteriores, la familiaridad de Sheets y las capacidades de exploración y creación de gráficos hacen que cargar datos de BigQuery en Sheets sea muy potente.
Sin embargo, existe una limitación práctica en cuanto al tamaño de los conjuntos de datos de BigQuery que puedes cargar en Sheets. Por ejemplo, BigQuery contiene información sobre preguntas, respuestas y usuarios de Stack Overflow. Incluso con BigSheets, estos conjuntos de datos a escala de petabytes son demasiado grandes para cargarlos directamente en Google Sheets. Sin embargo, todavía es posible escribir consultas que unan un pequeño conjunto de datos en Hojas con estos grandes conjuntos de datos en BigQuery y proceder a partir de ahí. Veamos un ejemplo.
De la sección anterior, tenemos una hoja de cálculo con los datos del cuadro de mando de la universidad. Supongamos que aún no tenemos los datos en BigQuery. Podríamos crear una tabla en BigQuery utilizando la hoja de cálculo como fuente, llamando a la tabla resultante college_scorecard_gs, como se muestra en la Figura 4-9.
Figura 4-9. Crear una tabla en BigQuery utilizando una hoja de cálculo de Google Sheets como fuente
Ahora podemos realizar una consulta en BigQuery que una esta tabla relativamente pequeña (7.700 filas) con una tabla masiva formada por los datos de Stack Overflow (10 millones de filas) para averiguar qué universidades aparecen con más frecuencia en los perfiles de los usuarios de Stack Overflow:
SELECT INSTNM, COUNT(display_name) AS numusers FROM `bigquery-public-data`.stackoverflow.users, ch04.college_scorecard_gs WHERE REGEXP_CONTAINS(about_me, INSTNM) GROUP BY INSTNM ORDER BY numusers DESC LIMIT 5
Se obtiene lo siguiente:21
| Fila | INSTNM | numusers |
|---|---|---|
| 1 | Instituto de Tecnología | 2364 |
| 2 | Universidad Nacional | 332 |
| 3 | Universidad Carnegie Mellon | 169 |
| 4 | Universidad de Stanford | 139 |
| 5 | Universidad de Maryland | 131 |
Las dos primeras entradas son sospechosas,22 pero parece que Carnegie Mellon y Stanford están bien representadas en Stack Overflow.
El resultado de esta consulta vuelve a ser lo suficientemente pequeño como para cargarlo directamente en Google Sheets y realizar filtrados y gráficos interactivos. Así pues, la capacidad de consulta SQL de los datos de Sheets desde BigQuery es especialmente útil para unir un pequeño conjunto de datos editables por humanos (en Google Sheets) con grandes conjuntos de datos empresariales (en BigQuery).
Consultas SQL sobre los datos de Cloud Bigtable
Cloud Bigtable es un servicio de base de datos NoSQL totalmente gestionado que escala hasta petabytes de datos. Cloud Bigtable está pensada para ser utilizada en situaciones en las que se desea una combinación de baja latencia (del orden de milisegundos), alto rendimiento (millones de operaciones por segundo), replicación para alta disponibilidad y escalabilidad sin fisuras (de gigabytes a petabytes). Por tanto, Cloud Bigtable se utiliza mucho en finanzas (conciliación y análisis comercial, detección de fraudes en los pagos, etc.), aplicaciones del Internet de las Cosas (IoT) (para el almacenamiento y procesamiento centralizados de datos de sensores en tiempo real) y publicidad (pujas, colocación y análisis de comportamiento en tiempo real). Aunque Cloud Bigtable sólo está disponible en GCP, es compatible con la API de código abierto Apache HBase, lo que permite una fácil migración de las cargas de trabajo en un entorno de nube híbrida.
Consultas NoSQL basadas en un prefijo de clave de fila
Cloud Bigtable proporciona consultas de alto rendimiento que buscan filas o conjuntos de filas que coinciden con una clave de fila específica, un prefijo de clave de fila o un rango de prefijos de. Aunque Cloud Bigtable requiere una instancia, consistente en uno o más clústeres lógicos, que se aprovisiona y está disponible en tu proyecto, utiliza ese clúster sólo para el cálculo (y no para el almacenamiento): los datos en sí se almacenan en Colossus, y los propios nodos sólo necesitan conocer la ubicación de los rangos de filas en Colossus. Como los datos no se almacenan en los nodos de Cloud Bigtable, es posible ampliar y reducir fácilmente el clúster de Cloud Bigtable sin una costosa migración de datos.
En el análisis financiero, un patrón común es almacenar datos de series temporales en Cloud Bigtable a medida que llegan en tiempo real y admitir consultas de baja latencia sobre esos datos basadas en la clave de fila (por ejemplo, todas las órdenes de compra, si las hay, de acciones de GOOG en los últimos 10 minutos). Esto permite que los cuadros de mando que requieren datos recientes proporcionen alertas y acciones automáticas basadas en la actividad reciente. Cloud Bigtable también permite obtener rápidamente una serie de datos (por ejemplo, todas las órdenes de compra de acciones de GOOG en un día determinado), una necesidad para el análisis financiero y la elaboración de informes. Los propios algoritmos de predicción necesitan ser entrenados con datos históricos (por ejemplo, la serie temporal de precios de compra de GOOG en los últimos cinco años), y esto es posible porque los marcos de aprendizaje automático como TensorFlow pueden leer y escribir directamente desde y hacia Cloud Bigtable. Estas tres cargas de trabajo (alertas en tiempo real, informes y formación en aprendizaje automático) pueden producirse en los mismos datos, y el clúster puede ampliarse y reducirse con picos de carga de trabajo debido a la separación de la informática y el almacenamiento.
Las tres cargas de trabajo del párrafo anterior implican la obtención de precios de compra de las acciones de Google. Cloud Bigtable proporcionará una recuperación eficiente de los registros si la fila-clave con la que se almacenan los datos de la serie temporal es de la forma GOOG#buy#20190119-090356.0322234, es decir, el nombre del valor y la marca de tiempo. Entonces, las consultas de precios de compra, ya sean de los últimos 10 minutos o de los últimos cinco años, implican solicitar registros que estén dentro de un rango de prefijos.
Pero, ¿qué ocurre si deseamos realizar un análisis ad hoc de todos los datos de Cloud Bigtable, y nuestra consulta no tiene una forma que permita recuperar sólo un subconjunto de registros, es decir, si nuestra consulta no filtra en función del prefijo de la clave de fila? Entonces el paradigma NoSQL de Cloud Bigtable se viene abajo, y es mejor recurrir en su lugar a las capacidades de consulta SQL ad hoc que ofrece BigQuery, entendiendo que los resultados de BigQuery estarán sujetos a una mayor latencia.
Consultas SQL ad hoc sobre datos de Cloud Bigtable
Al igual que BigQuery puede consultar directamente archivos en determinados formatos (CSV, Avro, etc.) en Google Cloud Storage tratándolo como una fuente de datos externa, BigQuery puede consultar directamente datos en Cloud Bigtable. Al igual que con los datos del Almacenamiento en la Nube, los datos de Cloud Bigtable pueden consultarse utilizando una tabla permanente o una tabla temporal. Una tabla permanente puede compartirse compartiendo el conjunto de datos del que forma parte; una tabla temporal sólo es válida mientras dure una consulta, por lo que no puede compartirse.
Una tabla en Cloud Bigtable se asigna a una tabla en BigQuery. En esta sección, utilizamos una serie temporal de datos de puntos de venta para ilustrarlo. Para seguir el ejemplo, ejecuta el script setup_data.sh del repositorio de GitHub de este libro para crear una instancia de Cloud Bigtable con algunos datos de ejemplo. Dado que el script de configuración crea una instancia de Cloud Bigtable con un clúster, recuerda eliminar la instancia cuando hayas terminado.
Comenzamos utilizando la interfaz de usuario de BigQuery para crear una tabla externa en BigQuery que apunte a los datos de Cloud Bigtable, como se muestra en la Figura 4-10. La ubicación es una cadena de la forma https://googleapis.com/bigtable/projects/[PROJECT_ID]/instances/[INSTANCE_ID]/tables/[TABLE_NAME]. Los campos PROJECT_ID, INSTANCE_ID, y TABLE_NAME hacen referencia al proyecto, la instancia y la tabla en Cloud Bigtable.23
Figura 4-10. Crear una tabla externa en BigQuery para que apunte a los datos de Cloud Bigtable.24
Los datos de Cloud Bigtable consisten en registros, cada uno de los cuales tiene una clave de fila y datos vinculados a la clave de fila que se organizan en familias de columnas, que son pares clave/valor, donde la clave es el nombre de la familia de columnas y el valor es un conjunto de columnas relacionadas.
Cloud Bigtable no exige que cada registro tenga todas las familias de columnas y todas las columnas permitidas en una familia de columnas; de hecho, la presencia o ausencia de una columna específica puede considerarse en sí misma un dato. Por tanto, BigQuery te permite crear una tabla vinculada a datos en Cloud Bigtable sin especificar explícitamente ningún nombre de columna. Si lo haces, BigQuery expone los valores de una familia de columnas como una matriz de columnas y cada columna como una matriz de valores escritos en diferentes marcas de tiempo.
En muchos casos, los nombres de las columnas se conocen de antemano, y si es así, es mejor proporcionar las columnas conocidas en la definición de la tabla. En nuestro caso, conocemos el esquema de cada registro en la logs-table de Cloud Bigtable:
-
Una clave de fila, que es el ID de la tienda seguido de la marca de tiempo de cada transacción
-
Una familia de columnas llamada "
sales" para capturar las transacciones de venta en caja -
Dentro de la familia de columnas
sales, capturamos:-
El ID del artículo (una cadena)
-
El precio al que se vendió el artículo (un número de coma flotante)
-
El número de artículos comprados en esta transacción (un número entero)
-
Observa en la Figura 4-10 que hemos especificado toda esta información en la sección Familias de columnas de la definición de la tabla.
Cloud Bigtable trata todos los datos simplemente como cadenas de bytes, por lo que los esquemas (cadena, flotante, entero) están pensados más para BigQuery, para que podamos evitar la necesidad de fundir los valores cada vez en nuestras consultas. Evitar el reparto es también la razón por la que pedimos que la clave de fila se trate como una cadena. Cuando se crea la tabla BigQuery, cada una de las columnas de Cloud Bigtable se asigna a una columna de BigQuery del tipo adecuado:
| precio.de.venta | REGISTRAR | NULABLE | Describe este campo... |
|---|---|---|---|
| celda.precio.venta | REGISTRAR | NULABLE | Describe este campo... |
| timestamp.precio.venta.celda | TIMESTAMP | NULABLE | Describe este campo... |
| valor.celda.precio.venta | FLOTA | NULABLE | Describe este campo... |
Una vez creada la tabla BigQuery, ya es posible realizar una consulta SQL a la antigua usanza para agregar el número total de itemid 12345 que se han vendido:
SELECT SUM(sales.qty.cell.value) AS num_sold FROM ch04.logs WHERE sales.itemid.cell.value = '12345'
Mejorar el rendimiento
Cuando realizamos una consulta federada sobre datos almacenados en Google Cloud Storage, el trabajo lo realizan los trabajadores de BigQuery. En cambio, cuando realizamos una consulta federada sobre datos almacenados en Cloud Bigtable, el trabajo se realiza en el clúster de Cloud Bigtable. Por tanto, el rendimiento de la segunda consulta está limitado por la capacidad del clúster de Cloud Bigtable y la carga que tenga en el momento en que se envía la consulta.
Como ocurre con cualquier consulta analítica, la velocidad global de la consulta también depende del número de filas que haya que leer y del tamaño de los datos que se lean. BigQuery intenta limitar la cantidad de datos que hay que leer leyendo sólo las familias de columnas a las que se hace referencia en la consulta, y Cloud Bigtable dividirá los datos entre los nodos para aprovechar la distribución de prefijos de clave de fila en todo el conjunto de datos.
Nota
Si tienes datos con una alta frecuencia de actualización o necesitas búsquedas puntuales de baja latencia, Cloud Bigtable proporcionará el mejor rendimiento para las consultas que puedan filtrar en un rango de prefijos de clave de fila. Puede ser tentador pensar en BigQuery como una forma de eludir el rendimiento de Cloud Bigtable mediante consultas puntuales ad hoc de los datos de Cloud Bigtable que no están limitadas por las claves de fila. Sin embargo, este patrón suele ofrecer un rendimiento decepcionante, y deberías probarlo en tu carga de trabajo antes de decidirte por una arquitectura de producción.
BigQuery almacena los datos en un orden orientado a columnas, que está optimizado para el escaneo de tablas, mientras que Cloud Bigtable almacena los datos en un orden orientado a filas, que está optimizado para pequeñas lecturas y escrituras. Las consultas de datos externos almacenados en Cloud Bigtable no ofrecen las ventajas del almacenamiento interno basado en columnas de BigQuery y sólo serán eficaces si leen un subconjunto de filas, no si realizan un escaneo completo de la tabla. Por lo tanto, debes asegurarte de que tus consultas federadas de BigQuery filtran la clave de fila de Bigtable; de lo contrario, tendrán que leer toda la tabla de Cloud Bigtable cada vez.
El mando que sí tienes bajo tu control es el número de nodos de tu clúster de Cloud Bigtable. Si vas a realizar consultas SQL de forma rutinaria contra tus datos de Cloud Bigtable, monitorea el uso de la CPU de Cloud Bigtable y aumenta el número de nodos de Cloud Bigtable si es necesario.
Al igual que con las consultas federadas sobre Google Cloud Storage, considera si es ventajoso configurar una canalización ELT cuando realices análisis sobre datos en Cloud Bigtable; es decir, considera extraer datos de Cloud Bigtable utilizando una consulta federada y cargarlos en una tabla BigQuery para su posterior análisis y transformaciones. Este enfoque, ilustrado en la Figura 4-11, te permite llevar a cabo tu carga de trabajo analítica en un entorno en el que no estás a merced de la carga operativa de Cloud Bigtable. El análisis de una tabla interna de BigQuery puede llevarse a cabo en miles de máquinas en lugar de en un clúster mucho más pequeño. Por tanto, las consultas analíticas finalizarán más rápidamente en BigQuery (suponiendo que estas analíticas no puedan realizarse utilizando prefijos de clave de fila) que si utilizas consultas federadas en una tabla externa. El inconveniente es, por supuesto, que los datos extraídos se duplican tanto en Cloud Bigtable como en BigQuery. Aun así, el almacenamiento suele ser barato, y las ventajas de escala y velocidad podrían ser suficiente compensación.
Figura 4-11. Utiliza una consulta federada para exportar las tablas seleccionadas a una tabla interna de BigQuery y haz que tus cargas de trabajo analíticas consulten la tabla interna
Es posible programar esa ingesta de datos en tablas internas de BigQuery para que se produzca periódicamente. Lo veremos en la siguiente sección.
Transferencias y exportaciones
Hasta ahora, hemos visto cómo cargar datos de forma puntual y cómo evitar el movimiento de datos utilizando consultas federadas. En esta sección, examinamos los servicios llave en mano para transferir datos a BigQuery desde diversas fuentes de forma periódica.
Servicio de transferencia de datos
El Servicio de Transferencia de Datos de BigQuery te permite programar cargas recurrentes de datos desde diversas fuentes de datos a BigQuery. Como ocurre con la mayoría de las funciones de BigQuery, puedes acceder al Servicio de Transferencia de Datos de BigQuery mediante la interfaz web o la herramienta de línea de comandos, o a través de una API REST. Para facilitar la repetición, te mostramos la herramienta de línea de comandos.
Después de configurar una transferencia de datos, BigQuery cargará automáticamente los datos según el calendario que especifiques. Sin embargo, en caso de que haya un problema con los datos originales, también puedes iniciar la recarga de datos para recuperarte de cualquier interrupción o vacío. Esto se denomina refrescar, y puedes iniciarlo desde la interfaz web.
El Servicio de Transferencia de Datos admite la carga de datos desde una serie de aplicaciones de Software como Servicio (SaaS), como Google Ads, Google Play, Amazon Redshift y YouTube, así como desde el Almacenamiento en la Nube de Google. Veremos cómo configurar la ingesta rutinaria de archivos que aparecen en el Almacenamiento en la Nube, observando por el camino cualquier diferencia con la transferencia de datos de un conjunto de datos SaaS, utilizando informes de canales de YouTube como ejemplo práctico.
Localidad de datos
Como hemos comentado antes en el capítulo, los conjuntos de datos BigQuery se crean en una región específica (como asia-northeast1, que es Tokio) o en una ubicación multirregional (por ejemplo, EU).25 Cuando configuras un Servicio de Transferencia de Datos a un conjunto de datos, éste procesa y escenifica los datos en la misma ubicación que el conjunto de datos BigQuery de destino.
Si tu cubo de Almacenamiento en la nube está en la misma región que tu conjunto de datos BigQuery, la transferencia de datos no incurre en cargos. La transferencia de datos entre regiones (por ejemplo, desde un cubo de Almacenamiento en la Nube en una región a un conjunto de datos de BigQuery en una región diferente) incurrirá en cargos de red, tanto si la transferencia se produce mediante cargas, exportaciones o transferencias de datos.
El Servicio de Transferencia de Datos de BigQuery tiene que estar activado (puedes hacerlo desde la interfaz web de BigQuery), y tienes que tener concedido el rol bigquery.admin para poder crear transferencias y escribir datos en el conjunto de datos de destino.
Configurar la tabla de destinos
El servicio de transferencia de datos no tiene la capacidad de crear una tabla nueva, autodetectar el esquema, etc. En su lugar, tienes que proporcionar una tabla plantilla que tenga el esquema deseado. Si vas a escribir todos los datos en una tabla particionada por columnas, especifica la columna de partición como una columna TIMESTAMP o DATE cuando crees el esquema de la tabla de destino. Trataremos las particiones en detalle en el Capítulo 7.
Aquí ilustramos el proceso en el conjunto de datos de puntuación universitaria. Lo tenemos almacenado en la multirregión de EE.UU., por lo que debes crear un conjunto de datos en la multirregión de EE.UU. si quieres probar los pasos siguientes.
En BigQuery, ejecuta la siguiente consulta:
CREATE OR REPLACE TABLE ch04.college_scorecard_dts AS SELECT * FROM ch04.college_scorecard_gcs LIMIT 0
Este es un ejemplo de sentencia DDL. Guardará el resultado de la consulta SELECT (que no tendrá filas y no incurrirá en ningún gasto) como una tabla llamada college_scorecard_dts en el conjunto de datos ch04.
Crear un trabajo de transferencia
En la línea de comandos, emite el comando siguiente para configurar un trabajo de transferencia:
bq mk --transfer_config --data_source=google_cloud_storage \
--target_dataset=ch04 --display_name ch04_college_scorecard \
--params='{"data_path_template":"gs://bigquery-oreilly-book/college_*.csv",
"destination_table_name_template":"college_scorecard_dts", "file_format":"CSV",
"max_bad_records":"10", "skip_leading_rows":"1", "allow_jagged_rows":"true"}'
Este comando especifica que la fuente de datos debe ser Google Cloud Storage (si estás transfiriendo desde YouTube Channel, por ejemplo, la fuente de datos sería youtube_channel) y que el conjunto de datos de destino es ch04. El nombre de visualización se utiliza como nombre legible por humanos en varias interfaces de usuario para referirse al trabajo de transferencia.
En el caso de YouTube, las tablas de destino se particionan automáticamente en el momento de la importación y se nombran adecuadamente. Sin embargo, en el caso de Almacenamiento en la Nube, tendrás que especificarlo explícitamente en el nombre de la tabla de destino. Por ejemplo, especificar mytable_{run_time|"%Y%m%d"} como plantilla del nombre de la tabla de destino indica que el nombre de la tabla debe empezar por mytable y que el tiempo de ejecución del trabajo debe añadirse utilizando los parámetros de formato datetime especificados.26 Un atajo conveniente es ytable_{run_date}. Esto simplemente utiliza la fecha en el formato AAAAMMDD. También es posible proporcionar un desfase temporal. Por ejemplo, para nombrar la tabla basándonos en la marca de tiempo 45 minutos después del tiempo de ejecución, podríamos especificar lo siguiente:
{run_time+45m|"%Y%m%d"}_mytable_{run_time|"%H%M%s"}
Se obtiene un nombre de tabla de la forma 20180915_mytable_004500.
Los parámetros en sí son específicos de la fuente de datos. En el caso de transferir archivos desde Google Cloud Storage, debemos especificar lo siguiente:
-
La ruta de los datos de entrada, con un comodín opcional.
-
La plantilla del nombre de la tabla de destino.
-
El formato de archivo. El servicio de transferencia de Almacenamiento en la Nube admite todos los formatos de datos que admite la capacidad de consulta federada (CSV, JSON, Avro, Parquet, etc.). En el caso de que el formato de archivo sea CSV, podemos especificar opciones específicas de CSV, como el número de líneas de cabecera que se deben omitir.
Los parámetros para la transferencia de datos del canal de YouTube son page_id (en YouTube) y table_suffix (en BigQuery).
Cuando ejecutes el comando bq mk, como se acaba de mostrar, obtendrás una URL como parte de un flujo de trabajo OAuth2; proporciona el token necesario iniciando sesión a través del navegador, y se creará el trabajo de transferencia.
También puedes iniciar un Servicio de Transferencia de Datos desde la interfaz web. Inicia una transferencia y elige la fuente de datos, como se ilustra en la Figura 4-12.
Figura 4-12. También puedes iniciar una transferencia de datos desde la interfaz web
Observa que no hemos especificado un horario; por defecto, el trabajo se ejecutará cada 24 horas, empezando "ahora". Es posible editar la programación de la tarea de transferencia desde la interfaz web de BigQuery, como se muestra en la Figura 4-13.
Figura 4-13. Editar la programación de la tarea de transferencia desde la interfaz web
El precio de las transferencias de datos varía según la fuente. En el momento de escribir este artículo, las transferencias de datos desde el Canal YouTube cuestan 5 $ por canal al mes, mientras que las transferencias de datos desde el Almacenamiento en la Nube no tienen ningún coste. Sin embargo, como el Servicio de Transferencia de Datos utiliza trabajos de carga para cargar los datos de Almacenamiento en la Nube en BigQuery, esto está sujeto a los límites de BigQuery sobre trabajos de carga.
Consultas programadas
BigQuery permite programar consultas para que se ejecuten de forma recurrente y guardar los resultados en tablas de BigQuery. En concreto, puedes utilizar una consulta federada para extraer datos de una fuente de datos externa, transformarlos y cargarlos en BigQuery. Como estas consultas programadas pueden incluir sentencias DDL y DML, es posible construir sofisticados flujos de trabajo puramente en SQL.
Puedes abrir el cuadro de diálogo para configurar una consulta programada haciendo clic en el botón Programar consulta de la IU de BigQuery, como se muestra en la Figura 4-14.27
Figura 4-14. Programar una consulta desde la interfaz de usuario de BigQuery
Las consultas programadas se basan en el Servicio de Transferencia de Datos, por lo que muchas de sus funciones son similares. Así, puedes especificar la tabla de destino utilizando los mismos ajustes de parámetros (por ejemplo, run_date y run_time) que para el Servicio de Transferencia de Datos (consulta la sección anterior).
Copia del conjunto de datos entre regiones
BigQuery admite la programación de copias de conjuntos de datos entre regiones a través del Servicio de Transferencia de Datos. En la interfaz web del Servicio de Transferencia de Datos, elige Copia entre Regiones como Origen. También tendrás que especificar como conjunto de datos de origen el nombre del conjunto de datos desde el que se van a copiar las tablas al conjunto de datos de destino, como se muestra en la Figura 4-15.
Dado que tanto el conjunto de datos de origen como el de destino son conjuntos de datos BigQuery, el iniciador debe tener permiso para iniciar transferencias de datos, listar tablas en el conjunto de datos de origen, ver el conjunto de datos de origen y editar el conjunto de datos de destino.
Una copia entre regiones también puede iniciarse desde bq mk especificando cross_region_copy como fuente de datos.
Figura 4-15. Inicia una copia programada de conjunto de datos entre regiones desde la interfaz de usuario del Servicio de Transferencia de Datos especificando que el origen es una copia entre regiones
Exportar los registros del Registro en la Nube
Los datos de registro de las máquinas virtuales (VM) y servicios de GCP28 pueden almacenarse, monitorizarse y analizarse mediante los registros de Cloud Logging. De este modo, Cloud Logging sirve a como vista unificada de toda la actividad de tu cuenta GCP. Por tanto, resulta útil exportar los registros de Cloud Logging y Firebase a BigQuery. Puedes hacerlo utilizando la interfaz de línea de comandos, una API REST o la interfaz de usuario web, que se muestra en la Figura 4-16.
Para exportar todos los registros del servicio BigQuery, haz clic en el botón Crear exportación situado en la parte superior del Visor de Registros de Nube y, a continuación, rellena la siguiente información:
-
Selecciona BigQuery y Todos los registros para ver los registros de BigQuery. ¿Ves tu actividad reciente?
-
Proporciona un nombre de fregadero, quizás
bq_logs. -
Especifica el servicio sumidero: BigQuery, porque queremos exportar a BigQuery.
-
Especifica el destino del sumidero:
ch04el conjunto de datos al que queremos exportar.
Figura 4-16. Para ver los registros de los trabajos de ingesta de BigQuery de la sección anterior, por ejemplo, debes ir a la sección Registro en la nube de la Consola en la nube de GCP
Veamos los registros generados al ejecutar una consulta. Ve a la interfaz de usuario de BigQuery e intenta ejecutar una consulta:
SELECT gender, AVG(tripduration / 60) AS avg_trip_duration FROM `bigquery-public-data`.new_york_citibike.citibike_trips GROUP BY gender HAVING avg_trip_duration > 14 ORDER BY avg_trip_duration
En la interfaz de usuario de BigQuery, si ahora haces (cambia la fecha adecuadamente)
SELECT protopayload_auditlog.status.message FROM ch04.cloudaudit_googleapis_com_data_access_20190128
encontrarás una lista de mensajes de registro de BigQuery, incluido un mensaje sobre la lectura de los resultados de la consulta anterior. Dependiendo de tu filtro de fechas, también deberías ver los registros correspondientes a operaciones anteriores que hayas realizado.
Ten en cuenta algunas cosas sobre la capacidad de exportación:
-
El esquema e incluso el nombre de la tabla fueron establecidos por Cloud Logging. Simplemente especificamos el conjunto de datos de destino.
-
Los datos se actualizaron casi en tiempo real. Éste es un ejemplo de buffer de streaming: una tabla BigQuery actualizada en tiempo real por Cloud Logging (aunque la latencia típica de las consultas BigQuery implica que los datos que ves tienen unos segundos de antigüedad).
Uso de Cloud Dataflow para leer/escribir desde BigQuery
Como ya hemos comentado, BigQuery admite consultas federadas desde fuentes como Google Sheets. Su Servicio de Transferencia de Datos admite fuentes como Google Ads y YouTube. Productos como Stackdriver Logging y Firestore ofrecen la posibilidad de exportar sus datos a BigQuery.
¿Qué ocurre si utilizas un producto como MySQL que no ofrece capacidad de exportación y no es compatible con el Servicio de Transferencia de Datos? Una opción es utilizar Cloud Dataflow. Cloud Dataflow es un servicio totalmente gestionado en GCP que simplifica la ejecución de canalizaciones de datos que se construyen utilizando la API de código abierto Apache Beam, gestionando detalles operativos como el rendimiento, el escalado, la disponibilidad, la seguridad y el cumplimiento, para que los usuarios puedan centrarse en programar en lugar de gestionar clusters de servidores. Puedes utilizar Dataflow para transformar y enriquecer datos tanto en modo streaming (tiempo real) como en modo batch (histórico), con el mismo código reutilizable tanto en streaming como en batch pipelines.
Utilizar una plantilla de flujo de datos para cargar directamente desde MySQL
Aunque podrías escribir tus propios pipelines Cloud Dataflow (lo hacemos en "Escribir un trabajo Dataflow"), en GitHub hay disponibles pipelines de plantillas Dataflow para muchas necesidades comunes. Mirando la lista de plantillas disponibles, parece que la plantilla Jdbc a BigQuery podría ajustarse a nuestros requisitos y permitirnos transferir datos desde nuestra base de datos MySQL a BigQuery.
Abre la Consola Cloud de GCP y navega hasta la sección Cloud Dataflow. A continuación, selecciona "Crear trabajo a partir de plantilla", elige "Jdbc a BigQuery" y rellena el formulario resultante con información sobre la tabla de la base de datos de origen en MySQL y la tabla de destino en BigQuery, como se ilustra en la Figura 4-17.
Figura 4-17. Creación de un trabajo de flujo de datos a partir de una plantilla para transferir datos de MySQL a BigQuery
Cuando pulses el botón "Ejecutar trabajo", se lanzará un trabajo Dataflow. Ejecutará la consulta JDBC que hayas especificado y escribirá las filas resultantes en BigQuery.
Escribir un trabajo de flujo de datos
Si tienes un formato para el que no hay consultas federadas, ni Servicio de Transferencia de Datos, ni capacidad de exportación, y ni plantilla Dataflow preconstruida, puedes escribir tu propia canalización Dataflow para cargar los datos en BigQuery.
Aunque tanto la consulta federada como un Servicio de Transferencia de Datos existen para archivos CSV en Google Cloud Storage, utilizaremos archivos CSV para demostrar cómo es esto. El código está escrito en la API de Apache Beam y puede escribirse en Python, Java o Go. Aquí utilizaremos Python.
El quid del código consiste en extraer los datos de entrada, transformarlos extrayendo y limpiando los campos deseados, y cargarlos en BigQuery:
INPATTERNS = 'gs://bigquery-oreilly-book/college_*.csv'
RUNNER = 'DataflowRunner'
with beam.Pipeline(RUNNER, options = opts) as p:
(p
| 'read' >> beam.io.ReadFromText(INPATTERNS, skip_header_lines=1)
| 'parse_csv' >> beam.FlatMap(parse_csv)
| 'pull_fields' >> beam.FlatMap(pull_fields)
| 'write_bq' >> beam.io.gcp.bigquery.WriteToBigQuery(bqtable, bqdataset,
schema=get_output_schema())
)
En este código, creamos una canalización Beam, especificando que será ejecutada por Cloud Dataflow. Otras opciones para el RUNNER incluyen DirectRunner (ejecutado en la máquina local) y SparkRunner (ejecutado por Apache Spark en un clúster Hadoop, como Cloud Dataproc en GCP).
El primer paso de la cadena es leer todos los archivos que coincidan con los patrones de entrada especificados. Estos archivos pueden estar en el disco local o en Google Cloud Storage. Los datos de los archivos de texto se envían línea a línea al siguiente paso de la cadena, donde se aplica el método parse_csv a cada línea:
def parse_csv(line):
try:
values = line.split(',')
rowdict = {}
for colname, value in zip(COLNAMES, values):
rowdict[colname] = value
yield rowdict
except:
logging.warn('Ignoring line ...')
El método parse_csv divide la línea a base de comas y convierte los valores en un diccionario, donde la clave es el nombre de la columna y el valor es el valor de la celda.
A continuación, este diccionario se envía al método pull_fields, que extraerá los datos de interés (la columna INSTNM y algunos campos numéricos) y los transformará:
def pull_fields(rowdict):
result = {}
# required string fields
for col in 'INSTNM'.split(','):
if col in rowdict:
result[col] = rowdict[col]
else:
logging.info('Ignoring line missing {}', col)
return
# float fields
for col in \
'ADM_RATE_ALL,FIRST_GEN,MD_FAMINC,SAT_AVG,MD_EARN_WNE_P10'.split(','):
try:
result[col] = (float) (rowdict[col])
except:
result[col] = None
yield result
Estos diccionarios con los campos extraídos se introducen en BigQuery fila a fila. El sumidero de BigQuery (beam.io.gcp.bigquery.WriteToBigQuery) requiere el nombre de la tabla, el nombre del conjunto de datos y un esquema de salida de la siguiente forma:
INSTNM:string,ADM_RATE_ALL:FLOAT64,FIRST_GEN:FLOAT64,...
Si es necesario, se crea la tabla BigQuery y se añaden filas. También existen otras opciones, por ejemplo, truncar la tabla (es decir, sustituirla).
Al ejecutar el programa Python29 lanzará una tarea de flujo de datos que leerá el archivo CSV, lo analizará línea por línea, extraerá los campos necesarios y escribirá los datos transformados en BigQuery.
Aunque hemos demostrado el programa Dataflow en una canalización por lotes (es decir, la entrada no es ilimitada), esencialmente puedes utilizar la misma canalización para analizar, transformar y escribir registros recibidos en modo streaming (por ejemplo, desde Cloud Pub/Sub), como ocurrirá en muchas aplicaciones de registro e IoT. El enfoque Dataflow proporciona así una forma de transformar los datos sobre la marcha y cargarlos en BigQuery.
Ten en cuenta que Dataflow utiliza inserciones en streaming para cargar los datos en BigQuery, tanto si operas en modo batch como en modo streaming. Las inserciones en streaming ofrecen la ventaja de que los datos aparecen a tiempo, en un búfer de streaming, y pueden consultarse incluso mientras se escriben los datos. La desventaja es que, a diferencia de los trabajos de carga de BigQuery, las inserciones en streaming no son gratuitas. Recuerda que cargar datos en BigQuery puede ser gratis, pero por razones de rendimiento, hay límites en el número de trabajos de carga que puedes hacer. Las inserciones en flujo proporcionan una forma de evitar los límites y cuotas impuestos a los trabajos de carga sin sacrificar el rendimiento de la consulta.
Utilizar directamente la API de streaming
Presentamos Apache Beam en Cloud Dataflow como una forma de extraer, transformar y cargar datos en BigQuery en modo streaming, pero no es el único marco de procesamiento de datos capaz de escribir en BigQuery. Si tu equipo está más familiarizado con Apache Spark, escribir la canalización ETL en Spark y ejecutarla en un clúster Hadoop (como Cloud Dataproc en GCP) es una alternativa viable a Dataflow. Esto se debe a que existen bibliotecas cliente para una variedad de lenguajes diferentes, y BigQuery admite una API de streaming.
Cubrimos la biblioteca cliente y el streaming con más detalle en el Capítulo 5, pero aquí tienes un fragmento que ilustra cómo cargar datos utilizando la API de streaming en Python después de tener un cliente:
# create an array of tuples and insert as data becomes available
rows_to_insert = [
(u'U. Puerto Rico', 0.18,0.46,23000,1134,32000),
(u'Guam U.', 0.43,0.21,28000,1234,33000)
]
errors = client.insert_rows(table, rows_to_insert) # API request
Cuando hay nuevos datos disponibles, se invoca el método insert_rows() del cliente BigQuery. Este método invoca a su vez el método tabledata.insertAll de la API REST. BigQuery almacena los datos en un búfer de flujo y están disponibles inmediatamente para su consulta, aunque pueden tardar hasta 90 minutos en estar disponibles para su exportación.
Trasladar datos locales
En el Capítulo 1, comentamos que uno de los factores clave que hacen que BigQuery funcione es la separación del cálculo y el almacenamiento a través de una red con un ancho de banda de petabits por segundo. BigQuery funciona mejor en conjuntos de datos que están dentro del centro de datos y detrás del cortafuegos de Google Cloud: si BigQuery tuviera que leer sus datos desde la red pública o desde una conexión de red más lenta, no tendría el mismo rendimiento. Por tanto, para que BigQuery funcione bien, es esencial que los datos estén en la nube.
BigQuery es una plataforma analítica altamente escalable y es el lugar recomendado para almacenar datos estructurados, excepto los destinados a un uso transaccional en tiempo real. Entonces, si BigQuery es el lugar donde almacenar todos los datos estructurados que se utilizarán para el análisis de datos, ¿cómo trasladas tus datos locales a BigQuery?
Métodos de migración de datos
Si tienes una buena red con rápidas velocidades de interconexión a Google Cloud, podrías utilizar bq load para cargar los datos en BigQuery. Como se ha comentado en este capítulo, es preferible que los datos que se cargan ya estén presentes en Google Cloud Storage. Puedes utilizar la herramienta de línea de comandos gsutil para copiar los datos desde las instalaciones locales al Almacenamiento en la Nube.
Cuando copies muchos archivos, especialmente archivos grandes, en Google Cloud Storage, utiliza la opción -m para activar el multihilo. El multiproceso permitirá a la herramienta gsutil copiar archivos en paralelo:
gsutil -m cp /some/dir/myfiles*.csv gs://bucket/some/dir
Dado que es probable que se sigan recopilando datos, moverlos no suele ser un proceso puntual, sino continuo. Un enfoque para manejar esto es lanzar una Función de la Nube para invocar automáticamente bq load siempre que aparezca un archivo en el Almacenamiento en la Nube.30 A medida que aumenta la frecuencia de llegada de archivos (y a medida que esos archivos se hacen más pequeños), es mejor que utilices Cloud Pub/Sub31 en lugar de Cloud Storage para almacenar los datos entrantes como mensajes que serán procesados por una canalización Cloud Dataflow y transmitidos directamente a BigQuery.
Estos tres enfoques -gsutil, Cloud Functions y Cloud Dataflow - se muestran en las tres primeras filas de la Tabla 4-2 y funcionan cuando la conexión de red es bastante buena.
| Qué quieres migrar | Método de migración recomendado |
|---|---|
| Archivos relativamente pequeños | gsutil cp -mbq load |
| Cargar archivos ocasionalmente (por ejemplo, una vez al día) en BigQuery cuando estén disponibles | gsutil cpFunción Nube invoca bq load |
| Carga de mensajes en streaming en BigQuery | Envía los datos a Cloud Pub/Sub y luego utiliza Cloud Dataflow para transmitirlos a BigQuery Normalmente, tienes que implementar la canalización en Python, Java, Go, etc. Alternativamente, utiliza la API de Streaming de la biblioteca cliente. Esto se tratará con más detalle en el Capítulo 5. |
| Particiones de colmena | Migrar la carga de trabajo Hive a Cloud Dataproc Consultar particiones Hive como tabla externa |
| Petabytes de datos o red deficiente | Aparato de transferenciabq load |
| De región a región o desde otras nubes | Servicio de transferencia de almacenamiento en la nube |
| Cargar desde un volcado MySQL | Plantillas de flujo de datos de código abierto que se pueden configurar y ejecutar |
| Transferencia desde Google Cloud Storage, Google Ads, Google Play, etc. a BigQuery | Servicio de transferencia de datos de BigQuery Configúralo en BigQuery. Todas las funciones del Servicio de Transferencia de Datos funcionan de forma similar. |
| Migrar desde otros almacenes de datos como Amazon Redshift, Teradata, etc. | El Servicio de Transferencia de Datos de BigQuery migra datos y esquemas. Dependiendo del almacén de datos de origen, podría utilizar agentes de migración que residen en el almacén de datos de origen y puede admitir transferencias tanto únicas como incrementales. |
| Stackdriver Logging, Firestore, etc. | Estas herramientas ofrecen la posibilidad de exportar a BigQuery. Configúralo en la otra herramienta (Stackdriver, Firestore, etc.). |
Aunque la migración de datos utilizando gsutil para organizar los datos en el Almacenamiento en la Nube y luego invocando bq load puede ser fácil de hacer si sólo tienes unos pocos conjuntos de datos pequeños, es más difícil si tienes muchos conjuntos de datos o si tus conjuntos de datos son grandes. A medida que aumenta el tamaño de los datos, también aumenta la incidencia de errores. Por tanto, migrar grandes conjuntos de datos requiere prestar atención a los detalles: por ejemplo, comprobar la suma de los datos en la captura y la ingesta, trabajar con cortafuegos para que no bloqueen las transferencias ni dejen caer paquetes, evitar la exfiltración de datos sensibles y asegurarte de que tus datos están encriptados y protegidos contra pérdidas durante y después de la migración.
Otro problema del método gsutil es que es muy probable que tu empresa no pueda dedicar ancho de banda a las transferencias de datos, porque ese ancho de banda dedicado suele ser demasiado caro e interrumpirá las operaciones rutinarias que transmiten datos a través de la red corporativa.
Para los casos en que no sea posible copiar datos a Google Cloud debido al tamaño de los datos o a limitaciones de la red, considera la posibilidad de utilizar el Dispositivo de Transferencia. Se trata de un servidor de almacenamiento en bastidor de gran capacidad que se te envía, y luego tú lo llenas y lo envías de vuelta a Google Cloud o a uno de sus socios autorizados. El Dispositivo de Transferencia se utiliza mejor para grandes cantidades de datos (de cientos de terabytes a petabytes) para los que la situación de tu red no satisface las demandas de transferencia.
Si tus datos no se encuentran en las instalaciones, sino en otra nube pública (como en un cubo del Servicio de Almacenamiento Simple de Amazon Web Services), puedes utilizar el Servicio de Transferencia de Almacenamiento en la Nube para migrar los datos. Los casos de uso más comunes incluyen ejecutar una aplicación en Amazon Web Services pero analizar sus datos de registro en BigQuery. El Servicio de Transferencia de Almacenamiento en la Nube también es una buena forma de transferir grandes cantidades de datos entre regiones de Google.
El Servicio de Transferencia de Datos de BigQuery automatiza la carga de datos en BigQuery desde propiedades de Google como YouTube, Google Ads, etc. Otras herramientas como Stackdriver Logging y Firestore ofrecen la posibilidad de exportar a BigQuery.
El Servicio de Transferencia de Datos de BigQuery también admite la migración automática de datos y esquemas de otros productos de almacén de datos como Amazon Redshift y Teradata. En el caso de Teradata, un agente de migración local se conecta al almacén de datos local y copia los datos en BigQuery. Se admiten transferencias tanto únicas como incrementales. También se admite la extracción partición por partición. En el momento de redactar este documento, sólo se migran los datos y el esquema, no las canalizaciones ETL ni los procedimientos almacenados, aunque existen herramientas asociadas que pueden realizar la traducción automática de SQL y la virtualización del almacén de datos. Consulta la documentación para obtener información detallada sobre lo que admite cada almacén de datos de origen.
Aunque puedes llevar a cabo la migración de datos tú mismo, es poco probable que sea algo en lo que tu departamento informático tenga mucha experiencia, dado que la migración suele ser una tarea puntual. Puede ser ventajoso recurrir a un socio autorizado de GCP32 para realizar la migración de datos.
Resumen
La herramienta de línea de comandos bq proporciona un único punto de entrada para interactuar con el servicio BigQuery en GCP. Después de que tus datos estén en Google Cloud Storage, puedes hacer una carga única de los datos utilizando la utilidad bq load. Admite la autodetección de esquemas, pero también puede utilizar un esquema específico que tú le proporciones. Dependiendo de si tu trabajo de carga está ligado a la CPU o a la E/S, puede ser ventajoso comprimir los datos o dejarlos sin comprimir.
Es posible dejar los datos en su sitio, especificar la estructura de los datos y utilizar BigQuery como único motor de consulta. Estos se denominan conjuntos de datos externos, y las consultas sobre conjuntos de datos externos se denominan consultas federadas. Utiliza las consultas federadas para el trabajo exploratorio, o cuando el uso principal de los datos sea en el formato externo (por ejemplo, consultas de baja latencia en Cloud Bigtable o trabajo interactivo en Sheets). EXTERNAL_QUERY ofrece la posibilidad de hacer uniones en tiempo real contra bases de datos MySQL y Postgres sin ningún movimiento de datos. Para conjuntos de datos grandes, relativamente estables y bien comprendidos que se actualizarán periódicamente y se consultarán a menudo, el almacenamiento nativo de BigQuery es una mejor opción. Las consultas federadas también son útiles en un flujo de trabajo de Extracción, Carga y Transformación (ELT) cuyos datos aún no se conocen bien.
Es posible establecer una transferencia programada de datos desde diversas plataformas a BigQuery. Otras herramientas también admiten mecanismos para exportar sus datos a BigQuery. Para la carga rutinaria de datos, considera el uso de Funciones en la Nube; para cargas continuas y en flujo, utiliza Flujo de Datos en la Nube. También es posible programar consultas (incluidas las consultas federadas) para que se ejecuten periódicamente y hacer que estas consultas carguen datos en tablas.
1 De seis a ocho cambios cada década; consulta https://oreil.ly/Merow.
2 Consulta https://abc7ny.com/news/border-of-north-and-south-carolina-shifted-on-january-1st/1678605/ y https://www.nytimes.com/2014/08/24/opinion/sunday/how-the-carolinas-fixed-their-blurred-lines.html.
3 Esto se establece a través de un cuadro desplegable en la Consola en la Nube de GCP, o la última vez que hiciste un gcloud init. Normalmente, un proyecto corresponde a una carga de trabajo o a un equipo pequeño.
4 Para ver una lista actualizada, consulta https://cloud.google.com/bigquery/docs/locations.
5 El algoritmo de autodetección sigue tratando cada vez más casos de esquina, por lo que es posible que esto no te ocurra a ti. Sin embargo, en general, la autodetección del esquema nunca será perfecta. Independientemente de los detalles sobre qué aspecto del esquema no se captura correctamente, nuestro punto más importante es el siguiente: utiliza el esquema autodetectado como punto de partida y construye sobre él, como hacemos en esta sección.
6 Es posible que una columna entera sea anulable, pero el archivo está codificando los valores NULL de una forma no estándar. BigQuery está interpretando el texto NULL como una cadena, y por eso falla la carga.
7 La cadena NULL en el archivo representa una falta de datos para ese campo, y esto es lo que debería significar también un valor NULL en nuestra tabla BigQuery.
8 Como hemos señalado en capítulos anteriores, creemos que todas las menciones de precios son correctas en el momento de escribir este libro, pero te rogamos que consultes las correspondientes fichas de políticas y precios(https://cloud.google.com/bigquery/pricing), ya que están sujetas a cambios.
9 En el momento de escribir esto, esta capacidad no existe en la "nueva" IU; debes acceder a ella a través de la herramienta de línea de comandos bq.
10 Las cadenas se ordenan léxicamente. Si se almacena como cadena, "100" sería menor que "20" por la misma razón que "abc" va antes que "de" cuando se ordenan las dos cadenas. Si se ordenan numéricamente, 20 es menor que 100, como cabría esperar.
11 El archivo contiene D/M/AAAA, mientras que el formato estándar para una fecha es AAAA-MM-DD (que coincide con la ISO 8601). Aunque la detección automática puede examinar varias filas y deducir si el 12/11/1965 es el 12 de noviembre o el 11 de diciembre, no queremos que la carga BigQuery basada en esquemas haga ninguna suposición de este tipo. La cadena de transformación que construiremos más adelante en este capítulo convierte las fechas al formato estándar. Por ahora, tratémoslo como una cadena.
12 El JSON delimitado por nuevas líneas a menudo recibe el nombre de jsonl, o "formato de líneas JSON".
13 Consulta https://blog.twitter.com/engineering/en_us/a/2013/dremel-made-simple-with-parquet.html. En el Capítulo 6 hablamos de Capacitor, el formato de almacenamiento backend de BigQuery, que es el sucesor de ColumnIO.
14 Pruébalo ejecutando los scripts load_*.sh en el 04_load del repositorio GitHub de este libro.
15 Este archivo en concreto incluye un "marcador de orden de bytes" (\u0eff) como primer carácter, así que eliminamos los primeros bytes utilizando cortar: cut -b 4-.
16 El script completo se llama load_external_gcs.sh y se encuentra en el repositorio de GitHub de este libro.
17 Para conocer la gramática de un JSONPath, consulta https://restfulapi.net/json-jsonpath/.
18 Entre estos socios se encuentran Alooma, Informatica y Talend. Para consultar la lista completa y actualizada de socios de BigQuery, visita https://cloud.google.com/bigquery/partners/.
19 En el momento de escribir esto, existen restricciones de tamaño en la tabla BigQuery.
20 Debido a los continuos cambios y mejoras en los productos, los gráficos que ves pueden ser diferentes.
21 Esta consulta será lenta porque estamos haciendo una coincidencia de expresión regular y haciéndolo 77.000 millones de veces.
22 Lo más probable es que las filas incluyan datos de varias universidades, como la Universidad Nacional de Singapur, la Universidad Nacional de Irlanda, el Instituto Tecnológico de Massachusetts, el Instituto Tecnológico de Georgia, etc.
23 Si has seguido el proceso ejecutando el archivo setup_data.sh en el repositorio de GitHub, el project_id será el ID único de tu proyecto, el instance_id será bqbook-instance, y el table_name será logs-table.
24 En el momento de escribir esto, esta capacidad sólo está disponible en la "antigua" interfaz de usuario en https://bigquery.cloud.google.com/ y no en la "nueva" interfaz de usuario que forma parte de la Consola en la Nube de GCP(https://console.cloud.google.com/bigquery).
25 Consulta https://cloud.google.com/bigquery/docs/locations para conocer las ubicaciones de los conjuntos de datos de BigQuery y https://cloud.google.com/storage/docs/bucket-locations para conocer las ubicaciones de Cloud Storage.
26 Para obtener una lista de las opciones de formato disponibles, consulta la documentación de BigQuery sobre el formato de las columnas de fecha y hora.
27 En el momento de escribir esto, sólo está disponible en la "IU clásica".
28 También de las máquinas virtuales y los servicios que se ejecutan en Amazon Web Services.
29 Consulta 04_load/dataflow.ipynb en el repositorio GitHub del libro.
30 En el capítulo 5 veremos cómo hacerlo mediante programación.
31 Un servicio de bus de mensajes: consulta https://cloud.google.com/pubsub/.
32 Véase https://cloud.google.com/bigquery/providers/. Mientras se escribía este libro, GCP anunció su intención de adquirir Alooma, un proveedor de servicios de migración a la nube; véase https://cloud.google.com/blog/topics/inside-google-cloud/google-announces-intent-to-acquire-alooma-to-simplify-cloud-migration.
Get Google BigQuery: La Guía Definitiva now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
