--- title: 'This Week in Databend #130' date: 2024-01-29 slug: category-weekly/2024-01-29-databend-weekly cover_url: 'weekly/weekly-130.jpg' image: 'weekly/weekly-130.jpg' tags: [weekly] category: Weekly description: 'Stay up to date with the latest weekly developments on Databend!' contributors: - name: akoshchiy - name: andylokandy - name: ariesdevil - name: b41sh - name: BohuTang - name: Chasen-Zhang - name: dantengsky - name: Dousir9 - name: drmingdrmer - name: everpcpc - name: flaneur2020 - name: Freejww - name: guojidan - name: JackTan25 - name: leiysky - name: lichuang - name: PsiACE - name: SkyFan2002 - name: soyeric128 - name: sundy-li - name: TCeason - name: wubx - name: Xuanwo - name: xudong963 - name: xx01cyx - name: youngsofun - name: zhang2014 - 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. ### Supporting `CREATE OR REPLACE DATABASE` `CREATE OR REPLACE DATABASE` is syntactic sugar designed to simplify the following SQL statements: ```sql DROP DATABASE IF EXISTS ... CREATE DATABASE ... ``` Support for `CREATE OR REPLACE TABLE` is also actively underway. {/* truncate */} 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 #14449 | feat: add create or replace database support](https://github.com/databendlabs/databend/pull/14449) - [Issue #14229 | tracking: CREATE OR REPLACE](https://github.com/databendlabs/databend/issues/14229) ## Code Corner Discover some fascinating code snippets or projects that showcase our work or learning journey. ### Predicting Iris Species with SQL Using a combination of `CASE WHEN` statements and simple criteria, we can mock a basic tree model with SQL, achieving nearly 97% accuracy in classifying iris species. ```SQL SELECT COUNT(*) AS total_count, SUM(CASE WHEN subquery.species = subquery.prediction THEN 1 ELSE 0 END) AS correct_count, SUM(CASE WHEN subquery.species <> subquery.prediction THEN 1 ELSE 0 END) AS incorrect_count, SUM(CASE WHEN subquery.species = subquery.prediction THEN 1 ELSE 0 END) / COUNT(*) AS accuracy FROM ( SELECT sepal_length, sepal_width, petal_length, petal_width, species, CASE WHEN petal_width < 0.75 THEN 1 WHEN petal_width >= 0.75 AND petal_width < 1.35 THEN 2 WHEN petal_width >= 0.75 AND petal_width < 1.75 AND petal_width >= 1.35 AND sepal_width < 2.65 THEN 3 WHEN petal_width >= 0.75 AND petal_width < 1.75 AND petal_width >= 1.35 AND sepal_width >= 2.65 THEN 2 WHEN petal_width >= 0.75 AND petal_width >= 1.75 THEN 3 END AS prediction FROM iris ) AS subquery; ``` The complete example, using the iris dataset from Hugging Face, includes steps such as data access and data cleaning. For further reading, check out: - [Analyzing Hugging Face Datasets with Databend](https://www.databend.com/blog/2024-01-18-analyzing-hugging-face-datasets-with-databend/) ## Highlights We have also made these improvements to Databend that we hope you will find helpful: - Added support for `SHOW USER FUNCTIONS`. - Added a system table `user_functions`. - Added conditional functions `nvl` and `nvl2`. - Added JSON processing function `minus` supports deletion by index or name. - Added support for Predicate Move-Around. ## 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. ### Adding Support for Inverted Index **Inverted Index** is a word-doc mapping structure commonly used in full-text search systems. Databend plans to introduce inverted indexes to meet the needs of full-text search scenarios. The reference syntax is as follows: ```sql -- Create index CREATE [ASYNC] INVERTED INDEX ivt_index ON