The release of DuckDB version 1.2.0 has been published, focusing on analytic query execution and conceptually resembling SQLite. DuckDB combines features of SQLite, such as compactness, embeddable library connectivity, single-file database storage, and a CLI interface, with capabilities and optimizations for executing analytical queries that cover a significant portion of stored data, such as aggregating all table contents or merging multiple 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 new compression methods has been implemented, which are not enabled by default to maintain compatibility of database files with previous versions of DuckDB. To utilize the enhanced format of database files, a mechanism to bind the database to a version number has been proposed — when opening a file using the 'STORAGE_VERSION' parameter, one can now specify the minimum supported database version ('ATTACH ‘file.db’ (STORAGE_VERSION ‘v1.2.0’);'). The SQL command COPY can be used to convert from the new format to the old one, for example: ATTACH ‘file1.db’; ATTACH ‘converted_file.db’ (STORAGE_VERSION ‘v1.0.0’); COPY FROM DATABASE file1 TO converted_file;
- Support for the SQL command 'ALTER TABLE … ADD PRIMARY KEY' has been added to allow the addition of a primary key to an existing table.
- The restriction that prevented the re-adding of identifiers for which a unique tracking index existed has been removed, if those identifiers were deleted in the current transaction. For example, the following SQL code now does not lead to an error: CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR); INSERT INTO students VALUES (1, ‘John Doe’); BEGIN; DELETE FROM students WHERE id = 1; INSERT INTO students VALUES (1, ‘Jane Doe’);
- Support for uploading CSV files in Latin-1 and UTF-16 encodings has been added (previously only UTF-8 encoding was supported). FROM read_csv(‘cities-latin-1.csv’, encoding = ‘latin-1’);
- Support for using multi-byte delimiters (up to 4 bytes) in CSV files has been added, allowing the use of emoji as column delimiters. a🦆b hello🦆world FROM read_csv(‘example.dsv’, sep = ‘🦆’);
- Strict mode for parsing CSV files is now enabled by default (‘strict_mode = true’), checking for compliance with the RFC 4180 specification. In strict mode, only one character is allowed for line separation, leading to an error when attempting to parse files that use not only the newline character but also the carriage return character (‘\r\n’).
- A new algorithm for determining new lines has been implemented in the CSV parser, supporting parallel operations. The use of the new algorithm has improved CSV parsing speed by approximately 15%.
- The size limit for lines in CSV files has been removed (previously, a line could not exceed 8 MB).
- When exporting data in Parquet format, support for hashes (dictionaries) and Bloom filter-based indexes has been implemented. The DELTA_BINARY_PACKED compression method has been introduced, significantly reducing the size of Parquet files.
- A safe mode has been added to the command line interface, activated via the ‘-safe’ option or the ‘.safe_mode’ command. In this mode, access is permitted only to the initially specified database file, and attempts to open other files will result in an error message.
- The input autocompletion in the command line interface has been improved. The code for autocompletion has been transitioned to use PEG (Parsing Expression Grammar).
- When executing commands in the command line interface, a visual representation of large numbers has been implemented; for example, when displaying the number 100000000, it will additionally show ‘(100 million)’.
- Support for a syntax in SQL has been added, allowing abbreviated names for tables and expressions to be specified before the values to which they refer (instead of using the syntax ‘expression AS name’): SELECT e1: some_long_and_winding_expression, e2: t2.a_column_name FROM t1: long_schema.some_long_table_name, t2: short_s.tbl;
- The "SELECT" command now supports the "RENAME" operation to rename fields displayed when specifying the expression "*": SELECT * RENAME (col1 AS new_col1) FROM integers;
- The "SELECT" command allows the use of "LIKE" and "SIMILAR TO" operations when outputting via "*": SELECT * LIKE 'val%' FROM key_val;
- The quality of pseudo-random number generation has been improved.
- The query optimizer has been modernized. Performance during the TPC-H SF100 test improved by 13%.
- A new C-like API has been proposed for extension development, which can be used, for instance, to create new aggregate or table functions.
- Support has been added for systems using the standard C library Musl.
Source: opennet.ru
