Datasets i destí¶

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

A la lliçó anterior vas unir pokemon amb generations, amb les teves pròpies mans. On és ara aquella consulta?

Busca-la. En el millor dels casos és en algun lloc de SQL → Query History. El més probable és que hagi desaparegut, i la setmana que ve la tornaràs a escriure de memòria, i et sortirà una mica diferent. Una consulta que has de tornar a escriure és una consulta que la propera vegada faràs malament. Aquesta lliçó et dona dues maneres de guardar la teva feina. Una guarda la pregunta. L'altra guarda la resposta. No són el mateix, i triar la que no toca és com un informe acaba ensenyant les xifres del mes passat.

Recepta o carmanyola¶

Fas una lasanya boníssima. Com t'assegures de poder-la tornar a menjar d'aquí a un mes?

Dues respostes. Apuntar la recepta. O cuinar-ne una bona tanda i guardar-la en una carmanyola a la nevera. Una analogia per a tota aquesta pàgina: la base de dades és una cuina, i les taules de PostgreSQL són els ingredients del rebost.

flowchart TB
    Q["<b>La teva consulta</b><br>pokemon JOIN generations"]
    Q -- "guardes la pregunta" --> V["<b>Dataset virtual</b><br>(la recepta)<br>desat a Superset com a text"]
    Q -- "guardes la resposta" --> T["<b>Taula nova</b><br>(la carmanyola)<br>desada a PostgreSQL com a files"]
    V -- "torna a executar la consulta<br>cada vegada" --> C["Gràfics"]
    T -- "llegeix les files desades" --> C

En el joc de crafting d'aquest mòdul, aquest és el moment en què deixes de portar el botí a les mans i el poses al cofre de la teva base. Les dades que només existeixen a la pantalla encara no estan extretes. Han d'arribar a algun lloc: el seu destí.

Datasets: de què pot dibuixar Superset¶

Un dataset és el que Superset anomena "alguna cosa a partir de la qual puc construir gràfics".

Superset no dibuixa gràfics directament d'una taula ni d'una consulta. Els dibuixa a partir d'un dataset, i n'hi ha de dos tipus:

Obre Datasets al menú superior i mira la columna Type. Ja en tens dos: pokemon i generations, tots dos Physical. Superset els va registrar quan vas pujar els fitxers CSV.

Guardar la pregunta: un dataset virtual¶

Hora d'escriure la primera recepta. Obre SQL → SQL Lab, base de dades Dades classe, esquema public, i executa el join de la lliçó anterior amb les columnes enumerades una per 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 sol vol dir INNER JOIN, i l'AS abans d'un àlies és opcional.) Després desa-la:

802 files, no 949: és un INNER JOIN, així que les 147 formes alternatives sense generació queden fora. Ja saps per què. Ara aquesta decisió queda escrita en un sol lloc, en comptes de tornar-la a escriure cada vegada.

Guardar la resposta: CREATE TABLE AS¶

CREATE TABLE nom AS SELECT ... executa una consulta una sola vegada i en desa el resultat com a taula nova a la base de dades.

Ara la carmanyola: un resum petit amb una fila per generació. Primer executa només el SELECT (des de la segona línia) i mira el resultat. Després afegeix la primera línia i executa-ho tot:

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;

Aquesta vegada no hi ha quadrícula de resultats. SQL Lab diu The query returned no data. No ha fallat res: les files han anat a la base de dades, no a la teva pantalla. Pregunta a la taula nova:

SELECT * FROM pokemon_by_generation ORDER BY generation_id;

Vuit files, una per generació. El LEFT JOIN comença des de generations, així que Galar es queda, amb n_pokemon = 0. Els seus avg_attack i max_speed són buits, no 0: no hi ha res per fer-ne la mitjana, i escriure 0 seria mentir.

La taula existeix a PostgreSQL, però Superset encara no té cap dataset per a ella. Posa l'etiqueta al pot:

Quin es queda desfasat?¶

Aquesta és la idea del dia. Una novena generació de Pokémon va sortir el 18 de novembre de 2022: la regió de Paldea, als jocs Scarlet i Violet. La nostra taula generations no ho sap. Imagina que algú hi afegeix aquesta novena fila.

Abans de continuar llegint, prediu. Quantes files mostrarà la taula pokemon_by_generation? I el mateix SELECT, executat una altra vegada com a consulta?

