This Week in Databend #137

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 New

Stay informed about the latest features of Databend.

Adding Support for Query Matching Inverted Index

Inverted indexes are the most commonly used data structures in document retrieval systems, storing the mapping of where a word is located in a document or a set of documents in full text search.

Databend now supports the use of match to match inverted indexes in queries.

CREATE TABLE t (id int, content string)

INSERT INTO t VALUES 
(1, 'The quick brown fox jumps over the lazy dog'),
(2, 'A picture is worth a thousand words'),
(3, 'The early bird catches the worm'),
(4, 'Actions speak louder than words'),
(5, 'Time flies like an arrow; fruit flies like a banana'),
(6, 'Beauty is in the eye of the beholder'),
(7, 'When life gives you lemons, make lemonade'),
(8, 'Put all your eggs in one basket'),
(9, 'You can not judge a book by its cover'),
(10, 'An apple a day keeps the doctor away')

CREATE INVERTED INDEX IF NOT EXISTS idx1 ON t(content)

REFRESH INVERTED INDEX idx1 ON t

SELECT id, score(), content FROM t WHERE match(content, 'word')
----
2 1.5948367 A picture is worth a thousand words
4 1.6550698 Actions speak louder than words

If you would like to learn more, please contact the Databend team or refer to the resources listed below:

PR | feat(query): Support query match inverted index

Code Corner

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

Leveraging TASK & STREAM to Capture and Track User Activity in Real-Time

Streams in Databend are dynamic real-time representations of table changes. Creating a stream can capture and track modifications to related tables for ongoing analysis.

A task encapsulates specific SQL statements intended to be executed at predefined time intervals, upon specific event triggers, or as part of a broader task sequence.

When creating a task, you can follow the workflow below:

The following example demonstrates how to combine TASK and STREAM to capture and track user activity in real-time, periodically synchronizing the user_activity_profiles table with data in activities_stream to ensure user_activity_profiles always accurately reflects the latest user activities

-- Define a task in Databend
CREATE TASK user_activity_task 
WAREHOUSE = 'default'
SCHEDULE = 1 MINUTE
-- Trigger task when new data arrives in activities_stream
WHEN stream_status('activities_stream') AS 
    -- Insert new records into user_activity_profiles
    INSERT INTO user_activity_profiles
    SELECT
        -- Join activities_stream with user_profiles based on user_id
        a.user_id, p.username, p.location, a.activity, a.timestamp
    FROM
        activities_stream AS a
        JOIN user_profiles AS p
            ON a.user_id = p.user_id
    -- Include only rows where the action is 'INSERT'
    WHERE a.change$action = 'INSERT';

If you're interested, please read the documentation below to learn how to complete this task using Databend Cloud.

Highlights

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

Added support for show views and desc view.
Added is_error, is_not_error, error_or functions.
Added pagination for task_history.
Added support for the PRQL query language.

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.

Supporting `CHANGES` Clause

The CHANGES clause allows querying for change tracking metadata of tables or views within a specified time interval without the need to create streams with explicit transaction offsets.

Combining multiple queries can be used to retrieve change tracking metadata between different transaction boundaries.

SELECT ...
FROM ...
   CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } )
   AT ( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STREAM => '<name>' } )
   [ END( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> } ) ]
[ ... ]

Issue #15028 | Feature: support CHANGES clause

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.