This Week in Databend #87

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.

Window Function

Aggregate window functions can operate on a group of rows and return a single value for each row in the underlying query. The OVER clause specifies how to partition the rows in the result set. When used with GROUP BY, aggregate window functions do not collapse rows but instead return all rows in the result set.

-- use aggregate window function
SELECT date, AVG(amount) over (partition by date)
FROM BookSold

June 21|544.0
June 21|544.0
June 22|454.5
June 22|454.5
June 23|643.0
June 23|643.0

Databend supports all aggregate functions as aggregate window functions.

Suggest Function Name on Typos

Databend has added an intelligent feature that automatically provides the closest matching item when you enter an incorrect function name.

#> select base64(1);
ERROR 1105 (HY000) at line 1: Code: 1008, displayText = error:
  --> SQL:1:8
  |
1 | select base64(1)
  |        ^^^^^^^^^ no function matches the given name: 'base64', do you mean 'to_base64'?

PR | feat(planner): suggest function name on typo

Code Corner

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

Dump Running Async Task Stack

Databend now supports dumping the running async task stacks. Simply visit http://<admin_api_address>/debug/async_tasks/dump to capture it in your browser.

Dump Running Async Task Stack

Calling the async_backtrace::taskdump_tree function can obtain information about the asynchronous task tree (collected by #[async_backtrace::framed]).

    let tree =
        async_backtrace::taskdump_tree(req.map(|x| x.wait_for_running_tasks).unwrap_or(false));

Tasks are divided into regular tasks and polling tasks (marked with [POLLING]). Record the stack information for each task, and output it to a string sorted by stack depth.

    for mut tasks in [tasks, polling_tasks] {
        tasks.sort_by(|l, r| Ord::cmp(&l.stack_frames.len(), &r.stack_frames.len()));

        for item in tasks.into_iter().rev() {
            for frame in item.stack_frames {
                writeln!(output, "{}", frame).unwrap();
            }

            writeln!(output).unwrap();
        }
    }

To learn more about how it works, refer to the following link:

PR | feat(query): try support dump running async task stack

New Way to Integrate with Jupyter Notebook

As described in Doc | Visualization Databend Data in Jupyter Notebook, we can use Jupyter Notebook to explore data in Databend.

However, through the trick of magic, ipython-sql provides another interesting way to make SQL queries look like they are running in SQL cells and maintain seamless integration with Python.

Install Dependencies

pip install ipython-sql databend-sqlalchemy

Work with Jupyter Notebook

In [1]: %load_ext sql

In [2]: %%sql databend://{username}:{password}@{host_port_name}/{database_name}?secure=false
   ...: SHOW DATABASES;

In [3]: result = %%sql SELECT * FROM numbers(100);

In [4]: %matplotlib inline
   ...: df = result.DataFrame()
   ...: df.plot()

Highlights

Here are some noteworthy items recorded here, perhaps you can find something that interests you.

Connecting to Databend with DBeaver, either by selecting the pre-configured MySQL driver or adding the Databend JDBC driver.
Databend provides integration with Redash to help you gain better insights into your data. Doc | Integrations - Redash
Master how to display information of columns in a given table. Doc | SHOW COLUMNS
Databend now supports generate_series and range table functions.
Databend now supports the ai_embedding_vector function, which returns a 1536-dimensional f32 vectors generated by the OpenAI Embeddings API.
Databend added support for [CREATE | DROP | SHOW] SHARE ENDPOINT DDL.

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.

Collect Metrics from Sled

sled is an embedded database. It has a metrics feature that exposed some metrics.

Databend Meta Service uses sled as the underlying Key-Value storage. We expect to obtain more metrics about sled in order to further improve observability and help with optimization.

Issue #7233 | make use of sled metrics feature for collect sled metrics

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