--- title: 'This Week in Databend #137' date: 2024-03-25 slug: category-weekly/2024-03-25-databend-weekly cover_url: 'weekly/weekly-137.jpg' image: 'weekly/weekly-137.jpg' tags: [weekly] category: Weekly description: 'Stay up to date with the latest weekly developments on Databend!' contributors: - name: andylokandy - name: b41sh - name: blackstar-baba - name: Chasen-Zhang - name: BohuTANG - name: dantengsky - name: Dousir9 - name: drmingdrmer - name: everpcpc - name: flaneur2020 - name: Freejww - name: JackTan25 - name: ncuwaln - name: PsiACE - name: SkyFan2002 - name: soyeric128 - name: sundy-li - name: TCeason - name: xudong963 - name: youngsofun - name: zhang2014 - name: ZhengLin-Li - name: ZhiHanZ - name: zhyass authors: - PsiACE --- [Databend](https://github.com/databendlabs/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](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. {/* truncate */} ```sql 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](https://databend.com/contact-us/) or refer to the resources listed below: - [PR | feat(query): Support query match inverted index](https://github.com/databendlabs/databend/pull/14997) ## 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: ![](../static/img/blog/task-workflow.png) 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 ```sql -- 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. - [Docs | Automating Data Loading with Tasks](https://docs.databend.com/guides/load-data/continuous-data-pipelines/task) - [Docs | Example: Tracking and Transforming Data in Real-Time](https://docs.databend.com/guides/load-data/continuous-data-pipelines/stream#example-tracking-and-transforming-data-in-real-time) ## 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. ```sql SELECT ... FROM ... CHANGES ( INFORMATION => { DEFAULT | APPEND_ONLY } ) AT ( { TIMESTAMP => | OFFSET => | STREAM => '' } ) [ END( { TIMESTAMP => | OFFSET => } ) ] [ ... ] ``` [Issue #15028 | Feature: support CHANGES clause](https://github.com/databendlabs/databend/issues/15028) Please let us know if you're interested in contributing to this feature, or pick up a good first issue at [https://link.databend.com/i-m-feeling-lucky](https://link.databend.com/i-m-feeling-lucky) to get started. ## New Contributors We always open arms to everyone and can't wait to see how you'll help our community grow and thrive. - [@ncuwaln](https://github.com/ncuwaln) implemented support for PRQL, [#14922](https://github.com/databendlabs/databend/pull/14922). - [@blackstar-baba](https://github.com/blackstar-baba) implemented support for `show views` & `desc view`, [#14926](https://github.com/databendlabs/databend/pull/14926). ## Changelog You can check the changelog of Databend Nightly for details about our latest developments. **Full Changelog**: [https://github.com/databendlabs/databend/compare/v1.2.378-nightly...v1.2.386-nightly](https://github.com/databendlabs/databend/compare/v1.2.378-nightly...v1.2.386-nightly)