1.截取年份

-- impala
from_unixtime(unix_timestamp(),'yyyy')
-- starrocks
from_unixtime(unix_timestamp(),'%Y')

2.截取年月

-- impala
from_unixtime(unix_timestamp(),'yyyyMM')
-- starrocks
from_unixtime(unix_timestamp(),'%Y%m') 

3.starrocks授权

CREATE USER 'db_user' IDENTIFIED BY 'db_user';
GRANT ALL ON DATABASE demo TO USER 'db_user';
GRANT ALL ON TABLE demo.* TO USER 'db_user';

--查询角色当前权限
SHOW GRANTS FOR 'db_user'; 

-- 授予角色物化视图权限
GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO user_name;
-- 创建自定义角色。
CREATE ROLE db1_admin;
-- 赋予角色 db1 的全部权限。用户可以在 db1 内创建表、视图、物化视图、用户自定义函数，并删除、更改 db1。
GRANT ALL ON DATABASE db1 TO ROLE db1_admin;
-- 赋予角色 db1 下所有表、视图、物化视图、用户自定义函数的所有权限。
GRANT ALL ON ALL TABLES IN DATABASE db1 TO ROLE db1_admin;
GRANT ALL ON ALL VIEWS IN DATABASE db1 TO ROLE db1_admin;
GRANT ALL ON ALL MATERIALIZED VIEWS IN DATABASE db1 TO ROLE db1_admin;
GRANT ALL ON ALL FUNCTIONS IN DATABASE db1 TO ROLE db1_admin;
-- 赋予角色某张表的权限
GRANT SELECT ON database1.table1 TO 'db_user'@'%';

--最大连接数（最大4096）
show property for 'db_user';
SET PROPERTY FOR 'db_user' 'max_user_connections' = 'db_user';
--授予用户建立CATALOG权限
GRANT CREATE EXTERNAL CATALOG ON SYSTEM TO 'db_user';
--授予CATALOG使用权限
GRANT USAGE ON ALL CATALOGS TO 'db_user';
GRANT all ON CATALOG jdbc_catalog_name TO 'db_user';
GRANT all ON DATABASE jdbc_catalog_name.* TO 'db_user';
SET CATALOG jdbc_catalog_name;
GRANT SELECT ON ALL VIEWS IN ALL DATABASES TO 'db_user';
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO 'db_user';

--撤销授权
REVOKE INSERT, SELECT, UPDATE ON ALL TABLES IN ALL DATABASES from  'db_user'@'%'

4.starrocks设置列默认值为插入时间

ALTER TABLE table_name ADD COLUMN time_in DATETIME DEFAULT CURRENT_TIMESTAMP;

5.starrocks创建异步物化视图

-- 创建物化视图
CREATE MATERIALIZED VIEW order_mv_2
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

-- 授予角色物化视图权限
GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO user_name;

--查询物化视图定义
show MATERIALIZED VIEWs from demo;

--启用物化视图
ALTER MATERIALIZED VIEW order_mv ACTIVE;

--修改物化视图刷新时间间隔
ALTER MATERIALIZED VIEW order_mv REFRESH ASYNC EVERY(INTERVAL 2 DAY);

--立即刷新视图
REFRESH MATERIALIZED VIEW order_mv;

6.mysql新建存储过程（starrocks不支持存储过程）

CREATE DEFINER=`user`@`%` PROCEDURE `MyModify_0408_insert_update`(loopnum int ,num int , num2 int , delnum int)
BEGIN
	SET @v_count := 0; 
	WHILE @v_count < loopnum  DO 
 	    SET @v_count := @v_count + 1;
       -- 插入
 	   INSERT INTO database_name.table1(c1,c2,c3)

		SELECT num,num2,FLOOR(RAND() * (200000000 - 100000000 + 1)) + 100000000,SUBSTRING(MD5(RAND()), 1, 10),now()
		FROM database_name.table2 
		limit 10000;
	
	END while;
end

7.starrocks查看内部http方式的链接信息

show load warnings ON ''

8.starrocks查询catalog报错：DECIMAL‘s precision should range from 1 to 38

-- 当oracle源库出现定义num不指定精度的时候会出现这个问题，在原查询语句前加入 /*+ SET_VAR(large_decimal_underlying_type = 'decimal') */即可
SELECT /*+ SET_VAR(large_decimal_underlying_type = 'decimal') */ * FROM catalog_name.sch_name.table_namelimit 1;

9.starrocks定义catalog与授权

--授权用户新建catalog权限
GRANT CREATE EXTERNAL CATALOG ON SYSTEM TO 'username';
--授权用户使用catalog权限
GRANT USAGE ON ALL CATALOGS TO 'username';
GRANT all ON all CATALOGS TO 'username';
GRANT all ON CATALOG catalog_name TO 'username';
GRANT all ON DATABASE catalog_name.* TO 'username';
--切换CATALOG 
SET CATALOG catalog_name;
--授予用户catalog表和视图的查询权限
GRANT SELECT ON ALL VIEWS IN ALL DATABASES TO 'username';
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO USER 'username'@'%';
--切回sr默认CATALOG 
SET CATALOG default_catalog; 
--删除catalog
drop catalog catalog_name;
--查询catalog创建语句
SHOW CREATE CATALOG catalog_name;