clickhouse SHOW 查询操作

clickhouse SHOW查询

• 查看建表信息

SHOW CREATE [TEMPORARY] [TABLE | DICTIONARY] [db.]table [INTO OUTFILE filename] [FORMART format];

• 查看所有库

SHOW DATABASES [INTO OUTFILE filename] [FORMAT format];

打印所有的数据库列表， 该查询等同于 SELECT name FROM system.databases [INTO OUTFILE filename] [FORMAT format]

• 查看当前服务操作进程

SHOW PROCESSLIST;

输出当前正在处理的请求列表，除了SHOW PROCESSLIST，等同于SELECT * FROM system.processes
提示（在控制台执行）：
$ watch -n1 "clickhouse-client --query='SHOW PROCESSLIST'"

• 查看所有表

SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE '<pattern>'|WHERE expr] [LIMIT <N>][INTO OUTFILE <filename>][FORMAT <format>];

如果未使用FROM语句，等同于如下查询：
SELECT name FROM system.tables WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>][INTO OUTFILE <filename>][FORMAT <format>];

• 查看所有字典

SHOW DICTIONARIES [FROM <db>] [LIKE '<pattern>'] [LIMIT <N>][INTO OUTFILE <filename>][FORMAT <format>];
-- 示例
SHOW DICTIONAARIES FROM db LIKE '%reg%' LIMIT 2; 

等同于如下查询SELECT name FROM system.dictionaries WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>][INTO OUTFILE <filename>][FORMAT <format>];

• 查看用户权限

SHOW GRANTS [FOR user];

如果未指定，输出当前用户权限

• 查看用户信息

-- 不会输出用户密码
SHOW CREATE USER [name | CURRENT_USRR];

• 查看角色信息

-- 查看角色信息
SHOW CREATE ROLE name;

• 查看row policy creation信息

SHOW CREATE [ROW] POLICY name ON [db.]table;

• 查看 quota信息

SHOW CREATE QUOTA [name | CURRENT]

• 查看settings profile creation信息

SHOW CREATE [SETTINGS] PROFILE name;