La recepta ha vist l'ingredient nou. La carmanyola, no.

Doncs quan és la carmanyola la tria correcta?

Una font que no és un fitxer: una API¶

Els teus Pokémon van arribar en un fitxer CSV. Si demà surt un Pokémon nou, ho sabrà el fitxer? No: un fitxer també és una carmanyola. Doncs d'on treu una aplicació la llista fresca?

Una API (Application Programming Interface, interfície de programació d'aplicacions) és una adreça a la qual preguntes i que respon amb dades en comptes d'una pàgina web.

JSON (JavaScript Object Notation) és el format de text en què responen la majoria d'API: noms i valors, on un valor pot contenir més noms i valors a dins.

La PokéAPI és una API gratuïta. Sense compte, sense inici de sessió: l'adreça és tota la pregunta. Obre aquestes dues al navegador (Firefox mostra el JSON com un arbre 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" } }, ... ] }

(Totes dues respostes estan retallades; count valia 1351 quan es va escriure aquesta pàgina i continua creixent.) Tres coses per llegir-hi:

• La primera resposta diu 1351 però en mostra 20. La resta són a l'adreça next. Dividir una resposta en pàgines s'anomena paginació.
• La part després del ? conté els paràmetres: instruccions addicionals com limit=20.
• Els tipus són una llista dins de la resposta. Les dades dins de dades s'anomenen imbricades.

Ara compara-ho amb la teva taula: SELECT pokemon, height, weight, type_1, type_2 FROM pokemon WHERE pokemon = 'charizard';

El mateix Pokémon, una altra forma i unes altres unitats. Una font mai no arriba amb la forma que necessites.

Del JSON a una taula: extreure, aplanar, carregar¶

Aplanar és convertir un JSON imbricat en files i columnes planes.

flowchart LR
    A["<b>API</b><br>PokéAPI<br>respon en JSON"] -- "pregunta" --> S["<b>Script</b><br>api_to_csv.py<br>tria i aplana"]
    S -- "escriu" --> C["<b>CSV</b><br>pokeapi_sample.csv<br>20 files, 7 columnes"]
    C -- "carrega" --> D["<b>Destí</b><br>taula pokeapi_sample<br>a PostgreSQL"]
    D --> DS["<b>Dataset físic</b><br>a Superset"]

Aplanar necessita un programa petit. Descarrega api_to_csv.py de la pàgina de Recursos. Són 16 línies 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 aquest mòdul no has d'escriure Python. Llegeix-lo com a quatre verbs: demanar la llista, demanar cada Pokémon, triar els valors (unitats convertides, tipus separats en dos), escriure una fila.

Ara obre Datasets i mira el Type de pokeapi_sample: Physical. Un cop les dades són a la base de dades, Superset no pot saber que venen d'una API. I és una carmanyola: una instantània, una còpia de l'API tal com era el minut que es va executar l'script. Per refrescar-la, torna a extreure i a carregar.

La llista de comprovació d'una API¶

Sis preguntes per respondre abans de construir res sobre una API, per exemple al teu projecte.

Idees clau¶

• Guarda la pregunta o guarda la resposta: un dataset virtual és una consulta desada (la recepta); CREATE TABLE ... AS desa les files en una taula nova (la carmanyola).
• Un dataset és el que Superset fa servir per construir gràfics. Un dataset físic és una etiqueta sobre una taula, no una còpia.
• Un dataset virtual està sempre fresc: executa la consulta cada vegada. Una taula feta amb CREATE TABLE ... AS és una instantània i es queda desfasada quan canvia l'origen.
• CREATE TABLE ... AS no mostra cap quadrícula de resultats: les files han anat a la base de dades. Comprova-ho amb un SELECT.
• Una API respon en JSON, sovint imbricat i paginat. Superset no la pot consultar: tu extreus, aplanes a un CSV i carregues.
• Abans d'usar una API, passa la llista de comprovació: adreça, clau, límits, paginació, llicència, instantània.

Comprovació ràpida¶

Ja pots portar dades a casa i desar-les, amb Superset. La mateixa feina existeix a totes les eines de dades, amb altres botons. Següent: Obtenir dades amb Power BI, on pots fer la mateixa pregunta: aquesta eina guarda la recepta o la carmanyola?