Datasets and destination¶

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

In the last lesson you joined pokemon with generations, with your own hands. Where is that query now?

Look for it. At best it is somewhere in SQL → Query History. More likely it is gone, and next week you will type it again from memory — and get it slightly wrong. A query you have to retype is a query you will get wrong next time. This lesson gives you two ways to keep your work. One keeps the question. The other keeps the answer. They are not the same thing, and choosing the wrong one is how a report ends up showing last month's numbers.

Recipe or tupperware¶

You make a great lasagne. How do you make sure you can eat it again next month?

Two answers. Write down the recipe. Or cook a big batch and put it in a tupperware in the fridge. One analogy for this whole page: the database is a kitchen, and the tables in PostgreSQL are the ingredients in the pantry.

flowchart TB
    Q["<b>Your query</b><br>pokemon JOIN generations"]
    Q -- "save the question" --> V["<b>Virtual dataset</b><br>(the recipe)<br>stored in Superset as text"]
    Q -- "store the answer" --> T["<b>New table</b><br>(the tupperware)<br>stored in PostgreSQL as rows"]
    V -- "runs the query again<br>every time" --> C["Charts"]
    T -- "reads the stored rows" --> C

In the crafting game of this module, this is the moment you stop carrying loot in your hands and put it in the chest at your base. Data that only exists on your screen is not extracted yet. It has to arrive somewhere: its destination.

Datasets: what Superset can draw from¶

A dataset is what Superset calls "something I can build charts from".

Superset does not draw charts straight from a table or from a query. It draws them from a dataset, and there are two kinds:

Open Datasets in the top menu and look at the Type column. You already have two: pokemon and generations, both Physical. Superset registered them when you uploaded the CSV files.

Save the question: a virtual dataset¶

Time to write the first recipe. Open SQL → SQL Lab, database Dades classe, schema public, and run the join from the last lesson with the columns listed one by one:

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 alone means INNER JOIN, and the AS before an alias is optional.) Then save it:

802 rows, not 949: it is an INNER JOIN, so the 147 alternate forms with no generation are left out. You already know why. Now that decision is written down in one place, instead of being retyped every time.

Store the answer: CREATE TABLE AS¶

CREATE TABLE name AS SELECT ... runs a query once and stores its result as a new table in the database.

Now the tupperware: a small summary with one row per generation. First run the SELECT on its own (from the second line) and look at the result. Then add the first line and run it all:

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;

This time there is no result grid. SQL Lab says The query returned no data. Nothing failed: the rows went into the database, not to your screen. Ask the new table:

SELECT * FROM pokemon_by_generation ORDER BY generation_id;

Eight rows, one per generation. The LEFT JOIN starts from generations, so Galar stays, with n_pokemon = 0. Its avg_attack and max_speed are blank, not 0: there is nothing to average, and writing 0 would be a lie.

The table exists in PostgreSQL, but Superset has no dataset for it yet. Label the jar:

Which one goes stale?¶

Here is the idea of the day. A ninth generation of Pokémon came out on 18 November 2022: the region of Paldea, in the games Scarlet and Violet. Our generations table does not know. Imagine somebody adds that ninth row.

Before you read on, predict. How many rows will the table pokemon_by_generation show? And the same SELECT, run again as a query?

The recipe saw the new ingredient. The tupperware did not.

So when is the tupperware the right choice?

A source that is not a file: an API¶

Your Pokémon came in a CSV file. If a new Pokémon is released tomorrow, will the file know? No: a file is a tupperware too. So where does an app get the fresh list?

An API (Application Programming Interface) is an address you ask, which answers with data instead of a web page.

JSON (JavaScript Object Notation) is the text format most APIs answer in: names and values, where a value can hold more names and values inside.

The PokéAPI is a free one. No account, no login: the address is the whole question. Open these two in your browser (Firefox shows JSON as a foldable tree):

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" } }, ... ] }

(Both answers are trimmed; count was 1351 when this page was written and keeps growing.) Three things to read in them:

• The first answer says 1351 but shows 20. The rest are at the next address. Splitting an answer into pages is called pagination.
• The part after the ? holds the parameters: extra instructions such as limit=20.
• The types are a list inside the answer. Data inside data is called nested.

Now compare with your table: SELECT pokemon, height, weight, type_1, type_2 FROM pokemon WHERE pokemon = 'charizard';

Same Pokémon, different shape and different units. A source never arrives in the shape you need.

From JSON to a table: extract, flatten, load¶

To flatten is to turn nested JSON into plain rows and columns.

flowchart LR
    A["<b>API</b><br>PokéAPI<br>answers in JSON"] -- "ask" --> S["<b>Script</b><br>api_to_csv.py<br>picks and flattens"]
    S -- "write" --> C["<b>CSV</b><br>pokeapi_sample.csv<br>20 rows, 7 columns"]
    C -- "upload" --> D["<b>Destination</b><br>table pokeapi_sample<br>in PostgreSQL"]
    D --> DS["<b>Physical dataset</b><br>in Superset"]

Flattening needs a small program. Download api_to_csv.py from the Resources page. It is 16 lines of 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"])

You do not have to write Python in this module. Read it as four verbs: ask for the list, ask for each Pokémon, pick the values (units converted, types split in two), write a row.

Now open Datasets and look at the Type of pokeapi_sample: Physical. Once the data is in the database, Superset cannot tell it came from an API. And it is a tupperware: a snapshot, a copy of the API as it was the minute the script ran. To refresh it, extract and load again.

The API checklist¶

Six questions to answer before you build anything on an API — for example in your project.

Key takeaways¶

• Keep the question or keep the answer: a virtual dataset is a saved query (the recipe); CREATE TABLE ... AS stores the rows in a new table (the tupperware).
• A dataset is what Superset builds charts from. A physical dataset is a label on a table, not a copy of it.
• A virtual dataset is always fresh: it runs the query every time. A table made with CREATE TABLE ... AS is a snapshot and goes stale when the source changes.
• CREATE TABLE ... AS shows no result grid: the rows went into the database. Check with a SELECT.
• An API answers in JSON, often nested and paginated. Superset cannot query it: you extract, flatten to a CSV and load.
• Before using an API, run the checklist: address, key, limits, pagination, licence, snapshot.

Quick check¶

You can now bring data home and store it — with Superset. The same job exists in every data tool, with other buttons. Next: Getting data with Power BI, where you can ask the same question: does this tool keep the recipe or the tupperware?