Consulta el conjunto de datos de Wikipedia en BigQuery

1. 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 ninguna infraestructura y no necesitas un administrador de base de datos. BigQuery usa un SQL conocido y puede aprovechar un modelo de pago por uso. BigQuery te permite enfocarte en el análisis de datos para buscar estadísticas valiosas.

En este codelab, explorarás el conjunto de datos de Wikipedia con BigQuery.

Qué aprenderás

• Cómo usar BigQuery
• Cómo cargar un conjunto de datos del mundo real en BigQuery
• Cómo escribir una consulta para obtener estadísticas sobre un conjunto de datos grande

Requisitos

• Un proyecto de Google Cloud
• Un navegador, como Google Chrome o Firefox

Encuesta

¿Cómo usarás este instructivo?

Solo lectura Léelo y completa los ejercicios. .

¿Cómo calificarías tu experiencia con Google Cloud?

Principiante Intermedio Avanzado .

2. Configuración y requisitos

Habilitar BigQuery

Si aún no tienes una Cuenta de Google, debes crear una.

1. Accede a la consola de Google Cloud y navega a BigQuery. También puedes abrir la IU web de BigQuery directamente si ingresas la siguiente URL en tu navegador.

https://console.cloud.google.com/bigquery

2. Acepta las Condiciones del Servicio.
3. Antes de poder usar BigQuery, debes crear un proyecto. Sigue las indicaciones para crear un proyecto nuevo.

Elige un nombre de proyecto y toma nota del ID del proyecto.

El ID del proyecto es un nombre único en todos los proyectos de Google Cloud. Se mencionará más adelante en este codelab como PROJECT_ID.

En este codelab, se usan recursos de BigQuery dentro de los límites de la zona de pruebas de BigQuery. No se requiere una cuenta de facturación. Si más adelante deseas quitar los límites de la zona de pruebas, puedes registrarte para la prueba gratuita de Google Cloud para agregar una cuenta de facturación.

Carga el conjunto de datos de Wikipedia en la siguiente sección.

3. Crea un conjunto de datos

Primero, crea un nuevo conjunto de datos en el proyecto. Un conjunto de datos se compone de varias tablas.

1. Para crear un conjunto de datos, haz clic en el nombre del proyecto que se encuentra en el panel de recursos y, luego, en Crear conjunto de datos:

2. Ingresa lab como el ID del conjunto de datos:

3. Haz clic en Crear conjunto de datos para crear un conjunto de datos vacío.

4. Carga datos con el programa de línea de comandos de bq

Activar Cloud Shell

1. En la consola de Cloud, haz clic en Activar Cloud Shell.

Si nunca iniciaste Cloud Shell, aparecerá una pantalla intermedia (mitad inferior de la página) que describe en qué consiste. Si ese es el caso, haz clic en Continuar (y no volverás a verlo). Así es como se ve la pantalla única:

El aprovisionamiento y la conexión a Cloud Shell solo tomará unos minutos.

Esta máquina virtual está cargada con todas las herramientas de desarrollo que necesitas. Ofrece un directorio principal persistente de 5 GB y se ejecuta en Google Cloud, lo que permite mejorar considerablemente el rendimiento de la red y la autenticación. Gran parte de tu trabajo en este codelab, si no todo, se puede hacer simplemente con un navegador o tu Chromebook.

Una vez conectado a Cloud Shell, debería ver que ya se autenticó y que el proyecto ya se configuró con tu ID del proyecto.

2. En Cloud Shell, ejecuta el siguiente comando para confirmar que está autenticado:

gcloud auth list

Resultado del comando

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Nota: gcloud es la herramienta de línea de comandos potente y unificada de Google Cloud. Ya está preinstalada en Cloud Shell. Notarás que esta función es compatible con la función de autocompletado con tabulador. Para obtener más información, consulta la descripción general de la herramienta de línea de comandos de gcloud.

3. Ejecuta el siguiente comando en Cloud Shell para confirmar que el comando de gcloud conoce tu proyecto:

gcloud config list project

Resultado del comando

[core]
project = <PROJECT_ID>

De lo contrario, puedes configurarlo con el siguiente comando:

gcloud config set project <PROJECT_ID>

Resultado del comando

Updated property [core/project].

Carga datos en BigQuery

Para tu comodidad, algunos datos del 10 de abril de 2019 del conjunto de datos de vistas de página de Wikimedia están disponibles en Google Cloud Storage, en gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. El archivo de datos es un archivo CSV de GZip. Puedes cargar este archivo directamente con la utilidad de línea de comandos de bq. Como parte del comando de carga, también describes el esquema del archivo.

