StarRocks常见维护SQL语句
👉 本文共约160个字，系统预计阅读时间或需1分钟。
集群管理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- 展示当前集群配置 admin show config -- 使用like谓词搜索当前Fe节点的配置 ADMIN SHOW FRONTEND CONFIG LIKE '%check_java_version%'; -- 展示某个表或分区副本分布状态 admin show replica distribution -- 查看表的分区的副本分布 admin show replica distribution FROMfromdb1.tbl1 partition(p1, p2); -- 增加节点 alter system add backend; -- 增加节点到某个cluster alter system add backend to cluster_name; -- 删除节点 alter system drop backend; -- 增加broker alter system add broker broker_name; -- 减少broker alter system drop broker broker_name; -- 设置一个 Load error hub，用于集中展示导入时的错误信息 alter system set load errors hub properties; -- 查看be节点 show backends; -- 查看当前broker show broker; -- 查看fe节点 show frontends; -- 查看所有用户 show all grants; -- 查看数据库 show proc "/dbs"; -- 启动 /usr/local/starrocks/fe/bin/start_fe.sh --daemon /usr/local/starrocks/be/bin/start_be.sh --daemon /usr/local/starrocks/apache_hdfs_broker/bin/start_broker.sh --daemon -- web界面 http://172.18.0.14:8030/ -- 登陆 mysql -h 127.0.0.1 -P9030 -uroot |
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | mysql> show all grants; +--------------+---------------------------------------------------------------------------------------------------------------+ | UserIdentity | Grants | +--------------+---------------------------------------------------------------------------------------------------------------+ | 'root'@'%' | GRANT NODE, ADMIN ON *.* TO 'root'@'%' | | 'lhr'@'%' | GRANT SELECT, LOAD, ALTER, CREATE, DROP ON *.* TO 'lhr'@'%' GRANT SELECT ON information_schema.* TO 'lhr'@'%' | +--------------+---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.04 sec) mysql> show proc "/dbs"; +-------+--------------------+----------+----------------+--------------------------+---------------------+ | DbId | DbName | TableNum | Quota | LastConsistencyCheckTime | ReplicaQuota | +-------+--------------------+----------+----------------+--------------------------+---------------------+ | 1 | information_schema | 27 | 8388608.000 TB | NULL | 9223372036854775807 | | 10002 | _statistics_ | 3 | 8388608.000 TB | NULL | 9223372036854775807 | | 10289 | db1 | 1 | 8388608.000 TB | NULL | 9223372036854775807 | +-------+--------------------+----------+----------------+--------------------------+---------------------+ 3 rows in set (0.04 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | _statistics_ | | db1 | | information_schema | +--------------------+ 3 rows in set (0.06 sec) mysql> |
DDL语法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- 创建索引 CREATE INDEX index_name ON table1 (siteid) USING BITMAP COMMENT 'balabala'; -- 创建物化视图 create materialized view k1_k2 as select k1, k2 from duplicate_table; -- 创建表 CREATE TABLE example_db.table_hash ( k1 TINYINT, k2 DECIMAL(10, 2) DEFAULT "10.5", v1 CHAR(10) REPLACE, v2 INT SUM ) ENGINE=olap AGGREGATE KEY(k1, k2) COMMENT "my first starrocks table" DISTRIBUTED BY HASH(k1) BUCKETS 32 PROPERTIES ("storage_type"="column"); -- 恢复名为 example_db 的 database RECOVER DATABASE example_db; -- 恢复名为 example_tbl 的 table RECOVER TABLE example_db.example_tbl; -- 恢复表 example_tbl 中名为 p1 的 partition RECOVER PARTITION p1 FROM example_tbl; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
DML语法
1 2 3 4 5 6 7 8 | -- 删除指定分区的数据 DELETE FROM my_table PARTITION p1 WHERE k1 = 3; -- 将指定表导出到指定位置 EXPORT TABLE testTbl TO "hdfs://hdfs_host:port/a/b/c/" WITH BROKER "broker_name ("username"="xxx", "password"="yyy"); -- 展示默认 db 的各个 table 的数据量，副本数量，汇总数据量和汇总副本数量 SHOW DATA; |
