【ClickHouse】2:clickhouse基本语法 背景介绍: 有三台CentOS7服务器安装了ClickHouse 一:登录数据库 二:角色账号相关操作 三:数据库相关操作 四:表相关操作
有三台CentOS7服务器安装了ClickHouse
HostName | IP | 安装程序 | 程序端口 |
centf8118.sharding1.db | 192.168.81.18 | clickhouse-server,clickhouse-client | 9000 |
centf8119.sharding2.db | 192.168.81.19 | clickhouse-server,clickhouse-client | 9000 |
centf8120.sharding3.db | 192.168.81.20 | clickhouse-server,clickhouse-client | 9000 |
一:登录数据库
本地登录:clickhouse-client
[root@centf8118 format_schemas]# clickhouse-client ClickHouse client version 20.6.4.44 (official build). Connecting to localhost:9000 as user default. Connected to ClickHouse server version 20.6.4 revision 54436. centf8118.sharding1.db :) show databases; SHOW DATABASES ┌─name───────────────────────────┐ │ _temporary_and_external_tables │ │ default │ │ system │ │ testdb │ └────────────────────────────────┘ 4 rows in set. Elapsed: 0.003 sec. centf8118.sharding1.db :) select now(); SELECT now() ┌───────────────now()─┐ │ 2020-08-26 15:11:22 │ └─────────────────────┘ 1 rows in set. Elapsed: 0.010 sec. centf8118.sharding1.db :)
远程登录:(用本机clickhouse-client连接远程clickhouse-server服务器)
clickhouse-client --host 192.168.81.20 --port 9000 --database default --user default --password ""
如果登录不上,telnet一下端口,看通不通。
telnet ping 192.168.81.20 9000
[root@centf8118 ~]# telnet 192.168.81.20 9000 Trying 192.168.81.20 ... telnet: connect to address 192.168.81.20: Connection refused
如果telent不同,查看iptables有没有添加端口。(三台机器都添加,我这里是添加的都是IP和端口区间,按实际需求配置。)
-A INPUT -p tcp -m iprange --src-range 192.168.81.18-192.168.81.20 -m tcp --dport 9000:9200 -j ACCEPT
如果iptables都配置了9000端口还是不通。查看监听端口:
[root@centf8118 ~]# netstat -anltp|grep :9000 tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 22710/clickhouse-se
如果是127.0.0.1:9000需要修改配置,放开远程访问。
vim /etc/clickhouse-server/config.xml
修改配置文件中的注释掉的<listen_host>0.0.0.0</listen_host>放出来。
修改配置后重启服务: server clickhouse-server restart
再次查看监听端口:
[root@centf8118 ~]# netstat -anltp|grep :9000 tcp 0 0 0.0.0.0:9000 0.0.0.0:* LISTEN 6060/clickhouse-ser
再尝试登录数据库:(安装后系统默认会创建一个登录账号default,密码为空字符串)
[root@centf8118 ~]# clickhouse-client --host 192.168.81.20 --port 9000 --database default --user default --password "" ClickHouse client version 20.6.4.44 (official build). Connecting to database default at 192.168.81.20:9000 as user default. Connected to ClickHouse server version 20.6.4 revision 54436. centf8120.sharding3.db :) show databases; SHOW DATABASES ┌─name───────────────────────────┐ │ _temporary_and_external_tables │ │ default │ │ system │ │ testdb │ │ tutorial │ └────────────────────────────────┘ 5 rows in set. Elapsed: 0.004 sec. centf8120.sharding3.db :)
客户端登录,默认是一行一段完整的SQL,如果是多行的sql,就会一行行执行并报错。
clickhouse-client -m 可以支持多行SQL;
二:角色账号相关操作
官方文档:https://clickhouse.tech/docs/en/sql-reference/statements/create/role/
# 创建角色,账号
CREATE ROLE [IF NOT EXISTS | OR REPLACE] name [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
CREATE USER [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name] [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}] [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE] [DEFAULT ROLE role [,...]] [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
Examples:
# 创建角色并赋予权限 CREATE ROLE accountant; GRANT SELECT ON db.* TO accountant; # 给用户添加角色 GRANT accountant TO mira;
# 修改角色 SET ROLE accountant; SELECT * FROM db.*;
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty' CREATE USER john DEFAULT ROLE role1, role2 ALTER USER user DEFAULT ROLE ALL ALTER USER john DEFAULT ROLE ALL EXCEPT role1, role2
# 查询角色,账号
# 修改角色,账号
ALTER ROLE [IF EXISTS] name [ON CLUSTER cluster_name] [RENAME TO new_name] [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
ALTER USER [IF EXISTS] name [ON CLUSTER cluster_name] [RENAME TO new_name] [IDENTIFIED [WITH {PLAINTEXT_PASSWORD|SHA256_PASSWORD|DOUBLE_SHA1_PASSWORD}] BY {'password'|'hash'}] [[ADD|DROP] HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE] [DEFAULT ROLE role [,...] | ALL | ALL EXCEPT role [,...] ] [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
# 删除角色,账号
DROP ROLE [IF EXISTS] name [,...] [ON CLUSTER cluster_name] DROP USER [IF EXISTS] name [,...] [ON CLUSTER cluster_name]
三:数据库相关操作
官方文档:https://clickhouse.tech/docs/en/sql-reference/statements/create/database/
# 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]
# 查询数据库
SHOW DATABASES [INTO OUTFILE filename] [FORMAT format]
# 删除数据库
DROP DATABASE [IF EXISTS] db [ON CLUSTER cluster]
四:表相关操作
# 创建表
# 新创建表 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2], ... ) ENGINE = engine
# 拷贝已存在的表结构 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]
# 查询表
SHOW [TEMPORARY] TABLES [{FROM | IN} <db>] [LIKE '<pattern>' | WHERE expr] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
SELECT name FROM system.tables WHERE database = <db> [AND name LIKE <pattern>] [LIMIT <N>] [INTO OUTFILE <filename>] [FORMAT <format>]
# 删除表
DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]