快速入门

快速使用

本章仅介绍如何快速非安全模式启动ZNBase，以及如何使用Ansible快速部署安全模式下的ZNBase集群。

首先您应下载ZNBase的二进制可执行文件（ZNBase下载地址），然后存放在您要部署的机器的任意路径下，进入到该目录后再进行如下操作。

注意事项：对于生产环境，必须参考安装部署章节进行实际部署！

非安全模式启动：

A. 启动集群服务


1. bini start --insecure [--store=<Data Directory>] [--advertise-addr=<Node Address>:<Node Port>] --listen-addr=<Node Address>:<Node Port>--http-addr=<Node Address>:<Node HTTP Port> --join=<Node List of Cluster> [--cache=<Percent or Actual Usage Size of Physical Memory>] [--max-sql-memory=<Percent or Actual Usage Size of Memory>] --background

B. 初始化数据库


1. bini init --insecure --host=<Address and Port of any Node>

C. 登入数据库


1. bini sql --insecure --host=<Address and Port of any Node>

注意事项：

1、<Node List of Cluster>:=<Node1 Address>[:<Node1 Port>,][…] 。代表集群的节点列表。

2、<Node Port>为节点的服务端口号，默认为26257，使用默认值时可省略。

3、<Node HTTP Port>为节点的HTTP服务端口号，默认为8080，使用默认值时可省略。

4、<Percent or Actual Usage Size of Physical Memory>代表为该参数分配的内存大小，可以为以小数表示的百分比形式，也可以为以MB、GB等表示表示的固定数值形式。

5、不指定--store参数时，系统会默认生成一个数据存放目录，目前是*“bini-data"*。

6、不指定--advertise-addr时，该参数默认值等于--listen-addr。

Ansible部署：

关于如何下载、安装、使用Ansible，请自行参考Ansible的官方文档快速入门 - 图1 (opens new window)。

一、使用条件

执行节点已安装ansible，执行节点已配置与NewSQL节点间的ssh互信

二、param.yaml配置文件参数说明


1. server_port: 26257  ----NewSQL服务端口
2. monitor_port: 8080  ----NewSQL监控页面端口
3. store_path: /opt/node_data  ----NewSQL存储路径
4. cache: .25  ----NewSQL的缓存大小，.25代表25%
5. max_sql_memory: .1  ----NewSQL*的临时SQL缓存大小，.1代表10%
6. certs_dir: /root/certs  ----用户证书文件存放路径（用于客户端连接）
7. make_certs_dir: /opt/certs  ----证书文件存放路径
8. make_ca_keys_dir: /opt/my-safe-directory  ----CA秘钥文件存放路径
9. db_admin: bini  ----拥有admin权限的数据库用户名
10. db_password: 123456  ---db_admin数据库用户的密码
11. ntpserver: 0.cn.pool.ntp.org  ----NTP服务器IP

三、使用方法

（1）在Ansible的host文件中增加组**bini_setup**，组内以IP形式配置部署bini的节点信息

img

（2）将bini的二进制文件放在playbook文件同级目录下

img

（3）修改param.yaml中的设置参数

img （4）执行ansible-playbook bini_secure.playbook -e "@param.yaml"命令，进行bini部署

img

imgimg （5）在NewSQL节点查看集群状态

登陆到/etc/ansible/hosts中配置的第一个节点，并执行如下命令：


1. bini node status --certs-dir=/root/certs/ --host=< IP Address of Node1 >:< Service Port of Node1 >

注：< IP Address of Node1 >表示第一节点的IP地址，< Service Port of Node1 >表示第一节点的服务端口号。

img

（6）在Ansible执行节点查看NewSQL节点进程


1. ansible bini_setup -m shell -a "ps -ef |grep bini |grep -vi grep"

3-6

基本操作

成功部署ZNBase集群之后，便可以在 ZNBase中执行 SQL 语句了。本文档介绍基本的 SQL 操作。完整的 SQL 语法规则，请参照 ZNBase SQL Language Reference.

1）创建数据库


1. \> CREATE DATABASE testdb;

2）创建新用户test


1. #非安全模式无法使用密码认证功能，故无法为新用户设置密码

3. \> CREATE USER test;

3）授予test用户在数据库testdb及public模式上的所有权限


1. \> GRANT ALL ON DATABASE testdb TO test;

3. \> GRANT ALL ON SCHEMA testdb.public TO test;

4) 使用test用户连接数据库testdb


1. bini sql --insecure --host=test1:26300 -u test -d testdb

5) 在默认模式public下创建表t1


1. \> CREATE TABLE t1(id INT PRIMARY KEY,amount DECIMAL(32,2) NOT NULL,from_account STRING(100) NOT NULL,to_account STRING(100) NOT NULL,create_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP());

6) 初始化数据，执行增删改查DML操作


1. \> INSERT INTO t1 (id,amount,from_account,to_account) VALUES(1,10000.01,'Jack','Tom'),(2,500.89,'Jack','Tom'),(3,3000.1,'Jack','Tom'),

3. (4,600000,'Tom','Bob'),(5,30000,'Jack','Allen'),(6,1000000,'Bob','Jack'),(7,1000,'Bob','Tom'),(8,999.99,'Allen','Bob'),(9,666.13,'Allen','Tom'),(10,88888,'Jack','Tom');

5. \> SELECT * FROM t1;

7. id |  amount  | from_account | to_account |    create_timestamp

9. +----+------------+--------------+------------+---------------------------------+

11. 1 |  10000.01 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00

13. 2 |   500.89 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00

15. 3 |  3000.10 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00

17. 4 | 600000.00 | Tom     | Bob    | 2020-11-13 10:01:12.52328+00:00

19. 5 |  30000.00 | Jack     | Allen   | 2020-11-13 10:01:12.52328+00:00

21. 6 | 1000000.00 | Bob     | Jack    | 2020-11-13 10:01:12.52328+00:00

23. 7 |  1000.00 | Bob     | Tom    | 2020-11-13 10:01:12.52328+00:00

25. 8 |   999.99 | Allen    | Bob    | 2020-11-13 10:01:12.52328+00:00

27. 9 |   666.13 | Allen    | Tom    | 2020-11-13 10:01:12.52328+00:00

29. 10 |  88888.00 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00

31. (10 rows)

35. Time: 3.011334ms

37. \> SELECT * FROM t1 LIMIT 1;

39. id | amount | from_account | to_account |    create_timestamp

41. +----+----------+--------------+------------+---------------------------------+

43. 1 | 10000.01 | Jack     | Tom    | 2020-11-13 10:01:12.52328+00:00

45. (1 row)

49. Time: 986.941µs

51. \>INSERT INTO t1(id,amount,from_account,to_account) VALUES(11,4800,'Carl','Mike'),(12,3300,'Carl','James'),(13,900,'Diego','Carl');

53. INSERT 3

57. Time: 5.402443ms

61. \> UPDATE t1 SET amount = 33000 WHERE id = 12;

63. DELETE FROM t1 WHERE id = 13;

65. UPDATE 1

69. Time: 23.905949ms

73. \> DELETE FROM t1 WHERE id = 13;

75. DELETE 1

79. Time: 6.483638ms

83. \> SELECT * FROM t1 WHERE id > 10;

85. id | amount | from_account | to_account |     create_timestamp

87. +----+----------+--------------+------------+----------------------------------+

89. 11 | 4800.00 | Carl     | Mike    | 2020-11-13 10:03:46.200896+00:00

91. 12 | 33000.00 | Carl     | James   | 2020-11-13 10:03:46.200896+00:00

93. (2 rows)

95. --分页查询

97. Time: 1.132672ms

99. \>SELECT * FROM t1 WHERE id > 10 LIMIT 1;

101. id | amount | from_account | to_account |     create_timestamp

103. +----+---------+--------------+------------+----------------------------------+

105. 11 | 4800.00 | Carl     | Mike    | 2020-11-27 10:05:49.914003+00:00

107. (1 row)

111. Time: 1.333644ms

