VeloDB

https://docs.velodb.io
Admin
VeloDB is a real-time analytics and search database powered by Apache Doris, unifying SQL analytics,...
Tokens:2,171,111
Snippets:25,627
Trust Score:8
Update:1 month ago
Show doc for...
Tokens:
Raw
### Setup Tables for Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/data-query/SELECT

Create and populate the student and tb_book tables used in the subsequent examples.

```sql
CREATE TABLE student (Name VARCHAR(32), age INT)  
DISTRIBUTED BY HASH(Name) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
  
INSERT INTO student VALUES  
  ('Alice', 18), ('Bob', 20), ('Carol', 22), ('Dave', 25), ('Eve', 30);  
  
CREATE TABLE tb_book (id INT, name VARCHAR(64), price DECIMAL(10,2), type VARCHAR(32))  
DISTRIBUTED BY HASH(id) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
  
INSERT INTO tb_book VALUES  
  (1, 'SQL Cookbook',                          39.99, 'database'),  
  (2, 'Doris Internals',                       49.99, 'database'),  
  (3, 'The Rust Programming Language',         45.00, 'programming'),  
  (4, 'Designing Data-Intensive Applications', 55.00, 'database'),  
  (5, 'Clean Code',                            35.00, 'programming');
```

--------------------------------

### Install Plugin Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/plugin/INSTALL-PLUGIN

Various methods for installing plugins, including local paths and remote URLs with optional MD5 verification.

```sql
INSTALL PLUGIN FROM "/home/users/doris/auditdemo.zip";  
```

```sql
INSTALL PLUGIN FROM "/home/users/doris/auditdemo/";  
```

```sql
INSTALL PLUGIN FROM "http://mywebsite.com/plugin.zip";  
```

```sql
INSTALL PLUGIN FROM "http://mywebsite.com/plugin.zip" PROPERTIES("md5sum" = "73877f6029216f4314d712086a146570");  
```

--------------------------------

### Setup Database Tables

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/scalar-functions/other-functions/field

Initializes the baseall and class_test tables used in subsequent examples.

```sql
-- setup  
CREATE TABLE baseall (k1 INT, k7 VARCHAR(64))  
DISTRIBUTED BY HASH(k1) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
  
INSERT INTO baseall VALUES  
    (1, 'wangjing04'),  
    (2, 'wangyu14'),  
    (3, 'yuanyuan06');  
  
CREATE TABLE class_test (class_name VARCHAR(64))  
DISTRIBUTED BY HASH(class_name) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
  
INSERT INTO class_test VALUES  
    ('Suzi'), ('Suzi'),  
    ('Ben'), ('Ben'),  
    ('Henry'), ('Henry'),  
    (NULL);
```

--------------------------------

### Setup Table for MEDIAN Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/median

Creates and populates a table to demonstrate MEDIAN calculations.

```sql
-- setup  
create table log_statis(  
    datetime datetime,  
    scan_rows int  
) distributed by hash(datetime) buckets 1  
properties ("replication_num"="1");  
insert into log_statis values  
    ('2025-08-25 10:00:00', 10),  
    ('2025-08-25 10:00:00', 50),  
    ('2025-08-25 10:00:00', 100),  
    ('2025-08-25 11:00:00', 20),  
    ('2025-08-25 11:00:00', 30),  
    ('2025-08-25 11:00:00', 40);
```

--------------------------------

### Setup table and data for ANY_VALUE examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/any-value

Creates a table t1 and inserts sample data to demonstrate aggregation behavior.

```sql
-- setup  
create table t1(  
        k1 int,  
        k_string varchar(100),  
        k_decimal decimal(10, 2)  
) distributed by hash (k1) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (1, 'apple', 10.01),  
    (1, 'banana', 20.02),  
    (2, 'orange', 30.03),  
    (2, null, null),  
    (3, null, null);
```

--------------------------------

### Get Query Plan Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/table-query-plan-action

A complete example showing the POST request and the corresponding response for retrieving a query plan.

```http
POST /api/db1/tbl1/_query_plan  
{  
    "sql": "select * from db1.tbl1;"  
}  
  
Response:  
{  
	"msg": "success",  
	"code": 0,  
	"data": {  
		"partitions": {  
			"10039": {  
				"routings": ["192.168.1.1:9060"],  
				"version": 2,  
				"versionHash": 982459448378619656,  
				"schemaHash": 1294206575  
			}  
		},  
		"opaqued_query_plan": "DAABDAACDwABD...",  
		"status": 200  
	},  
	"count": 0  
}  
```

--------------------------------

### Setup Table and Data for Deletion Examples

Source: https://docs.velodb.io/cloud/26.x/data-operate/delete/delete-manual

Creates a Unique-Key list-partitioned table and inserts sample data for testing delete operations.

```sql
CREATE TABLE my_table (  
  k1 INT,  
  status VARCHAR(32),  
  dt DATE  
)  
UNIQUE KEY (k1)  
PARTITION BY LIST (k1) (  
  PARTITION p1 VALUES IN (1, 2, 3, 4, 5),  
  PARTITION p2 VALUES IN (6, 7, 8, 9, 10)  
)  
DISTRIBUTED BY HASH (k1) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
  
INSERT INTO my_table VALUES  
  (1, 'active',   '2024-09-01'),  
  (3, 'outdated', '2024-10-15'),  
  (5, 'active',   '2024-10-20'),  
  (6, 'outdated', '2024-10-05'),  
  (8, 'outdated', '2024-10-25'),  
  (10, 'active',  '2024-11-10');  
```

