Big Data Sql

By the end of this lesson you'll be able to run the same SQL you already know across terabytes and petabytes of data — using distributed engines like Hive, Spark SQL, and Presto/Trino, and the laptop-sized powerhouse DuckDB — and you'll know why columnar storage and partitioning make those queries fast.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Imagine billions of these rows spread across thousands of files in a data lake, one folder per day. Every query in this lesson runs against this events shape — a tiny preview is shown here so you can picture the columns.

Big data just means data too large or fast for a single database to handle comfortably — so the work is spread across many machines. The good news: you query it with the SQL you already know.

A normal database (PostgreSQL, MySQL) runs your query on one machine . A distributed query engine splits the work across a cluster of machines that each scan part of the data, then combines the partial answers. You still write SELECT … GROUP BY … ; the engine handles the splitting.

The big four you'll meet: Apache Hive (SQL compiled into batch jobs over Hadoop — slow but bottomless), Spark SQL (the same idea but in memory, far faster for interactive work), Presto/Trino (a "federated" MPP engine — MPP = massively parallel processing — that can join across many different sources at once), and DuckDB (not distributed at all, but astonishingly fast on a single machine for files up to ~100 GB).

A single database is one librarian searching one library. A distributed engine is a thousand librarians, each searching one wing, then handing their slips to a head librarian who tallies the total. Same request ("how many books on SQL?"), wildly more shelves — and it finishes in about the same time because everyone works at once.

A traditional database stores data row by row : all of row 1's values together, then all of row 2's. That's perfect when you fetch whole records one at a time. Analytics is the opposite — you usually want one or two columns across millions of rows (e.g. SUM(amount) ).

Columnar formats like Parquet and ORC store all of one column's values together. So SUM(amount) reads only the amount column and skips the other 49 columns on disk. Columns also compress brilliantly (similar values sit next to each other), so files are smaller and faster to read.

An EXTERNAL table is a SQL definition layered over files that already exist in storage (HDFS, S3). Hive doesn't copy or own the data — it just describes the columns so you can query the files with SQL. Drop the table and the files stay.

Partitioning physically splits the data into folders by a column — here, one folder per event_date . When your WHERE filters on that column, the engine reads only the matching folders. That's partition pruning (a form of predicate pushdown : the filter is pushed down to the storage layer so unneeded data is never read). Filtering one day out of a year can mean scanning 1 folder instead of 365.

Fill in the blanks so the query prunes to a single day's folder and totals revenue per event_type . The expected result is in the comments so you can check yourself.

Spark SQL runs the same standard SQL but keeps data in memory across the cluster, which makes repeated, interactive queries far faster than Hive's batch jobs. You register your files as a temporary view once, then query them like any table — window functions, CTEs, and joins all work.

Notice APPROX_COUNT_DISTINCT : at billions of rows, an exact distinct count is expensive, so big-data engines offer fast approximate versions (a couple of percent error) that finish in a fraction of the time.

DuckDB is the "SQLite for analytics": a single embedded engine, no server, that reads CSV and Parquet files directly . There's no loading step — you point read_parquet('…') (or read_csv_auto('…') ) at a file or a wildcard of files and query them immediately.

Because Parquet is columnar, DuckDB reads only the columns your query touches — exactly the storage win from Section 2, now in one line on your laptop. For datasets up to ~100 GB it's often faster than spinning up a cluster, and it costs nothing to run.

One concept this time — name the DuckDB function that reads Parquet files, and the count function for the body.

Presto/Trino is a federated MPP engine: it can join data living in different systems in a single SQL statement — a Postgres table, files in a Hive lake, even a document store — without first copying everything into one place. You reference each source by a catalog.schema.table name.

When should distributed SQL beat a single database? When the data no longer fits (or no longer fits affordably ) on one machine, when it spans multiple systems, or when scans are so large that splitting them across a cluster finishes work a lone server never could. For everyday app data — frequent small reads and writes — a single relational database is still simpler, cheaper, and faster.

Q: Do I have to learn a new language for big data?

No — that's the whole point. Hive, Spark SQL, Presto/Trino, DuckDB, and BigQuery all speak standard SQL. Your SELECT … GROUP BY … JOIN skills transfer directly; only the execution engine changes.

Q: When should I use DuckDB instead of a cluster?

If your data fits on one machine (roughly up to ~100 GB of Parquet), DuckDB is usually faster, simpler, and free — no cluster to manage. Reach for Spark/Presto when the data outgrows a single node or spans many systems.

Q: What's the real difference between Parquet and CSV?

CSV is row-oriented text: every query reads every column and there's no compression. Parquet is columnar and compressed, so analytics read only the needed columns and far fewer bytes. For big-data SQL, prefer Parquet (or ORC).

No. They're built for big scans and aggregation, not single-row lookups or constant updates. Keep your transactional app on PostgreSQL/MySQL; use big-data SQL for the analytics on top of it.

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then run it in DuckDB (or any SQL playground with sample data) to confirm.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary