BigQuery para los profesionales del almacén de datos

Actualizado en septiembre de 2017

En este artículo, se explica cómo utilizar BigQuery como un almacén de datos. Primero se comparan los conceptos comunes del almacén de datos con los que existen en BigQuery y, a continuación, se describe cómo realizar las tareas estándares de almacenamiento de datos en BigQuery.

Comparación de los modelos de servicio

En la siguiente tabla, se comparan los conceptos estándares del almacén de datos con los de BigQuery:

Almacén de datos BigQuery
Almacén de datos El servicio de BigQuery reemplaza la configuración típica de hardware para un almacén de datos tradicional. Es decir, actúa como una página principal colectiva para todos los datos analíticas en una organización.
Data mart Los conjuntos de datos son colecciones de tablas que pueden dividirse en líneas comerciales o un dominio analítico determinado. Cada conjunto de datos está vinculado a un proyecto de Google Cloud Platform (GCP).
Data Lake Es posible que data lake contenga archivos en Cloud Storage o Google Drive, o datos transaccionales en Cloud Bigtable. BigQuery puede definir un esquema y emitir consultas directamente sobre datos externos como fuentes de datos federadas.
Tablas y vistas Las Tablas y vistas funcionan de la misma manera en BigQuery que en un almacén de datos tradicional.
Otorgamientos La Administración de identidades y accesos de Cloud (Cloud IAM) se utiliza para otorgar permisos a fin de realizar acciones específicas en BigQuery.

Conjuntos de datos

BigQuery organiza las tablas de datos en unidades denominadas conjuntos de datos. Estos conjuntos de datos están orientados al proyecto de GCP. Cuando haces referencia a una tabla desde la línea de comandos, en las consultas de SQL o en el código, la mencionas mediante la siguiente construcción:

project.dataset.table

Estos alcances múltiples (proyecto, conjunto de datos y tabla) pueden ayudarte a estructurar la información de manera lógica. Puedes utilizar varios conjuntos de datos para separar tablas que pertenecen a diferentes dominios analíticos, y puedes utilizar el alcance a nivel de proyecto para aislar conjuntos de datos de acuerdo con las necesidades de tu negocio.

A continuación, se incluye una descripción general estructural de BigQuery:

Descripción general estructural de BigQuery

Cómo realizar el aprovisionamiento y el dimensionamiento del sistema

No es necesario que aprovisiones recursos antes de utilizar BigQuery, a diferencia de muchos sistemas de RDBMS. BigQuery asigna recursos de almacenamiento y consulta de forma dinámica según los patrones de uso.

  • Los recursos de almacenamiento se asignan a medida que los consumes, y su asignación se anula a medida que quitas datos o eliminas tablas.
  • Los recursos de consulta se asignan de acuerdo con el tipo de consulta y la complejidad. Cada consulta utiliza una cierta cantidad de ranuras, que son unidades de procesamiento que comprenden una determinada cantidad de CPU y RAM.

No es necesario que hagas un compromiso de uso mínimo para utilizar BigQuery. El servicio asigna y cobra por los recursos según el uso real. Según la configuración predeterminada, todos los clientes de BigQuery tienen acceso a 2,000 ranuras para las operaciones de consulta. También puedes reservar un número fijo de ranuras para el proyecto. Si deseas obtener más información sobre qué enfoque utilizar, consulta la sección Costos.

Administración de almacenamiento

De forma interna, BigQuery almacena datos en un formato de columna patentado que se denomina Capacitor, el cual ofrece varios beneficios para las cargas de trabajo del almacén de datos. BigQuery utiliza un formato patentado porque puede evolucionar en paralelo con el motor de consulta, que aprovecha el gran conocimiento del diseño de datos para optimizar la ejecución de consultas. BigQuery utiliza patrones de acceso a consultas para determinar el número óptimo de fragmentos físicos y cómo se codifican.

Los datos se almacenan físicamente en el sistema de archivos distribuidos de Google, denominado Colossus, que garantiza la durabilidad mediante el uso de la codificación de borrado para almacenar fragmentos de datos redundantes en varios discos físicos. Además, los datos se replican en varios centros de datos.

También puedes ejecutar las consultas de BigQuery en los datos ubicados fuera del almacenamiento de BigQuery, como los datos almacenados en Cloud Storage, Google Drive o Cloud Bigtable, mediante el uso de fuentes de datos federadas. Sin embargo, estas fuentes no están optimizadas para las operaciones de BigQuery, por lo que podrían no funcionar tan bien como los datos almacenados en BigQuery.

Mantenimiento

BigQuery es un servicio completamente administrado, lo que significa que el equipo de ingeniería de BigQuery se encarga de las actualizaciones y el mantenimiento por ti. Las actualizaciones no deberían requerir tiempo de inactividad ni obstaculizar el rendimiento del sistema.

Muchos sistemas tradicionales requieren procesos de vacío de uso intensivo de recursos para ejecutarse a diversos intervalos a fin de volver a reproducir aleatoriamente y clasificar los bloques de datos, y recuperar espacio. BigQuery no tiene un equivalente del proceso de vacío, ya que el motor de almacenamiento administra y optimiza de manera constante la forma en que los datos se almacenan y replican. Además, debido a que BigQuery no utiliza índices en las tablas, no necesitas volver a compilar los índices.

