REPLACE_IF_NOT_NULL

MAX

r6in.8xlarge

enable_persistent_index

true

LOCAL

partial_update_mode = 'column'

update_compaction_num_threads_per_disk

update_compaction_per_tablet_min_interval_seconds

lake_enable_batch_publish_version

r6in.8xlarge

r6in

c6id

m6id

batch_publish

star_cache

persistent_index

APPLY

REPLACE_IF_NOT_NULL

r6in

m6id

=

IN

>

<

IS NULL

campaign_id

WHERE campaign_id = 123 AND status = 'active' AND region = 'US'

=

WHERE campaign_id = ?

campaign_id

IN (x, x, x)

WHERE campaign_id IN (1, 2, 3) AND status = 'active' AND country = 'US'

AND

Apply

ORDER BY

campaign_id

ORDER BY

campaign_id

IN

IN

IN

m6id

c6id

ORDER BY

sql
CREATE TABLE my_table (
    id BIGINT NOT NULL,
    campaign_id INT NOT NULL,
    timestamp DATETIME,
    ...
)
PRIMARY KEY (id)                 -- Used for uniqueness and indexing updates
ORDER BY (campaign_id, timestamp) -- Used for physical storage order and Prefix Index
DISTRIBUTED BY HASH(id);

campaign_id

sql
ALTER TABLE my_table ORDER BY (campaign_id, timestamp);

ORDER BY

campaign_id

ORDER BY

campaign_id

ORDER BY

campaign_id

ALTER TABLE ... ORDER BY (campaign_id, ...)

ALTER TABLE ... ORDER BY

sql
ALTER TABLE requests ORDER BY (received, auction_id, campaign_id);

ORDER BY

received

DATETIME

auction_id

BIGINT

campaign_id

auction_id

VARCHAR

campaign_id

ALTER TABLE ... ORDER BY

WHERE campaign_id IN (...)

sql
    ALTER TABLE requests SET ("bloom_filter_columns" = "campaign_id");

ORDER BY

campaign_id

received

received

received

campaign_id

received

auction_id

auction_id

auction_id

campaign_id

IN (x, x, x)

campaign_id

campaign_id

ORDER BY

sql
ALTER TABLE requests ORDER BY (campaign_id, auction_id);

campaign_id

IN

auction_id

auction_id

campaign_id

received

auction_id

ORDER BY (received, auction_id, campaign_id)

auction_id

campaign_id

IN (campaign_id)

campaign_id

ORDER BY (campaign_id, auction_id)

received

ORDER BY

auction_id

campaign_id

INT

(received, auction_id, campaign_id)

campaign_id

ORDER BY

received

auction_id

campaign_id

(campaign_id, auction_id)

campaign_id

campaign_id

auction_id

WHERE campaign_id IN (1234567, 7654321)

auction_id

received

received

ALTER TABLE

campaign_id

sql
    ALTER TABLE requests SET ("bloom_filter_columns" = "campaign_id");

sql
    ALTER TABLE requests ORDER BY (campaign_id, auction_id);

campaign_id

auction_id

ORDER BY

MemTable

MemTable

campaign_id

INT

auction_id

campaign_id

auction_id

INT

(INT, VARCHAR(32))

auction_id

ORDER BY

auction_id

sql
-- This keeps your queries fast and speeds up ingestion
ALTER TABLE requests ORDER BY (campaign_id);

WHERE campaign_id IN (...)

ARRAY<STRING>

ARRAY

VARCHAR

ARRAY

WHERE array_contains(tags, 'promo')

'promo'

WHERE tags = ['a', 'b']

array_contains

array_contains

arrays_overlap

array_contains(arr, val)

arrays_overlap(arr1, arr2)

val

array_contains

OR array_contains(...)

array_contains_any

arrays_overlap

VARCHAR

MATCH

array_contains

item_id

tag

campaign_id

INT

campaign_id

auction_id

campaign_id

received

received

campaign_id

received

ORDER BY (campaign_id)

(campaign_id, received)

ORDER BY (campaign_id)

ORDER BY (campaign_id, received)

auction_id

campaign_id

ALTER TABLE ... ORDER BY

sql
    SHOW ALTER TABLE COLUMN;

State

RUNNING

FINISHED

Progress

10/100

sql
    SHOW TABLET FROM requests WHERE State="ALTER";

ALTER

ALTER TABLE

18:00:07

ALTER

ALTER

2/3200

sql
CANCEL ALTER TABLE COLUMN FROM requests;

EXPLAIN ANALYZE

received

sql
-- This creates partitions for the next 48 hours
EXPLAIN ANALYZE
INSERT INTO requests (received)
SELECT DATE_ADD(NOW(), INTERVAL d hour)
FROM table(generate_series(1, 48)) AS g(d);

sql
SHOW PARTITIONS FROM requests;

sql
ALTER TABLE requests ORDER BY (campaign_id);

