StarRocks

https://docs.starrocks.io/docs
Admin
StarRocks is a fast, cost-effective analytics database designed for real-time analytics and data...
Tokens:2,387,426
Snippets:18,072
Trust Score:8.7
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### Start FE and BE nodes with example output

Source: https://docs.starrocks.io/docs/administration/stargo

Example output for starting FE and BE nodes separately. The FE command starts all FE nodes, and the BE command starts all BE nodes. The output shows the startup progress for each node.

```plain
[root@nd1 sr-controller]# ./sr-ctl cluster start sr-c1 --role FE
[20220303-191529  OUTPUT] Start cluster [clusterName = sr-c1]
[20220303-191529    INFO] Starting FE cluster ....
[20220303-191529    INFO] Starting FE node [FeHost = 192.168.xx.xx, EditLogPort = 9010]
[20220303-191600    INFO] Starting FE node [FeHost = 192.168.xx.xx, EditLogPort = 9010]
[20220303-191610    INFO] Starting FE node [FeHost = 192.168.xx.xx, EditLogPort = 9010]

[root@nd1 sr-controller]# ./sr-ctl cluster start sr-c1 --role BE
[20220303-194215  OUTPUT] Start cluster [clusterName = sr-c1]
[20220303-194215    INFO] Starting BE node [BeHost = 192.168.xx.xx, HeartbeatServicePort = 9050]
[20220303-194216    INFO] Starting BE node [BeHost = 192.168.xx.xx, HeartbeatServicePort = 9050]
[20220303-194217    INFO] Starting BE node [BeHost = 192.168.xx.xx, HeartbeatServicePort = 9050]
[20220303-194217    INFO] Starting BE cluster ...

```

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

### SHOW PLUGINS example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/cluster-management/plugin/SHOW_PLUGINS

Example of showing the installed plugins.

```sql
SHOW PLUGINS;

```

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

### Use quickstart database

Source: https://docs.starrocks.io/docs/quick_start/shared-nothing

Selects the quickstart database before running the example queries.

```sql
USE quickstart;

```

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

### Start transaction: BEGIN WORK

Source: https://docs.starrocks.io/docs/loading/SQL_transaction

Alternative way to start a transaction in the example.

```sql
BEGIN WORK;

```

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

### Start a transaction: example

Source: https://docs.starrocks.io/docs/loading/Stream_Load_transaction_interface

Example curl command to begin a transaction with label streamload_txn_example1_table1, database test_db, and table table1.

```bash
curl --location-trusted -u <jack>:<123456> -H "label:streamload_txn_example1_table1" \
    -H "Expect:100-continue" \
    -H "db:test_db" -H "table:table1" \
    -XPOST http://<fe_host>:<fe_http_port>/api/transaction/begin

```

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

### Quick Start: docker-dev.sh commands

Source: https://docs.starrocks.io/docs/developers/development-environment/dev-env-setup

Quick start commands for the Docker-based development environment. Requires Docker installed and running, at least 8GB RAM for Docker, and at least 20GB free disk space.

```bash
# Open development shell
./docker-dev.sh shell

# Build Frontend only
./docker-dev.sh build-fe

# Build Backend only  
./docker-dev.sh build-be

# Build everything
./docker-dev.sh build-all

# Clean build everything
./docker-dev.sh clean-build

# Run Frontend tests
./docker-dev.sh test-fe

```

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

### Install and start NTP service

Source: https://docs.starrocks.io/docs/deployment/preparation/environment_configurations

Installs the NTP service if it is not present, then starts and enables the ntpd service.

```bash
sudo yum install ntp ntpdate && \
sudo systemctl start ntpd.service && \
sudo systemctl enable ntpd.service

```

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

### Create database and use it

Source: https://docs.starrocks.io/docs/quick_start/shared-nothing

Creates the 'quickstart' database and switches to it. Type these two lines at the StarRocks prompt and press enter after each.

```sql
CREATE DATABASE IF NOT EXISTS quickstart;

USE quickstart;
```

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

### Create and enter the quickstart directory

Source: https://docs.starrocks.io/docs/quick_start/shared-data

Create a directory named quickstart and change into it to store the lab files.

```bash
mkdir quickstart
cd quickstart

```

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

### Create database and table for quickstart

Source: https://docs.starrocks.io/docs/quick_start/routine-load