--------------------------------

### Create test_table

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/table-and-view/table/DESC-TABLE

Setup script to create the test_table used in subsequent examples.

```sql
CREATE TABLE test_table (  
  user_id BIGINT NOT NULL COMMENT 'Key1',  
  name    VARCHAR(20)     COMMENT 'username',  
  age     INT             COMMENT 'user_age'  
) ENGINE=OLAP  
UNIQUE KEY (user_id)  
DISTRIBUTED BY HASH(user_id) BUCKETS 1  
PROPERTIES ("replication_num" = "1");  
```

--------------------------------

### Setup Table for SEM Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/sem

Creates a table t1 and inserts sample data including nulls and duplicate values for testing aggregate functions.

```sql
-- setup  
create table t1(  
        id int,  
        k_double double,  
) distributed by hash (id) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (1, 222.222),  
    (2, 3.3),  
    (3, 3.3),  
    (4, null);
```

--------------------------------

### Setup Table for AVG Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/avg

Creates a table with various numeric types and populates it with sample data for testing the AVG function.

```sql
-- setup  
create table t1(  
        k_tinyint tinyint,  
        k_smallint smallint,  
        k_int int,  
        k_bigint bigint,  
        k_largeint largeint,  
        k_double double,  
        k_decimal decimalv3(10, 5),  
        k_null_int int  
) distributed by hash (k_int) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (1, 10, 100, 1000, 10000, 1.1, 222.222, null),  
    (2, 20, 200, 2000, 20000, 2.2, 444.444, null),  
    (3, 30, 300, 3000, 30000, 3.3, null, null);
```

--------------------------------

### Setup Table for AVG_WEIGHTED Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/avg-weighted

Creates a table t1 with numeric columns and populates it with sample data for testing weighted average calculations.

```sql
-- setup  
create table t1(  
        k1 int,  
        k2 int,  
        k3 decimal(10, 2),  
        k4 double,  
        category varchar(50)  
) distributed by hash (k1) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (10, 100, 5.5, 1.0, 'A'),  
    (20, 200, 10.0, 2.0, 'A'),  
    (30, 300, 15.5, 3.0, 'B'),  
    (40, 400, 20.0, 4.0, 'B'),  
    (50, 0, 25.0, 0.0, 'C'),  
    (60, 600, 30.0, 5.0, 'C');
```

--------------------------------

### Complete Arrow Flight SQL Workflow

Source: https://docs.velodb.io/cloud/db-connect/arrow-flight-sql-connect

A full example covering library installation, connection setup, DDL execution, data insertion, and query execution.

```python
# Doris Arrow Flight SQL Test  
  
# step 1, library is released on PyPI and can be easily installed.  
# pip install adbc_driver_manager  
# pip install adbc_driver_flightsql  
import adbc_driver_manager  
import adbc_driver_flightsql.dbapi as flight_sql  
  
# step 2, create a client that interacts with the Doris Arrow Flight SQL service.  
# Modify arrow_flight_sql_port in fe/conf/fe.conf to an available port, such as 8070.  
# Modify arrow_flight_sql_port in be/conf/be.conf to an available port, such as 8050.  
conn = flight_sql.connect(uri="grpc://{FE_HOST}:{fe.conf:arrow_flight_sql_port}", db_kwargs={  
            adbc_driver_manager.DatabaseOptions.USERNAME.value: "root",  
            adbc_driver_manager.DatabaseOptions.PASSWORD.value: "",  
        })  
cursor = conn.cursor()  
  
# interacting with Doris via SQL using Cursor  
def execute(sql):  
    print("\n### execute query: ###\n " + sql)  
    cursor.execute(sql)  
    print("### result: ###")  
    print(cursor.fetchallarrow().to_pandas())  
  
# step3, execute DDL statements, create database/table, show stmt.  
execute("DROP DATABASE IF EXISTS arrow_flight_sql FORCE;")  
execute("show databases;")  
execute("create database arrow_flight_sql;")  
execute("show databases;")  
execute("use arrow_flight_sql;")  
execute("""CREATE TABLE arrow_flight_sql_test  
    (  
         k0 INT,  
         k1 DOUBLE,  
         K2 varchar(32) NULL DEFAULT \"\" COMMENT \"\",  
         k3 DECIMAL(27,9) DEFAULT \"0\",  
         k4 BIGINT NULL DEFAULT '10',  
         k5 DATE,  
    )  
    DISTRIBUTED BY HASH(k5) BUCKETS 5  
    PROPERTIES(\"replication_num\" = \"1\");""")  
execute("show create table arrow_flight_sql_test;")  
  
  
# step4, insert into  
execute("""INSERT INTO arrow_flight_sql_test VALUES  
        ('0', 0.1, \"ID\", 0.0001, 9999999999, '2023-10-21'),  
        ('1', 0.20, \"ID_1\", 1.00000001, 0, '2023-10-21'),  
        ('2', 3.4, \"ID_1\", 3.1, 123456, '2023-10-22'),  
        ('3', 4, \"ID\", 4, 4, '2023-10-22'),  
        ('4', 122345.54321, \"ID\", 122345.54321, 5, '2023-10-22');""")  
  
  
# step5, execute queries, aggregation, sort, set session variable  
execute("select * from arrow_flight_sql_test order by k0;")  
execute("set exec_mem_limit=2000;")  
execute("show variables like \"%exec_mem_limit%\";")  
execute("select k5, sum(k1), count(1), avg(k3) from arrow_flight_sql_test group by k5;")  
  
# step6, close cursor   
cursor.close()
```

