BigQuery para los profesionales de almacenes de datos

Actualizado en septiembre de 2017

En este artículo, se explica cómo usar 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.
Data lake Tu data lake puede contener archivos de Cloud Storage o Google Drive, o bien datos transaccionales de Bigtable. BigQuery puede definir un esquema y emitir consultas de forma directa 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 (IAM) se usa 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 tienen los permisos de tu proyecto de Google Cloud. Puedes hacer referencia a una tabla desde la línea de comandos, en las consultas de SQL o en el código 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

Dimensionamiento del sistema y aprovisionamiento

No es necesario el aprovisionamiento de recursos antes de usar BigQuery, a diferencia de lo que sucede con 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 usa una cierta cantidad de ranuras, que son unidades de procesamiento que comprenden una cantidad determinada de CPU y RAM.

No se requiere un compromiso de uso mínimo para usar 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 tu proyecto. Si deseas obtener más información sobre qué enfoque usar, 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 usa patrones de acceso a consultas para determinar la cantidad óptima de fragmentos físicos y cómo se codifican.

Los datos se almacenan de forma física 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 consultas de BigQuery en datos fuera del almacenamiento de BigQuery, como los datos almacenados en Cloud Storage, Google Drive o Bigtable, mediante 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, dado que BigQuery no usa índices en las tablas, no es necesario 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 del servicio. Además, como conserva un historial completo de los cambios de tus tablas durante 7 días, BigQuery te permite consultar una instantánea de los datos de un momento determinado mediante decoradores de tablas o SYSTEM_TIME AS OF en la cláusula FROM. Puedes revertir los cambios con facilidad sin tener que solicitar una recuperación de las copias de seguridad. Cuando se borra una tabla de forma explícita, su historial se vacía después de 7 días.

Administra 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 en los conjuntos de datos varían según las funciones que tengan asignadas en cada conjunto de datos.

Cómo otorgar permisos

En un sistema tradicional de RDBMS, otorgas permisos que habilitan la visualización o la modificación de 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 RDBMS te permiten otorgar permisos a los usuarios de un directorio externo, como LDAP. El modelo de BigQuery para administrar usuarios y permisos se parece al último modelo.

BigQuery proporciona funciones predefinidas para controlar el acceso a los recursos. También puedes crear funciones de 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 en el uso 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, las herramientas ODBC y los derechos de acceso. Mediante Google Cloud, puedes aumentar en gran medida la productividad de un analista.

Para incorporar a un analista en Google Cloud, debes otorgarle acceso a los proyectos relevantes, presentarle la IU web de Google Cloud Console y BigQuery, y compartirle algunas consultas para ayudarlo a familiarizarse con los datos:

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

Administra 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. BigQuery ejecuta consultas interactivas de forma predeterminada, lo que significa que la consulta se ejecuta lo antes posible. Las consultas interactivas cuentan para las cuotas de consulta. Las consultas por lotes se ponen en cola y se ejecutan en cuanto los recursos inactivos están disponibles, generalmente, en unos minutos.

BigQuery no admite la priorización detallada de consultas interactivas o por lotes. Dadas la velocidad y la escala de las operaciones de 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.

Supervisa y audita

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

BigQuery crea registros de auditoría de las acciones del usuario de forma automática. 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.

Administra 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 cómo manejar los cambios en el almacén sin 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 claros 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 mediante archivos JSON o Avro. Después de crear ese esquema, puedes realizar operaciones SELECT, INSERT, UPDATE y DELETE en cualquier campo individual con una notación de puntos, por ejemplo, Order.Item.SKU. Para ver ejemplos, consulta la Documentación de BigQuery.

Ventajas de la desnormalización

BigQuery es, en esencia, un motor analítico. Admite acciones de DML, pero no debe usarse como un almacén de procesamiento de transacciones en línea (OLTP). El debate sobre los cambios de 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 para tablas pequeñas. Sin embargo, generalmente, no son tan eficaces como las estructuras desnormalizadas.

En el siguiente grafo, 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.

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

Carga 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 migración a Google Cloud, el patrón común es usar herramientas existentes de extracción, transformación y carga (ETL) que permiten transformar los datos en el esquema ideal para BigQuery. Una vez que los datos se transforman, se transfieren a Cloud Storage como archivos CSV, JSON o Avro y, desde allí, se cargan en BigQuery mediante los trabajos de carga o la 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 SQL de BigQuery. 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 aumentas el uso de Google Cloud, es probable que captures los datos de origen de forma directa en Bigtable, Datastore o Cloud Spanner y que uses Dataflow para aplicar operaciones ETL a los datos de BigQuery, ya sea por lotes o por transmisiones.

Captura el código fuente directamente

Usa 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 las funciones y los requisitos específicos que debes tener en cuenta cuando eliges un formato, consulta Formatos de datos de BigQuery.

Además de archivos CSV, también puedes usar archivos de datos con delimitadores que no sean comas mediante la marca --field_delimiter. Si deseas obtener más detalles, consulta las marcas de carga de bq.

BigQuery admite la carga de archivos comprimidos gzip. Sin embargo, cargar archivos comprimidos no es tan rápido como cargar 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 está ejecutando el trabajo. Sobre todo, los trabajos de carga no afectan los otros recursos de BigQuery.

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

BigQuery determina el esquema de datos de la siguiente manera:

Puedes especificar un esquema de forma explícita, para lo que debes pasar 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 tus archivos CSV siempre contienen una fila de encabezado que deberá ignorarse después de la carga inicial y de la creación de la tabla, puedes usar la marca --skip_leading_rows para ignorar la fila. Si deseas obtener más detalles, consulta las marcas de carga de 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. A fin de automatizar el proceso, puedes configurar Cloud Functions para que detecte un evento de Cloud Storage asociado con la llegada de archivos nuevos en un depósito determinado y, luego, iniciar el trabajo de carga de BigQuery.

Usa las 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 usar la transmisión en situaciones en las que los beneficios superan los costos.

Cuando transmites datos a las tablas de BigQuery, envías tus registros directamente a BigQuery mediante la API de BigQuery. Si usas Cloud Logging, también puedes transmitir los registros de tu proyecto de Google Cloud directamente a BigQuery, incluidos los registros de solicitud de App Engine y la información de registro personalizada enviada a Cloud Logging.

Controla 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 la tabla en BigQuery cumplen con los criterios 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, modificar 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 está diseñado para adaptarse y puede escalarse horizontalmente a medida que crece el tamaño del almacén, por lo que no es necesario borrar los datos más antiguos. Si 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 en un análisis especial cuando surja la necesidad. Si aún deseas borrar los datos más antiguos, puedes usar la compatibilidad nativa de BigQuery para las tablas particionadas 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 lentamente cambiantes

Un esquema de datos normalizado minimiza el impacto de las dimensiones lentamente cambiantes (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 para el 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: "principal" 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 o 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 cambia por “sombra”. La antigua vista “principal” (que ahora es la “sombra”) podría detenerse hasta el siguiente ciclo de actualización o mantenerse 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, usa 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, usa la Técnica 2: Carga de particiones locales o la Técnica 1: Cambio de vista.

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

Este método realiza un seguimiento de los 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

Consulta datos

BigQuery admite consultas de SQL estándar 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 funcionalidades 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 estos tipos de campos en especial. 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 o un evento y almacenar en caché los resultados para su posterior consumo.

Si usas Airflow para organizar otras actividades automatizadas y ya estás familiarizado con la herramienta, usa la API de Apache Airflow para BigQuery a tal fin. En esta entrada de blog, se te guiará a través del proceso de instalación de Airflow y la creación de un flujo de trabajo en BigQuery.

Para lograr organizaciones más simples, puedes aprovechar 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, lleva a cabo 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 de modo que hagan referencia solo a las columnas que son relevantes para la consulta. Cuando se usan tablas particionadas por fecha, asegúrate de que solo se analicen las particiones relevantes. Puedes lograr esto mediante filtros de partición basados en PARTITIONTIME o PARTITIONDATE.

Para comprender las características del rendimiento después de que se ejecuta una consulta, revisa la explicación detallada del plan de consultas. 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 afecta el 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 consultas que superan la complejidad de SQL. Las UDF te permiten extender las funciones de SQL integradas; toman una lista de valores, que pueden ser arreglos o estructuras, y muestran un valor único, 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. A fin de obtener más información, consulta Cómo guardar y compartir consultas.

Cómo 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

Google Data Studio, disponible en la versión Beta en el momento en que se redactó este documento, así como muchas herramientas de socios que ya se integraron a BigQuery, se puede usar para extraer estadísticas de BigQuery y compilar visualizaciones de datos interactivos y sofisticados.

Si te encuentras en una situación en la que tienes que elegir una herramienta, puedes buscar una comparación completa de proveedores en Gartner's magic quadrant report (Informe del Cuadrante Mágico de Gartner) y el G2 score report (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 usar bibliotecas cliente, que están disponibles para los lenguajes de programación más comunes, o puedes usar la API de REST de BigQuery directamente.

Si deseas obtener un ejemplo concreto, consulta este instructivo, que usa 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 se integra de forma nativa en BigQuery a nivel de la API, puedes usar los controladores JDBC y ODBC de BigQuery. Los controladores proporcionan un puente para interactuar con BigQuery en aplicaciones heredadas o aplicaciones que no pueden modificarse con facilidad, 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 la factura y atribuir el costo al consumo, consulta Visualiza la Facturación de Google Cloud mediante 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 durante 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 la consulta y la creación de vistas, no restablecen el temporizador.

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

Cómo 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ó con anterioridad.

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.

Cómo 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 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, revisa Precios de consulta de BigQuery.

Para ayudar a determinar de antemano la cantidad de datos que una búsqueda determinada analizará, puedes usar el validador de consultas en la IU web. En el caso del desarrollo personalizado, puedes configurar 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. Accede a 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.

Próximos pasos