Create the quickstart database, verify it uses the s3_volume storage volume, then create the site_clicks table. The SHOW CREATE DATABASE output confirms the storage_volume property is set to s3_volume.

```sql
CREATE DATABASE IF NOT EXISTS quickstart;

```

```text
SHOW CREATE DATABASE quickstart \G

```

```sh
*************************** 1. row ***************************
       Database: quickstart
Create Database: CREATE DATABASE `quickstart`
PROPERTIES ("storage_volume" = "s3_volume")

```

```sql
USE quickstart;

```

```sql
CREATE TABLE site_clicks (
    `uid` bigint NOT NULL COMMENT "uid",
    `site` string NOT NULL COMMENT "site url",
    `vtime` bigint NOT NULL COMMENT "vtime"
)
DISTRIBUTED BY HASH(`uid`)
PROPERTIES("replication_num"="1");

```

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

### Start transaction: START TRANSACTION

Source: https://docs.starrocks.io/docs/loading/SQL_transaction

Starts a transaction in the example. Alternative: BEGIN WORK.

```sql
START TRANSACTION;

```

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

### Example 1: Get the current all pipes

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/meta-functions/inspect_all_pipes

Example 1: Get the current all pipes. The output shows an empty array `[]` when no pipes exist.

```text
mysql> select inspect_all_pipes();
+---------------------+
| inspect_all_pipes() |
+---------------------+
| []                  |
+---------------------+
1 row in set (0.01 sec)

```

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

### SHOW CREATE DATABASE example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/Database/SHOW_CREATE_DATABASE

Example of the SHOW CREATE DATABASE command and its output. The output includes the database name and the SQL command used to create it.

```sql
mysql > show create database zj_test;
+----------+---------------------------+
| Database | Create Database           |
+----------+---------------------------+
| zj_test  | CREATE DATABASE `zj_test` |
+----------+---------------------------+

```

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

### Example 4: Start range exceeds maximum returns NULL

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/bitmap-functions/bitmap_subset_limit

Example 4: When the start range (10) exceeds the maximum element of the BITMAP and the limit is positive, NULL is returned. Note: the example uses bitmap_subset_in_range instead of bitmap_subset_limit.

```plaintext
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 10, 15)) value;
+-------+
| value |
+-------+
| NULL  |
+-------+
```

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

### SHOW REPOSITORIES example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/backup_restore/SHOW_REPOSITORIES

Example of viewing all repositories created in StarRocks.

```sql
SHOW REPOSITORIES;

```

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

### Create database and verify storage volume

Source: https://docs.starrocks.io/docs/quick_start/shared-data

Create the quickstart database and verify it uses the s3_volume storage volume with SHOW CREATE DATABASE. The output confirms the storage_volume property is set to s3_volume.

```text
CREATE DATABASE IF NOT EXISTS quickstart;

```

```text
SHOW CREATE DATABASE quickstart \G

```

```sh
*************************** 1. row ***************************
       Database: quickstart
Create Database: CREATE DATABASE `quickstart`
PROPERTIES ("storage_volume" = "s3_volume")

```

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

### Example 1: Obtain 4 elements starting from 1

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/bitmap-functions/bitmap_subset_limit

Example 1: Obtains 4 elements from the BITMAP value starting from element value 1. The output is 1,3,5,7.

```plaintext
select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 1, 4)) value;
+---------+
|  value  |
+---------+
| 1,3,5,7 |
+---------+
```

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

### Install sqlalchemy-starrocks from source

Source: https://docs.starrocks.io/docs/integrations/superset_support

Installs the starrocks dialect from source code. Requires root privileges when installing inside a Docker-based Superset setup.

```shell
pip install .

```

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

### SHOW PROCESSLIST Example with Output

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROCESSLIST

Example 1: lists the operations state via the user root. The output shows the fields returned by SHOW PROCESSLIST, including ServerName, Id, User, Host, Db, Command, ConnectionStartTime, Time, State, Info, IsPending, Warehouse, CNGroup, Catalog, and QueryId.

```sql
SHOW PROCESSLIST\G

*************************** 6. row ***************************
         ServerName: starrocks-fe_9010_1782850099498
                 Id: 33560554
               User: root
               Host: 172.18.0.4:54818
                 Db:
            Command: Query
ConnectionStartTime: 2026-07-02 01:21:14
               Time: 0
              State: OK
               Info: show processlist
          IsPending: false
          Warehouse: default_warehouse
            CNGroup:
            Catalog: default_catalog
            QueryId: 019f1eb3-246c-7899-ab3e-40a018645bba
```

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

