E-MapReduce:Use DuckDB in a Notebook

Background

DuckDB is a lightweight, high-performance embedded database engine optimized for Online Analytical Processing (OLAP) scenarios.

• Features

  • Embedded architecture: Runs as a library within your application without a separate server, similar to SQLite. Supports both in-memory and on-disk modes.

  • Columnar storage: Stores data by column to optimize aggregate queries and scan performance.

  • Vectorized execution: Processes data in batches using SIMD instructions to reduce CPU overhead.

  • Standard compliance: Supports SQL-92 and SQL:2011 standards, including CTEs, window functions, JOINs (including ASOF JOIN), and subqueries.

  • Direct data access: Queries CSV, Parquet, and JSON files directly without an import step.

  • Zero-copy integration: Seamlessly converts to and from in-memory data structures like Pandas and Arrow, avoiding data migration overhead.

  • Federated query: Uses the httpfs extension to access remote files (such as those on S3) or connect to external databases like PostgreSQL for federated queries.

• Use cases

  • Interactive analysis: Quickly processes datasets from gigabytes to terabytes, serving as an alternative to Pandas or Excel for large-scale data.

  • Edge computing: Can be embedded in endpoint devices for local data analysis.

  • Data science: Integrates seamlessly with the Python and R ecosystems, acting as a data preprocessing engine for machine learning.

  • Real-time OLAP: Supports analytical workloads that require both frequent updates and complex queries.

Limitations

• Version requirements:

  • esr-4.x: esr-4.7.0 and later.

  • esr-3.x: esr-3.6.0 and later.

• The following limitations apply when reading Paimon tables with DuckDB:

  • Operation types: You can only read from Paimon append-only tables. Reading from a primary key table and write operations are not supported.

  • Format requirement: Only Paimon tables with file.format = 'parquet' are supported.

Procedure

1. In the Alibaba Cloud EMR Serverless Spark console, click Development in the left-side navigation pane.

2. Create a Notebook.

   1. On the Development tab, click the icon.

   2. In the dialog box, enter a name, for Type, select Interactive Development > Notebook, and then click OK.

3. In the upper-right corner, select a running Notebook session.

   You can also select Create Notebook Session from the drop-down list to create a new Notebook session. For more information, see Manage Notebook sessions.

   Note

   Because DuckDB is a single-node SQL engine and uses only the resources of the Notebook Spark driver, we recommend that you increase the spark.driver.cores and spark.driver.memory resource configurations.

4. In the Python code of a Notebook, you can use DuckDB by running the import duckdb command. The following are several common methods for using DuckDB.

   Read files

   • View the content of Parquet, CSV, and JSON files.

     import duckdb
     # View the content of a Parquet file
     duckdb.sql("SELECT * FROM read_parquet('oss://<bucket-name>/path/to/test.parquet')")
     # View the content of a CSV file
     duckdb.sql("SELECT * FROM read_csv('oss://<bucket-name>/path/to/test.csv')")
     # View the content of a JSON file
     duckdb.sql("SELECT * FROM read_json('oss://<bucket-name>/path/to/test.json')")

   • View the schema of a Parquet file.

     import duckdb
     # View the schema of a Parquet file
     duckdb.sql("SELECT * FROM parquet_schema('oss://<bucket-name>/path/to/test.parquet')")

     ┌─────────────┬───────────────┬────────────┬─────────────┬───────┬───────────┬──────────┬──────────────┐
     │  file_name  │     name      │    type    │ type_length │ scale │ precision │ field_id │ logical_type │
     │   varchar   │   varchar     │  varchar   │   varchar   │ int64 │   int64   │  int64   │   varchar    │
     ├─────────────┼───────────────┼────────────┼─────────────┼───────┼───────────┼──────────┼──────────────┤
     │ oss://xxx   │ paimon_schema │ NULL       │ NULL        │ NULL  │ NULL      │ NULL     │ NULL         │
     │ oss://xxx   │ id            │ INT32      │ NULL        │ NULL  │ NULL      │ 0        │ NULL         │
     │ oss://xxx   │ name          │ BYTE_ARRAY │ NULL        │ NULL  │ NULL      │ 1        │ StringType() │
     └─────────────┴───────────────┴────────────┴─────────────┴───────┴───────────┴──────────┴──────────────┘

   • Export the content of a Parquet file to a CSV file.

     import duckdb
     # Export the content of a Parquet file to a CSV file
     duckdb.sql("COPY (SELECT * FROM read_parquet('oss://<bucket-name>/path/to/test.parquet')) TO 'oss://<bucket-name>/path/to/output.csv' (HEADER, DELIMITER ',');")

   Note

   Before you read from or write to OSS files, make sure that the OSS service is activated, a bucket is created, and you have the required permissions to access the bucket.

   Access DLF catalog

   Note

   DLF catalogs created through data sharing are not supported.

   • View the mounted DLF catalogs in the current workspace. For more information, see Manage data catalogs.

     import duckdb
     duckdb.sql("show databases")

     import duckdb
     duckdb.sql("show databases")
     ┌───────────────┐
     │ database_name │
     │    varchar    │
     ├───────────────┤
     │ memory        │
     │ test_paimon   │
     └───────────────┘

   • Query data from a table in a DLF catalog.

     import duckdb
     duckdb.sql("use <catalogName>.<databaseName>")
     duckdb.sql("select * from <tableName> limit 10")

     import duckdb
     duckdb.sql("use test_paimon.default")
     duckdb.sql("select * from orders limit 10")
     ┌──────────┬───────┬──────────┐
     │ order_id │ price │ customer │
     │  int64   │ int64 │ varchar  │
     ├──────────┼───────┼──────────┤
     │     1002 │   199 │ Bob      │
     │     1003 │   450 │ Charlie  │
     │     1004 │   780 │ Diana    │
     └──────────┴───────┴──────────┘

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