1. 将Doris密码加密

SELECT PASSWORD('密码明文');

2. 创建一个Doris账号

CREATE USER some_user_rw@'%' IDENTIFIED BY PASSWORD '*加密的密码文本';

2.1 修改Doris密码

登录后, 执行命令

SET PASSWORD = PASSWORD('密码明文');

或者 管理员帮助其他账号修改密码:

SET PASSWORD FOR '账号' = PASSWORD('密码明文');

3. 给Doris账号授权

• 某个表的只读权限

  GRANT Select_priv ON 数据库名字.表名字 TO some_user_rw@'%';
  

• 某个表的读写权限

  GRANT Select_priv,Load_priv,Alter_priv,Create_priv,Drop_priv ON 数据库名字.表名字 TO some_user_rw@'%'
  

• 某个库的读写权限

  GRANT Select_priv,Load_priv,Alter_priv,Create_priv,Drop_priv ON 数据库名字 TO some_user_rw@'%'
  

4. 回收一个账号的读权限

• 回收整库的读权限

  REVOKE Select_priv ON 数据库 FROM some_user_rw@'%'; 
  

• 回收某个表的读权限

  REVOKE Select_priv ON 数据库名字.表名字 FROM some_user_rw@'%';
  

5. 添加一个be节点进入集群

ALTER SYSTEM ADD BACKEND "be ip:heartbeat port";

6. 摘除一个be节点

• 迁移数据, 再摘除节点

  ALTER SYSTEM DECOMMISSION BACKEND "be ip:heartbeat port";
  

• 摘除节点, 删除数据

  ALTER SYSTEM dropp BACKEND "be ip:heartbeat port";
  

7. 添加fe节点到集群中

• 第一步: 注册fe节点

  ALTER SYSTEM ADD FOLLOWER或者OBSERVER "fe ip:edit_log_port";
  

• 第二步: 启动fe节点

  ./bin/start_fe.sh --helper master fe ip:edit_log_port --daemon
  

8. 摘除fe节点

ALTER SYSTEM DROP FOLLOWER或者OBSERVER "fe_host:edit_log_port";

9. 添加broker节点

• 第一步: 注册broker节点

  ALTER SYSTEM ADD BROKER 自定义的broker名字 "broker ip:port"
  

• 第二步: 启动broker

  sh bin/start_broker.sh --daemon
  

10. 修改某一个数据库配额

错误详细：

1064, 'errCode = 2, detailMessage = Database[default_cluster:vk_dwd] data size exceeds quota[3.000 TB]')

对应的处理SQL：

ALTER DATABASE 数据库名字 SET DATA QUOTA 3t; 

11. 添加/修改一个表的colocate_with属性

alter table 数据库名.表名 set ("colocate_with" = "自定义名字");

12. 给一个字段创建BITMAP索引(目前只支持BITMAP索引)

CREATE INDEX 自定义索引名 ON 数据库名.表名 (字段名) USING BITMAP COMMENT '索引备注';

13. 查看某个表有哪些索引

SHOW INDEX FROM 数据库名.表名;

14. 删除索引

DROP INDEX 索引名 ON 数据库名.表名;

15. 查看Schema Change任务

SHOW ALTER TABLE COLUMN;

16. 设置会话级别的查询超时时间为30s的变量值

set query_timeout=30;

17. 设置会话级别sql profile自动上报的变量值

set is_report_success=true;

18. 查看变量值

show variables like '%vectorized_engine_enable%';

19. export语句demo

EXPORT TABLE 数据库名.表名 TO "hdfs://namenode-ip:8020/路径" 
PROPERTIES
(
    "exec_mem_limit"="2147483648",
    "timeout" = "3600"
)
WITH BROKER "hdfs_broker"
(
	"username" = "hdfs授权账号",
	"password" = "hdfs授权密码"
);

20. stream load语句demo

curl -H "label:自定义导入唯一标识" --location-trusted -u doiris账号:doris密码 -T 本地文件路径 http://fe ip:fe http端口/api/数据库名/表名/_stream_load

21. 添加一个broker load demo

LOAD LABEL vk_dw.vk_dw_starlight_conversion_da_load_20210221_1613955357_retry_01 (
    DATA INFILE(
        "hdfs://vipkidcold/app/warehouse/starlight_conversion_da/pt=20210221/*"
    ) INTO TABLE `vk_dw_starlight_conversion_da` FORMAT AS ORC(_col0, _col1, _col2)
    SET
        (
            `student_id` = _col0,
            `day_type` = _col1,
            `type` = _col2
        )
) WITH BROKER hdfs_broker (
    "hadoop.security.authentication" = "simple",
    "username" = "xxxx",
    "password" = "xxxxx",
    "dfs.nameservices" = "vipkidcold",
    "dfs.ha.namenodes.vipkidcold" = "nn1,nn2",
    "dfs.namenode.rpc-address.vipkidcold.nn1" = "xxxxx1:8020",
    "dfs.namenode.rpc-address.vipkidcold.nn2" = "xxxxx2:8020"
) PROPERTIES ("exec_mem_limit" = "4294967296");

