--- title: 'Databend Monthly (Oct 2024)' date: 2024-10-31 slug: category-weekly/monthly-oct2024 cover_url: 'monthly/oct2024/cover.png' description: "We're excited to bring you the latest updates, new features, and improvements for October 2024 in Databend! We hope these enhancements are helpful, and we look forward to your feedback." tags: [databend, monthly] category: Weekly authors: - Eric --- We're excited to bring you the latest updates, new features, and improvements for October 2024 in Databend! We hope these enhancements are helpful, and we look forward to your feedback. {/* truncate */} ### Databend Cloud: Multi-Cluster Warehouses A multi-cluster warehouse automatically adjusts compute resources by adding or removing clusters based on workload demand. It ensures high concurrency and performance while optimizing cost by scaling up or down as needed. ![How Multi-Cluster Warehouse Works](../static/img/blog/monthly/oct2024/mc.png) You can enable Multi-Cluster for a warehouse when you create it and set the maximum number of clusters that the warehouse can scale up to. For more information, see [Multi-Cluster Warehouses](https://docs.databend.com/guides/cloud/using-databend-cloud/warehouses#multi-cluster-warehouses). ### Unsetting FUSE Engine Options You can now unset Fuse Engine options for a table with the command [ALTER TABLE OPTIONS](https://docs.databend.com/sql/sql-commands/ddl/table/alter-table-option), reverting them to their default values. ```sql title='Syntax:' ALTER TABLE [ . ] UNSET OPTIONS () ``` ```sql title='Examples:' ALTER TABLE fuse_table UNSET OPTIONS (block_per_segment, data_retention_period_in_hours); SHOW CREATE TABLE fuse_table; -[ RECORD 1 ]----------------------------------- Table: fuse_table Create Table: CREATE TABLE fuse_table ( a INT NULL ) ENGINE=FUSE COMPRESSION='lz4' STORAGE_FORMAT='native' ``` ### New Options for Data Unloading New copy options have been introduced for the [COPY INTO](https://docs.databend.com/sql/sql-commands/dml/dml-copy-into-location#copyoptions) command: - OVERWRITE: When `true`, existing files with the same name at the target path will be overwritten. Note: `OVERWRITE = true` requires `USE_RAW_PATH = true` and `INCLUDE_QUERY_ID = false`. - INCLUDE_QUERY_ID: When `true`, a unique UUID will be included in the exported file names. - USE_RAW_PATH: When `true`, the exact user-provided path (including the full file name) will be used for exporting the data. If set to `false`, the user must provide a directory path. ### Handling Invalid Date & Time Values Databend automatically converts invalid Date or Timestamp values to their minimum valid equivalents, `1000-01-01` for dates and `1000-01-01 00:00:00` for timestamps, ensuring consistency when working with out-of-range or incorrectly formatted dates and timestamps. ```sql title='Examples:' -- Attempts to add one day to the maximum date, exceeding the valid range. -- Result: Returns DateMIN (1000-01-01) instead of an error. SELECT ADD_DAYS(TO_DATE('9999-12-31'), 1); ┌────────────────────────────────────┐ │ add_days(to_date('9999-12-31'), 1) │ ├────────────────────────────────────┤ │ 1000-01-01 │ └────────────────────────────────────┘ -- Attempts to subtract one minute from the minimum date, which would be invalid. -- Result: Returns DateMIN (1000-01-01 00:00:00), ensuring stability in results. SELECT SUBTRACT_MINUTES(TO_DATE('1000-01-01'), 1); ┌────────────────────────────────────────────┐ │ subtract_minutes(to_date('1000-01-01'), 1) │ ├────────────────────────────────────────────┤ │ 1000-01-01 00:00:00 │ └────────────────────────────────────────────┘ ``` ### New Date Function: DATE_DIFF [DATE_DIFF](https://docs.databend.com/sql/sql-functions/datetime-functions/date-diff) calculates the difference between two dates or timestamps based on a specified time unit. The result is positive if the `` is after the ``, and negative if it's before. ```sql title='Examples:' SELECT DATE_DIFF(HOUR, YESTERDAY(), TODAY()); ┌───────────────────────────────────────┐ │ DATE_DIFF(HOUR, yesterday(), today()) │ ├───────────────────────────────────────┤ │ 24 │ └───────────────────────────────────────┘ ``` ### New Aggregate Function: MODE [MODE](https://docs.databend.com/sql/sql-functions/aggregate-functions/aggregate-mode) returns the value that appears most frequently in a group of values. ```sql title='Examples:' SELECT MONTH(sale_date) AS month, MODE(product_id) AS most_sold_product FROM sales GROUP BY month ORDER BY month; ┌─────────────────────────────────────┐ │ month │ most_sold_product │ ├─────────────────┼───────────────────┤ │ 1 │ 101 │ │ 2 │ 102 │ └─────────────────────────────────────┘ ``` ### New JSON Functions We introduced a bunch of [JSON functions](https://docs.databend.com/sql/sql-functions/semi-structured-functions/) to make handling and manipulating JSON data in Databend easier. - JSON_ARRAY_DISTINCT: Removes duplicate elements from a JSON array and returns an array with only distinct elements. ```sql title='Examples:' SELECT JSON_ARRAY_DISTINCT('["apple", "banana", "apple", "orange", "banana"]'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_distinct('["apple", "banana", "apple", "orange", "banana"]'::VARIANT): ["apple","banana","orange"] ``` - JSON_ARRAY_EXCEPT: Returns a new JSON array containing the elements from the first JSON array that are not present in the second JSON array. ```sql title='Examples:' SELECT JSON_ARRAY_EXCEPT( '["apple", "banana", "orange"]'::JSON, '["banana", "grapes"]'::JSON ); -[ RECORD 1 ]----------------------------------- json_array_except('["apple", "banana", "orange"]'::VARIANT, '["banana", "grapes"]'::VARIANT): ["apple","orange"] -- Return an empty array because all elements in the first array are present in the second array. SELECT json_array_except('["apple", "banana", "orange"]'::VARIANT, '["apple", "banana", "orange"]'::VARIANT) -[ RECORD 1 ]----------------------------------- json_array_except('["apple", "banana", "orange"]'::VARIANT, '["apple", "banana", "orange"]'::VARIANT): [] ``` - JSON_ARRAY_FILTER: Filters elements from a JSON array based on a specified Lambda expression, returning only the elements that satisfy the condition. ```sql title='Examples:' SELECT JSON_ARRAY_FILTER( ['apple', 'banana', 'avocado', 'grape']::JSON, d -> d::String LIKE 'a%' ); -[ RECORD 1 ]----------------------------------- json_array_filter(['apple', 'banana', 'avocado', 'grape']::VARIANT, d -> d::STRING LIKE 'a%'): ["apple","avocado"] ``` - JSON_ARRAY_INSERT: Inserts a value into a JSON array at the specified index and returns the updated JSON array. ```sql title='Examples:' -- The new element is inserted at position 0 (the beginning of the array), shifting all original elements to the right SELECT JSON_ARRAY_INSERT('["task1", "task2", "task3"]'::VARIANT, 0, '"new_task"'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_insert('["task1", "task2", "task3"]'::VARIANT, 0, '"new_task"'::VARIANT): ["new_task","task1","task2","task3"] -- The new element is inserted at position 1, between task1 and task2 SELECT JSON_ARRAY_INSERT('["task1", "task2", "task3"]'::VARIANT, 1, '"new_task"'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_insert('["task1", "task2", "task3"]'::VARIANT, 1, '"new_task"'::VARIANT): ["task1","new_task","task2","task3"] -- If the index exceeds the length of the array, the new element is appended at the end of the array SELECT JSON_ARRAY_INSERT('["task1", "task2", "task3"]'::VARIANT, 6, '"new_task"'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_insert('["task1", "task2", "task3"]'::VARIANT, 6, '"new_task"'::VARIANT): ["task1","task2","task3","new_task"] -- The new element is inserted just before the last element (task3) SELECT JSON_ARRAY_INSERT('["task1", "task2", "task3"]'::VARIANT, -1, '"new_task"'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_insert('["task1", "task2", "task3"]'::VARIANT, - 1, '"new_task"'::VARIANT): ["task1","task2","new_task","task3"] -- Since the negative index exceeds the array’s length, the new element is inserted at the beginning SELECT JSON_ARRAY_INSERT('["task1", "task2", "task3"]'::VARIANT, -6, '"new_task"'::VARIANT); -[ RECORD 1 ]----------------------------------- json_array_insert('["task1", "task2", "task3"]'::VARIANT, - 6, '"new_task"'::VARIANT): ["new_task","task1","task2","task3"] ``` - JSON_ARRAY_INTERSECTION: Returns the common elements between two JSON arrays. ```sql title='Examples:' -- Find the intersection of two JSON arrays SELECT json_array_intersection('["Electronics", "Books", "Toys"]'::JSON, '["Books", "Fashion", "Electronics"]'::JSON); -[ RECORD 1 ]----------------------------------- json_array_intersection('["Electronics", "Books", "Toys"]'::VARIANT, '["Books", "Fashion", "Electronics"]'::VARIANT): ["Electronics","Books"] -- Find the intersection of the result from the first query with a third JSON array using an iterative approach SELECT json_array_intersection( json_array_intersection('["Electronics", "Books", "Toys"]'::JSON, '["Books", "Fashion", "Electronics"]'::JSON), '["Electronics", "Books", "Clothing"]'::JSON ); -[ RECORD 1 ]----------------------------------- json_array_intersection(json_array_intersection('["Electronics", "Books", "Toys"]'::VARIANT, '["Books", "Fashion", "Electronics"]'::VARIANT), '["Electronics", "Books", "Clothing"]'::VARIANT): ["Electronics","Books"] ``` - JSON_ARRAY_OVERLAP: Checks if there is any overlap between two JSON arrays and returns true if there are common elements; otherwise, it returns false. ```sql title='Examples:' SELECT json_array_overlap( '["apple", "banana", "cherry"]'::JSON, '["banana", "kiwi", "mango"]'::JSON ); -[ RECORD 1 ]----------------------------------- json_array_overlap('["apple", "banana", "cherry"]'::VARIANT, '["banana", "kiwi", "mango"]'::VARIANT): true SELECT json_array_overlap( '["grape", "orange"]'::JSON, '["apple", "kiwi"]'::JSON ); -[ RECORD 1 ]----------------------------------- json_array_overlap('["grape", "orange"]'::VARIANT, '["apple", "kiwi"]'::VARIANT): false ``` - JSON_ARRAY_REDUCE: Reduces a JSON array to a single value by applying a specified Lambda expression. ```sql title='Examples:' SELECT JSON_ARRAY_REDUCE( [2, 3, 4]::JSON, (acc, d) -> acc::Int * d::Int ); -[ RECORD 1 ]----------------------------------- json_array_reduce([2, 3, 4]::VARIANT, (acc, d) -> acc::Int32 * d::Int32): 24 ``` - JSON_ARRAY_TRANSFORM (Aliases: JSON_ARRAY_APPLY and JSON_ARRAY_MAP): Transforms each element of a JSON array using a specified transformation Lambda expression. ```sql title='Examples:' SELECT JSON_ARRAY_TRANSFORM( [1, 2, 3, 4]::JSON, data -> (data::Int * 10) ); -[ RECORD 1 ]----------------------------------- json_array_transform([1, 2, 3, 4]::VARIANT, data -> data::Int32 * 10): [10,20,30,40] ``` - JSON_OBJECT_PICK: Creates a new JSON object containing only the specified keys from the input JSON object. If a specified key doesn’t exist in the input object, it is omitted from the result. ```sql title='Examples:' -- Pick a single key: SELECT json_object_pick('{"a":1,"b":2,"c":3}'::VARIANT, 'a'); -- Result: {"a":1} -- Pick multiple keys: SELECT json_object_pick('{"a":1,"b":2,"d":4}'::VARIANT, 'a', 'b'); -- Result: {"a":1,"b":2} -- Pick with non-existent key (non-existent keys are ignored): SELECT json_object_pick('{"a":1,"b":2,"d":4}'::VARIANT, 'a', 'c'); -- Result: {"a":1} ``` - JSON_OBJECT_DELETE: Deletes specified keys from a JSON object and returns the modified object. If a specified key doesn’t exist in the object, it is ignored. ```sql title='Examples:' -- Delete a single key: SELECT json_object_delete('{"a":1,"b":2,"c":3}'::VARIANT, 'a'); -- Result: {"b":2,"c":3} -- Delete multiple keys: SELECT json_object_delete('{"a":1,"b":2,"d":4}'::VARIANT, 'a', 'c'); -- Result: {"b":2,"d":4} -- Delete a non-existent key (key is ignored): SELECT json_object_delete('{"a":1,"b":2}'::VARIANT, 'x'); -- Result: {"a":1,"b":2} ```