# Cloud BigQuery
En este tutorial vamos a exporar un Dataset público con BigQuery para familiarizarnos con esta poderosa herramienta.
BigQuery está optimizado para almacenar y consultar datasets masivos, por lo que es interesante para nuestro curso.
Posibilita que con lenguaje SQL se puedan consultar los datos haciendo uso del poder de procesamiento de la infraestructura de Google.
Se puede usar BigQuery por línea de comando (bq), por la consola web, haciendo llamadas con REST API y librerías en Python, Java y otros.
Para ir a la Consola seleccionamos en el menú de navegación > **BigQuery**.
## Consultar un dataset público
Vamos primero a cargar un dataset público llamado *USA Names* en BigQuery. El dataset está en el siguiente [Link](https://drive.google.com/file/d/1H3o8GLrDG3n9ReamJiimJYIQLiN3NMdM/view?usp=sharing) por si quieren bajarlo y explorarlo localmente.
1. En el panel izquierdo, haz click en **AGREGAR** > **Conjunto de datos públicos**.
2. Luego busca **Bigquery-public** y selecciona el resultado.
3. Luego selecciona **Ver conjunto de datos**, lo que mostrará todos los datos del conjunto.
4. Haz click en `bigquery-public-data` en la lista para expandirlo y baja en la lista de datasets públicos, hasta que encuentres **usa_names**. Haz click en **usa_names** para expandir el dataset.
5. Haz click en **usa_1910_2013** para abrir la tabla.
6. Haz click en **Consulta** sobre el esquema y click en **En una pestaña nueva** para abrir el editor en un nuevo tab.
## Consulta el dataset público.
Vamos a consultar *bigquery-public-data.usa_names.usa_1910_2013* por el nombre y género de las personas en el dataset, y luego la lista de los 10 top nombres en orden descendente.
Copia y pega la siguiente consulta en el editor de consultas, reeemplazando la consulta que está.
```sql=
SELECT
name, gender,
SUM(number) AS total
FROM
`bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
name, gender
ORDER BY
total DESC
LIMIT
10
```
En la parte superior derecha de la ventana, mira el validador de la consulta. Se ve un ícono con marca verde si la consulta es válida. Si no lo es verás un ícono rojo con signo de exclamación. Cuando la consulta es válida, BigQuery te mostrará la cantidad de datos que la consulta procesa cuando la corres. Esto ayuda a determinar el costo de la consulta.
Haz click en **Ejecutar**.
Los resultados de la consulta se ven abajo del editor.
En el tab **Detalles de la ejecución** se muestra el tiempo que se demoró y cuantos datos procesó la consulta.
[Opcional]: Siga los mismos pasos con el dataset disponible el 2021 **Top 25 Google Search terms**. Haga sobre este dataset esta consulta para comenzar a explorar, simplemente consulta los top 25 términos en el intervalo indicado, mire como cambia los MB leidos con la consulta con y sin el *WHERE*:
```
SELECT * FROM `bigquery-public-data.google_trends.top_terms`
WHERE refresh_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
```
Haz la misma consulta sobre la tabla *top_rising_terms*.
La siguiente es una consulta más compleja, para comparar los resultados de las dos tablas mencionados, se hace join de las tablas, agrupados por semana y fecha de refresco.
```sql=
WITH rising AS (
SELECT
week,
refresh_date,
ARRAY_AGG(DISTINCT term) AS terms
FROM `bigquery-public-data.google_trends.top_rising_terms`
WHERE refresh_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY
week,
refresh_date)
SELECT
top.refresh_date,
top.week,
top.dma_name,
top.dma_id,
top.term AS top_term,
top.score AS top_score,
top.rank AS top_rank,
rising.terms AS rising_terms,
FROM `bigquery-public-data.google_trends.top_terms` top
JOIN rising
ON
top.refresh_date = rising.refresh_date
AND top.week = rising.week
ORDER BY
week desc, refresh_date desc;
````
[Opcional] Siga los mismos pasos con el dataset covid, ¿Qué columas tiene el dataset? ¿Qué tamaño es el dataset? ¿Qué consulta haría sobre este dataset?
## Crear una tabla y dataset
1. Descarga los datos a tu computador local, son aproximadamente 7M. [Link.](https://drive.google.com/file/d/1H3o8GLrDG3n9ReamJiimJYIQLiN3NMdM/view?usp=sharing)
2. Descomprime el archivo en tu computador.
3. El archivo contiene un pdf llamado NationalReadMe.pdf, que describe el dataset.
4. Abre el archivo llamado *yo2014.txt* para ver como luce. El archivo es un CSV (comma-separated value) que tiene tres columnas: Nombre, sexo y número de niños con ese nombre. El archivo no tiene fila de cabecera.
5. Fíjese en la localización de este archivo.
6. En la consola web de BigQuery, en el panel izquierdo, en la sección **Explorador**, haz click en el ID del proyecto.
7. Haz click en los tres puntos al lado del ID del proyecto y luego click en **Crear conjunto de datos**.
8. En la página para crear el dataset define lo siguiente:
* Para el **ID del conjunto de datos** escribe usa_names_2014.
* En **Tipo de ubicación** elige **us** que significa que estamos eligiendo múltiples regiones en Estados Unidos.
* Deja los demás valores por defecto.
9. Haz click en **Crear conjunto de datos**
## Cargar datos
1. Haz click en los tres puntos al lado de **usa_names_2014** encontrado en el panel de la izquierda, luego haz click en **Crear tabla**.
En la página **Crear tabla** usa los valores por defecto excepto por:
* Para **Crear tabla desde** elige **Subir** desde el menu.
* Para **Seleccionar archivo** haz click en **Explorar** y navega a donde tienes el archivo, luego abrelo.
* Para **Formato de archivo** elige CSV del menu.
* Para **Table name** pon **usa_names_2014_table** y en dataset el nombre que le pusiste en el paso anterior.
* En la sección **Esquema**, haz click en Editar como texto y copia lo siguiente:
```
name:string,gender:string,count:integer
````
Haz click en **Crear tabla**
## Consultar la tabla
En el editor de consultas, haz click en el signo + para una nueva consulta SQL.
Copia y pega la siguiente consulta en el editor (trae los top 5 nombres de mujeres en el 2014):
```sql=
SELECT
name, count
FROM
`usa_names_2014.usa_names_2014_table`
WHERE
gender = 'F'
ORDER BY count DESC LIMIT 10
````
Haz click en **Ejecutar**, los resultados saldrán en la ventana inferior.
Si sabes SQL haz alguna otra consulta!
## Consulta por consola
Podemos usar el comando bq para interactuar con BigQuery, pero antes debemos agregar el Role **Usuario de Bigquery** a *Compute Engine default service account*. (Explícitamente ya no pondremos como se hace esta acción ya que está repetida en varios tutoriales).
Luego ya podemos empezar a probar comandos, pruebalos de a uno:
```
bq ls
bq ls bigquery-public-data:
```
Para el siguiente comando sube el arachivo yob2015.txt a Cloud Shell, crearemos una instancia y tabla pero ahora con el comando *bq*.
```
bq mk nombres
bq load nombres.names2015 yob2015.txt name:string,gender:string,count:integer
bq show nombres.names2015
```
```
bq show bigquery-public-data:samples.shakespeare
```
Transforma cualquier consulta por consola usando *bq query --use_legacy_sql=false*
```
bq query --use_legacy_sql=false \
'SELECT
word,
SUM(word_count) AS count
FROM
`bigquery-public-data`.samples.shakespeare
WHERE
word LIKE "%raisin%"
GROUP BY
word'
```
La respuesta tendrá esta forma
Con BigTable puedes hacer muchas cosas más:
* Guardar y compartir consultas.
* Crear vistas y nuevas tablas a partir de las consultas realizadas. Fíjate que tienes que tener un dataset llamado mydataset, o ca
```sql=
CREATE TABLE mydataset.trips AS (
SELECT
bikeid,
start_time,
duration_minutes
FROM
bigquery-public-data.austin_bikeshare.bikeshare_trips
);
```
* Puedes consultar conjuntos de datos que no están en BigQuery como Cloud Storage, BigTable o Google Drive. Ojo que esto no tiene tan buen rendimiento como tener los datos almacenados en BigQuery.
Para ilustar esto vamos a subir un archivo CSV a Cloud Storage, llamado [movie-actores.csv](https://drive.google.com/file/d/1Q6zQJaz4FEuWowc2WQGmnUpoowwQhHTn/view?usp=sharing) (Explícitamente ya no pondremos en el tutorial como se realiza esta acción)
Luego que esté arriba crearemos la tabla externa con:
```
export BUCKET="mi-nombre-de-bucket-unico"
bq mkdef --source_format=CSV --autodetect=true gs://$BUCKET/movie-actors.csv > mitablactores
bq mk --table --external_table_definition=mitablactores \
midataset.mitablactores
```
Podemos consultar ahora los datos almacenados en Cloud Storage, usando el editor de consultas:
```sql=
SELECT * FROM `id_proyecto.midataset.mitablactores` LIMIT 1000
```
* Si trabajas con BigData **DEBES** estudiar como optimizar las consultas en cuanto a costo y tiempo. Por ejemplo, hacer "SELECT *"" es costoso en tablas grandes y recorrer toda la tabla puede ser evitado usando particionamiento por ejemplo, o vistas (VIEWS). Encuentra más información en este [Link](https://cloud.google.com/bigquery/docs/best-practices-performance-compute)
**Antes de cerrar el tutorial borra las tablas creadas en BigTable y buckets en Cloud Storage si haz creado alguno.**
Sign in with Wallet
Connect another wallet