Copia de seguridad y recuperación

BigQuery aborda la copia de seguridad y la recuperación ante desastres a nivel de servicio. Además, a través del mantenimiento de un historial completo de cambios de 7 días en comparación con las tablas, BigQuery te permite consultar una instantánea de un momento determinado de los datos. Puedes revertir fácilmente los cambios sin tener que solicitar una recuperación de las copias de seguridad. (Cuando una tabla se borra explícitamente, su historial se vacía después de 2 días. En el momento de redactar este artículo, la característica de instantánea solo se admite en el SQL heredado).

Cómo administrar flujos de trabajo

En esta sección, se analizan las tareas administrativas, por ejemplo, cómo organizar los conjuntos de datos, otorgar permisos y, además, incorporar trabajo en BigQuery. En la sección, también se analiza cómo administrar las cargas de trabajo simultáneas, supervisar el estado del almacén de datos y auditar el acceso de los usuarios.

Cómo organizar los conjuntos de datos

Puedes segmentar los conjuntos de datos en proyectos separados según la clase de datos o la unidad de negocios, o consolidarlos en proyectos comunes para mayor simplicidad.

Puedes invitar a un analista de datos a colaborar en un conjunto de datos existente en cualquier función limitada que definas. Cuando los analistas de datos inician sesión en la IU web de BigQuery, solo ven los conjuntos de datos que se han compartido con ellos en los proyectos. Las actividades que pueden realizar frente a los conjuntos de datos varían según las funciones en cada conjunto de datos.

Cómo otorgar permisos

En un sistema tradicional de RDBMS, otorgas permisos para ver o modificar las tablas; para ello, creas otorgamientos de SQL y los aplicas a un usuario determinado dentro del sistema de base de datos. Además, algunos sistemas de RDBMS te permiten otorgar permisos a los usuarios en un directorio externo, como LDAP. El modelo de BigQuery para administrar usuarios y permisos se asemeja al último modelo.

BigQuery proporciona funciones predefinidas para controlar el acceso a los recursos. También puedes crear funciones de Cloud IAM personalizadas que contengan el conjunto definido de permisos y, a continuación, asignar esas funciones a usuarios o grupos. Puedes asignar una función a una dirección de correo electrónico de Google o a un grupo de G Suite.

Un aspecto importante de la operación de un almacén de datos es permitir el acceso compartido pero controlado a los mismos datos para grupos diferentes de usuarios. Por ejemplo, los departamentos de finanzas, RR.HH. y marketing acceden a las mismas tablas, pero sus niveles de acceso difieren. Las herramientas de almacenamiento de datos tradicionales posibilitan esto; para ello, aplican la seguridad a nivel de fila. Puedes obtener los mismos resultados en BigQuery; para ello, define las vistas autorizadas y los permisos a nivel de fila.

Integración

Tradicionalmente, la integración de analistas de datos nuevos suponía un plazo de entrega considerable. Para permitir que los analistas ejecutaran consultas simples, tenías que mostrarles dónde residían las fuentes de datos, y configurar las conexiones y las herramientas ODBC y los derechos de acceso. Con el uso de GCP, puedes acelerar de manera significativa el tiempo de un analista hacia la productividad.

Para incorporar un analista en GCP, otorgas acceso a los proyectos pertinentes, los presentas a Google Cloud Platform Console y la IU web de BigQuery, y compartes algunas consultas para ayudarlo a familiarizarse con los datos:

  • GCP Console proporciona una vista centralizada de todos los activos en el entorno de nube. Es posible que los activos más relevantes para los analistas de datos sean los depósitos de Cloud Storage, en los cuales pueden colaborar en los archivos.
  • La IU web de BigQuery presenta la lista de conjuntos de datos a los que tiene acceso el analista. Los analistas pueden realizar tareas en la consola según la función que les otorgues, por ejemplo, ver metadatos; obtener una vista previa de los datos; ejecutar, guardar y compartir consultas.

Cómo administrar las cargas de trabajo y la simultaneidad

BigQuery limita la frecuencia máxima de solicitudes entrantes y aplica las cuotas correspondientes a cada proyecto. Las políticas específicas varían según la disponibilidad de los recursos, el perfil del usuario, el historial de uso del servicio y otros factores. Para obtener más detalles, consulta la Política de cuotas de BigQuery.

BigQuery ofrece dos tipos de prioridades de consulta: interactiva y por lotes. Según la configuración predeterminada, BigQuery ejecuta consultas interactivas, lo cual significa que la consulta se ejecuta tan pronto como sea posible. Las consultas interactivas cuentan para las cuotas de consulta. Las consultas por lotes se ponen en cola y se ejecutan tan pronto como los recursos inactivos están disponibles, generalmente, en unos minutos.

BigQuery no admite la priorización detallada de consultas interactivas o por lotes. Dada la velocidad y la escala a la que opera BigQuery, no se aplican muchos de los problemas tradicionales de cargas de trabajo. Si necesitas una priorización explícita de consultas, puedes separar las cargas de trabajo sensibles en un proyecto con una cantidad explícita de ranuras reservadas. Comunícate con tu representante de Google para que te ayude a convertirte en un cliente de tasa fija.

Cómo supervisar y auditar

Puedes supervisar BigQuery con Stackdriver, en donde se definen varios gráficos y alertas según las métricas de BigQuery. Por ejemplo, puedes supervisar la capacidad de procesamiento del sistema con la métrica Tiempo de consulta o visualizar las tendencias de la demanda de consultas según la métrica Ranuras asignadas. Cuando necesitas planificar por adelantado para una consulta exigente, puedes utilizar la métrica Ranuras disponibles. Para mantenerte proactivo en cuanto el estado del sistema, puedes crear alertas en función de los límites que defines. Stackdriver proporciona un portal de autoservicio basado en la Web. Puedes controlar el acceso al portal a través de las cuentas de Stackdriver.

BigQuery crea automáticamente registros de auditoría de las acciones del usuario. Puedes exportar los registros de auditoría a otro conjunto de datos en BigQuery en un lote o como una transmisión de datos, y utilizar tu herramienta de análisis preferida para visualizar los registros. Si deseas obtener más detalles, consulta Cómo analizar los registros de auditoría con BigQuery.

Cómo administrar los datos

En esta sección, se analizan las consideraciones de diseño de esquemas, la desnormalización, la forma en que funciona la partición y los métodos para cargar datos en BigQuery. La sección concluye con una revisión sobre el manejo de cambios en el almacén, a la vez que no existe tiempo de inactividad del análisis.

Cómo diseñar esquemas

Sigue estos lineamientos generales a fin de diseñar el esquema óptimo para BigQuery:

  • Desnormaliza una tabla de dimensiones que tenga más de 10 gigabytes, a menos que observes indicios sólidos de que los costos de manipulación de datos y de operación de UPDATE y DELETE superan los beneficios de las consultas óptimas.
  • Mantén normalizada una tabla de dimensiones inferior a 10 gigabytes, a menos que la tabla rara vez pase por las operaciones de UPDATE y DELETE.
  • Aprovecha al máximo los campos anidados y repetidos en las tablas desnormalizadas.

Desnormalización

El método convencional de desnormalización de datos implica escribir un hecho, junto con todas sus dimensiones, en una estructura de tabla plana. Por ejemplo, para las transacciones de ventas, deberías escribir cada hecho en un registro, junto con las dimensiones correspondientes, como información del cliente y del pedido.

Por el contrario, el método preferido para desnormalizar los datos aprovecha la compatibilidad nativa de BigQuery para las estructuras anidadas y repetidas en los datos de entrada JSON o Avro. La expresión de registros con el uso de estructuras anidadas y repetidas puede proporcionar una representación más natural de los datos subyacentes. En el caso del pedido de venta, la parte externa de una estructura JSON contiene la información del cliente y del pedido, y la parte interna de la estructura contiene los elementos de una sola línea individual del pedido, que se representan como elementos anidados y repetidos.

{
  "orderID": "ORDER",
  "custID": "EMAIL",
  "custName": "NAME",
  "timestamp": "TIME",
  "location": "LOCATION",
  "purchasedItems": [
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    },
    {
      "sku": "SKU",
      "description": "DESCRIPTION",
      "quantity": "QTY",
      "price": "PRICE"
    }
  ]
}

La expresión de registros con el uso de campos anidados y repetidos simplifica la carga de datos con el uso de archivos JSON o Avro. Una vez que hayas creado tal esquema, puedes realizar las operaciones SELECT, INSERT, UPDATE y DELETE en cualquier campo individual con una notación de puntos, por ejemplo, Order.Item.SKU. Si deseas obtener ejemplos, consulta la documentación de BigQuery.

Ventajas de la desnormalización

BigQuery es básicamente un motor analítico. Admite acciones DML, pero no está diseñado para que se lo utilice como un almacén de procesamiento de transacciones en línea (OLTP). El debate sobre Cómo cambiar datos proporciona lineamientos para manejar los cambios, a la vez que no existe tiempo de inactividad del análisis y se ofrece un rendimiento de procesamiento analítico en línea (OLAP) óptimo. Si bien las estructuras de datos normalizadas o parcialmente normalizadas, como un esquema en estrella o un copo de nieve, son adecuadas para las operaciones update/delete, no son óptimas para las cargas de trabajo OLAP. Cuando realizas operaciones OLAP en tablas normalizadas, varias tablas deben ser JOIN para realizar las agregaciones obligatorias. Las JOIN son posibles con BigQuery y, a veces, se recomiendan tablas pequeñas. Sin embargo, generalmente, no son tan eficaces como las estructuras desnormalizadas.

En el siguiente gráfico, se compara el rendimiento de las consultas mediante las JOIN con los filtros simples en relación con el tamaño de la tabla. El rendimiento de las consultas muestra una disminución mucho más pronunciada en la presencia de JOIN.

Tiempo de consulta con el uso de JOINS en comparación con los filtros

Desventajas de la desnormalización

Los esquemas desnormalizados no son óptimos para el almacenamiento, pero el bajo costo de almacenamiento de BigQuery aborda los problemas sobre la ineficiencia del almacenamiento. Puedes comparar los costos con las ganancias en la velocidad de consulta para ver por qué el almacenamiento no es un factor importante.