--------------------------------

### Setup Table for MAX_BY Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/max-by

Creates a table with various data types including arrays, maps, and structs to demonstrate MAX_BY functionality.

```sql
-- setup  
create table tbl(  
    k1 int,  
    k2 int,  
    arr ARRAY<INT>,  
    mp MAP<STRING, INT>,  
    st STRUCT<a: INT, b: STRING>,  
) distributed by hash(k1) buckets 1  
properties ("replication_num"="1");  
insert into tbl values  
    (0, 100, [1,2,3], {"x": 1, "y": 4}, NAMED_STRUCT("a", 1, "b", "x")),  
    (1, 4,   [5, 6],  {"x": 2, "y": 3}, NAMED_STRUCT("a", 2, "b", "y")),  
    (4, 1,   [7],     {"x": 3, "y": 2}, NAMED_STRUCT("a", 3, "b", "z")),  
    (3, 1,   [8],     {"x": 4, "y": 1}, NAMED_STRUCT("a", 4, "b", "r"));
```

--------------------------------

### Create a file example

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/security/CREATE-FILE

Examples demonstrating how to create files with different configurations and database associations.

```sql
CREATE FILE "ca.pem"  
PROPERTIES  
(  
    "url" = "https://test.bj.bcebos.com/kafka-key/ca.pem",  
    "catalog" = "kafka"  
);  
```

```sql
CREATE FILE "client.key"  
IN my_database  
PROPERTIES  
(  
    "url" = "https://test.bj.bcebos.com/kafka-key/client.key",  
    "catalog" = "my_catalog",  
    "md5" = "b5bb901bf10f99205b39a46ac3557dd9"  
);  
```

```sql
  CREATE FILE "client_1.key"  
  FROM my_database  
  PROPERTIES  
  (  
     "url" = "https://test.bj.bcebos.com/kafka-key/client.key",  
     "catalog" = "my_catalog",  
     "md5" = "b5bb901bf10f99205b39a46ac3557dd9"  
  );  
```

--------------------------------

### Get Query Information Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/query-profile-action

Example request and response for retrieving query information.

```http
GET /rest/v2/manager/query/query_info  
  
{  
    "msg": "success",    
    "code": 0,    
    "data": {    
        "column_names": [    
            "Query ID",    
            "FE Node",    
            "Query User",    
            "Execution Database",    
            "Sql",    
            "Query Type",    
            "Start Time",    
            "End Time",    
            "Execution Duration",    
            "Status"    
        ],    
        "rows": [    
            [    
                "d7c93d9275334c35-9e6ac5f295a7134b",    
                "127.0.0.1:8030",    
                "root",    
                "default_cluster:testdb",    
                "select c.id, c.name, p.age, p.phone, c.date, c.cost from cost c join people p on c.id = p.id where p.age > 20 order by c.id",    
                "Query",    
                "2021-07-29 16:59:12",    
                "2021-07-29 16:59:12",    
                "109ms",    
                "EOF"    
            ]    
        ]    
    },    
    "count": 0    
}  
```

--------------------------------

### Setup Environment for REVOKE Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/account-management/REVOKE-FROM

Creates the necessary users, roles, databases, and workload groups, and grants initial privileges required for the subsequent REVOKE operations.

```sql
CREATE DATABASE db1;  
CREATE USER jack@'192.%' IDENTIFIED BY 'jack_pwd';  
CREATE USER jack@'%' IDENTIFIED BY 'jack_pwd';  
CREATE ROLE role1;  
CREATE ROLE role2;  
CREATE ROLE my_role;  
CREATE WORKLOAD GROUP g1 PROPERTIES("max_cpu_percent"="10");  
  
GRANT SELECT_PRIV ON db1.* TO 'jack'@'192.%';  
GRANT USAGE_PRIV ON RESOURCE 'spark_resource' TO 'jack'@'192.%';  
GRANT 'role1','role2' TO 'jack'@'192.%';  
GRANT USAGE_PRIV ON WORKLOAD GROUP 'g1' TO 'jack'@'%';  
GRANT USAGE_PRIV ON WORKLOAD GROUP '%' TO 'jack'@'%';  
GRANT USAGE_PRIV ON COMPUTE GROUP 'group1' TO 'jack'@'%';  
GRANT USAGE_PRIV ON COMPUTE GROUP 'group1' TO ROLE 'my_role';  
GRANT USAGE_PRIV ON STORAGE VAULT 'vault1' TO 'jack'@'%';  
GRANT USAGE_PRIV ON STORAGE VAULT 'vault1' TO ROLE 'my_role';  
GRANT USAGE_PRIV ON STORAGE VAULT '%' TO 'jack'@'%';
```

