Blog

Databend Monthly (Nov 2024)

avatarEricDec 1, 2024
Databend Monthly (Nov 2024)

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.

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.

Warehouse Access Control

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.

Tracking Metrics with Prometheus

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):
Example:
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):
Example:
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 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:

New Clause: SETTINGS

The SETTINGS Clause was introduced to configure specific settings that influence the execution behavior of the SQL statement it precedes.

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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Example:
SELECT LTRIM('xxdatabend', 'xx');

┌───────────────────────────┐
│ ltrim('xxdatabend', 'xx')
├───────────────────────────┤
│ databend │
└───────────────────────────┘
  • RTRIM: Removes specific characters from the end (right side) of a string.
Example:
SELECT RTRIM('databendxx', 'xx');

┌───────────────────────────┐
│ rtrim('databendxx', 'xx')
├───────────────────────────┤
│ databend │
└───────────────────────────┘
  • TRIM_BOTH: Removes specific characters from both ends of a string.
Example:
SELECT TRIM_BOTH('xxdatabendxx', 'xx');

┌─────────────────────────────────┐
│ trim_both('xxdatabendxx', 'xx')
├─────────────────────────────────┤
│ databend │
└─────────────────────────────────┘
  • TRIM_LEADING: Removes specific characters from the beginning (left side) of a string.
Example:
SELECT TRIM_LEADING('xxdatabend', 'xx');

┌──────────────────────────────────┐
│ trim_leading('xxdatabend', 'xx')
├──────────────────────────────────┤
│ databend │
└──────────────────────────────────┘
  • TRIM_TRAILING: Removes specific characters from the end (right side) of a string.
Example:
SELECT TRIM_TRAILING('databendxx', 'xx');

┌───────────────────────────────────┐
│ trim_trailing('databendxx', 'xx')
├───────────────────────────────────┤
│ databend │
└───────────────────────────────────┘
Share this post

Subscribe to our newsletter

Stay informed on feature releases, product roadmap, support, and cloud offerings!