Puedes encontrar el ID del proyecto en la Consola de APIs o en la línea de comandos: "gcloud config list" y busca el valor de "project".

Además, cuando se ejecuta bq por primera vez, es posible que se te pida que selecciones un proyecto predeterminado. Una vez seleccionados, puedes volver a ejecutar el comando anterior para cargar los datos.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

La carga de datos puede tardar unos minutos. Espera a que se complete.

Utilizó algunas opciones avanzadas para cargar el archivo de vistas de página:

• Establece --source_format CSV para indicar que el archivo se debe analizar como un archivo CSV. Este paso es opcional porque CSV es el formato predeterminado.
• Establece --field_delimiter " " a fin de indicar que se usa un solo espacio para delimitar los campos.
• Configura --allow_jagged_rows para incluir las filas con menos cantidad de columnas e ignorar los errores mientras se carga el archivo CSV.
• Configura --quote "" para indicar que las cadenas no están entre comillas.
• Configura --max_bad_records 3 para que ignore como máximo 3 errores durante el análisis del archivo CSV.

Puedes obtener más información sobre la línea de comandos de bq en la documentación.

5. Obtén una vista previa del conjunto de datos

En la consola de BigQuery, abre una de las tablas que acabas de cargar.

1. Expande el proyecto.
2. Expande el conjunto de datos.
3. Selecciona la tabla.

Puedes ver el esquema de la tabla en la pestaña Esquema. 4. Para saber cuántos datos hay en la tabla, navega a la pestaña Detalles:

5. Abre la pestaña Vista previa para ver una selección de filas de la tabla.

6. Redacta una consulta

1. Haz clic en Redactar consulta nueva:

Esto abre el Editor de consultas:

2. Para calcular la cantidad total de vistas de Wikimedia entre las 2 y las 3 p.m. del 10 de abril de 2019, escribe esta consulta:

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. Haz clic en Ejecutar.

Luego de unos segundos, el resultado aparecerá en la parte inferior y también indicará cuántos datos se procesaron:

Esta consulta procesó 123.9 MB, a pesar de que la tabla es de 691.4 MB. BigQuery solo procesa los bytes de las columnas que se utilizan en la consulta, por lo que la cantidad total de datos procesados puede ser significativamente menor que el tamaño de la tabla. Con el agrupamiento en clústeres y la partición, la cantidad de datos procesados puede reducirse aún más.

Puedes procesar/consultar hasta 1 TB de datos gratis cada mes. Consulta la página de precios de BigQuery para obtener más detalles.

7. Consultas más avanzadas

Cómo buscar páginas vistas de Wikipedia

El conjunto de datos de Wikimedia contiene vistas de página de todos los proyectos de Wikimedia (incluidos Wikipedia, Wiktionary, Wikibooks y Wikiquotes). Para limitar la consulta a páginas de Wikipedia en inglés únicamente, agrega una sentencia WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

Observa que, cuando consultas una columna adicional, wiki, la cantidad de datos procesados aumentó de 124 MB a 204 MB.

BigQuery admite muchas de las cláusulas de SQL conocidas, como CONTAINS, GROUP BY, ORDER BY y una serie de funciones de agregación. Además, puedes usar expresiones regulares para consultar campos de texto. Prueba una:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Obtén más información sobre la sintaxis de BigQuery en la documentación de referencia de consulta de BigQuery.

Cómo realizar consultas en varias tablas

Puedes seleccionar un rango de tablas para formar la unión con una tabla comodín.

1. Primero, crea una segunda tabla para realizar consultas cargando las páginas vistas de la próxima hora en una tabla nueva:

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. En el Editor de consultas, realiza consultas en las dos tablas que cargaste con “pageviews_2019”. como prefijo:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

Puedes filtrar las tablas de forma más selectiva con la seudocolumna _TABLE_SUFFIX. Esta consulta se limita a las tablas correspondientes al 10 de abril.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. Realice una limpieza

De forma opcional, borra el conjunto de datos que creaste con el comando bq rm. Usa la marca -r para quitar cualquier tabla que contenga.

bq rm -r lab

9. ¡Felicitaciones!

Utilizaste BigQuery y SQL para consultar el conjunto de datos de páginas vistas de Wikipedia en el mundo real. Tienes el poder de consultar conjuntos de datos a escala de petabytes.

Más información

• Consulta la subreddit de BigQuery para ver cómo otros utilizan BigQuery hoy.
• Encuentra conjuntos de datos públicos disponibles en BigQuery.
• Obtén más información sobre cómo cargar datos en BigQuery.