DuckDB SQL quick start

Scenario

This scenario uses sample e-commerce order data stored in OSS. The data is in CSV format and includes the order ID, customer name, product name, quantity, unit price, and order date. You will perform the following analysis tasks:

• Get an overview of the order data.

• Calculate the total sales amount for each product.

The sample order data (demo.csv) is as follows:

order_id,customer,product,quantity,price,order_date
1001,Alice,Laptop,1,6999.00,2024-01-15
1002,Bob,Wireless Mouse,2,129.00,2024-01-15
1003,Charlie,Mechanical Keyboard,1,459.00,2024-01-16
1004,Alice,Monitor,1,2499.00,2024-01-16
1005,David,Wireless Mouse,3,129.00,2024-01-17
1006,Bob,Laptop,1,6999.00,2024-01-17
1007,Charlie,USB Hub,2,89.00,2024-01-18
1008,Alice,Mechanical Keyboard,1,459.00,2024-01-18

Prerequisites

• You have created an EMR Serverless Spark workspace. For instructions, see Create a workspace.

• You have activated OSS and created a bucket. Ensure the execution role for your EMR Serverless Spark workspace has permission to access the bucket.

Step 1: Create a DuckDB session

1. Log on to the E-MapReduce console.

2. In the left-side navigation pane, choose EMR Serverless > Spark.

3. On the Spark page, click the name of your target workspace.

4. In the left-side navigation pane, click Sessions.

5. On the Sessions page, click the DuckDB Sessions tab.

6. Click Create DuckDB Session, enter a session name such as duckdb-demo, select a deployment queue and an engine version, specify the resource configuration, and click Create.

7. Wait for the session status to become Running.

For more information about session configuration, see DuckDB session.

Step 2: Create a DuckDB SQL task

1. In the left-side navigation pane, click Development.

2. Click Create Task. In the dialog box, select Interactive Development > DuckDB SQL, enter a task name such as orders-analysis, and click OK.

3. In the upper-right corner, select the duckdb-demo session.

Step 3: View the order data

In the task editor, run the following SQL statements to read the CSV file from OSS and view the data. Replace the OSS path with your actual path.

1. View all the order data.

   SELECT * FROM 'oss://your-bucket/demo/orders.csv';

   The query returns the following data:

   order_id	customer	product	quantity	price	order_date
   1001	Alice	Laptop	1	6999.00	2024-01-15
   1002	Bob	Wireless Mouse	2	129.00	2024-01-15
   1003	Charlie	Mechanical Keyboard	1	459.00	2024-01-16
   1004	Alice	Monitor	1	2499.00	2024-01-16
   1005	David	Wireless Mouse	3	129.00	2024-01-17
   1006	Bob	Laptop	1	6999.00	2024-01-17
   1007	Charlie	USB Hub	2	89.00	2024-01-18
   1008	Alice	Mechanical Keyboard	1	459.00	2024-01-18

2. View the total number of records and the date range.

   SELECT
     count(*) AS total_orders,
     count(DISTINCT customer) AS total_customers,
     count(DISTINCT product) AS total_products,
     min(order_date) AS first_date,
     max(order_date) AS last_date
   FROM 'oss://your-bucket/demo/orders.csv';

   Expected result:

   total_orders	total_customers	total_products	first_date	last_date
   8	4	5	2024-01-15	2024-01-18

Step 4: Analyze the order data

1. Calculate the total sales amount for each product and sort the results by sales amount in descending order.

   SELECT
     product AS product_name,
     sum(quantity) AS total_qty,
     sum(quantity * price) AS total_amount
   FROM 'oss://your-bucket/demo/orders.csv'
   GROUP BY product
   ORDER BY total_amount DESC;

   Expected result:

   product_name	total_qty	total_amount
   Laptop	2	13998.00
   Monitor	1	2499.00
   Mechanical Keyboard	2	918.00
   Wireless Mouse	5	645.00
   USB Hub	2	178.00

2. Analyze customer spending.

   SELECT
     customer AS customer_name,
     count(*) AS order_count,
     sum(quantity * price) AS total_spent
   FROM 'oss://your-bucket/demo/orders.csv'
   GROUP BY customer
   ORDER BY total_spent DESC;

   Expected result:

   customer_name	order_count	total_spent
   Alice	3	9957.00
   Bob	2	7257.00
   Charlie	2	637.00
   David	1	387.00

Next steps

• For a complete overview of DuckDB SQL development features, see Develop DuckDB SQL jobs.

• For details on configuring and managing DuckDB sessions, see DuckDB session.

• For details on managing and using the data catalog, see Manage a data catalog.

• For more information about DuckDB features, see the DuckDB official documentation.