Datasets y destino¶

Ejercicio datasets-and-destination-annex.pdf · PDF

En la lección anterior uniste pokemon con generations, con tus propias manos. ¿Dónde está ahora esa consulta?

Búscala. Con suerte estará en algún lugar de SQL → Query History. Lo más probable es que haya desaparecido, y la semana que viene la volverás a escribir de memoria... y te saldrá un poco mal. Una consulta que tienes que reescribir es una consulta que te saldrá mal la próxima vez. Esta lección te da dos maneras de conservar tu trabajo. Una guarda la pregunta. La otra guarda la respuesta. No son lo mismo, y elegir mal es la forma de acabar con un informe que muestra las cifras del mes pasado.

Receta o táper¶

Haces una lasaña buenísima. ¿Cómo te aseguras de poder volver a comerla el mes que viene?

Dos respuestas. Anotar la receta. O cocinar una tanda grande y guardarla en un táper en la nevera. Una analogía para toda esta página: la base de datos es una cocina, y las tablas de PostgreSQL son los ingredientes de la despensa.

flowchart TB
    Q["<b>Tu consulta</b><br>pokemon JOIN generations"]
    Q -- "guardar la pregunta" --> V["<b>Dataset virtual</b><br>(la receta)<br>guardado en Superset como texto"]
    Q -- "guardar la respuesta" --> T["<b>Tabla nueva</b><br>(el táper)<br>guardada en PostgreSQL como filas"]
    V -- "ejecuta la consulta de nuevo<br>cada vez" --> C["Gráficos"]
    T -- "lee las filas guardadas" --> C

En el juego de crafteo de este módulo, este es el momento en que dejas de llevar el botín en las manos y lo guardas en el cofre de tu base. Los datos que solo existen en tu pantalla todavía no están extraídos. Tienen que llegar a algún sitio: su destino.

Datasets: de qué puede dibujar Superset¶

Un dataset es lo que Superset llama "algo a partir de lo que puedo construir gráficos".

Superset no dibuja gráficos directamente desde una tabla ni desde una consulta. Los dibuja desde un dataset, y hay dos tipos:

Abre Datasets en el menú superior y mira la columna Type. Ya tienes dos: pokemon y generations, ambos Physical. Superset los registró cuando subiste los archivos CSV.

Guardar la pregunta: un dataset virtual¶

Es hora de escribir la primera receta. Abre SQL → SQL Lab, base de datos Dades classe, esquema public, y ejecuta el join de la lección anterior con las columnas listadas una a una:

SELECT p.id, p.pokemon, p.type_1, p.type_2, p.hp, p.attack, p.defense, p.speed,
       g.generation_id, g.region, g.release_year
FROM pokemon p
JOIN generations g ON p.generation_id = g.generation_id

(JOIN a secas significa INNER JOIN, y el AS antes de un alias es opcional.) Ahora guárdala:

802 filas, no 949: es un INNER JOIN, así que las 147 formas alternativas sin generación quedan fuera. Ya sabes por qué. Ahora esa decisión queda escrita en un solo lugar, en vez de reescribirla cada vez.

Guardar la respuesta: CREATE TABLE AS¶

CREATE TABLE nombre AS SELECT ... ejecuta una consulta una sola vez y guarda su resultado como una tabla nueva en la base de datos.

Ahora el táper: un pequeño resumen con una fila por generación. Primero ejecuta solo el SELECT (desde la segunda línea) y mira el resultado. Luego añade la primera línea y ejecuta todo:

CREATE TABLE pokemon_by_generation AS
SELECT g.generation_id, g.region, g.release_year,
       COUNT(p.id) AS n_pokemon,
       ROUND(AVG(p.attack)) AS avg_attack,
       MAX(p.speed) AS max_speed
FROM generations g
LEFT JOIN pokemon p ON p.generation_id = g.generation_id
GROUP BY g.generation_id, g.region, g.release_year;

Esta vez no hay cuadrícula de resultados. SQL Lab dice The query returned no data. No ha fallado nada: las filas han ido a la base de datos, no a tu pantalla. Pregúntale a la tabla nueva:

SELECT * FROM pokemon_by_generation ORDER BY generation_id;

Ocho filas, una por generación. El LEFT JOIN parte de generations, así que Galar se queda, con n_pokemon = 0. Su avg_attack y su max_speed están en blanco, no a 0: no hay nada que promediar, y escribir 0 sería mentir.

La tabla existe en PostgreSQL, pero Superset todavía no tiene un dataset para ella. Etiqueta el tarro:

¿Cuál se queda desfasado?¶

Aquí va la idea del día. El 18 de noviembre de 2022 salió una novena generación de Pokémon: la región de Paldea, en los juegos Escarlata y Púrpura. Nuestra tabla generations no lo sabe. Imagina que alguien añade esa novena fila.

Antes de seguir leyendo, haz una predicción. ¿Cuántas filas mostrará la tabla pokemon_by_generation? ¿Y el mismo SELECT, ejecutado de nuevo como consulta?

La receta vio el ingrediente nuevo. El táper no.

Entonces, ¿cuándo es el táper la elección correcta?

Una fuente que no es un archivo: una API¶

Tus Pokémon llegaron en un archivo CSV. Si mañana sale un Pokémon nuevo, ¿lo sabrá el archivo? No: un archivo también es un táper. Entonces, ¿de dónde saca una app la lista actualizada?

