The route

Read with SQL: let the database do the work

In World 3 you joined and summed in Odara's SQL node, after reading whole files. With a database you have a choice: the PostgreSQL Read node sends your query to the database, and only the answer comes back. This level asks the database for the revenue per category — four rows travel over the network instead of 3,008.

Goal

A PostgreSQL Read whose query computes revenue per category in the database, printed to a Console.

You'll need

  • Level 4.1 done: the three tables loaded, the academy_pg connection saved.
  • About 15 minutes.

Steps

1. A query, not a table

Create a pipeline w4-2-read. From INGEST ▸ Databases, drop a PostgreSQL Read named revenue_in_db, choose academy_pg in Use Connection, and write in SQL Query:

SELECT p.category,
       COUNT(*) AS orders,
       SUM(o.quantity * p.list_price) AS revenue
FROM orders o
JOIN products p ON p.sku = o.sku
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY revenue DESC

This is PostgreSQL's SQL, run by PostgreSQL. The tables are the real table names — not input — because nothing is being passed in: the node is the source.

(The screenshots say academy.orders: that's their schema. Yours is orders.)

2. Preview

Preview Output:

The query runs in the database; four rows come back

Four rows. The database joined 3,000 orders to 8 products, filtered and grouped them, and sent only the result.

3. Notice the decimals

Look at storage: 39829.000000000065 (or a similar tail). list_price was loaded as double precision — a binary floating-point type — and a sum of values like 89.90 can't be exact in binary. The difference is far below a cent, but it will show up in reports. Round it where the money is computed:

ROUND(SUM(o.quantity * p.list_price)::numeric, 2) AS revenue

PostgreSQL's ROUND(x, 2) wants a numeric, hence the ::numeric cast.

4. Execute

Add a Console, Execute, and expand the output.

The per-category revenue, computed by PostgreSQL

Expected result

category orders revenue
seats 1709 262078
services 200 148794
storage 557 39829
api 216 16302

The four add up to 467,003 — the same total World 3 got in Odara. Same data, same answer, very different amount of data moved.

Common pitfalls

relation "orders" does not exist. The table is in another schema, or the name is different. In your own database it's public.orders; check what 4.1 created.

function round(double precision, integer) does not exist. PostgreSQL only rounds to decimals on numeric. Cast first: ROUND(x::numeric, 2).

Using input in the query. That's the name of the incoming data in Odara's SQL node. In a PostgreSQL Read there is no input; you query the database's own tables.

Reading a whole table to filter it in Odara. It works, but every row crosses the network first. Filter and aggregate in the source query when you can; use Odara's SQL node for what the database can't do — joining it with a file, for example.

What you built

A source that pushes the work to where the data lives. That's the first rule of working with databases: move the question, not the data.

Check yourself

One question, drawn from a larger bank. Get it right to pass.

  1. When is Odara's SQL node the better place for a join?