starrocks语法学习（与impala对比）

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';
-- 授予角色物化视图权限
GRANT SELECT ON ALL 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;
--最大连接数（最大4096）
show property for 'db_user';
SET PROPERTY FOR 'crm' 'max_user_connections' = 'db_user';
--授予用户建立CATALOG权限
GRANT CREATE EXTERNAL CATALOG ON SYSTEM TO 'db_user';
--授予CATALOG使用权限
GRANT all ON CATALOG jdbc_catalog_name TO 'db_user';
GRANT all ON DATABASE jdbc_catalog_name.database 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';

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 VIEWS IN ALL DATABASES TO user_name;

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 ''