--------------------------------

### Create a database for examples

Source: https://docs.velodb.io/cloud/26.x/table-design/data-partitioning/data-bucketing

Initializes the demo database required for the subsequent table creation examples.

```sql
CREATE DATABASE IF NOT EXISTS demo;  
```

--------------------------------

### Cluster Connection Information Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/cluster-action

A practical example of the GET request and the corresponding JSON response.

```http
GET /rest/v2/manager/cluster/cluster_info/conn_info

Response:
{
    "msg": "success",
    "code": 0,
    "data": {
        "http": [
            "127.0.0.1:8030"
        ],
        "mysql": [
            "127.0.0.1:9030"
        ]
    },
    "count": 0
}
```

--------------------------------

### Create Database Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/database/CREATE-DATABASE

Practical examples for creating a database with default settings, custom replica allocation, or a specific storage vault.

```sql
CREATE DATABASE db_test;  
```

```sql
CREATE DATABASE `db_test`  
PROPERTIES (  
	"replication_allocation" = "tag.location.group_1:3"  
);  
```

```sql
CREATE DATABASE `db_test`  
PROPERTIES (  
	"storage_vault_name" = "hdfs_demo_vault"  
);  
```

--------------------------------

### Get DDL Statement Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/get-ddl-stmt-action

Example request and response for retrieving the DDL statement of a specific table.

```http
GET GET /api/_get_ddl?db=db1&table=tbl1

Response
{
	"msg": "OK",
	"code": 0,
	"data": {
		"create_partition": [],
		"create_table": ["CREATE TABLE `tbl1` (\n  `k1` int(11) NULL COMMENT \"\",\n  `k2` int(11) NULL COMMENT \"\"\n) ENGINE=OLAP\nDUPLICATE KEY(`k1`, `k2`)\nCOMMENT \"OLAP\"\nDISTRIBUTED BY HASH(`k1`) BUCKETS 1\nPROPERTIES (\n\"replication_num\" = \"1\",\n\"version_info\" = \"1,0\",\n\"in_memory\" = \"false\",\n\"storage_format\" = \"DEFAULT\"\n);"],
		"create_rollup": []
	},
	"count": 0
}
```

--------------------------------

### Numeric Range Examples

Source: https://docs.velodb.io/enterprise/4.x/sql-manual/sql-functions/scalar-functions/array-functions/array-range

Examples demonstrating numeric sequences with default and custom start, end, and step values.

```sql
ARRAY_RANGE(5) -> [0, 1, 2, 3, 4]
```

```sql
ARRAY_RANGE(0, 5) -> [0, 1, 2, 3, 4]
```

```sql
ARRAY_RANGE(2, 6, 2) -> [2, 4]
```

```sql
ARRAY_RANGE(3, 3) -> []
```

```sql
ARRAY_RANGE(3, 2) -> []
```

```sql
ARRAY_RANGE(-1, 3) -> NULL
```

```sql
ARRAY_RANGE(1, 3, 0) -> NULL
```

--------------------------------

### Start application dependencies

Source: https://docs.velodb.io/cloud/observability/dogstack/quickstart

Launch the required backend services for the e-commerce demo.

```bash
cd ../doris-opentelemetry-demo  
docker compose \
  -f docker-compose.minimal.yml \
  -f docker-compose.app-only.yml \
  up -d --no-deps valkey-cart flagd  
```

--------------------------------

### Setup product_sales table

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/topn-weighted

Creates and populates a sample table for demonstrating weighted top-N calculations.

```sql
-- setup  
CREATE TABLE product_sales (  
    product_id INT,  
    sale_amount DECIMAL(10,2),  
    sale_date DATE  
) DISTRIBUTED BY HASH(product_id)  
PROPERTIES (  
    "replication_num" = "1"  
);  
INSERT INTO product_sales VALUES  
(1, 100.00, '2024-01-01'),  
(2, 50.00, '2024-01-01'),  
(1, 150.00, '2024-01-01'),  
(3, 75.00, '2024-01-01'),  
(1, 200.00, '2024-01-01'),  
(2, 80.00, '2024-01-01'),  
(1, 120.00, '2024-01-01'),  
(4, 90.00, '2024-01-01');
```

--------------------------------

### Get SQL Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/query-profile-action

Example request and response for retrieving the SQL statement associated with a specific query ID.

```http
GET /rest/v2/manager/query/sql/d7c93d9275334c35-9e6ac5f295a7134b

Response:
{
    "msg": "success",
    "code": 0,
    "data": {
        "sql": "select c.id, c.name, p.age, p.phone, c.date, c.cost from cost c join people p on c.id = p.id where p.age > 20 order by c.id"
    },
    "count": 0
}
```

--------------------------------

### Get FE Configuration Item Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/node-action

Example request and response for retrieving a specific FE configuration item.

```json
{
    "conf_name":[
        "agent_task_resend_wait_time_ms"
    ]
}
```