22. 查看某个库中broker load记录

SHOW LOAD FROM 数据库名;

23. 查看某次broker load记录

use 数据库名;
show load where label='唯一标志名';

24. 添加一个routine load(kafka load)任务demo

CREATE ROUTINE LOAD db_xxxxx.kafka_load_001 ON tbl_xxxxx
COLUMNS TERMINATED BY ",",
COLUMNS(id, name, age)
PROPERTIES
(
    "desired_concurrent_number"="3",
    "max_batch_interval" = "20",
    "max_batch_rows" = "300000",
    "max_batch_size" = "209715200",
    "strict_mode" = "false"
)
FROM KAFKA
(
    "kafka_broker_list" = "kafkaip:9092",
    "kafka_topic" = "topic名字",
    "property.group.id" = "自定义的kafka group名字",
    "property.client.id" = "自定义的kafka client id",
    "property.kafka_default_offsets" = "OFFSET_BEGINNING"
);

25. 查看routine load

SHOW ROUTINE LOAD;

26. 查看Fe支持的配置变量

ADMIN SHOW FRONTEND CONFIG;

27. 修改Fe配置变量: max_running_txn_num_per_db变量修改为110

ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "110");

28. 新建一个备份任务

BACKUP SNAPSHOT test.test_classroom_course_lesson_mapping_da_001_03_08_01
TO hdfs_repo_prod
ON (test_classroom_course_lesson_mapping_da_001)
PROPERTIES ("type" = "full");

29. 查备份任务完成情况

SHOW SNAPSHOT ON hdfs_repo_prod where snapshot='test_classroom_course_lesson_mapping_da_001_03_08_01';
+------------------------------------------------------+---------------------+--------+
| Snapshot                                             | Timestamp           | Status |
+------------------------------------------------------+---------------------+--------+
| test_classroom_course_lesson_mapping_da_001_03_08_01 | 2021-03-08-19-45-34 | OK     |
+------------------------------------------------------+---------------------+--------+

30. 还原数据

RESTORE SNAPSHOT test.test_classroom_course_lesson_mapping_da_001_03_08_01
FROM `hdfs_repo_prod`
ON ( test_classroom_course_lesson_mapping_da_001)
PROPERTIES
(
    "backup_timestamp"="2021-03-08-19-45-34",
    "allow_load"="true"
);

31. 创建hdfs仓库. 用于数据备份、还原

CREATE REPOSITORY `hdfs_repo_prod`
WITH BROKER `hdfs_broker`
ON LOCATION "hdfs://nn:8020/tmp/liwei5/_doris_exported_"
PROPERTIES
(
     "hadoop.security.authentication" = "simple",
     "username" = "xxx",
     "password" = "xxxx"
);

32. 查询全部已经创建的hdfs仓库

show repositories;

33. 取消数据还原任务. 每个数据库只能有一个还原任务. 所以取消不用指定任务名

cancel restore from 数据库名;

34. 取消数备份任务. 每个数据库只能有一个备份任务. 所以取消不用指定任务名

CANCEL BACKUP FROM 数据库名

35. 查询hive字段的长度

select
    length(follow_comment) as f_length,
    follow_comment
from
    vk_dm.vk_dm_super_class_technology_losses_da
where
    pt = '20210308'
order by
    f_length desc
limit
    10;

36. 往proxysql中添加一条查询规则

当sql匹配到EXPORT TABLE时, 直接转发到生产集群(集群id=1), 不用镜像流量(mirror_hostgroup=NULL)到其他集群

insert into
    mysql_query_rules(
        rule_id,
        active,
        match_digest,
        destination_hostgroup,
        mirror_hostgroup,
        apply
    )
values
    (170, 1, 'EXPORT TABLE', 1, NULL, 1);

37. 使proxysql的查询规则永久生效

load mysql query rules to runtime;
save mysql query rules to disk;

38 重命名表

ALTER TABLE vk_dwd_evt_poster_is_valid_da_tmp RENAME vk_dwd_evt_poster_is_valid_da;

39 误drop表后恢复

RECOVER TABLE callcenter_ts.app_csc_tousu_gongdan_staffjob_sum;

40 查看全部rollup创建任务

SHOW ALTER TABLE ROLLUP;

41 删除rollup

ALTER TABLE test.test_doris_sink_test_di_bitmap DROP ROLLUP student_id_rollup;