The release of DuckDB 1.4.0 has been published, aimed at executing analytical queries and conceptually similar to SQLite. DuckDB combines properties of SQLite, such as compactness, library integration, database storage in a single file, and a CLI interface, with capabilities and optimizations for executing analytical queries that cover a significant portion of stored data, such as performing aggregations of entire table contents or merging several large tables. The project code is written in C++ and is distributed under the MIT License.
DuckDB offers an extended SQL dialect that includes additional features for handling very complex and time-consuming queries. It allows the use of complex types (arrays, structures, unions) and enables arbitrary and nested correlated subqueries. Support for the simultaneous execution of multiple queries and direct execution of queries from files in CSV and Parquet formats is also provided. Import support from PostgreSQL databases is available.
The project uses a shell from SQLite, a parser from PostgreSQL, the Date Math component from MonetDB, its own implementation of window functions (based on the Segment Tree Aggregation algorithm), a regular expression handler based on the RE2 library, and its own query optimizer, MVCC Mechanism for managing concurrent execution of tasks (Multi-Version Concurrency Control), as well as a vectorized query execution engine based on the Hyper-Pipelining Query Execution algorithm, allowing it to process large sets of values in a single operation.
In the new version:
- Support for storing database files in encrypted form has been added. The AES-256 algorithm in GCM mode is used for encryption. Not only the main data file, but also WAL logs and temporary files are encrypted. The keys for database encryption are specified by the ATTACH command via the ENCRYPTION_KEY parameter. ATTACH 'encrypted.db' AS enc_db (ENCRYPTION_KEY 'quack_quack');
- Support for the 'MERGE INTO' command has been added, which can serve as an alternative to the 'INSERT ... ON CONFLICT' expression, requires no primary key, and works with arbitrary merging conditions. The 'MERGE INTO' command allows the creation of conditional SQL expressions that combine INSERT, UPDATE, and DELETE operations in one expression. For example, with MERGE, you can merge two tables, inserting missing records and updating existing ones. WITH deletes(item_id, delete_threshold) AS (VALUES (10, 3000)) MERGE INTO Stock USING deletes USING (item_id) WHEN MATCHED AND balance < delete_threshold THEN DELETE RETURNING merge_action, *;
- Additionally, support for write operations on tables in the Apache Iceberg format has been added (previously only read operations were supported), allowing data to be transferred from Iceberg to DuckDB and back.
- The command line client has been enhanced with a progress indicator for operations with predicted time remaining until completion.
- A window function 'FILL' has been added, which can be used to interpolate missing values in ordered windows. FROM (VALUES (1, 1), (2, NULL), (3, 42)) t(c1, c2) SELECT fill(c2) OVER (ORDER BY c1) f; 1 21 42
- The Teradata Connector plugin has been implemented for connecting to Teradata databases. This plugin allows manipulation of tables, executing queries, and running SQL commands directly in Teradata using DuckDB.
- Added support for checkpointing in-memory tables, enabling compression support. Additionally, during checkpointing, it cleans up deleted rows and frees up space after delete operations. ATTACH ‘:memory:’ AS memory_compressed (COMPRESS);
- Several performance optimizations have been proposed: The implementation of sorting has been translated to use the k-way merge sort algorithm, reducing data movement. Common Table Expressions (CTE) are now materialized by default.
Source: opennet.ru