Uno de los desafíos cuando trabajas con un esquema desnormalizado es mantener la integridad de los datos. Según la frecuencia del cambio y su extensión, el mantenimiento de la integridad de los datos puede requerir mayor tiempo de máquina y, a veces, tiempo humano para realizar pruebas y verificaciones.

Cómo realizar la partición de las tablas

BigQuery admite la partición de tablas por fecha. Puedes habilitar la partición durante el proceso de creación de tablas. BigQuery crea particiones basadas en fechas nuevas de forma automática, sin necesidad de almacenamiento adicional. Además, puedes especificar un plazo de vencimiento para los datos en las particiones.

Los datos nuevos que se insertan en una tabla particionada se escriben en la partición sin procesar en el momento de la inserción. Para controlar explícitamente en qué partición se cargan los datos, el trabajo de carga puede especificar una partición de fecha en particular.

Cómo cargar datos

Antes de que los datos puedan cargarse en BigQuery para las cargas de trabajo analíticas, generalmente, se almacenan en un producto de Cloud Storage y en un formato que es nativo a su origen. Durante las primeras etapas de la migración a GCP, el patrón común es utilizar las herramientas existentes de extracción, transformación y carga (ETL) para transformar los datos en el esquema ideal para BigQuery. Una vez que se transforman los datos, se transfieren a Cloud Storage como archivos CSV, JSON o Avro, y desde allí se cargan en BigQuery con el uso de trabajos de carga o transmisión. Otra alternativa es transferir archivos a Cloud Storage en el esquema que es nativo al almacenamiento de datos local existente, que se carga en un conjunto de tablas de etapa de pruebas en BigQuery y, a continuación, se transforma en el esquema ideal para BigQuery con el uso de comandos de BigQuery SQL. Estos dos enfoques se visualizan a continuación:

Primer enfoque sobre la carga de datos

Segundo enfoque sobre la carga de datos

A medida que expandes el alcance en GCP, es probable que captures los datos de origen directamente en Cloud Bigtable, Cloud Datastore o Cloud Spanner, y utilices Cloud Dataflow para los datos de ETL en BigQuery en lotes o flujos.

Cómo capturar el código fuente de manera directa

Cómo usar trabajos de carga

En esta sección, se da por sentado que los datos están en Cloud Storage como un grupo de archivos en un formato de archivo compatible. Si deseas obtener más información sobre cada formato de datos, así como los requisitos y las características específicos que deben tenerse en cuenta cuando se elige un formato, consulta Formatos de datos de BigQuery.

Además de CSV, también puedes utilizar los archivos de datos con delimintadores distintos de comas mediante el uso de la marca --field_delimiter. Si deseas obtener más detalles, consulta marcas de carga bq.

BigQuery admite la carga de archivos comprimidos gzip. Sin embargo, la carga de archivos comprimidos no es tan rápida como la carga de archivos sin comprimir. Para situaciones urgentes o en las que la transferencia de archivos sin comprimir a Cloud Storage tiene restricciones de ancho de banda o tiempo, realiza una prueba de carga rápida para ver qué alternativa funciona mejor.

Debido a que los trabajos de carga son asíncronos, no es necesario mantener una conexión de cliente mientras se ejecuta el trabajo. Lo que es más importante, los trabajos de carga no afectan los otros recursos de BigQuery.

Un trabajo de carga crea una tabla de destino si no existe una.

BigQuery determina el esquema de datos de la siguiente manera:

Puedes especificar un esquema explícitamente; para ello, pasa el esquema como un argumento al trabajo de carga. Los trabajos de carga en curso pueden adjuntarse a la misma tabla con el uso del mismo procedimiento que la carga inicial, pero no requieren que se pase el esquema con cada trabajo.

Si los archivos CSV siempre contienen una fila de encabezado que debe ignorarse después de la carga inicial y la creación de la tabla, puedes utilizar la marca --skip_leading_rows para ignorar la fila. Si deseas obtener más detalles, consulta marcas de carga bq.

BigQuery establece los límites diarios sobre la cantidad y el tamaño de los trabajos de carga que puedes realizar por proyecto y por tabla. Además, BigQuery establece límites en los tamaños de los archivos y los registros de carga individuales. Si deseas obtener más detalles, consulta Política de cuotas.

Puedes iniciar trabajos de carga a través de la IU web de BigQuery. Para automatizar el proceso, puedes configurar Cloud Function para detectar un evento de Cloud Storage que esté asociado con la llegada de archivos nuevos en un depósito determinado y, luego, iniciar el trabajo de carga de BigQuery.

Cómo utilizar inserciones de transmisión

Para un enfoque alternativo y complementario, también puedes transmitir los datos directamente a BigQuery. Los datos transmitidos se ponen a disposición inmediatamente y se pueden consultar junto con los datos de la tabla existente en tiempo real.

Para situaciones que pueden beneficiarse de la información en tiempo real, como la detección de fraudes o la supervisión de las métricas del sistema, la transmisión puede ser un diferenciador significativo. Sin embargo, a diferencia de los trabajos de carga, que son gratuitos en BigQuery, existe un cargo para la transmisión de datos. Por lo tanto, es importante utilizar la transmisión en situaciones en las que los beneficios superen los costos.

