Entering the Far Archive
O que você vai aprender
- to address BigQuery tables:
project.dataset.table, and why backticks matter - to hide and substitute columns without listing them:
SELECT * EXCEPT(...)andSELECT * REPLACE(...) - to read a cell that holds an array: a BigQuery table does not have to be flat
- to understand why, in a columnar store, the choice of columns decides how much is read
The signal that was not noise
The epilogue of the previous expedition ended with a faint signal from the dead sector. For eight weeks the station wrote it off as an echo. Today the receiver caught it again — and this time it carried a header.
The Far Archive — the columnar mega-vault of the last Earth colony. Not a shop's storefront and not an analytics warehouse: a log of the colony's LIFE across years, far too large to read row by row.
The archive has a feature no database in the course had before: a meter. Every query to it feeds on the energy of a dying donor station, and the meter charges that energy for every byte read. You cannot simply "look at all the data" here — you will have to learn to ask precisely.
QUERY stares at the header longer than usual and, for the first time in two courses, says:
I do not know this . We learn it together. But one rule I got straight away: this archive charges money for every byte it reads.
A table address: three names instead of one
In Postgres you wrote FROM users — and the database looked for the table in the current schema. In BigQuery the full table address has three tiers:
SELECT * FROM `project.dataset.table`
- project — whose archive this is (and whose account pays for the query);
- — a section of the archive, the analogue of a schema;
- table — the table itself.
Backticks ` are not decoration: without them a name with a hyphen or a dot falls apart into pieces. Wrapping the full address in backticks is the first habit worth picking up.
The course sandbox is already connected TO THE ARCHIVE'S DATASET, so the short table name is enough in the cells below — as if you had already run USE dataset. You will need the full address once there are many tables and they live in different sections.
The archive's first table is fa_users, the register of the colony's residents. Run it:
* is appropriate here: the table is a teaching one, and we need its whole shape. You do not ask a working log that way, and why — that is the next lesson. Look closely at the tags column.Why this archive is columnar
In a row store (Postgres, MySQL) data lies in rows: reading one row is cheap, reading one column out of a billion rows is expensive, because the disk drags whole rows in anyway.
BigQuery stores data in columns: the status values of all the orders lie next to each other, apart from items and total. Two consequences follow, and they will overturn your habits:
- A query pays only for the columns it READS.
SELECT name FROM fa_userstouches one column;SELECT *touches every one. - "Fetch a single row by id" is the worst case here, not the best: there are no indexes, and the archive was built for questions asked "across all the rows at once".
The station's meter draws energy by exactly that rule — by the columns read. The full price list is in the next lesson; for now remember the main thing: SELECT * in BigQuery is not laziness, it is an invoice.
SELECT * EXCEPT — hide a column without listing the rest
In Postgres, to print "every column but one" you listed all the others by hand. In a table of 40 columns that is 39 lines of pain.
BigQuery solves it with a single construct:
SELECT * EXCEPT(tags) FROM fa_users
"Every column except tags". A list works too: EXCEPT(tags, joined_at). Try it:
SELECT * REPLACE — substitute on the fly
The second construct of the same family: keep every column, but substitute an expression for one of them, leaving the rest alone:
SELECT * REPLACE(UPPER(status) AS status) FROM fa_orders
The name after AS must match the name of an existing column — REPLACE substitutes, it does not add. Run it and look at the status column: REPLACE swapped its values, and the other columns stayed where they were.
An array in a cell: the table is no longer flat
Go back to the first output: the tags column held not strings but lists of strings. In Postgres you would have moved them into a separate user_tags table and connected it with a . BigQuery lets a column have the type ARRAY<STRING> — the whole list lives right inside the cell.
ARRAY_LENGTH measures the length of the list:
SELECT name, ARRAY_LENGTH(tags) AS n FROM fa_users
Which of the colonists carry the most tags — and which carry none at all?
SELECT id, name, city, phone, email, plan, created_at, updated_at, source, score, manager, note FROM leads — every column except raw_payload. How do you say that in BigQuery in a single line?SELECT ARRAY_LENGTH(tags) FROM fa_users return for a colonist whose tags were never filled in — the cell holds an empty array []?Cliffhanger: how to unfold an array into rows
A list in a cell is convenient right up to the moment you want to count by element: how many colonists carry the tag "engineer"? For that the array has to be unfolded into rows — in Postgres that was unnest() in a lateral join, in BigQuery it is a whole idiom, CROSS JOIN UNNEST, and it hides a catch that silently loses rows.
The chapter "Arrays and structs" is devoted to it. But before the archive lets you go any deeper, it will demand that you understand its meter — the next lesson is about what exactly you are paying for.
Lock in today's material on the practice tasks:
Principais pontos
- the full table address:
`project.dataset.table`in backticks; the sandbox already stands inside the - BigQuery stores data in columns: you pay for the columns read, and
SELECT *is the most expensive way to ask SELECT * EXCEPT(a, b)— every column but the named ones;SELECT * REPLACE(expr AS col)— substitution on the fly- a column can be an array:
ARRAY<STRING>lives inside the cell,ARRAY_LENGTHmeasures its length, an empty array ≠
Next — the archive's meter: what the bytes are charged for and why LIMIT does not save you.