Doris的SQL

发表于 更新于 分类于 本文字数： 229 阅读时长 ≈ 1 分钟
记录一些会常用的SQL

查看fe配置项

1
ADMIN SHOW FRONTEND CONFIG [LIKE "pattern"];

设置fe配置项

1
ADMIN SET FRONTEND CONFIG ("key" = "value");

查看系统变量

1
SHOW VARIABLES [LIKE "pattern"];

刷新指定 Catalog/Database/Table 的元数据

1
2
3
REFRESH CATALOG catalog_name;
REFRESH DATABASE [catalog_name.]database_name;
REFRESH TABLE [catalog_name.][database_name.]table_name;

创建CirroData Catalog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE
    CATALOG `cirrodata_dailybuild11` PROPERTIES(
        "type" = "cirrodata" ,
        "password" = "*XXX" ,
        "hadoop.username" = "hdfs" ,
        "fs.defaultFS" = "hdfs://nameservice1" ,
        "dfs.nameservices" = "nameservice1" ,
        "dfs.namenode.rpc-address.nameservice1.namenode93" = "172.16.44.57:8020" ,
        "dfs.namenode.rpc-address.nameservice1.namenode107" = "172.16.44.58:8020" ,
        "dfs.ha.namenodes.nameservice1" = "namenode93,namenode107" ,
        "dfs.client.failover.proxy.provider.nameservice1" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider" ,
        "create_time" = "2024-02-19 18:51:57.762" ,
        "cirro.version" = "2.15" ,
        "cirro.root_name" = "cirrodata_dailybuild11" ,
        "cirro.database" = "REG_PARTITION" ,
        "cirro.cluster_name" = "cirrodata_dailybuild11"
    )
;

查看指定表的列信息

1
SHOW [FULL] COLUMNS FROM tbl;

查看查询计划

1
EXPLAIN VERBOSE select ...

查看查询 Profile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--
SET is_report_success=true;

-- 执行查询

-- 获取profile列表，复制query_id，如：c257c52f93e149ee-ace8ac14e8c9fef9
show query profile "/";

-- 查看整体执行计划树，每个节点都标注了自己所属的 Fragment
show query profile "/c257c52f93e149ee-ace8ac14e8c9fef9";

-- 查看具体 Fragment 下的 Instance 列表
show query profile "/c257c52f93e149ee-ace8ac14e8c9fef9/1";

-- 查看具体 Instance
show query profile "/c257c52f93e149ee-ace8ac14e8c9fef9/1/c257c52f93e149ee-ace8ac14e8c9ff03";

查看版本

1
SHOW VARIABLES LIKE "version_comment";