Cuando transmites datos a las tablas de BigQuery, puedes enviar los registros directamente a BigQuery mediante el uso de la API de BigQuery. Si utilizas Logging, el servicio de registro de GCP, también puedes transmitir los registros del proyecto de GCP directamente a BigQuery; esto incluye los registros de solicitud de App Engine y la información de registro personalizada que se envía a Logging.

Cómo manejar los cambios

Muchos almacenes de datos operan según Acuerdos de Nivel de Servicio (ANS) estrictos, que exigen poco o ningún tiempo de inactividad. Si bien Google maneja el tiempo de actividad de BigQuery, puedes controlar la disponibilidad y la capacidad de respuesta de los conjuntos de datos con tu enfoque para reflejar el cambio en los datos.

Todas las modificaciones de tabla en BigQuery son compatibles con ACID. Esto se aplica a operaciones DML, las consultas con tablas de destino y los trabajos de carga. Una tabla que pasa por inserciones, actualizaciones y eliminaciones mientras se entregan las consultas de usuarios controla la simultaneidad con facilidad y las transiciones de un estado a otro de manera atómica. Por lo tanto, la modificación de una tabla no requiere tiempo de inactividad. Sin embargo, es posible que el proceso interno requiera una fase de prueba y validación antes de que los datos recién actualizados estén disponibles para su análisis. Además, dado que las operaciones DML compiten con la carga de trabajo analítica en las ranuras, es posible que prefieras aislarlas. Por estas razones, podrías causar tiempo de inactividad. En este artículo, se utiliza el término "tiempo de inactividad del análisis" para evitar confusiones con el tiempo de inactividad del servicio de BigQuery.

Puedes aplicar la mayoría de las técnicas antiguas y comprobadas para manejar el tiempo de inactividad del análisis. En esta sección, se amplían algunos de los desafíos y las soluciones conocidos.

Período variable

Un almacén de datos tradicional, a diferencia de un data lake, retiene datos solo por un período fijo, por ejemplo, los últimos 5 años. En cada ciclo de actualización, los datos nuevos se agregan al almacén y los datos más antiguos se eliminan, lo que mantiene la duración fija. Por lo general, este concepto se empleó para solucionar las limitaciones de las tecnologías más antiguas.

BigQuery esta diseñado para escalar y puede escalar a medida que crece el tamaño del almacén, por lo que no es necesario borrar los datos más antiguos. Cuando conservas el historial completo, puedes brindar más información sobre tu negocio. Si el costo de almacenamiento es una preocupación, puedes aprovechar los precios de almacenamiento a largo plazo de BigQuery; para ello, archiva los datos más antiguos y utilízalos para un análisis especial cuando surja la necesidad. Si aún tienes buenos motivos para eliminar los datos más antiguos, puedes utilizar la compatibilidad nativa de BigQuery para las tablas con particiones de fecha y el vencimiento de la partición. En otras palabras, BigQuery puede borrar automáticamente los datos más antiguos.

Cómo cambiar esquemas

Mientras se diseña y se desarrolla un almacén de datos, es normal modificar los esquemas de tablas mediante la incorporación, la actualización o la eliminación de columnas, o incluso la incorporación o la eliminación de tablas enteras. A menos que el cambio tenga la forma de una columna o tabla agregada, podría anular las consultas guardadas y los informes que hacen referencia a la tabla borrada, una columna cuyo nombre se ha cambiado, y así sucesivamente.

Una vez que el almacén de datos está en producción, tales cambios pasan por un control de cambios estricto. Es posible que decidas manejar cambios de esquema menores durante un tiempo de inactividad del análisis, pero, en general, los cambios de esquemas reflejados se programan como actualizaciones de versión. Puedes diseñar, desarrollar y probar la actualización en paralelo mientras la versión anterior del almacén de datos entrega las cargas de trabajo de análisis. Puedes seguir el mismo enfoque para aplicar los cambios de esquema a un almacén de datos de BigQuery.

Dimensiones que cambian lentamente

Un esquema de datos normalizado minimiza el impacto de las Dimensiones que cambian lentamente (SCD) mediante el aislamiento del cambio en las tablas de dimensiones. Por lo general, este esquema es conveniente frente a un esquema desnormalizado, en el que SCD puede provocar actualizaciones generalizadas en la tabla de hechos plana. Sin embargo, como se explica en la sección de diseño de esquemas, utiliza la normalización con cuidado para BigQuery.

Cuando se trata de SCD, no existe una solución que satisfaga todas las necesidades. Es importante comprender la naturaleza del cambio y aplicar la solución o la combinación de soluciones más adecuada al problema. En el resto de esta sección, se describen algunas soluciones y cómo aplicarlas a los tipos de SCD.

Técnica 1: Cambio de vista