persistent_index

ALTER

sql
    -- Execute this before running the ALTER command
    SET admin_replica_column_scan_threads = 8;

ALTER

received

auction_id

imp_key

auction_id

received

auction_id

partition_live_number

partition_live_number

Current Time - (partition_live_number _ hour)

ALTER TABLE

partition_live_number

sql
ALTER TABLE requests SET ("partition_live_number" = "96");

ALTER

sql
-- Create partitions for the next 48 hours
EXPLAIN ANALYZE
INSERT INTO requests (received)
SELECT DATE_ADD(NOW(), INTERVAL d hour)
FROM table(generate_series(1, 48)) AS g(d);

sql
ALTER TABLE requests ORDER BY (campaign_id);

Alter job conflicts with partition creation

ALTER

ALTER

FINISHED

SHOW ALTER TABLE COLUMN

sql
ALTER TABLE requests SET ("partition_live_number" = "24");

5609 (number of partition's index is not equal with schema's)

ALTER TABLE

RUNNING

ALTER TABLE ... ORDER BY

INSERT

EXPLAIN ANALYZE

sql
CANCEL ALTER TABLE COLUMN FROM requests;

FINISHED

CANCELLED

SHOW ALTER TABLE COLUMN;

EXPLAIN ANALYZE

sql
-- This creates partitions for the next 72 hours to give you plenty of time
EXPLAIN ANALYZE
INSERT INTO requests.requests (received, auction_id, imp_key)
SELECT DATE_ADD(NOW(), INTERVAL d hour), 'dry_run_id', 'dry_run_key'
FROM table(generate_series(1, 72)) AS g(d);

sql
SHOW PARTITIONS FROM requests;

sql
ALTER TABLE requests ORDER BY (campaign_id);

ALTER

sql
-- Increase threads for the background scan/sort (default is usually 1 or 2)
SET admin_replica_column_scan_threads = 4;

ORDER BY

ALTER TABLE

ALTER

PARTITION BY date_trunc('hour', received)

ORDER BY

EXPLAIN ANALYZE INSERT

ALTER TABLE ... ORDER BY

memory_limitation_per_thread_for_schema_change

<be_ip>

8040

bash
curl -X POST http://<be_ip>:8040/api/update_config?memory_limitation_per_thread_for_schema_change=8

SHOW ALTER TABLE COLUMN;

CANCELLED

CANCEL ALTER TABLE COLUMN FROM requests;

sql
EXPLAIN ANALYZE
INSERT INTO requests.requests (received, auction_id, imp_key)
SELECT DATE_ADD(NOW(), INTERVAL d hour), 'dry_run', 'dry_run'
FROM table(generate_series(1, 72)) AS g(d);

sql
ALTER TABLE requests ORDER BY (campaign_id);

ORDER BY

ALTER

Limit: 2147483648

curl

172.30.75.179

bash
curl <http://172.30.75.179:8040/api/show_config?conf=memory_limitation_per_thread_for_schema_change>

2

curl -X POST

8040

be_http_port

8

bash
    curl -X POST "http://<be_ip>:8040/api/update_config?memory_limitation_per_thread_for_schema_change=8"

alter_tablet_worker_count

memory_limitation_per_thread_for_schema_change

Limit: 8589934592

Used: 8.1GB, Limit: 8GB

16

memory_limitation_per_thread_for_schema_change * memory_ratio_for_sorting_schema_change

0.8

0.5

bash
        curl -X POST "http://<be_ip>:8040/api/update_config?memory_ratio_for_sorting_schema_change=0.5"

show_config

8

2

update_config

memory_ratio_for_sorting_schema_change

Sorted Schema Change

ALTER

sql
    ALTER TABLE requests DROP PARTITION p20240101; -- Repeat for old data

alter_tablet_worker_count

bash
    curl -X POST "http://<be_ip>:8040/api/update_config?alter_tablet_worker_count=10"

ALTER TABLE

ORDER BY

sql
    INSERT INTO requests_new
    SELECT * FROM requests
    WHERE received >= '2024-01-01 00:00:00' AND received < '2024-01-02 00:00:00';

sql
    ALTER TABLE requests SWAP WITH requests_new;

Limit: 2147483648

be.conf

curl

curl

172.30.75.179

curl <http://172.30.75.179:8040/api/show_config?conf=memory_limitation_per_thread_for_schema_change>

SWAP

alter_tablet_worker_count

ALTER

ARRAY<INT>

BITMAP

BITMAP

ARRAY_CONTAINS

BITMAP_CONTAINS

ARRAY<INT>

sql
CREATE TABLE user_tags (
    user_id BIGINT,
    tag_bits BITMAP -- Instead of ARRAY<INT>
) 
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id);

to_bitmap()

bitmap_from_string()

sql
-- If inserting from an existing array column
INSERT INTO user_tags
SELECT user_id, bitmap_from_array(my_old_array_column) FROM old_table;

