--- title: 'Databend Monthly (Nov 2024)' date: 2024-12-01 slug: category-weekly/monthly-nov2024 cover_url: 'monthly/nov2024/cover.png' description: "We're excited to bring you the latest updates, new features, and improvements for November 2024 in Databend! We hope these enhancements are helpful, and we look forward to your feedback." tags: [databend, monthly] category: Weekly authors: - Eric --- import TOCInline from '@theme/TOCInline'; We're excited to bring you the latest updates, new features, and improvements for Nov 2024 in Databend! We hope these enhancements are helpful, and we look forward to your feedback. {/* truncate */} ### Warehouse Access Control Databend Cloud now allows you to manage warehouse access with role-based controls by assigning a specific role to a warehouse, so only users with that role can access the warehouse. Please note that warehouse access control is _not_ enabled out of the box. To enable it, go to **Support** > **Create New Ticket** and submit a request. For more information, see [Warehouse Access Control](https://docs.databend.com/guides/cloud/using-databend-cloud/warehouses#warehouse-access-control). ![Warehouse Access Control](@site/static/img/blog/monthly/nov2024/warehouse-role.png) ### Tracking Metrics with Prometheus Databend Cloud (Business and Dedicated plans only) has added support for integrating with Prometheus: You can now monitor query performance, resource usage, and system metrics in real time using Prometheus. For how to integrate with Prometheus and the available metrics list, see [Tracking Metrics with Prometheus](https://docs.databend.com/guides/cloud/manage/metrics). ![Tracking Metrics with Prometheus](@site/static/img/blog/monthly/nov2024/metrics.png) ### New Way to Install BendSQL BendSQL now provides a convenient Shell script for installation. You can choose between two options: - Install BendSQL to the user's home directory (~/.bendsql): ```bash title='Example:' # highlight-next-line curl -fsSL https://repo.databend.com/install/bendsql.sh | bash B E N D S Q L Installer -------------------------------------------------------------------------------- Website: https://databend.com Docs: https://docs.databend.com Github: https://github.com/databendlabs/bendsql -------------------------------------------------------------------------------- >>> We'll be installing BendSQL via a pre-built archive at https://repo.databend.com/bendsql/v0.22.2/ >>> Ready to proceed? (y/n) >>> Please enter y or n. >>> y -------------------------------------------------------------------------------- >>> Downloading BendSQL via https://repo.databend.com/bendsql/v0.22.2/bendsql-aarch64-apple-darwin.tar.gz ✓ >>> Unpacking archive to /Users/eric/.bendsql ... ✓ >>> Adding BendSQL path to /Users/eric/.zprofile ✓ >>> Adding BendSQL path to /Users/eric/.profile ✓ >>> Install succeeded! 🚀 >>> To start BendSQL: bendsql --help >>> More information at https://github.com/databendlabs/bendsql ``` - Install BendSQL to a specified directory (e.g., /usr/local): ```bash title='Example:' # highlight-next-line curl -fsSL https://repo.databend.com/install/bendsql.sh | bash -s -- -y --prefix /usr/local B E N D S Q L Installer -------------------------------------------------------------------------------- Website: https://databend.com Docs: https://docs.databend.com Github: https://github.com/databendlabs/bendsql -------------------------------------------------------------------------------- >>> Downloading BendSQL via https://repo.databend.com/bendsql/v0.22.2/bendsql-aarch64-apple-darwin.tar.gz ✓ >>> Unpacking archive to /usr/local ... ✓ >>> Install succeeded! 🚀 >>> To start BendSQL: bendsql --help >>> More information at https://github.com/databendlabs/bendsql ``` ### New Data Types: GEOMETRY & GEOGRAPHY Databend has added basic support for [geospatial data types](https://docs.databend.com/sql/sql-reference/data-types/geospatial) to handle spatial data: - **GEOMETRY**: Uses a planar coordinate system (Cartesian coordinates) suitable for 2D geometric objects. Coordinates are represented as (X, Y) pairs, with units determined by the associated spatial reference system (SRS). The default SRID is 0, but custom SRIDs can be specified. Ideal for small-scale measurements like city or provincial analyses, it offers high computational speed and low resource usage but may introduce significant errors over larger areas. - **GEOGRAPHY**: Uses a geographic coordinate system (spherical coordinates) based on latitude (-90° to 90°) and longitude (-180° to 180°), adhering to WGS 84 (SRID 4326). Designed for global or large-scale spatial data, it provides accuracy over vast distances but with higher computational complexity and resource requirements. It can be converted to GEOMETRY when needed. Explore the links below to discover all the available geospatial functions organized by category: - [Geometry Functions](https://docs.databend.com/sql/sql-functions/geometry-functions/) - [H3](https://docs.databend.com/sql/sql-functions/geo-functions/) ### New Clause: SETTINGS The [SETTINGS Clause](https://docs.databend.com/sql/sql-commands/query-syntax/settings) was introduced to configure specific settings that influence the execution behavior of the SQL statement it precedes. ```sql title='Example:' -- Allow the COPY INTO operation to utilize up to 100 threads for parallel processing: SETTINGS (max_threads = 100) COPY INTO ... ``` ### New Map Lambda Functions - MAP_FILTER: Filters key-value pairs from a map using a lambda expression to define the condition. ```sql title='Example:' SELECT MAP_FILTER({101:15, 102:8, 103:12, 104:5}, (product_id, stock) -> (stock < 10)) AS low_stock_products; ┌────────────────────┐ │ low_stock_products │ ├────────────────────┤ │ {102:8,104:5} │ └────────────────────┘ ``` - MAP_TRANSFORM_KEYS: Applies a transformation to each key in a map using a lambda expression. ```sql title='Example:' SELECT MAP_TRANSFORM_KEYS({101: 29.99, 102: 45.50, 103: 15.00}, (product_id, price) -> product_id + 1000) AS updated_product_ids; ┌────────────────────────────────────┐ │ updated_product_ids │ ├────────────────────────────────────┤ │ {1101:29.99,1102:45.50,1103:15.00} │ └────────────────────────────────────┘ ``` - MAP_TRANSFORM_VALUES: Applies a transformation to each value in a map using a lambda expression. ```sql title='Example:' SELECT MAP_TRANSFORM_VALUES({101: 100.0, 102: 150.0, 103: 200.0}, (product_id, price) -> price * 0.9) AS discounted_prices; ┌───────────────────────────────────┐ │ discounted_prices │ ├───────────────────────────────────┤ │ {101:90.00,102:135.00,103:180.00} │ └───────────────────────────────────┘ ``` - JSON_MAP_FILTER: Filters key-value pairs in a JSON object based on a specified condition, defined using a lambda expression. ```sql title='Example:' SELECT JSON_MAP_FILTER('{"status":"active", "user":"admin", "time":"2024-11-01"}'::VARIANT, (k, v) -> k = 'status') AS filtered_metadata; ┌─────────────────────┐ │ filtered_metadata │ ├─────────────────────┤ │ {"status":"active"} │ └─────────────────────┘ ``` - JSON_MAP_TRANSFORM_KEYS: Applies a transformation to each key in a JSON object using a lambda expression. ```sql title='Example:' SELECT JSON_MAP_TRANSFORM_KEYS('{"name":"John", "role":"admin"}'::VARIANT, (k, v) -> CONCAT(k, '_v1')) AS versioned_metadata; ┌──────────────────────────────────────┐ │ versioned_metadata │ ├──────────────────────────────────────┤ │ {"name_v1":"John","role_v1":"admin"} │ └──────────────────────────────────────┘ ``` - JSON_MAP_TRANSFORM_VALUES: Applies a transformation to each value in a JSON object using a lambda expression. ```sql title='Example:' SELECT JSON_MAP_TRANSFORM_VALUES('{"product1":"laptop", "product2":"phone"}'::VARIANT, (k, v) -> CONCAT(v, ' - Special Offer')) AS promo_descriptions; ┌──────────────────────────────────────────────────────────────────────────┐ │ promo_descriptions │ ├──────────────────────────────────────────────────────────────────────────┤ │ {"product1":"laptop - Special Offer","product2":"phone - Special Offer"} │ └──────────────────────────────────────────────────────────────────────────┘ ``` ### New Date Functions - LAST_DAY: Returns the last day of the specified interval (week, month, quarter, or year) based on the provided date or timestamp. ```sql title='Example:' SELECT LAST_DAY(to_date('2024-11-13'), month) AS billing_date; ┌──────────────┐ │ billing_date │ ├──────────────┤ │ 2024-11-30 │ └──────────────┘ ``` - NEXT_DAY: Returns the date of the upcoming specified day of the week after the given date or timestamp. ```sql title='Example:' SELECT NEXT_DAY(to_date('2024-11-13'), monday) AS next_monday; ┌─────────────┐ │ next_monday │ ├─────────────┤ │ 2024-11-18 │ └─────────────┘ ``` - PREVIOUS_DAY: Returns the date of the most recent specified day of the week before the given date or timestamp. ```sql title='Example:' SELECT PREVIOUS_DAY(to_date('2024-11-13'), friday) AS last_friday; ┌─────────────┐ │ last_friday │ ├─────────────┤ │ 2024-11-08 │ └─────────────┘ ``` ### New Trim Functions - LTRIM: Removes specific characters from the beginning (left side) of a string. ```sql title='Example:' SELECT LTRIM('xxdatabend', 'xx'); ┌───────────────────────────┐ │ ltrim('xxdatabend', 'xx') │ ├───────────────────────────┤ │ databend │ └───────────────────────────┘ ``` - RTRIM: Removes specific characters from the end (right side) of a string. ```sql title='Example:' SELECT RTRIM('databendxx', 'xx'); ┌───────────────────────────┐ │ rtrim('databendxx', 'xx') │ ├───────────────────────────┤ │ databend │ └───────────────────────────┘ ``` - TRIM_BOTH: Removes specific characters from both ends of a string. ```sql title='Example:' SELECT TRIM_BOTH('xxdatabendxx', 'xx'); ┌─────────────────────────────────┐ │ trim_both('xxdatabendxx', 'xx') │ ├─────────────────────────────────┤ │ databend │ └─────────────────────────────────┘ ``` - TRIM_LEADING: Removes specific characters from the beginning (left side) of a string. ```sql title='Example:' SELECT TRIM_LEADING('xxdatabend', 'xx'); ┌──────────────────────────────────┐ │ trim_leading('xxdatabend', 'xx') │ ├──────────────────────────────────┤ │ databend │ └──────────────────────────────────┘ ``` - TRIM_TRAILING: Removes specific characters from the end (right side) of a string. ```sql title='Example:' SELECT TRIM_TRAILING('databendxx', 'xx'); ┌───────────────────────────────────┐ │ trim_trailing('databendxx', 'xx') │ ├───────────────────────────────────┤ │ databend │ └───────────────────────────────────┘ ```