Esta técnica se basa en dos vistas de los datos: "prinicipal" frente a "sombra". El truco consiste en ocultar la tabla real y exponer la vista "principal" a los usuarios. En los ciclos de actualización, se crea/actualiza la vista "sombra" y pasa por las pruebas de corrección de datos, mientras los usuarios trabajan en la vista "principal". En el momento del cambio, la vista "principal" se intercambia con la vista "sombra". La vista "principal" antigua y la vista "sombra" nueva podrían destruirse hasta el próximo ciclo de actualización o conservarse para algunos flujos de trabajo según las reglas y los procesos definidos por la organización.

Las dos vistas podrían basarse en una tabla común y diferenciarse por una columna, por ejemplo, "view_type", o basarse en tablas distintas. No se recomienda el primer método, ya que las operaciones DML en la vista "sombra" de la tabla podrían ralentizar las consultas de usuario en comparación con la vista "principal" sin ofrecer ningún beneficio real.

Si bien el cambio de vista no ofrece ningún tiempo de inactividad del análisis, tiene un costo mayor, porque durante el ciclo de actualización, existen dos copias de los datos. Lo que es aún más importante, si los ciclos de actualización se producen a una tasa mayor a 90 días, este enfoque podría impedir que la organización aproveche los precios de almacenamiento a largo plazo. El plazo de noventa días se basa en la política de precios en el momento en que se redactó este documento. Asegúrate de verificar la política más reciente.

A veces, segmentos de datos diferentes cambian a su propio ritmo. Por ejemplo, los datos de ventas en Norteamérica se actualizan diariamente, mientras que los datos en Asia-Pacífico se actualizan cada dos semanas. En tales situaciones, es mejor partir la tabla según el factor determinante del cambio, en este caso, el país. Luego, el cambio de vista se aplica a las particiones afectadas y no a todo el almacén de datos. En el momento de la redacción de este documento, solo puedes realizar particiones según un atributo de datos personalizado, como País; para ello, divide explícitamente los datos en varias tablas.

Técnica 2: Carga de particiones in situ

Cuando el cambio en los datos puede aislarse mediante una partición y se tolera un breve tiempo de inactividad del análisis, el cambio de vista podría ser excesivo. En su lugar, los datos de las particiones afectadas pueden habilitarse por etapas en otras tablas de BigQuery o exportarse a archivos en Cloud Storage, en los que pueden reemplazarse durante el tiempo de inactividad del análisis.

Para reemplazar datos en una partición de destino con datos de una consulta de otra tabla:

bq query --use_legacy_sql=false --replace \
         --destination_table 'flight_data.fact_flights_part$20140910' \
         'select * from `ods.load_flights_20140910`

Para reemplazar datos en una partición de destino con la carga desde Cloud Storage:

bq load  --replace \
         --source_format=NEWLINE_DELIMITED_JSON
         'flight_data.fact_flights_part$20140910' \
         gs://{bucket}/load_flights_20140910.json
Técnica 3: Actualizar las opciones de máscara de datos

Una dimensión pequeña y con cambios frecuentes es la opción principal para la normalización. En esta técnica, las actualizaciones para tal dimensión se habilitan por etapas en una tabla o vista aislada que está unida incondicionalmente al resto de los datos.

SELECT f.order_id as order_id, f.customer_id as customer_id,
    IFNULL(u.customer_first_name, f.customer_first_name) as customer_first_name,
    IFNULL(u.customer_last_name, f.customer_last_name) as customer_last_name
FROM fact_table f
LEFT OUTER JOIN pending_customer_updates u
ON f.customer_id = u.customer_id
SCD Tipo 1: Reemplazar el valor de un atributo

La SCD Tipo 1 reemplaza el valor de un atributo por datos nuevos sin mantener el historial. Por ejemplo, si el producto "crema hidratante estupenda" formaba parte de la categoría "salud y belleza" y ahora se clasifica como "cosméticos", el cambio se verá de la siguiente manera:

Antes:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC crema hidratante estupenda: 100 oz salud y belleza

Después:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY
123 ABC crema hidratante estupenda: 100 oz salud y belleza
cosméticos

Si el atributo está en una tabla de dimensión normalizada, el cambio es muy aislado. Simplemente, actualiza la fila afectada en la tabla de dimensiones. Para las tablas de dimensiones más pequeñas con actualizaciones frecuentes de tipo 1, utiliza la Técnica 3: Actualizar las opciones de máscara de datos.

Si el atributo está incorporado en la tabla de hechos de manera desnormalizada, el cambio es bastante general. Tendrás que actualizar todas las filas de hechos en que se repite el atributo. En este caso, utiliza la Técnica 2: Carga de particiones in situ o la Técnica 1: Cambio de vista.

SCD Tipo 2: Cambiar el valor de un atributo y mantener el historial

Este método rastrea datos históricos ilimitados mediante la creación de varios registros para una clave natural con claves subrogadas independientes. Por ejemplo, el mismo cambio que se ilustra en SCD Tipo 1 se manejaría de la siguiente manera:

Antes:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC crema hidratante estupenda: 100 oz salud y belleza 31 de enero de 2009 NULL

Después:

PRD_SK PRD_ID PRD_DESC PRD_CATEGORY START_DATE END_DATE
123 ABC crema hidratante estupenda: 100 oz salud y belleza 31 de enero de 2009 18 de julio de 2017
124 ABC crema hidratante estupenda: 100 oz cosméticos 19 de julio de 2017 NULL