115. \>SELECT * FROM t1 WHERE id > 10 LIMIT 1 OFFSET 1;

117. id | amount | from_account | to_account |     create_timestamp

119. +----+----------+--------------+------------+----------------------------------+

121. 12 | 33000.00 | Carl     | James   | 2020-11-27 10:05:49.914003+00:00

123. (1 row)

127. Time: 782.286µs

131. \>SELECT * FROM t1 WHERE id > 10 FETCH FIRST 1 ROW ONLY;

133. id | amount | from_account | to_account |     create_timestamp

135. +----+---------+--------------+------------+----------------------------------+

137. 11 | 4800.00 | Carl     | Mike    | 2020-11-27 10:05:49.914003+00:00

139. (1 row)

143. Time: 950.55µs

147. \> SELECT * FROM t1 WHERE id > 10 OFFSET 1 FETCH NEXT 1 ROW ONLY;

149. id | amount | from_account | to_account |     create_timestamp

151. +----+----------+--------------+------------+----------------------------------+

153. 12 | 33000.00 | Carl     | James   | 2020-11-27 10:05:49.914003+00:00

155. (1 row)

159. Time: 921.126µs

161. --聚集函数及统计函数

163. \>SELECT from_account,SUM(amount) expense FROM t1 GROUP BY from_account order by 1;

165. from_account | expense

167. +--------------+------------+

169. Allen    |  1666.12

171. Bob     | 1001000.00

173. Carl     |  37800.00

175. Jack     | 132389.00

177. Tom     | 600000.00

179. (5 rows)

183. Time: 1.76955ms

187. \>SELECT from_account,SUM(amount) OVER(PARTITION BY from_account) total_expense,

189. ROW_NUMBER() OVER(PARTITION BY from_account ORDER BY create_timestamp,to_account) rn,amount,to_account FROM t1;

191. from_account | total_expense | rn |  amount  | to_account

193. +--------------+---------------+----+------------+------------+

195. Allen    |    1666.12 | 1 |   999.99 | Bob

197. Allen    |    1666.12 | 2 |   666.13 | Tom

199. Bob     |  1001000.00 | 2 |  1000.00 | Tom

201. Bob     |  1001000.00 | 1 | 1000000.00 | Jack

203. Carl     |   37800.00 | 2 |  4800.00 | Mike

205. Carl     |   37800.00 | 1 |  33000.00 | James

207. Jack     |   132389.00 | 2 |  88888.00 | Tom

209. Jack     |   132389.00 | 3 |  10000.01 | Tom

211. Jack     |   132389.00 | 4 |   500.89 | Tom

213. Jack     |   132389.00 | 5 |  3000.10 | Tom

215. Jack     |   132389.00 | 1 |  30000.00 | Allen

217. Tom     |   600000.00 | 1 | 600000.00 | Bob

219. (12 rows)

223. Time: 1.212536ms

225. --公共表表达式、视图及多表查询

227. \>WITH u AS (SELECT DISTINCT from_account account FROM t1 UNION SELECT DISTINCT to_account FROM t1)

229. SELECT u.account, IFNULL(b.income,0) income, IFNULL(a.expense,0) expense,IFNULL(b.income,0)-IFNULL(a.expense,0) net_income FROM u

231. LEFT JOIN (SELECT from_account,SUM(amount) expense FROM t1 GROUP BY from_account) a ON u.account = a.from_account

233. LEFT JOIN (SELECT to_account,SUM(amount) income FROM t1 GROUP BY to_account) b ON u.account = b.to_account;

235. account |  income  | expense  | net_income

237. +---------+------------+------------+------------+

239. Carl  |     0 |  37800.00 | -37800.00

241. Jack  | 1000000.00 | 132389.00 | 867611.00

243. Tom   | 104055.13 | 600000.00 | -495944.87

245. Bob   | 600999.99 | 1001000.00 | -400000.01

247. Allen  |  30000.00 |  1666.12 |  28333.88

249. Mike  |  4800.00 |     0 |  4800.00

251. James  |  33000.00 |     0 |  33000.00

253. (7 rows)

257. Time: 2.458159ms