StarRocks测试

创建主键表

CREATE TABLE `dim_org` (
  `dt` varchar(65533) NOT NULL COMMENT "",
  `data_from` varchar(65533) NOT NULL COMMENT "",
  `org_no` varchar(65533) NOT NULL COMMENT "",
  `address` varchar(65533) NULL COMMENT "",
  `area` varchar(65533) NULL COMMENT "",
  `beactive` varchar(65533) NULL COMMENT "",
  `org_code` varchar(65533) NULL COMMENT "",
  `cost_center` varchar(65533) NULL COMMENT "",
  `org_name` varchar(65533) NULL COMMENT "",
  `org_scale` varchar(65533) NULL COMMENT "",
  `is_head` varchar(65533) NULL COMMENT "",
  `org_attr` varchar(65533) NULL COMMENT "",
  `open_date` varchar(65533) NULL COMMENT "",
  `last_modify_time` varchar(65533) NULL COMMENT "",
  `store_type` varchar(65533) NULL COMMENT "",
  `org_type` varchar(65533) NULL COMMENT "",
  `phone` varchar(65533) NULL COMMENT "",
  `contact` varchar(65533) NULL COMMENT "",
  `head_count` int(11) NULL COMMENT "",
  `parent_org` varchar(65533) NULL COMMENT "",
  `data_center` varchar(65533) NULL COMMENT "",
  `short_name` varchar(65533) NULL COMMENT "",
  `region` varchar(65533) NULL COMMENT "",
  `rec_balance` varchar(65533) NULL COMMENT "",
  `oper_area` varchar(65533) NULL COMMENT "",
  `close_date` varchar(65533) NULL COMMENT "",
  `tax_type` varchar(65533) NULL COMMENT "",
  `is_estore` varchar(65533) NULL COMMENT "",
  `is_join` varchar(65533) NULL COMMENT "",
  `is_medicare` varchar(65533) NULL COMMENT "",
  `medicare_date` varchar(65533) NULL COMMENT "",
  `is_buy` varchar(65533) NULL COMMENT "",
  `buy_date` varchar(65533) NULL COMMENT "",
  `is_close` varchar(65533) NULL COMMENT "",
  `house_type` varchar(65533) NULL COMMENT "",
  `retail_format` varchar(65533) NULL COMMENT "",
  `area_type` varchar(65533) NULL COMMENT "",
  `sale_type` varchar(65533) NULL COMMENT "",
  `new_old` varchar(65533) NULL COMMENT "",
  `buss_type` varchar(65533) NULL COMMENT "",
  `majr_deficit` varchar(65533) NULL COMMENT "",
  `city` varchar(65533) NULL COMMENT "",
  `majr_org` varchar(65533) NULL COMMENT "",
  `district` varchar(65533) NULL COMMENT "",
  `mdm_org_no` varchar(65533) NULL COMMENT "",
  `managstate` varchar(65533) NULL COMMENT "",
  `saleslevel` varchar(65533) NULL COMMENT "",
  `businessdistrict` varchar(65533) NULL COMMENT "",
  `openingt` varchar(65533) NULL COMMENT "",
  `openinge` varchar(65533) NULL COMMENT "",
  `storestatus` varchar(65533) NULL COMMENT "",
  `timeline` varchar(65533) NULL COMMENT "",
  `province` varchar(65533) NULL COMMENT "",
  `marposition` varchar(65533) NULL COMMENT "",
  `operarea` varchar(65533) NULL COMMENT "",
  `tradingarea` varchar(65533) NULL COMMENT "",
  `storegroup_id` varchar(65533) NULL COMMENT "",
  `storegroup_name` varchar(65533) NULL COMMENT "",
  `old_data_from` varchar(65533) NULL COMMENT "",
  `company_code` varchar(65533) NULL COMMENT "",
  `old_company_code` varchar(65533) NULL COMMENT "",
  `mdm_company_code` varchar(65533) NULL COMMENT "",
  `marposition_raw` varchar(65533) NULL COMMENT ""
) ENGINE=OLAP 
PRIMARY KEY(`dt`, `data_from`, `org_no`)
DISTRIBUTED BY HASH(`data_from`, `org_no`) BUCKETS 32 
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
); |

JOIN测试

StarRocks:

trino:

60万数据量的表自关联 是trino的几十倍速度

更新测试

更新前：

更新后：

创建表为主键表，能够实现增量更新， 整表全部更新只需3秒，更新效率也很不错 。

注意 ：测试环境仅为单节点环境。

https://docs.starrocks.io/