Si el atributo está en una tabla de dimensiones normalizada, el cambio está aislado. Simplemente, actualiza la fila anterior y agrega una fila nueva en la tabla de dimensiones. Para las tablas de dimensiones más pequeñas con actualizaciones frecuentes de tipo 1, utiliza la Técnica 3: Actualizar las opciones de máscara de datos.

Si el atributo está incorporado en la tabla de hechos de manera desnormalizada, la situación puede ser más favorable, siempre y cuando no se mantengan fechas de inicio y finalización explícitas para el valor y, en cambio, se confíe en las fechas de transacción. Debido a que el valor anterior sigue siendo verdadero para la fecha y la hora en que ocurrieron las transacciones anteriores, no necesitas cambiar las filas de la tabla de hechos anteriores. La tabla de hechos se verá de la siguiente manera:

TRANSACTION_DATE PRD_SK PRD_ID PRD_DESC PRD_CATEGORY UNIDADES IMPORTE
18 de julio de 2017 123 ABC crema hidratante estupenda: 100 oz salud y belleza 2 25.16
19 de julio de 2017 124 ABC crema hidratante estupenda: 100 oz cosméticos 1 13.50

Cómo consultar datos

BigQuery admite consultas de SQL estándares y es compatible con ANSI SQL 2011. La Referencia de SQL de BigQuery proporciona una descripción integral de todas las funciones, los operadores y las capacidades de regex que se admiten.

Debido a que BigQuery admite campos anidados y repetidos como parte del modelo de datos, la compatibilidad de SQL se amplió para admitir específicamente estos tipos de campos. Por ejemplo, mediante el uso del conjunto de datos públicos de GitHub, podrías emitir el comando UNNEST, que te permite iterar en un campo repetido:

SELECT
  name, count(1) as num_repos
FROM
  `bigquery-public-data.github_repos.languages`, UNNEST(language)
GROUP BY name
ORDER BY num_repos
DESC limit 10

Consultas interactivas

La IU web de BigQuery permite realizar consultas interactivas de los conjuntos de datos y proporciona una vista consolidada de los conjuntos de datos en todos los proyectos a los que tienes acceso. La consola también proporciona varias características útiles, como guardar y compartir consultas ad hoc, ajustar y editar consultas históricas, explorar tablas y esquemas, y recopilar metadatos de tablas. Consulta la IU web de BigQuery para obtener más información.

captura de pantalla de la iu web de bigquery

Consultas automatizadas

Es una práctica común automatizar la ejecución de consultas según una programación/un evento y almacenar en caché los resultados para su posterior consumo.

Si estás utilizando Airflow para organizar otras actividades automatizadas y ya estás familiarizado con la herramienta, utiliza la API de Apache Airflow para BigQuery a tal fin. Esta entrada de blog te guía a través del proceso de instalación de Airflow y creación de un flujo de trabajo frente a BigQuery.

Para lograr organizaciones más simples, puedes confiar en los trabajos cron. En esta entrada de blog, se muestra cómo encapsular una consulta como una aplicación de App Engine y ejecutarla como un trabajo cron programado.

Optimización de consultas

Cada vez que BigQuery ejecuta una consulta, ejecuta un análisis de columna completa. BigQuery no utiliza ni admite índices. Debido a que el rendimiento y los costos de las consultas de BigQuery se basan en la cantidad de datos analizados durante una consulta, puedes diseñar tus consultas para que hagan referencia solo a las columnas que son relevantes para la consulta. Cuando se utilizan tablas con particiones de fecha, asegúrate de que solo se analicen las particiones relevantes. Puedes lograr esto mediante el uso de filtros basados en PARTITIONTIME o PARTITIONDATE.

Para comprender las características de rendimiento después de que se ejecuta una consulta, revisa la explicación del plan de consultas detallada. La explicación desglosa las etapas por las que pasó la consulta, el número de filas de entrada/salida en cada etapa, y el perfil de tiempo dentro de cada etapa. El uso de los resultados de la explicación puede ayudarte a comprender y optimizar las consultas.

captura de pantalla con resultados de bigquery

Fuentes externas

Puedes ejecutar consultas sobre los datos que existen fuera de BigQuery con las fuentes de datos federadas, pero este enfoque tiene implicaciones de rendimiento. Utiliza fuentes de datos federadas solo si los datos deben mantenerse externamente. También puedes utilizar la federación de consultas para realizar ETL desde una fuente externa a BigQuery. Este enfoque te permite definir ETL mediante el uso de una sintaxis SQL familiar.

Funciones definidas por el usuario

BigQuery también admite funciones definidas por el usuario (UDF) para las consultas que superan la complejidad de SQL. Las UDF te permiten ampliar las funciones integradas de SQL; toman una lista de valores, que pueden ser arreglos o estructuras, y muestran un solo valor, que también puede ser un arreglo o una estructura. Las UDF se escriben en JavaScript y pueden incluir recursos externos, como bibliotecas de encriptación o de otro tipo.

Uso compartido de consultas