### locate example with pos

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/string-functions/locate

Example of locating 'bar' in 'foobarbar' starting from position 5. Returns 7.

```plaintext
MySQL > SELECT LOCATE('bar', 'foobarbar', 5);
+-------------------------------+
| locate('bar', 'foobarbar', 5) |
+-------------------------------+
|                             7 |
+-------------------------------+
```

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

### Prepare Querybook repository and start container

Source: https://docs.starrocks.io/docs/integrations/BI_integrations/Querybook

Steps to clone the Querybook repository, create a local requirements file, add the required packages (starrocks and mysqlclient), and start the container.

```sql
git clone git@github.com:pinterest/querybook.git
cd querybook

```

```sql
touch requirements/local.txt

```

```sql
echo -e "starrocks\nmysqlclient" > requirements/local.txt 

```

```sql
make

```

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

### locate example without pos

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/string-functions/locate

Example of locating 'bar' in 'foobarbar' without a starting position. Returns 4.

```plaintext
MySQL > SELECT LOCATE('bar', 'foobarbar');
+----------------------------+
| locate('bar', 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+
```

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

### SHOW CREATE VIEW Example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/View/SHOW_CREATE_VIEW

Displays the CREATE VIEW statement for example_view in the example_db database. The output includes the view name, the CREATE VIEW statement, and character set information.

```plain
SHOW CREATE VIEW example_db.example_view;

+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View         | Create View                                                                                                                                                                                                                                                                                                                     | character_set_client | collation_connection |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| example_view | CREATE VIEW `example_view` (k1, k2, k3, v1) COMMENT "VIEW" AS SELECT `default_cluster:db1`.`example_table`.`k1` AS `k1`, `default_cluster:db1`.`example_table`.`k2` AS `k2`, `default_cluster:db1`.`example_table`.`k3` AS `k3`, `default_cluster:db1`.`example_table`.`v1` AS `v1` FROM `default_cluster:db1`.`example_table`; | utf8                 | utf8_general_ci      |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

```

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

### Example: milliseconds_diff with date-only start

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/date-time-functions/milliseconds_diff

Shows the result when the start time is a date without time. The difference is 1000 milliseconds.

```plaintext
select milliseconds_diff('2024-01-25 00:00:01', '2024-01-25');
+--------------------------------------------------------+
| milliseconds_diff('2024-01-25 00:00:01', '2024-01-25') |
+--------------------------------------------------------+
|                                                   1000 |
+--------------------------------------------------------+
```

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

### Create BE integration configuration file

Source: https://docs.starrocks.io/docs/integrations/datadog_support

Creates the BE integration configuration directory and copies the example configuration file to the expected location.

```shell
sudo mkdir -p /etc/datadog-agent/conf.d/starrocks_be.d
sudo cp contrib/datadog-connector/starrocks_be/datadog_checks/starrocks_be/data/conf.yaml.example /etc/datadog-agent/conf.d/starrocks_be.d/conf.yaml
```

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

### Create demo database and users table

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/prepared_statement

Creates the demo database and users table used in the prepared statement examples.

```sql
CREATE DATABASE IF NOT EXISTS demo;
USE demo;
CREATE TABLE users (
  id BIGINT NOT NULL,
  country STRING,
  city STRING,
  revenue BIGINT
)
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);

```

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

### starts_with examples with mysql output

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/string-functions/starts_with

Two example queries showing starts_with returning 1 when the string starts with the prefix and 0 when it does not.

```plain
mysql> select starts_with("hello world","hello");
+-------------------------------------+
|starts_with('hello world', 'hello')  |
+-------------------------------------+
| 1                                   |
+-------------------------------------+

mysql> select starts_with("hello world","world");
+-------------------------------------+
|starts_with('hello world', 'world')  |
+-------------------------------------+
| 0                                   |
+-------------------------------------+

```

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

### Get boolean value for key

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_bool

Example 1: Gets the value for key "k1" which is true, returning 1.

```plain
MySQL > SELECT get_json_bool('{"k1":true, "k2":"false"}', "$.k1");
+----------------------------------------------------+
| get_json_bool('{"k1":true, "k2":"false"}', '$.k1') |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
```

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

### SHOW FULL FUNCTIONS example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/Function/SHOW_FUNCTIONS

