Image by Editor | Midjourney
Parquet is a powerful, columnar storage format for faster and more efficient data analysis. You can use DuckDB, an in-memory analytical database, to work with—run queries on and analyze—Parquet files.
In this article, we’ll walk through how to analyze a restaurant orders dataset stored in a Parquet file using DuckDB.
Let’s get started!
🔗 Link to the code on GitHub
Installing DuckDB and Getting Started
First, install DuckDB if you haven’t already:
$ curl https://install.duckdb.org | sh
Now, open your terminal and launch DuckDB. Be sure to use the right path on your machine replacing user with the right user:
$ /home/user/.duckdb/cli/latest/duckdb
If you need help, check the installation guide.
▶️ To follow along, get the parquet file: restaurant_orders.parquet
To preview the first 5 rows of restaurant_orders.parquet, run:
SELECT * FROM read_parquet('restaurant_orders.parquet') LIMIT 5;
This gives us a quick preview of the dataset (truncated output):
┌──────────┬───────────────┬───┬─────────────────────┬────────────────┐ │ order_id │ customer_name │ … │ order_time │ payment_method │ │ int64 │ varchar │ │ varchar │ varchar │ ├──────────┼───────────────┼───┼─────────────────────┼────────────────┤ │ 1 │ Grace │ … │ 2024-02-01 18:00:00 │ PayPal │ │ 2 │ David │ … │ 2024-02-01 18:05:00 │ Credit Card │ │ 3 │ Eve │ … │ 2024-02-01 18:10:00 │ PayPal │ │ 4 │ Grace │ … │ 2024-02-01 18:15:00 │ PayPal │ │ 5 │ Charlie │ … │ 2024-02-01 18:20:00 │ Debit Card │ ├──────────┴───────────────┴───┴─────────────────────┴────────────────┤ │ 5 rows 8 columns (4 shown) │ └─────────────────────────────────────────────────────────────────────┘
Understanding the Dataset Structure
To understand the schema of our dataset, we can run:
DESCRIBE SELECT * FROM read_parquet('restaurant_orders.parquet') LIMIT 5;
This will show the data types for each column.
┌────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤ │ order_id │ BIGINT │ YES │ NULL │ NULL │ NULL │ │ customer_name │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ table_number │ BIGINT │ YES │ NULL │ NULL │ NULL │ │ menu_item │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ price │ DOUBLE │ YES │ NULL │ NULL │ NULL │ │ quantity │ BIGINT │ YES │ NULL │ NULL │ NULL │ │ order_time │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ payment_method │ VARCHAR │ YES │ NULL │ NULL │ NULL │ └────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
Performing Basic Analysis
Now let’s start analyzing our data.
1. Count the Total Number of Orders
Let’s find out how many orders are in the dataset:
SELECT COUNT(*) AS total_orders FROM read_parquet('restaurant_orders.parquet');
Output:
┌──────────────┐ │ total_orders │ │ int64 │ ├──────────────┤ │ 30 │ └──────────────┘
2. Calculate Total Revenue
Let’s see how much revenue the restaurant generated:
SELECT SUM(price * quantity) AS total_revenue FROM read_parquet('restaurant_orders.parquet');
Output:
┌────────────────────┐ │ total_revenue │ │ double │ ├────────────────────┤ │ 1770.9800000000005 │ └────────────────────┘
3. Find the Most Popular Menu Items
Which items are ordered the most? Let’s group by menu_item and sum the quantities:
SELECT menu_item, SUM(quantity) AS total_quantity
FROM read_parquet('restaurant_orders.parquet')
GROUP BY menu_item
ORDER BY total_quantity DESC
LIMIT 5;
Output:
┌───────────┬────────────────┐ │ menu_item │ total_quantity │ │ varchar │ int128 │ ├───────────┼────────────────┤ │ Pizza │ 16 │ │ Sushi │ 15 │ │ Salad │ 14 │ │ Tacos │ 14 │ │ Soup │ 7 │ └───────────┴────────────────┘
4. Analyze Sales by Payment Method
How do customers pay for their meals? Let’s find out:
SELECT payment_method, COUNT(*) AS order_count
FROM read_parquet('restaurant_orders.parquet')
GROUP BY payment_method
ORDER BY order_count DESC;
Output:
┌────────────────┬─────────────┐ │ payment_method │ order_count │ │ varchar │ int64 │ ├────────────────┼─────────────┤ │ PayPal │ 9 │ │ Credit Card │ 8 │ │ Cash │ 7 │ │ Debit Card │ 6 │ └────────────────┴─────────────┘
Advanced Analysis with Window Functions
Window functions can be helpful to calculate interesting info:
1. Running Total of Revenue Over Time
Let’s find the running revenue:
SELECT order_time, SUM(price * quantity)
OVER (ORDER BY order_time) AS running_revenue
FROM read_parquet('restaurant_orders.parquet');
Output:
┌─────────────────────┬────────────────────┐ │ order_time │ running_revenue │ │ varchar │ double │ ├─────────────────────┼────────────────────┤ │ 2024-02-01 18:00:00 │ 55.28 │ │ 2024-02-01 18:05:00 │ 69.36 │ │ 2024-02-01 18:10:00 │ 128.28 │ │ 2024-02-01 18:15:00 │ 195.75 │ │ 2024-02-01 18:20:00 │ 212.96 │ │ 2024-02-01 18:25:00 │ 339.83000000000004 │ │ 2024-02-01 18:30:00 │ 360.88000000000005 │ │ 2024-02-01 18:35:00 │ 413.80000000000007 │ │ 2024-02-01 18:40:00 │ 472.6400000000001 │ │ 2024-02-01 18:45:00 │ 506.6600000000001 │ │ 2024-02-01 18:50:00 │ 547.7600000000001 │ │ 2024-02-01 18:55:00 │ 556.1100000000001 │ │ 2024-02-01 19:00:00 │ 654.9300000000001 │ │ 2024-02-01 19:05:00 │ 774.1800000000001 │ │ 2024-02-01 19:10:00 │ 816.0000000000001 │ │ 2024-02-01 19:15:00 │ 826.5000000000001 │ │ 2024-02-01 19:20:00 │ 951.6000000000001 │ │ 2024-02-01 19:25:00 │ 1062.0300000000002 │ │ 2024-02-01 19:30:00 │ 1099.8400000000001 │ │ 2024-02-01 19:35:00 │ 1218.9700000000003 │ │ 2024-02-01 19:40:00 │ 1243.9600000000003 │ │ 2024-02-01 19:45:00 │ 1286.2200000000003 │ │ 2024-02-01 19:50:00 │ 1306.6400000000003 │ │ 2024-02-01 19:55:00 │ 1482.0000000000005 │ │ 2024-02-01 20:00:00 │ 1515.0500000000004 │ │ 2024-02-01 20:05:00 │ 1574.7200000000005 │ │ 2024-02-01 20:10:00 │ 1598.3000000000004 │ │ 2024-02-01 20:15:00 │ 1674.2600000000004 │ │ 2024-02-01 20:20:00 │ 1733.1500000000005 │ │ 2024-02-01 20:25:00 │ 1770.9800000000005 │ ├─────────────────────┴────────────────────┤ │ 30 rows 2 columns │ └──────────────────────────────────────────┘
2. Ranking Most Expensive Orders
Now we’ll find the most expensive orders:
SELECT order_id, customer_name, price * quantity AS order_value,
RANK() OVER (ORDER BY price * quantity DESC) AS rank
FROM read_parquet('restaurant_orders.parquet')
LIMIT 5;
Output:
┌──────────┬───────────────┬────────────────────┬───────┐ │ order_id │ customer_name │ order_value │ rank │ │ int64 │ varchar │ double │ int64 │ ├──────────┼───────────────┼────────────────────┼───────┤ │ 24 │ Hannah │ 175.36 │ 1 │ │ 6 │ Hannah │ 126.87 │ 2 │ │ 17 │ David │ 125.10000000000001 │ 3 │ │ 14 │ Charlie │ 119.25 │ 4 │ │ 20 │ Charlie │ 119.13 │ 5 │ └──────────┴───────────────┴────────────────────┴───────┘
Wrapping Up
DuckDB makes working with Parquet files efficient—allowing us to analyze structured data quickly.
In this tutorial, we covered essential queries like counting records, computing revenue, grouping data, and even using window functions.
Try running these queries on your own dataset to get comfortable with DuckDB!
