Puntos de control
Create a new dataset to store the tables
/ 10
Create a table
/ 10
Working as intended
/ 20
Create a table
/ 20
Create view
/ 10
Create a new view to returns recent 10 transactions
/ 20
Run a query with session_user in views for limiting data access
/ 10
Cómo crear tablas permanentes y vistas con acceso controlado en BigQuery
GSP410
Descripción general
BigQuery es la base de datos analítica de bajo costo, no-ops y completamente administrada de Google. Con BigQuery, puedes consultar muchos terabytes de datos sin tener que administrar infraestructuras y sin necesitar un administrador de base de datos. BigQuery usa SQL y puede aprovechar el modelo de pago por uso. BigQuery te permite enfocarte en el análisis de datos para encontrar estadísticas valiosas.
Utilizarás un conjunto de datos de comercio electrónico que tiene millones de registros de Google Analytics para Google Merchandise Store cargados en BigQuery. Tienes una copia de ese conjunto de datos para este lab y explorarás los campos y las filas disponibles para obtener estadísticas.
En este lab, aprenderás a crear nuevas tablas de informes permanentes y revisiones lógicas a partir de un conjunto de datos existente de comercio electrónico.
Configuración y requisitos
Antes de hacer clic en el botón Comenzar lab
Lee estas instrucciones. Los labs son cronometrados y no se pueden pausar. El cronómetro, que comienza a funcionar cuando haces clic en Comenzar lab, indica por cuánto tiempo tendrás a tu disposición los recursos de Google Cloud.
Este lab práctico te permitirá realizar las actividades correspondientes en un entorno de nube real, no en uno de simulación o demostración. Para ello, se te proporcionan credenciales temporales nuevas que utilizarás para acceder a Google Cloud durante todo el lab.
Para completar este lab, necesitarás lo siguiente:
- Acceso a un navegador de Internet estándar (se recomienda el navegador Chrome)
- Tiempo para completar el lab: Recuerda que, una vez que comienzas un lab, no puedes pausarlo.
Cómo iniciar su lab y acceder a la consola de Google Cloud
-
Haga clic en el botón Comenzar lab. Si debe pagar por el lab, se abrirá una ventana emergente para que seleccione su forma de pago. A la izquierda, se encuentra el panel Detalles del lab que tiene estos elementos:
- El botón Abrir la consola de Google
- Tiempo restante
- Las credenciales temporales que debe usar para el lab
- Otra información para completar el lab, si es necesaria
-
Haga clic en Abrir la consola de Google. El lab inicia recursos y abre otra pestaña en la que se muestra la página de acceso.
Sugerencia: Ordene las pestañas en ventanas separadas, una junto a la otra.
Nota: Si ve el diálogo Elegir una cuenta, haga clic en Usar otra cuenta. -
Si es necesario, copie el nombre de usuario del panel Detalles del lab y péguelo en el cuadro de diálogo Acceder. Haga clic en Siguiente.
-
Copie la contraseña del panel Detalles del lab y péguela en el cuadro de diálogo de bienvenida. Haga clic en Siguiente.
Importante: Debe usar las credenciales del panel de la izquierda. No use sus credenciales de Google Cloud Skills Boost. Nota: Usar su propia Cuenta de Google podría generar cargos adicionales. -
Haga clic para avanzar por las páginas siguientes:
- Acepte los términos y condiciones.
- No agregue opciones de recuperación o autenticación de dos factores (esta es una cuenta temporal).
- No se registre para obtener pruebas gratuitas.
Después de un momento, se abrirá la consola de Cloud en esta pestaña.
Cómo abrir la consola de BigQuery
- En la consola de Google Cloud, seleccione elmenú de navegación > BigQuery.
Se abrirá el cuadro de mensaje Te damos la bienvenida a BigQuery en la consola de Cloud. Este cuadro de mensaje contiene un vínculo a la guía de inicio rápido y las notas de la versión.
- Haga clic en Listo.
Se abrirá la consola de BigQuery.
Tarea 1. Crea un nuevo conjunto de datos para almacenar las tablas
-
En BigQuery, haz clic en el ícono Ver acciones junto a tu ID del proyecto y selecciona Crear conjunto de datos.
-
Configura el ID de conjunto de datos en ecommerce y deja las demás opciones con sus valores predeterminados (Ubicación de los datos, Vencimiento predeterminado de la tabla).
-
Haz clic en CREAR CONJUNTO DE DATOS.
Haz clic en Revisar mi progreso para verificar el objetivo.
Tarea 2. Soluciona problemas en instrucciones CREATE TABLE
Tu equipo de analistas de datos te proporcionó las siguientes instrucciones de consulta diseñadas para crear una tabla permanente en tu nuevo conjunto de datos de comercio electrónico. Lamentablemente, no funcionan correctamente.
Diagnostica por qué cada consulta es incorrecta y ofrece una solución.
Reglas para crear tablas con SQL en BigQuery
Lee estas reglas para crear tablas y úsalas como guía cuando corrijas las consultas incorrectas:
- La lista de columnas especificada o las columnas inferidas de una cláusula query_statement (o ambas) deben estar presentes.
- Cuando están presentes la lista de columnas y la cláusula as query_statement, BigQuery ignora los nombres en la cláusula as query_statement y hace coincidir las columnas con la lista de columnas por posición.
- Cuando la cláusula as query_statement está presente, pero no la lista de columnas, BigQuery determina los nombres y tipos de columnas a partir de la cláusula as query_statement.
- Los nombres de columna deben especificarse mediante la lista de columnas o la cláusula as query_statement.
- No se permiten nombres de columna duplicados.
Consulta 1: Columnas, columnas, columnas
- Agrega esta consulta al editor de consultas de BigQuery y, luego, ejecútala, diagnostica el error y responde las preguntas que siguen:
Error: CREATE TABLE has columns with duplicate name fullVisitorId at [7:2]
¿Cuál de las reglas para crear tablas se infringe en la consulta anterior?
Consulta 2: Revisión de columnas
- Agrega esta consulta al editor de consultas de BigQuery y, luego, ejecútala, diagnostica el error y responde las preguntas que siguen:
Error: The number of columns in the column definition list does not match the number of columns produced by the query at [5:1]
¿Cuál de las reglas para crear tablas se infringe en la consulta anterior?
fullVisitorId
y channelGrouping
, pero en la instrucción de la consulta se especificaron todas las columnas devueltas (\*).Consulta 3: ¡Es válida! ¿O no?
- Agrega esta consulta al editor de consultas de BigQuery y, luego, ejecútala, diagnostica el error y responde las preguntas que siguen:
Valid: This query will process 1.1 GiB when run.
Recuerde la regla n.° 2: Cuando están presentes la lista de columnas y la cláusula as
query_statement, BigQuery ignora los nombres en la cláusula as
query_statement y hace coincidir las columnas con la lista de columnas por posición.
Haz clic en Revisar mi progreso para verificar el objetivo.
Consulta 4: El guardián
- Ejecuta la siguiente consulta en el Editor de BigQuery y, luego, diagnostica el error y responde las preguntas que siguen.
Valid: This query will process 907.52 MiB when run.
Corrige la consulta modificada y vuelve a ejecutarla para comprobar que se ejecuta correctamente.
Consulta 5: Funciona según lo previsto
- Ejecuta esta consulta en el Editor de BigQuery y responde las preguntas que siguen:
- Explora el panel de tu conjunto de datos de comercio electrónico para confirmar que exista
all_sessions_raw_(1)
.
¿Por qué no se muestra el nombre completo de la tabla?
Respuesta: El sufijo de la tabla 20170801 se particiona automáticamente por día. Si creáramos más tablas para otros días, all_sessions_raw_(N)
aumentaría por N días distintos de datos. En otro lab, se exploran distintas maneras de particionar tus tablas de datos.
Haz clic en Revisar mi progreso para verificar el objetivo.
Consulta 6: Tu turno para practicar
Objetivo: En el Editor de consultas, crea una nueva tabla permanente que almacene todas las transacciones con ingresos del 1 de agosto de 2017.
Usa las reglas a continuación como guía:
- Crea una nueva tabla en tu conjunto de datos de comercio electrónico y nómbrala revenue_transactions_20170801. Reemplaza la tabla si ya existe.
- Extrae tus datos sin procesar de la tabla data-to-insights.ecommerce.all_sessions_raw.
- Divide el campo de ingresos entre 1,000,000 y almacénalo como FLOAT64 en lugar de un NÚMERO ENTERO.
- En tu tabla final, incluye únicamente transacciones con ingresos (sugerencia: usa una cláusula WHERE).
- Incluye solo transacciones con fecha 20170801.
- Incluye estos campos:
- fullVisitorId como un campo de cadena OBLIGATORIO
- visitId como un campo de cadena OBLIGATORIO (sugerencia: deberás escribir convert)
- channelGrouping como un campo de cadena OBLIGATORIO
- totalTransactionRevenue como un campo de FLOAT64
- Agrega descripciones breves para los cuatro campos anteriores haciendo referencia al esquema.
- Asegúrate de anular los registros duplicados que tienen el mismo
fullVisitorId
yvisitId
(sugerencia: usa DISTINCT).
- Escribe la respuesta a la prompt anterior en BigQuery y compárala con las siguientes respuestas.
Respuesta posible:
-
Después de ejecutar correctamente la consulta, confirma en tu conjunto de datos de comercio electrónico que exista la nueva tabla revenue_transactions_20170801 y selecciónala.
-
Verifica el esquema. Para eso, compáralo con el ejemplo a continuación. Ten en cuenta los tipos de campos, los datos obligatorios y la descripción opcional:
Cómo manejar actualizaciones de datos de origen ascendentes
¿Qué estrategias se pueden usar para solucionar los datos inactivos?
Hay dos formas de solucionar los datos inactivos en las tablas de informes:
- Volver a ejecutar consultas que se insertan en registros nuevos para actualizar periódicamente las tablas permanentes. Esto se puede realizar con consultas programadas de BigQuery o con un flujo de trabajo de Cloud Dataprep/Cloud Dataflow.
- Usar vistas lógicas para volver a ejecutar una consulta almacenada cada vez que se seleccione la vista.
En el resto de este lab, te enfocarás en cómo crear vistas lógicas.
Haz clic en Revisar mi progreso para verificar el objetivo.
Tarea 3. Crea vistas
Las vistas son consultas guardadas que se ejecutan cada vez que se las llama. En BigQuery, las vistas son lógicas y no materializadas. Como parte de la vista, solo se almacena la consulta; no los datos subyacentes.
Consulta las últimas 100 transacciones
- Copia y pega la siguiente consulta y ejecútala en BigQuery:
- Analiza para filtrar los resultados. ¿Cuál fue la última transacción de más de $2,000?
Respuesta:
date |
fullVisitorId |
visitId |
channelGrouping |
totalTransactionRevenue |
20170801 |
9947542428111966715 |
1501608078 |
Referral |
2934.61 |
Si se agregaran nuevos registros a este conjunto de datos públicos de comercio electrónico, también se actualizaría la última transacción.
- Para ahorrar tiempo y permitir una mejor organización y colaboración, puedes guardar tus consultas de informes comunes como vistas, tal como se muestra a continuación:
vw_
o un sufijo como _vw
o _view
al nombre de la vista.
También puedes usar OPTIONS para agregar una descripción y etiquetas a la vista.
- Copia y pega la siguiente consulta y ejecútala en BigQuery:
-
Busca la tabla
vw_latest_transactions
que creaste recientemente en tu conjunto de datos de comercio electrónico y selecciónala. -
Selecciona la pestaña DETALLES.
-
Confirma que la descripción y las etiquetas de tu vista se muestren correctamente en la IU de BigQuery.
También puedes ver la consulta que define la vista en la página Detalles. Esto te servirá para entender la lógica de las vistas que tú o tu equipo crearon.
Haz clic en Revisar mi progreso para verificar el objetivo.
- Ahora ejecuta esta consulta para crear una nueva vista:
Error: Already Exists: Table project-name:ecommerce.vw_latest_transactions
Si ya creaste la vista antes, es probable que recibas un error. ¿Puedes determinar por qué?
Respuesta: La instrucción para crear vistas se actualizó de CREATE OR REPLACE a solo CREATE, por lo que no podrás reemplazar tablas o vistas si estas ya existen. Una tercera opción, CREATE VIEW IF NOT EXISTS, te permitirá crear tablas o vistas solo si estas no existen; en caso contrario, omitirá la creación para que no se produzca un error.
Crea vistas: tu turno para practicar
Situación: Tu equipo contra fraudes te pidió que crearas un informe que muestre las 10 transacciones más recientes cuyo importe del pedido sea 1,000 o más para revisarlo manualmente.
Tarea: Crea una nueva vista que devuelva las 10 transacciones más recientes con ingresos mayores a 1,000, del 1 de enero de 2017 en adelante.
Usa estas reglas como guía:
-
Crea una nueva vista en tu conjunto de datos de comercio electrónico y nómbrala "vw_large_transactions". Reemplaza la vista si ya existe.
-
Agrega una descripción para la vista: "transacciones grandes para revisión".
-
Agrega una etiqueta para la vista [("org_unit", "loss_prevention")].
-
Extrae tus datos sin procesar de la tabla
data-to-insights.ecommerce.all_sessions_raw
. -
Divide el campo de ingresos entre 1,000,000.
-
Incluye únicamente transacciones con ingresos iguales o superiores a 1,000.
-
Incluye solo transacciones con fecha 20170101 o una fecha posterior, y ordénelas de modo que se muestren primero las más recientes.
-
Incluye solo currencyCode = 'USD'.
-
Muestra estos campos:
- date
- fullVisitorId
- visitId
- channelGrouping
- totalTransactionRevenue AS revenue
- currencyCode
- v2ProductName
-
Asegúrate de anular los registros duplicados (sugerencia: usa DISTINCT).
-
Inténtalo:
Solución posible:
Observa que debes repetir la división en la cláusula WHERE porque no puedes usar nombres de campo con alias como filtros.
Haz clic en Revisar mi progreso para verificar el objetivo.
Crédito adicional
Situación: Tu departamento contra fraudes agradece la consulta y la supervisa a diario con el objetivo de detectar pedidos sospechosos. Ahora te pidió que, además de los resultados que enviaste antes, incluyas una muestra de los productos que conforman cada pedido.
Usa la función de agregación de cadenas de BigQuery STRING_AGG y el campo v2ProductName
para modificar tu consulta anterior de modo que devuelva en orden alfabético 10 de los nombres de los productos de cada pedido.
Solución posible:
Observa aquí las dos adiciones para agregar la lista de productos en cada pedido con STRING_AGG(). Además, puesto que se realiza una agregación, se agrega la cláusula GROUP BY necesaria para los otros campos.
Cómo usar SESSION_USER() en vistas para limitar el acceso a los datos
Situación: El líder de tu equipo de datos te pidió que buscaras la forma de limitar qué personas de tu organización podían ver los datos obtenidos mediante la vista que creaste recientemente. La información de pedidos es especialmente sensible y debe compartirse solo con los usuarios que necesitan verla.
Tarea: Modifica la vista que creaste antes para que solo los usuarios conectados con un dominio de sesión qwiklabs.net puedan ver los datos en la vista subyacente. Nota: En un lab posterior sobre acceso, aprenderás a crear listas de grupos de usuarios permitidos específicos. Por ahora, realizarás la validación en función del dominio del usuario de la sesión.
- Para ver tu propia información de acceso a la sesión, ejecuta la siguiente consulta que usa SESSION_USER():
Verás xxxx@qwiklabs.net.
- Modifica la siguiente consulta para agregar un filtro adicional con el objetivo de permitir que solo los usuarios del dominio
qwiklabs.net
puedan ver los resultados:
Solución posible:
- Ejecuta la consulta anterior para confirmar que puedes ver los registros que se devolvieron.
Ahora, quita todos los dominios del filtro IN REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('')
, vuelve a ejecutar la consulta y confirma que no se devuelve ningún registro.
- Vuelve a crear la vista vw_large_transactions y reemplázala por la nueva consulta anterior. Como parámetro adicional de OPTIONS, agrega
expiration_timestamp
para que toda la vista sea de 90 días a partir de ahora:
Solución posible:
Haz clic en Revisar mi progreso para verificar el objetivo.
- Confirma con la instrucción SELECT que puedes ver los datos que se devuelven en la vista (con el acceso al dominio) y la marca de tiempo de vencimiento en los detalles de la vista:
¡Felicitaciones!
Creaste con éxito tablas y vistas con acceso controlado por medio de DDL (lenguaje de definición de datos) de SQL dentro de BigQuery.
Finaliza la Quest
Este lab de autoaprendizaje forma parte de la Quest BigQuery for Marketing Analysts. Una Quest es una serie de labs relacionados que forman una ruta de aprendizaje. Si completas esta Quest, obtendrás una insignia como reconocimiento por tu logro. Puedes hacer públicas tus insignias y agregar vínculos a ellas en tu currículum en línea o en tus cuentas de redes sociales. Inscríbete en esta Quest y obtén un crédito inmediato de realización. Consulta el catálogo de Google Cloud Skills Boost para ver todas las Quests disponibles.
Realiza tu próximo lab
Continúa tu Quest con Cómo transferir nuevos conjuntos de datos a BigQuery o revisa estas sugerencias:
- Crea un almacén de datos mediante uniones y fusiones
- Cómo crear tablas particionadas por fecha en BigQuery
Próximos pasos y más información
¿Ya tienes una cuenta de Google Analytics y quieres consultar tus propios conjuntos de datos en BigQuery? Sigue esta guía de exportación.
Última actualización del manual: 24 de enero de 2023
Prueba más reciente del lab: 24 de enero de 2023
Copyright 2024 Google LLC. All rights reserved. Google y el logotipo de Google son marcas de Google LLC. Los demás nombres de productos y empresas pueden ser marcas de las respectivas empresas a las que estén asociados.