Example output of SHOW FULL FUNCTIONS in a database, showing custom function signatures and properties.

```plain
mysql> show full functions in testDb\G
*************************** 1. row ***************************
        Signature: my_add(INT,INT)
      Return Type: INT
    Function Type: Scalar
Intermediate Type: NULL
       Properties: {"symbol":"_ZN9starrocks_udf6AddUdfEPNS_15FunctionContextERKNS_6IntValES4_","object_file":"http://host:port/libudfsample.so","md5":"cfe7a362d10f3aaf6c49974ee0f1f878"}
*************************** 2. row ***************************
        Signature: my_count(BIGINT)
      Return Type: BIGINT
    Function Type: Aggregate
Intermediate Type: NULL
       Properties: {"object_file":"http://host:port/libudasample.so","finalize_fn":"_ZN9starrocks_udf13CountFinalizeEPNS_15FunctionContextERKNS_9BigIntValE","init_fn":"_ZN9starrocks_udf9CountInitEPNS_15FunctionContextEPNS_9BigIntValE","merge_fn":"_ZN9starrocks_udf10CountMergeEPNS_15FunctionContextERKNS_9BigIntValEPS2_","md5":"37d955f80f56969e2676da3d5b5b9d2f","update_fn":"_ZN9starrocks_udf11CountUpdateEPNS_15FunctionContextERKNS_6IntValEPNS_9BigIntValE"}

2 rows in set (0.00 sec)

```

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

### time_slice function examples

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/date-time-functions/time_slice

Examples demonstrating how to use the time_slice function to convert DATETIME values to the start or end of specified time intervals.

```APIDOC
## time_slice

### Description
Converts a given DATETIME value to the beginning or end of a specified time interval.

### Syntax
`time_slice(datetime, INTERVAL N unit, boundary)`

### Parameters
- `datetime` (DATETIME) - Required - The input datetime value.
- `INTERVAL N unit` - Required - The interval size and unit (e.g., 5 second, 5 day, 1 millisecond, 1 microsecond).
- `boundary` (optional) - The boundary setting: `FLOOR` (default) returns the beginning of the interval, `CEIL` returns the end of the interval.

### Examples

#### Example 1: Convert to beginning of a 5-second interval (default boundary)
```sql
select time_slice(id_datetime, interval 5 second)
from test_all_type_select
order by id_int;
```

#### Example 2: Convert to beginning of a 5-day interval with FLOOR
```sql
select time_slice(id_datetime, interval 5 day, FLOOR)
from test_all_type_select
order by id_int;
```

#### Example 3: Convert to end of a 5-day interval with CEIL
```sql
select time_slice(id_datetime, interval 5 day, CEIL)
from test_all_type_select
order by id_int;
```

#### Example 4: Convert to end of a 1-millisecond interval with CEIL
```sql
select id_datetime, time_slice(id_datetime, interval 1 millisecond, CEIL)
from test_all_type_select
order by id_int;
```

#### Example 5: Convert to end of a 1-microsecond interval with CEIL
```sql
select id_datetime, time_slice(id_datetime, interval 1 microsecond, CEIL)
from test_all_type_select
order by id_int;
```
```

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

### Check Thrift and Protobuf installation paths

Source: https://docs.starrocks.io/docs/developers/development-environment/IDEA

Use 'brew list' to inspect the installation directories of Thrift and Protobuf, as they may vary.

```bash
brew list thrift@0.13.0
brew list protobuf
```

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

### Get root-level VARIANT type

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/variant-functions/variant_typeof

Example 1: Gets the type of a VARIANT value at the root level. The output shows the type is 'Object'.

```sql
SELECT variant_typeof(data) AS type
FROM bluesky
LIMIT 1;

```

```plaintext
+--------+
| type   |
+--------+
| Object |
+--------+

```

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

### SYNC example output

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/cluster-management/nodes_processes/SYNC

Example execution of the SYNC statement, showing the expected output.

```plain
mysql> SYNC;
Query OK, 0 rows affected (0.00 sec)

```

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

### Example 3: NULL when end range less than start range

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/bitmap-functions/bitmap_subset_in_range

Returns NULL because the end range 3 is less than the start range 4.

```plaintext
select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,1,3,1,5,3,5,7,7,9'), 4, 3)) value;
+-------+
| value |
+-------+
| NULL  |
+-------+

```

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