BigQuery permite a los colaboradores guardar y compartir consultas entre los miembros del equipo. Esta característica puede ser especialmente útil en los ejercicios de exploración de datos o como un medio para acelerar la velocidad en un conjunto de datos nuevo o un patrón de consulta nuevo. Si deseas obtener más información, consulta Cómo guardar y compartir consultas.

Analizar datos

En esta sección, se presentan varias formas de conectarse a BigQuery y analizar los datos. Para aprovechar al máximo BigQuery como motor analítico, deberías almacenar los datos en el almacenamiento de BigQuery. Sin embargo, el caso práctico específico podría beneficiarse del análisis de fuentes externas, ya sea por sí mismas o mediante la unión a JOIN con los datos en el almacenamiento de BigQuery.

Herramientas listas para usar

Data Studio, disponible en la versión Beta en el momento en que se redactó este documento, así como muchas herramientas de socios que están integradas a BigQuery, se pueden utilizar para extraer estadísticas de BigQuery y compilar visualizaciones de datos interactivas y sofisticadas.

Si te encuentras en una situación en la que tienes que elegir una herramienta, puedes encontrar una comparación completa de proveedores en el Informe del Cuadrante Mágico de Gartner y el Informe de puntuación G2 de G2 Crowd. El informe de Gartner se puede obtener de muchos de los sitios de nuestros socios, como Tableau.

logotipos de socios

Desarrollo personalizado

Para compilar plataformas y aplicaciones personalizadas basadas en BigQuery, puedes utilizar bibliotecas cliente, que están disponibles para los lenguajes de programación más comunes, o puedes utilizar la API de REST de BigQuery directamente.

Si deseas obtener un ejemplo concreto, consulta este instructivo, que utiliza las bibliotecas de Python para conectarse a BigQuery y generar paneles interactivos y personalizados.

Conectores de terceros

Para conectarte a BigQuery desde una aplicación que no está integrada de forma nativa a BigQuery al nivel de la API, puedes utilizar los controladores JDBC y ODBC de BigQuery. Los conectores proporcionan un puente para interactuar con BigQuery en aplicaciones heredadas o aplicaciones que no pueden modificarse fácilmente, como Microsoft Excel. Aunque ODBC y JDBC admiten la interacción con BigQuery mediante SQL, los controladores no son tan expresivos como los que tratan directamente con la API.

Costos

La mayoría de los almacenes de datos atienden varias entidades comerciales dentro de la organización. Un desafío común es analizar el costo de operación por entidad comercial. Para obtener orientación sobre cómo dividir tu factura y atribuir el costo al consumo, consulta Cómo visualizar la facturación de GCP mediante el uso de BigQuery y Data Studio.

Existen tres dimensiones de costos principales para BigQuery: costos de carga, almacenamiento y consulta. En esta sección, se analiza cada dimensión en detalle.

Almacenar datos

Los precios de almacenamiento se prorratean por MB/s.

Si una tabla no se edita por 90 días consecutivos, se clasifica como almacenamiento a largo plazo, y el precio de almacenamiento para esa tabla disminuye automáticamente un 50%, a $0.01 por GB, por mes. No disminuye el rendimiento, la durabilidad, la disponibilidad ni otras funciones cuando una tabla se considera como almacenamiento a largo plazo. Cuando se modifican los datos de una tabla, BigQuery restablece el temporizador en la tabla, y cualquier dato de la tabla muestra el precio de almacenamiento normal. Las acciones que no manipulan directamente los datos, como realizar consultas y crear vistas, no restablecen el temporizador.

Si deseas obtener más detalles, consulta los precios de almacenamiento de BigQuery.

Cargar datos

Puedes cargar datos en BigQuery con un trabajo de carga convencional sin cargo. Una vez que se cargan los datos, pagas por el almacenamiento como se mencionó anteriormente.

Las inserciones de transmisión se cobran según la cantidad de datos que se transmiten. Si deseas obtener más información, consulta los costos de las inserciones de transmisión que se enumeran en los precios de almacenamiento de BigQuery.

Consultar datos

Para las consultas, BigQuery ofrece dos modelos de precios: según la demanda o tasa fija.

Precios según demanda

En el modelo según la demanda, BigQuery cobra por la cantidad de datos a los que se accede durante la ejecución de la consulta. Debido a que BigQuery utiliza un formato de almacenamiento en columnas, solo se accede a las columnas relevantes para tu consulta. Si solo ejecutas informes de manera semanal o mensual, y has realizado consultas en menos de 1 TB de tus datos, es posible que el costo de las consultas en tu factura sea muy bajo. Si deseas obtener más detalles sobre cómo se cobran las consultas, observa los precios de consulta de BigQuery.

Para ayudar a determinar cuántos datos analizará de antemano una consulta, puedes utilizar el validador de consultas en la IU web. En el caso del desarrollo personalizado, puedes establecer la marca dryRun en la solicitud a la API y hacer que BigQuery no ejecute el trabajo. En su lugar, se muestran estadísticas sobre el trabajo, como cuántos bytes se procesarán. Consulta la API de consulta para obtener más información.

API de consulta

Precios de tasa fija

Los clientes que prefieren gastos mensuales más uniformes pueden optar por habilitar los precios de tasa fija. Si deseas obtener más información, consulta Precios de tasa fija de BigQuery.

¿Qué sigue?

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…