This Week in Databend #114

Databend is a modern cloud data warehouse, serving your massive-scale analytics needs at low cost and complexity. Open source alternative to Snowflake. Also available in the cloud: https://app.databend.com .

What's On In Databend

Stay connected with the latest news about Databend.

MERGE

The MERGE statement performs INSERT, UPDATE, or DELETE operations on rows within a target table, all in accordance with conditions and matching criteria specified within the statement, using data from a specified source.

A MERGE statement usually contains a MATCHED and / or a NOT MATCHED clause, instructing Databend on how to handle matched and unmatched scenarios. For a MATCHED clause, you have the option to choose between performing an UPDATE or DELETE operation on the target table. Conversely, in the case of a NOT MATCHED clause, the available choice is INSERT.

-- Merge data into 'salaries' based on employee details from 'employees'
MERGE INTO salaries
USING (SELECT * FROM employees)
ON salaries.employee_id = employees.employee_id
WHEN MATCHED AND employees.department = 'HR' THEN
    UPDATE SET
        salaries.salary = salaries.salary + 1000.00
WHEN MATCHED THEN
    UPDATE SET
        salaries.salary = salaries.salary + 500.00
WHEN NOT MATCHED THEN
    INSERT (employee_id, salary)
    VALUES (employees.employee_id, 55000.00);

MERGE is currently in an experimental state. Before using the MERGE command, you need to run SET enable_experimental_merge_into = 1; to enable the feature.

If you are interested in learning more, please check out the resources below:

Docs | MERGE

Code Corner

Discover some fascinating code snippets or projects that showcase our work or learning journey.

Introducing `DATABEND_DATA_PATH` to Python Binding and Local Mode

Databend's local mode now allows users to control the storage location of metadata and data files by setting the DATABEND_DATA_PATH environment variable.

DATABEND_DATA_PATH=/tmp/data/ databend-query local -q "create table abc(a int); insert into abc values(3);"

DATABEND_DATA_PATH also works with Databend Python Binding, but it must be defined before using databend.

import os

os.environ["DATABEND_DATA_PATH"] = "/tmp/def/"

from databend import SessionContext

If you are interested in learning more, please check out the resources below:

PR #13089 | feat: support DATABEND_DATA_PATH in bendpy and databend-local

Highlights

We have also made these improvements to Databend that we hope you will find helpful:

Improved Hash Join, resulting in a 10% performance improvement in certain scenarios.
Enhanced distributed execution of MERGE.
Improved CI by using quickinstall to install relevant binary tools and executing unit tests with nextest.

What's Up Next

We're always open to cutting-edge technologies and innovative ideas. You're more than welcome to join the community and bring them to Databend.

Delete Files When Dropping Internal Stage

In Databend, an Internal Stage stores data files in the storage backend specified in databend-query.toml.

Considering that users will not be able to access the staged files after dropping an Internal Stage, it is necessary to remove the staged files when dropping the Internal Stage.

Issue #12986 | remove files at the same time of drop internal stage

Please let us know if you're interested in contributing to this feature, or pick up a good first issue at https://link.databend.rs/i-m-feeling-lucky to get started.