Una API (Application Programming Interface) es una dirección a la que preguntas y que responde con datos en lugar de una página web.

JSON (JavaScript Object Notation) es el formato de texto en el que responden la mayoría de las API: nombres y valores, donde un valor puede contener más nombres y valores dentro.

La PokéAPI es una API gratuita. Sin cuenta, sin inicio de sesión: la dirección es toda la pregunta. Abre estas dos en tu navegador (Firefox muestra el JSON como un árbol plegable):

https://pokeapi.co/api/v2/pokemon?limit=20

{ "count": 1351,
  "next": "https://pokeapi.co/api/v2/pokemon?offset=20&limit=20",
  "previous": null,
  "results": [ { "name": "bulbasaur", "url": "https://pokeapi.co/api/v2/pokemon/1/" }, ... ] }

https://pokeapi.co/api/v2/pokemon/charizard

{ "id": 6, "name": "charizard", "height": 17, "weight": 905,
  "types": [ { "slot": 1, "type": { "name": "fire" } },
             { "slot": 2, "type": { "name": "flying" } } ],
  "stats": [ { "base_stat": 78, "stat": { "name": "hp" } }, ... ] }

(Las dos respuestas están recortadas; count valía 1351 cuando se escribió esta página y sigue creciendo.) Tres cosas que leer en ellas:

• La primera respuesta dice 1351 pero muestra 20. El resto está en la dirección next. Dividir una respuesta en páginas se llama paginación.
• La parte que hay después de la ? contiene los parámetros: instrucciones adicionales como limit=20.
• Los tipos son una lista dentro de la respuesta. Datos dentro de datos se llaman anidados.

Ahora compara con tu tabla: SELECT pokemon, height, weight, type_1, type_2 FROM pokemon WHERE pokemon = 'charizard';

El mismo Pokémon, otra forma y otras unidades. Una fuente nunca llega con la forma que necesitas.

De JSON a una tabla: extraer, aplanar, cargar¶

Aplanar es convertir un JSON anidado en filas y columnas planas.

flowchart LR
    A["<b>API</b><br>PokéAPI<br>responde en JSON"] -- "preguntar" --> S["<b>Script</b><br>api_to_csv.py<br>elige y aplana"]
    S -- "escribir" --> C["<b>CSV</b><br>pokeapi_sample.csv<br>20 filas, 7 columnas"]
    C -- "subir" --> D["<b>Destino</b><br>tabla pokeapi_sample<br>en PostgreSQL"]
    D --> DS["<b>Dataset físico</b><br>en Superset"]

Aplanar necesita un programa pequeño. Descarga api_to_csv.py de la página de Recursos. Son 16 líneas de Python:

import csv, json, urllib.request

def get(url):
    req = urllib.request.Request(url, headers={"User-Agent": "erd-class"})
    return json.load(urllib.request.urlopen(req))

page = get("https://pokeapi.co/api/v2/pokemon?limit=20")   # page 1 of the list
with open("pokeapi_sample.csv", "w", newline="") as f:
    out = csv.writer(f)
    out.writerow(["id", "name", "height_m", "weight_kg", "type_1", "type_2", "hp"])
    for item in page["results"]:                  # each item: a name and a url
        p = get(item["url"])                      # one request per Pokemon
        types = [t["type"]["name"] for t in p["types"]] + [""]
        out.writerow([p["id"], p["name"], p["height"] / 10, p["weight"] / 10,
                      types[0], types[1], p["stats"][0]["base_stat"]])
print(page["count"], "Pokemon in the API. Next page:", page["next"])

En este módulo no tienes que escribir Python. Léelo como cuatro verbos: pedir la lista, pedir cada Pokémon, elegir los valores (unidades convertidas, tipos separados en dos), escribir una fila.

Ahora abre Datasets y mira el Type de pokeapi_sample: Physical. Una vez que los datos están en la base de datos, Superset no puede saber que vinieron de una API. Y es un táper: una instantánea, una copia de la API tal como estaba el minuto en que se ejecutó el script. Para refrescarla, vuelve a extraer y a cargar.

La lista de comprobación de una API¶

Seis preguntas que contestar antes de construir nada sobre una API, por ejemplo en tu proyecto.

Ideas clave¶

• Conserva la pregunta o conserva la respuesta: un dataset virtual es una consulta guardada (la receta); CREATE TABLE ... AS guarda las filas en una tabla nueva (el táper).
• Un dataset es lo que Superset usa para construir gráficos. Un dataset físico es una etiqueta sobre una tabla, no una copia de ella.
• Un dataset virtual está siempre fresco: ejecuta la consulta cada vez. Una tabla creada con CREATE TABLE ... AS es una instantánea y se queda desfasada cuando cambia el origen.
• CREATE TABLE ... AS no muestra cuadrícula de resultados: las filas han ido a la base de datos. Compruébalo con un SELECT.
• Una API responde en JSON, a menudo anidado y paginado. Superset no puede consultarla: tú extraes, aplanas a un CSV y cargas.
• Antes de usar una API, repasa la lista: dirección, clave, límites, paginación, licencia, instantánea.

Comprobación rápida¶

Ya sabes traer datos a casa y guardarlos, con Superset. El mismo trabajo existe en todas las herramientas de datos, con otros botones. Siguiente: Obtener datos con Power BI, donde puedes hacerte la misma pregunta: ¿esta herramienta guarda la receta o el táper?