```json
{
    "msg": "success",
    "code": 0,
    "data": {
        "column_names": [
            "配置项",
            "节点",
            "节点类型",
            "配置值类型",
            "MasterOnly",
            "配置值",
            "可修改"
        ],
        "rows": [
            [
                "agent_task_resend_wait_time_ms",
                "127.0.0.1:8030",
                "FE",
                "long",
                "true",
                "50000",
                "true"
            ]
        ]
    },
    "count": 0
}
```

--------------------------------

### Start application services

Source: https://docs.velodb.io/cloud/observability/dogstack/quickstart

Launch the microservices for the e-commerce demo application.

```bash
docker compose \
  -f docker-compose.minimal.yml \
  -f docker-compose.app-only.yml \
  up -d --no-deps \
  ad cart currency email payment product-catalog quote recommendation shipping \
  checkout image-provider frontend load-generator frontend-proxy  
```

--------------------------------

### Get Table Row Count Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/table-row-count-action

Example request and response for retrieving the row count of a specific table.

```http
GET /api/db1/tbl1/_count

Response:
{
	"msg": "success",
	"code": 0,
	"data": {
		"size": 1,
		"status": 200
	},
	"count": 0
}
```

--------------------------------

### Get Query Details Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/query-detail-action

Example request and response for retrieving query details after a specific Unix timestamp.

```http
GET /api/query_detail?event_time=1596462079958

Response:
{
	"msg": "success",
	"code": 0,
	"data": {
		"query_details": [{
			"eventTime": 1596462699216,
			"queryId": "f732084bc8e74f39-8313581c9c3c0b58",
			"startTime": 1596462698969,
			"endTime": 1596462699216,
			"latency": 247,
			"state": "FINISHED",
			"database": "db1",
			"sql": "select * from tbl1"
		}, {
			"eventTime": 1596463013929,
			"queryId": "ed2d0d80855d47a5-8b518a0f1472f60c",
			"startTime": 1596463013913,
			"endTime": 1596463013929,
			"latency": 16,
			"state": "FINISHED",
			"database": "db1",
			"sql": "select k1 from tbl1"
		}]
	},
	"count": 0
}
```

--------------------------------

### Build Index Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/table-and-view/index/BUILD-INDEX

Examples demonstrating how to build an index on an entire table or specific partitions.

```sql
BUILD INDEX index1 ON table1
```

```sql
BUILD INDEX index1 ON table1 PARTITION(p1, p2)
```

--------------------------------

### Download File Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/get-small-file

A complete example of a GET request to download a file with a specific token and file ID.

```http
GET /api/get_small_file?token=98e8c0a6-3a41-48b8-a72b-0432e42a7fe5&file_id=11002  
  
Response:  
  
< HTTP/1.1 200  
< Vary: Origin  
< Vary: Access-Control-Request-Method  
< Vary: Access-Control-Request-Headers  
< Content-Disposition: attachment;fileName=ca.pem  
< Content-Type: application/json;charset=UTF-8  
< Transfer-Encoding: chunked  
  
... File Content ...  
```

--------------------------------

### FE Version Info Request Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/fe-version-info-action

A complete example showing the GET request and the corresponding JSON response.

```http
GET /api/fe_version_info

Response:
{
    "msg":"success",
    "code":0,
    "data":{
        "feVersionInfo":{
            "dorisBuildVersionPrefix":"doris",
            "dorisBuildVersionMajor":0,
            "dorisBuildVersionMinor":0,
            "dorisBuildVersionPatch":0,
            "dorisBuildVersionRcVersion":"trunk",
            "dorisBuildVersion":"doris-0.0.0-trunk",
            "dorisBuildHash":"git://4b7b503d1cb3/data/doris/doris/be/../@a04f9814fe5a09c0d9e9399fe71cc4d765f8bff1",
            "dorisBuildShortHash":"a04f981",
            "dorisBuildTime":"Fri, 09 Sep 2022 07:57:02 UTC",
            "dorisBuildInfo":"root@4b7b503d1cb3",
            "dorisJavaCompileVersion":"openjdk full version \"1.8.0_332-b09\""
        }
    },
    "count":0
}
```

--------------------------------

### Setup Table and Data

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/percentile-approx

Create a sample table and insert data for percentile calculations.

```sql
-- setup  
CREATE TABLE response_times (  
    request_id INT,  
    response_time DECIMAL(10, 2)  
) DUPLICATE KEY(`request_id`)  
DISTRIBUTED BY HASH(`request_id`) BUCKETS AUTO  
PROPERTIES (  
    "replication_allocation" = "tag.location.default: 1"  
);  
INSERT INTO response_times VALUES  
(1, 10.5),  
(2, 15.2),  
(3, 20.1),  
(4, 25.8),  
(5, 30.3),  
(6, 35.7),  
(7, 40.2),  
(8, 45.9),  
(9, 50.4),  
(10, 100.6);
```

--------------------------------

### Create Test Tables

Source: https://docs.velodb.io/cloud/26.x/query-acceleration/hints/leading-hint

Setup script to create the database and tables used for demonstrating join order hints.