### SHOW DYNAMIC PARTITION TABLES Example

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/SHOW_DYNAMIC_PARTITION_TABLES

Example that displays the status of all partitioned tables with dynamic partitioning properties configured in the database 'db_test'.

```sql
SHOW DYNAMIC PARTITION TABLES FROM db_test;

```

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

### Get value for key 'k1'

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/json-functions/json-query-and-processing-functions/get_json_int

Example query that gets the integer value for key 'k1' from a JSON string. The output shows the result 1.

```plaintext
MySQL > SELECT get_json_int('{"k1":1, "k2":"2"}', "$.k1");
+--------------------------------------------+
| get_json_int('{"k1":1, "k2":"2"}', '$.k1') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+

```

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

### Create and populate sales_record table

Source: https://docs.starrocks.io/docs/sql-reference/sql-functions/Window_function

Creates a sample table sales_record, inserts data, and queries it to show the initial data used in the QUALIFY examples.

```sql
-- Create a table.
CREATE TABLE sales_record (
   city_id INT,
   item STRING,
   sales INT
) DISTRIBUTED BY HASH(`city_id`);

-- Insert data into the table.
insert into sales_record values
(1,'fruit',95),
(2,'drinks',70),
(3,'fruit',87),
(4,'drinks',98);

-- Query data from the table.
select * from sales_record order by city_id;
+---------+--------+-------+
| city_id | item   | sales |
+---------+--------+-------+
|       1 | fruit  |    95 |
|       2 | drinks |    70 |
|       3 | fruit  |    87 |
|       4 | drinks |    98 |
+---------+--------+-------+

```

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

### Create example table details and insert data

Source: https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE

Creates the example table 'details' as a Duplicate Key table with hash bucketing on user_id and partitions by day. Also inserts sample data for three days (November 26-28, 2023) to be used in subsequent ALTER TABLE examples.

```sql
CREATE TABLE IF NOT EXISTS details (
    event_time DATETIME NOT NULL COMMENT "datetime of event",
    event_type INT NOT NULL COMMENT "type of event",
    user_id INT COMMENT "id of user",
    device_code INT COMMENT "device code",
    channel INT COMMENT ""
)
DUPLICATE KEY(event_time, event_type)
PARTITION BY date_trunc('day', event_time)
DISTRIBUTED BY HASH(user_id);

-- Insert data of several days
INSERT INTO details (event_time, event_type, user_id, device_code, channel) VALUES
-- Data of November 26th
('2023-11-26 08:00:00', 1, 101, 12345, 2),
('2023-11-26 09:15:00', 2, 102, 54321, 3),
('2023-11-26 10:30:00', 1, 103, 98765, 1),
-- Data of November 27th
('2023-11-27 08:30:00', 1, 104, 11111, 2),
('2023-11-27 09:45:00', 2, 105, 22222, 3),
('2023-11-27 11:00:00', 1, 106, 33333, 1),
-- Data of November 28th
('2023-11-28 08:00:00', 1, 107, 44444, 2),
('2023-11-28 09:15:00', 2, 108, 55555, 3),
('2023-11-28 10:30:00', 1, 109, 66666, 1);
```

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

### Verify AuditLoader installation with SHOW PLUGINS output

Source: https://docs.starrocks.io/docs/administration/management/audit_loader

Output of SHOW PLUGINS showing the AuditLoader plugin with Status INSTALLED, indicating successful installation. The example also shows the built-in audit logger plugin.

```plain
mysql> SHOW PLUGINS\G
*************************** 1. row ***************************
    Name: __builtin_AuditLogBuilder
    Type: AUDIT
Description: builtin audit logger
    Version: 0.12.0
JavaVersion: 1.8.31
ClassName: com.starrocks.qe.AuditLogBuilder
    SoName: NULL
    Sources: Builtin
    Status: INSTALLED
Properties: {}
*************************** 2. row ***************************
    Name: AuditLoader
    Type: AUDIT
Description: Available for versions 3.3.11+. Load audit log to starrocks, and user can view the statistic of queries
    Version: 5.0.0
JavaVersion: 11
ClassName: com.starrocks.plugin.audit.AuditLoaderPlugin
    SoName: NULL
    Sources: /x/xx/xxx/xxxxx/auditloader.zip
    Status: INSTALLED
Properties: {}
2 rows in set (0.01 sec)
```
Tokens:
Raw