{"id":121901,"date":"2025-02-13T14:22:07","date_gmt":"2025-02-13T12:22:07","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/reliz-subd-duckdb-1-2-0"},"modified":"2025-02-13T14:22:07","modified_gmt":"2025-02-13T12:22:07","slug":"reliz-subd-duckdb-1-2-0","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/news\/reliz-subd-duckdb-1-2-0","title":{"rendered":"Release of DuckDB Database 1.2.0","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
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. <\/p>\n
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. <\/p>\n
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. <\/p>\n
In the new version: <\/p>\n
\n
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 use the enhanced database file format, it is now possible to bind the database to a version number \u2014 when opening a file with the parameter \u00abSTORAGE_VERSION\u00bb, you can specify the minimum supported database version (\u00abATTACH \u2018file.db\u2019 (STORAGE_VERSION \u2018v1.2.0\u2019);\u00bb). You can use the SQL command COPY to convert the new format to the old one, for example: ATTACH \u2018file1.db\u2019; ATTACH \u2018converted_file.db\u2019 (STORAGE_VERSION \u2018v1.0.0\u2019); COPY FROM DATABASE file1 TO converted_file;\n
Support for the SQL command \u00abALTER TABLE ... ADD PRIMARY KEY\u00bb has been added to add a primary key to an existing table.\n
The restriction that prevented the re-adding of identifiers for which there is a uniqueness tracking index has been removed if those identifiers were deleted in the current transaction. For example, the following SQL code now does not produce an error: CREATE TABLE students (id INTEGER PRIMARY KEY, name VARCHAR); INSERT INTO students VALUES (1, \u2018John Doe\u2019); BEGIN; DELETE FROM students WHERE id = 1; INSERT INTO students VALUES (1, \u2018Jane Doe\u2019);\n
Support has been added for loading CSV files in Latin-1 and UTF-16 encodings (previously only UTF-8 encoding was supported). FROM read_csv(\u2018cities-latin-1.csv\u2019, encoding = \u2018latin-1\u2019);\n
Support has been added for using multi-byte delimiters (up to 4 bytes) in CSV files, allowing for the use of emojis as column delimiters. a\ud83d\udc69\u200d\ud83d\udcbbb hello\ud83d\udc69\u200d\ud83d\udcbbworld FROM read_csv(\u2018example.dsv\u2019, sep = \u2018\ud83d\udc69\u200d\ud83d\udcbb\u2019);\n
Strict CSV file parsing mode is enabled by default (\u2018strict_mode = true\u2019), which checks adherence to the formatting specification of RFC 4180. In strict mode, only a single character can be used to separate lines, which will result in an error when trying to parse files that use not only a newline character but also a carriage return character (\u2018\r\n\u2019) for line separation.\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%.\n
The size limit for lines in CSV files has been removed (previously, a line could not exceed 8 MB).\n
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.\n
A safe mode has been added to the command line interface, activated using the option \u2018-safe\u2019 or the command \u2018.safe_mode\u2019. In this mode, access is only allowed to the originally specified database file, and attempting to open other files will result in an error message.\n
The input autocompletion in the command line interface has been improved. The code for autocompletion has been transitioned to use PEG (Parsing Expression Grammar).\n
When executing commands in the command line interface, there is a visual representation of large numbers. For example, the number 100000000 will be displayed as (100 million).\n
SQL now supports a syntax where abbreviated table and expression names can be used before the values they refer to (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;\n
The 'SELECT' command now supports the 'RENAME' operation for renaming fields output when using the '*' expression: SELECT * RENAME (col1 AS new_col1) FROM integers;\n
In the 'SELECT' command, the use of the 'LIKE' and 'SIMILAR TO' operations is allowed when outputting through '*': SELECT * LIKE 'val%' FROM key_val;\n
The quality of pseudo-random number generation has been improved.\n
The query optimizer has been modernized. Performance during the TPC-H SF100 test improved by 13%.\n
A new C-like API has been proposed for extension development, which can be used, for instance, to create new aggregate or table functions.\n
Support has been added for systems using the standard C library Musl. <\/ul>\n