```sql
CREATE DATABASE testleading;  
USE testleading;  
  
create table t1 (c1 int, c11 int) distributed by hash(c1) buckets 3 properties('replication_num' = '1');  
create table t2 (c2 int, c22 int) distributed by hash(c2) buckets 3 properties('replication_num' = '1');  
create table t3 (c3 int, c33 int) distributed by hash(c3) buckets 3 properties('replication_num' = '1');  
create table t4 (c4 int, c44 int) distributed by hash(c4) buckets 3 properties('replication_num' = '1');  
```

--------------------------------

### Extract Bitmap Subset Examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/scalar-functions/bitmap-functions/sub-bitmap

Examples demonstrating how to extract subsets from a Bitmap using various starting positions and cardinality limits.

```sql
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 0, 3)) value;
```

```sql
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), -3, 2)) value;
```

```sql
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 2, 100)) value;
```

```sql
select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 2, NULL)) value;
```

--------------------------------

### Example of SHOW CREATE USER

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/account-management/SHOW-CREATE-USER

An example showing the command usage and the resulting output format containing the user identity and creation statement.

```sql
SHOW CREATE USER '<user_identity>'
```

```text
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| User Identity | Create Stmt                                                                                                                                                                     |  
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| xxxxxxx       | CREATE USER 'xxxxxxx'@'%' IDENTIFIED BY ***  PASSWORD_HISTORY DEFAULT PASSWORD_EXPIRE INTERVAL 864000 SECOND FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 86400 SECOND COMMENT "" |  
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
```

--------------------------------

### Query Profile Example Request and Response

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/query-profile-action

A concrete example of a GET request for a specific query ID and its corresponding successful response.

```http
GET /rest/v2/manager/query/profile/fragments/d7c93d9275334c35-9e6ac5f295a7134b

Response:
{
    "msg": "success",
    "code": 0,
    "data": [
        {
            "fragment_id": "0",
            "time": "36.169ms",
            "instance_id": {
                "d7c93d9275334c35-9e6ac5f295a7134e": {
                    "host": "172.19.0.4:9060",
                    "active_time": "36.169ms"
                }
            }
        },
        {
            "fragment_id": "1",
            "time": "20.710ms",
            "instance_id": {
                "d7c93d9275334c35-9e6ac5f295a7134c": {
                    "host": "172.19.0.5:9060",
                    "active_time": "20.710ms"
                }
            }
        },
        {
            "fragment_id": "2",
            "time": "7.83ms",
            "instance_id": {
                "d7c93d9275334c35-9e6ac5f295a7134d": {
                    "host": "172.19.0.6:9060",
                    "active_time": "7.83ms"
                },
                "d7c93d9275334c35-9e6ac5f295a7134f": {
                    "host": "172.19.0.7:9060",
                    "active_time": "10.873ms"
                }
            }
        }
    ],
    "count": 0
}
```

--------------------------------

### Get load state request example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/get-load-state

Example request and response for retrieving the status of a load transaction using a specific label.

```http
GET /api/example_db/get_load_state?label=my_label

{
	"msg": "success",
	"code": 0,
	"data": "VISIBLE",
	"count": 0
}
```

--------------------------------

### Create and Drop Database Example

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/database/DROP-DATABASE

Demonstrates creating a test database and subsequently removing it.

```sql
CREATE DATABASE db_test;  
```

```sql
DROP DATABASE db_test;  
```

--------------------------------

### Create Branch Examples

Source: https://docs.velodb.io/cloud/26.x/lakehouse/catalogs/iceberg-catalog

Examples demonstrating various ways to create and configure branches with retention policies.

```SQL
-- Create branch "b1".  
ALTER TABLE tbl CREATE BRANCH b1;  
ALTER TABLE tb1 CREATE BRANCH IF NOT EXISTS b1;  
-- Create or replace branch "b1".  
ALTER TABLE tb1 CREATE OR REPLACE BRANCH b1;  
-- Create or replace branch "b1" based on snapshot "123456".  
ALTER TABLE tb1 CREATE OR REPLACE BRANCH b1 AS OF VERSION 123456;  
-- Create or replace branch "b1" based on snapshot "123456", branch retained for 1 day.  
ALTER TABLE tb1 CREATE OR REPLACE BRANCH b1 AS OF VERSION 123456 RETAIN 1 DAYS;  
-- Create branch "b1" based on snapshot "123456", branch retained for 30 days. Keep the latest 3 snapshots in the branch.  
ALTER TABLE tb1 CREATE BRANCH b1 AS OF VERSION 123456 RETAIN 30 DAYS WITH SNAPSHOT RETENTION 3 SNAPSHOTS;  
-- Create branch "b1" based on snapshot "123456", branch retained for 30 days. Snapshots in the branch are retained for at most 2 days.  
ALTER TABLE tb1 CREATE BRANCH b1 AS OF VERSION 123456 RETAIN 30 DAYS WITH SNAPSHOT RETENTION 2 DAYS;  
-- Create branch "b1" based on snapshot "123456", branch retained for 30 days. Keep the latest 3 snapshots in the branch, and snapshots in the branch are retained for at most 2 days.  
ALTER TABLE tb1 CREATE BRANCH b1 AS OF VERSION 123456 RETAIN 30 DAYS WITH SNAPSHOT RETENTION 3 SNAPSHOTS 2 DAYS;  
```