-- If inserting individual values (e.g., in a Stream Load or INSERT)
INSERT INTO user_tags VALUES (1, to_bitmap(12345));

array_contains

sql
-- Old way (Slow)
SELECT * FROM table WHERE array_contains(my_array, 12345);

-- New way (Fast)
SELECT * FROM table WHERE bitmap_contains(tag_bits, 12345);

AGGREGATE KEY

BITMAP_UNION

sql
CREATE TABLE user_behavior (
    user_id BIGINT,
    viewed_ids BITMAP BITMAP_UNION
)
AGGREGATE KEY(user_id)
DISTRIBUTED BY HASH(user_id);

-- Every time you insert a single ID, it's added to the "set" for that user
INSERT INTO user_behavior VALUES (1, to_bitmap(55555));

array_to_bitmap

BITMAP

sql
CREATE TABLE my_table (
    user_id BIGINT,
    tags BITMAP -- This will store your array of 5-digit ints
) 
DUPLICATE KEY(user_id)
DISTRIBUTED BY HASH(user_id);

{"user_id": 1, "raw_tags": [12345, 67890]}

$.raw_tags

tmp_array

tags = array_to_bitmap(tmp_array)

net/http

go
func StreamLoad(jsonData []byte) {
    url := "http://<FE_HOST>:<FE_HTTP_PORT>/api/my_db/my_table/_stream_load"
    req, _ := http.NewRequest("PUT", url, bytes.NewBuffer(jsonData))
    
    // Auth
    req.SetBasicAuth("admin", "password")
    
    // Required StarRocks Headers
    req.Header.Set("Expect", "100-continue")
    req.Header.Set("format", "json")
    req.Header.Set("strip_outer_array", "true") // If sending a list of objects
    
    // The Conversion Magic
    // 1. Tell SR where to find the array in your JSON
    req.Header.Set("jsonpaths", `["$.user_id", "$.raw_tags"]`)
    
    // 2. Map JSON fields to table columns + conversion
    // user_id: maps directly
    // tmp_array: a temporary name for the raw JSON array
    // tags: the actual table column, populated by the function
    req.Header.Set("columns", `user_id, tmp_array, tags=array_to_bitmap(tmp_array)`)

    client := &http.Client{}
    resp, err := <http://client.Do|client.Do>(req)
    // ... handle response
}

array_to_bitmap

[12345, 12345]

BITMAP

tags = base64_to_bitmap(tmp_base64_string)

array_to_bitmap

BITMAP

DUPLICATE KEY

sql
CREATE TABLE user_tags (
    user_id BIGINT,
    -- This column receives the raw JSON array from your Go code
    tags_raw ARRAY<INT>, 
    -- This generated column automatically converts the array to a bitmap
    tags_bitmap BITMAP AS array_to_bitmap(tags_raw)
) 
PRIMARY KEY(user_id) -- Must be Primary or Unique Key to support BITMAP
DISTRIBUTED BY HASH(user_id);

{"user_id": 1, "tags_raw": [10001, 10002]}

tags_raw

tags_bitmap

sql
    SELECT * FROM user_tags WHERE bitmap_contains(tags_bitmap, 10001);

ARRAY

columns

DUPLICATE KEY

BITMAP

ARRAY

array_to_bitmap

tags=array_to_bitmap(tags_json)

array_contains

select rule_group_ids_bitmap from requests where rule_group_ids_bitmap is not null;

BITMAP

INT

bitmap type couldnt as predicate

BITMAP

WHERE

IS NOT NULL

bitmap_count()

sql
-- This checks if the bitmap contains at least one integer
SELECT rule_group_ids_bitmap 
FROM requests 
WHERE bitmap_count(rule_group_ids_bitmap) > 0;

SELECT

NULL

sql
SELECT bitmap_to_string(rule_group_ids_bitmap) 
FROM requests 
WHERE bitmap_count(rule_group_ids_bitmap) > 0;

sql
    SELECT * FROM requests WHERE bitmap_contains(rule_group_ids_bitmap, 12345);

sql
    SELECT bitmap_count(rule_group_ids_bitmap) FROM requests;

sql
    SELECT bitmap_count(bitmap_intersect(col1, col2)) FROM table;

sql
SELECT 
    bitmap_count(rule_group_ids_bitmap) as qty, 
    bitmap_to_string(rule_group_ids_bitmap) as contents
FROM requests 
WHERE rule_group_ids_bitmap IS NOT NULL 
  AND bitmap_count(rule_group_ids_bitmap) > 0;

ARRAY

BITMAP

BITMAP

BITMAP

bitmap_contains

ARRAY

array_contains

BITMAP

ARRAY

BITMAP

BITMAP

UNION

INTERSECT

BITMAP

bitmap_union_count

ARRAY<INT>

array_contains

BITMAP

ARRAY