StarRocks(SR)的基本概念、架构及基本使用详解

-- 示例：按天分区，按 user_id 分桶
create table user_behavior (
    event_time datetime,
    user_id bigint,
    item_id bigint,
    behavior varchar(32)
)
engine=olap
duplicate key(event_time, user_id)
partition by range(event_time) (
    partition p20241201 values less than ("2024-12-02"),
    partition p20241202 values less than ("2024-12-03")
)
distributed by hash(user_id) buckets 10;

-- 创建物化视图：按天统计 uv
create materialized view uv_daily_mv
as
select 
    date(event_time) as dt,
    bitmap_union(to_bitmap(user_id)) as uv
from user_behavior
group by date(event_time);

+---------------------+
|     client (mysql)  |
+----------+----------+
           |
     +-----v-----+
     |   fe (sql解析, 优化)  |
     +-----+-----+
           |
   +-------v--------+       +------------------+
   | be1 (tablet a) | <---> | be2 (tablet b)   |
   +----------------+       +------------------+
           |                        |
   +-------v--------+       +------v-----------+
   | be3 (副本)      |       | be4 (副本)        |
   +----------------+       +------------------+

create table sales (
    sale_date date,
    region varchar(32),
    product_id int,
    amount decimal(10,2),
    user_id bigint
)
engine=olap
aggregate key(sale_date, region, product_id)
partition by range(sale_date) (
    start ("2024-01-01") end ("2025-01-01") every (interval 1 day)
)
distributed by hash(product_id) buckets 16
properties(
    "replication_num" = "3",
    "storage_format" = "default"
);

curl --location-trusted -u user:passwd \
    -h "label:load_20241229" \
    -h "column_separator:," \
    -t data.csv http://fe_host:8030/api/db/sales/_stream_load

create routine load db.sales_kafka on sales
properties(
    "desired_concurrent_number"="3",
    "max_batch_interval" = "20"
)
from kafka(
    "kafka_broker_list" = "kafka:9092",
    "kafka_topic" = "sales_topic",
    "property.kafka_default_offsets" = "offset_beginning"
);

load label db.load_hdfs_001
(data infile("hdfs://path/to/data.parquet")
 into table sales
 format as "parquet")
with broker "my_broker";

-- 多表 join（支持 shuffle/broadcast）
select 
    u.city, 
    sum(s.amount) as total
from sales s
join user_dim u on s.user_id = u.id
where s.sale_date >= '2024-12-01'
group by u.city
order by total desc
limit 10;
-- bitmap 精确去重（uv）
select 
    count(distinct user_id)  -- 自动转为 bitmap_union
from user_behavior;

-- upsert（通过 stream load 或 routine load）
-- 新数据自动覆盖旧主键
-- delete（谨慎使用，仅支持 duplicate/unique 模型）
delete from sales where sale_date < '2023-01-01';