--------------------------------

### Get Load Job Information Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/get-load-info-action

Example request and response for retrieving load job details using a specific label.

```http
GET /api/example_db/_load_info?label=my_label

Response
{
	"msg": "success",
	"code": 0,
	"data": {
		"dbName": "default_cluster:db1",
		"tblNames": ["tbl1"],
		"label": "my_label",
		"clusterName": "default_cluster",
		"state": "FINISHED",
		"failMsg": "",
		"trackingUrl": ""
	},
	"count": 0
}
```

--------------------------------

### REGR_SXX Usage Example

Source: https://docs.velodb.io/enterprise/4.x/sql-manual/sql-functions/aggregate-functions/regr-sxx

Setup and execution of REGR_SXX on a sample table.

```sql
CREATE TABLE test_regr (  
  `id` int,  
  `x` double,  
  `y` double  
) DUPLICATE KEY (`id`)  
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO  
PROPERTIES (  
  "replication_allocation" = "tag.location.default: 1"  
);  
  
INSERT INTO test_regr VALUES  
(1, 0, NULL),  
(2, 1, 3),  
(2, 2, 5),  
(2, 3, 7),  
(2, 4, 9),  
(2, 5, NULL);
```

```sql
SELECT id, REGR_SXX(y, x) FROM test_regr GROUP BY id ORDER BY id;
```

```text
+------+--------------------+  
| id   | REGR_SXX(y, x)     |  
+------+--------------------+  
|    1 |               NULL |  
|    2 |                5.0 |  
+------+--------------------+
```

--------------------------------

### Setup Table and Data

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/approx-count-distinct

Creates a sample table and inserts data to demonstrate approximate distinct counting.

```sql
create table t1(  
        k1 int,  
        k_string varchar(100),  
        k_tinyint tinyint  
) distributed by hash (k1) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (1, 'apple', 10),  
    (1, 'banana', 20),  
    (1, 'apple', 10),  
    (2, 'orange', 30),  
    (2, 'orange', 40),  
    (2, 'grape', 50),  
    (3, null, null);
```

--------------------------------

### REGR_SYY Usage Example

Source: https://docs.velodb.io/enterprise/4.x/sql-manual/sql-functions/aggregate-functions/regr-syy

Setup and execution of the REGR_SYY function on a sample table.

```sql
CREATE TABLE test_regr (  
  `id` int,  
  `x` double,  
  `y` double  
) DUPLICATE KEY (`id`)  
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO  
PROPERTIES (  
  "replication_allocation" = "tag.location.default: 1"  
);  
  
INSERT INTO test_regr VALUES  
(1, 0, NULL),  
(2, 1, 3),  
(2, 2, 5),  
(2, 3, 7),  
(2, 4, 9),  
(2, 5, NULL);
```

```sql
SELECT id, REGR_SYY(y, x) FROM test_regr GROUP BY id ORDER BY id;
```

```text
+------+-----------------+  
| id   | REGR_SYY(y, x)  |  
+------+-----------------+  
|    1 |            NULL |  
|    2 |            20.0 |  
+------+-----------------+
```

--------------------------------

### Setup Table and Data

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/max

Creates a sample table and inserts data to demonstrate MAX function behavior.

```sql
-- setup  
create table t1(  
        k1 int,  
        k_string varchar(100),  
        k_decimal decimal(10, 2),  
        k_array array<int>  
) distributed by hash (k1) buckets 1  
properties ("replication_num"="1");  
insert into t1 values   
    (1, 'apple', 10.01, [10, 20, 30]),  
    (1, 'banana', 20.02, [10, 20]),  
    (2, 'orange', 30.03, [10, 20, 40]),  
    (2, null, null, [10, 20, null]),  
    (3, null, null, null);
```

--------------------------------

### Warm Up Examples

Source: https://docs.velodb.io/enterprise/4.x/lakehouse/data-cache

Examples demonstrating how to warm up entire tables, specific columns by partition, or specific columns by filter conditions.

```sql
WARM UP SELECT * FROM hive_db.tpch100_parquet.lineitem;
```

```sql
WARM UP SELECT l_orderkey, l_shipmode  
FROM hive_db.tpch100_parquet.lineitem  
WHERE dt = '2025-01-01';
```

```sql
WARM UP SELECT l_shipmode, l_linestatus  
FROM hive_db.tpch100_parquet.lineitem  
WHERE l_orderkey = 123456;
```

--------------------------------

### Setup response_times table

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/quantile-union

Create and populate a table to demonstrate quantile calculations.

```sql
-- setup  
CREATE TABLE response_times (  
    request_id INT,  
    response_time DOUBLE,  
    region STRING  
) DUPLICATE KEY(request_id)  
DISTRIBUTED BY HASH(request_id) BUCKETS AUTO  
PROPERTIES (  
    "replication_allocation" = "tag.location.default: 1"  
);  
INSERT INTO response_times VALUES  
(1, 10.5, 'east'),  
(2, 15.2, 'east'),  
(3, 20.1, 'west'),  
(4, 25.8, 'east'),  
(5, 30.3, 'west'),  
(6, 35.7, 'east'),  
(7, 40.2, 'west'),  
(8, 45.9, 'east'),  
(9, 50.4, 'west'),  
(10, 100.6, 'east');
```

