Connecting Superset to a database¶

Exercise superset-first-connection-annex.pdf · PDF

The first time you play an online game, it asks you things before it lets you in. Which server? Which world? Your account? Your password? Get one wrong and you stay in the lobby, staring at an error message.

A database does exactly the same. On the last page you saw that, of the six kinds of data source, the relational database is the one that asks for the most before it opens. Today you learn what it asks for, why, and how to read the error when the door stays shut.

Then you walk through that door: you put a real dataset into the database and ask it your first question.

Superset does not hold your data¶

Start with the picture, because everything else hangs from it.

flowchart LR
    B["<b>Your browser</b><br>what you see and click"] -- "web page" --> S["<b>Superset</b><br>(the game client)<br>connects, asks, draws"]
    S -- "SQL, through<br>the driver" --> D[("<b>PostgreSQL</b><br>(the game server)<br>where the rows live")]
    D -- "rows" --> S

There are three players here, and they are three different programs:

A database server is a program that keeps data in tables and answers questions about them for any program that connects with the right details. PostgreSQL is the one we use.

A connection is the set of details one program needs to reach a database server and be let in.

Superset is a client. A game client with no server to join shows you a pretty menu and nothing else. So the first job in Superset is always the same: tell it where the server is.

Five things and a translator¶

Every database connection, in every tool you will ever use, asks for the same five things. Learn them once.

Three of those words are new:

• The host is the network name or address of the machine where the database server runs.
• A port is a numbered door on a machine. One machine can run many programs that listen to the network, and each one waits behind its own number. PostgreSQL's usual door is 5432. Superset's own web page uses a different one, 8088: do not mix them up.
• One database server can hold several separate databases, like one game server hosting several worlds. You must say which one you want.

And then there is a sixth thing, which no form asks you to type:

A driver is a small piece of software that lets one program talk to one specific kind of database.

Superset speaks SQL. But PostgreSQL, MySQL and the rest each expect that SQL to arrive over the network in their own way. The driver is the translator. Superset's driver for PostgreSQL is called psycopg2; for MySQL it would need a different one.

All five in one line: the connection string¶

Typing five boxes is one way to give the details. The other is one line of text.

A connection string is a single line that holds every detail of a connection, in a fixed order.

For PostgreSQL in Superset it has this pattern:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE

With invented values, so you can see where each part goes:

postgresql://your_user:your_password@db.example.com:5432/shop

Read the punctuation and you can decode any connection string: :// after the kind of database, : between user and password, @ before the host, : before the port, / before the database.

Where it goes in Superset¶

In Superset, a connection is created once and then everybody who uses that Superset works through it. On your class VM it already exists, under the name Dades classe. This is how one is made, so you know what you are looking at.

Step 5 is not one of the five settings. It is a permission: a switch on Superset's side that decides what people may do through this connection. Without it, nobody can load a file into that database from Superset.

So the full list for "configuring access" is: host, port, database, user, password, driver, and permissions.

Reading the error¶

Connections fail. That is normal, and the error message is not noise: it tells you how far you got.

Which is better news: connection refused or wrong password? Wrong password. It means the host and the port were right and a server answered. You are one typo away. Connection refused means you have not even found the server yet.

Loading a CSV into the database¶

On the last page you downloaded pokemon_df.csv. A file on your disk is loot in your backpack. To query it with SQL, it has to become a table in the database.

Superset can do that for you: it reads the file and creates the table in PostgreSQL, through the connection.

Two new words:

• A schema is a folder of tables inside a database. public is the one PostgreSQL gives you from the start.
• A dataset is Superset's name for "something I can build charts from". Here it is a label pointing at your new table. You will meet datasets properly in Datasets and destination.

Step 6 is where the previous page pays off. If your file uses ; between values and the form still says ,, the upload "works" and you get a table with one single, very wide column. And if the numbers use a decimal comma, set File settings → Decimal character to , as well.

One thing the upload does quietly: in our file, a missing value is written as the two letters NA. Superset loads those cells as NULL, the database's way of saying "there is no value here". Charmander has only one type, so its type_2 is NULL. That is your first taste of missing values, the big topic of UT2.

First questions in SQL Lab¶

SQL (Structured Query Language) is the language for asking questions to a relational database. One question is called a query.

SQL Lab is the page of Superset where you write a query, run it and see the answer as a table.

Open SQL → SQL Lab. On the left choose the database Dades classe and the schema public. Then ask three questions, one at a time, pressing Run after each.

How many rows are there?

SELECT COUNT(*) FROM pokemon;

SELECT means "show me", FROM pokemon says which table, and COUNT(*) counts rows. 949 Pokémon: the same number you counted in the text editor. The whole file arrived.

What do the rows look like?

SELECT * FROM pokemon LIMIT 10;

The * means "every column" and LIMIT 10 means "stop after ten rows". You get 10 rows and 22 columns. These are the first four rows and some of the columns:

Always run this one on a table you have never seen. It costs a second and it shows you the column names, the kind of value in each one, and surprises like that NULL.

Which type hits hardest?

SELECT type_1, COUNT(*) AS n, ROUND(AVG(attack)) AS avg_attack
FROM pokemon
GROUP BY type_1
ORDER BY avg_attack DESC;

The answer has 18 rows, one per type. The first five:

Read it as a sentence: for each main type, count the Pokémon and average their attack, then sort from the highest average down. Dragon types hit hardest, with an average attack of 109. The most common type, water, has 126 Pokémon but sits far down the list with an average of 75.

Do not worry if that third query looks like a lot. GROUP BY is the only really new idea in it, and it gets its own page: SQL Lab: groups.

Notice what just happened. In the crafting game, this was the whole of stage 1: you found the loot (a CSV on a website), carried it home (the upload) and stored it where you can work with it (a table). And you asked a question a text editor could never answer.

Try it: your own first connection¶

On your class VM, with Superset open in the browser:

If you downloaded your own dataset in the open-data hunt, upload it too, with its own table name, and adapt the third query to the question you wrote on your card.

Key takeaways¶

• Superset is a client: it connects, asks and draws. Your rows live in the database server (PostgreSQL), not in Superset.
• A connection needs five things: host, port, database, user and password. PostgreSQL's usual port is 5432.
• The driver is the translator between a tool and one kind of database. No driver, no connection, even with the right password.
• A connection string packs all of it into one line, password included. Treat it as a secret.
• Error messages say how far you got: connection refused points at host or port; incorrect password means you already reached the server.
• localhost means "the machine where this program runs". For Superset, that is its own server, not your computer.
• Uploading a CSV creates a real table in the database. Check schema and delimiter in the form. Then ask it questions in SQL Lab.

Quick check¶

The door is open and the table is in. So far you asked for everything; next you learn to ask for exactly the rows you need: SQL Lab: filters.