--------------------------------

### Get Query Profile by ID

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/profile-action

Example request and response for retrieving a query profile.

```http
GET /api/profile?query_id=f732084bc8e74f39-8313581c9c3c0b58  
  
Response:  
{
	"msg": "success",
	"code": 0,
	"data": {
		"profile": "query profile ..."
	},
	"count": 0
}
```

--------------------------------

### Create User Example

Source: https://docs.velodb.io/enterprise/4.x/management-guide/workload-management/compute-group

Example syntax for creating a user with specific roles.

```sql
CREATE USER jack IDENTIFIED BY '123456' DEFAULT ROLE "admin"
```

```sql
CREATE USER jack1 IDENTIFIED BY '123456'
```

--------------------------------

### Setup sales_data table

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/percentile

Creates and populates a sample table for percentile calculations.

```sql
-- Setup  
CREATE TABLE sales_data  
(  
    product_id INT,  
    sale_price DECIMAL(10, 2)  
) DUPLICATE KEY(`product_id`)  
DISTRIBUTED BY HASH(`product_id`) BUCKETS AUTO  
PROPERTIES (  
    "replication_allocation" = "tag.location.default: 1"  
);  
INSERT INTO sales_data VALUES  
(1, 10.00),  
(1, 15.00),  
(1, 20.00),  
(1, 25.00),  
(1, 30.00),  
(1, 35.00),  
(1, 40.00),  
(1, 45.00),  
(1, 50.00),  
(1, 100.00);
```

--------------------------------

### Create Tables for Joined Delete

Source: https://docs.velodb.io/cloud/26.x/data-operate/delete/delete-manual

Setup tables required for the joined delete example.

```sql
CREATE TABLE t1  
    (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)  
UNIQUE KEY (id)  
DISTRIBUTED BY HASH (id)  
PROPERTIES('replication_num'='1', "function_column.sequence_col" = "c4");  
  
CREATE TABLE t2  
    (id INT, c1 BIGINT, c2 STRING, c3 DOUBLE, c4 DATE)  
DISTRIBUTED BY HASH (id)  
PROPERTIES('replication_num'='1');  
  
CREATE TABLE t3  
    (id INT)  
DISTRIBUTED BY HASH (id)  
PROPERTIES('replication_num'='1');  
```

--------------------------------

### Setup sales_data table

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-functions/aggregate-functions/percentile-array

Creates and populates a sample table for percentile calculations.

```sql
-- setup  
CREATE TABLE sales_data (  
    id INT,  
    amount DECIMAL(10, 2)  
) DUPLICATE KEY(`id`)  
DISTRIBUTED BY HASH(`id`) BUCKETS AUTO  
PROPERTIES (  
    "replication_allocation" = "tag.location.default: 1"  
);  
INSERT INTO sales_data VALUES  
(1, 10.5),  
(2, 15.2),  
(3, 20.1),  
(4, 25.8),  
(5, 30.3),  
(6, 35.7),  
(7, 40.2),  
(8, 45.9),  
(9, 50.4),  
(10, 100.6);
```

--------------------------------

### Obtain All FE Session Information Response

Source: https://docs.velodb.io/enterprise/4.x/management-guide/open-api/fe-http/session-action

Example JSON response for the GET /rest/v1/session/all endpoint.

```json
{
	"msg": "success",
	"code": 0,
	"data": {
		"column_names": ["FE", "Id", "User", "Host", "Cluster", "Db", "Command", "Time", "State", "Info"],
		"rows": [{
		    "FE": "10.14.170.23",
			"User": "root",
			"Command": "Sleep",
			"State": "",
			"Cluster": "default_cluster",
			"Host": "10.81.85.89:31465",
			"Time": "230",
			"Id": "0",
			"Info": "",
			"Db": "db1"
		},
		{
            "FE": "10.14.170.24",
			"User": "root",
			"Command": "Sleep",
			"State": "",
			"Cluster": "default_cluster",
			"Host": "10.81.85.88:61465",
			"Time": "460",
			"Id": "1",
			"Info": "",
			"Db": "db1"
		}]
	},
	"count": 2
}
```

--------------------------------

### Example of SHOW CREATE VIEW

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/table-and-view/view/SHOW-CREATE-VIEW

Demonstrates creating a view and then retrieving its definition using SHOW CREATE VIEW.

```sql
CREATE VIEW vtest AS SELECT 1, 'test';
SHOW CREATE VIEW vtest;
```

--------------------------------

### Set FE configuration examples

Source: https://docs.velodb.io/cloud/26.x/sql-manual/sql-statements/cluster-management/instance-management/SET-FRONTEND-CONFIG

Examples demonstrating how to set configuration for the current FE node versus all FE nodes in the cluster.

```sql
ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");
```

```sql
ADMIN SET ALL FRONTENDS CONFIG ("disable_balance" = "true");
```
Tokens:
Raw