GBase 8a常用命令:
查询版本信息:
$ gbase -uroot -p
gbase> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.6.2.43-R7-free.110605 |
+-------------------------+
1 row in set (Elapsed: 00:00:00.00)
查询当前登录用户:
gbase> select user();
数据库基本用法和MySQL一致:
查询数据库列表:
gbase> show databases;
创建数据库:
gbase> create database testdb01;
切换数据库:
gbase> use testdb01;
查询数据库中所有tables表数量/信息(包括系统表、用户表、视图):
desc information_schema.tables;
select count(*) from information_schema.tables ;
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,TABLE_DATA_SIZE,OWNER_UID from information_schema.tables;
查询指定数据库的表(包括视图):
select table_schema as db_name,table_name from information_schema.TABLES where table_schema = 'testdb01';
select count(*) from information_schema.TABLES where table_schema = 'testdb01';
查询表的列(字段)信息:
desc information_schema.COLUMNS;
select * from information_schema.COLUMNS where table_schema = 'testdb01' and table_name = 'tb_test01';
select table_schema as db_name, table_name, column_name from information_schema.COLUMNS where table_schema = 'testdb01' and table_name = 'tb_test01';
统计某表的总数据量大小(MB)和条数:
SELECT TABLE_SCHEMA, TABLE_NAME, (DATA_LENGTH/1024/1024) as SIZE_DATA_MB, (INDEX_LENGTH/1024/1024) as SIZE_INDEX_MB, ((DATA_LENGTH+INDEX_LENGTH)/1024/1024) as TOTAL_SIZE_MB, TABLE_ROWS as TOTAL_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'testdb01' AND TABLE_NAME = 'tb_test01';
常用个控制参数:
show variables like 'gbase_parallel%';
gbase_parallel_execution :并行控制参数,bool变量,缺省值为0(关闭并行)。
gbase_parallel_max_thread_in_pool :express后台线城池的总线程数,0~4096,默认为系统CPU核数,通常不应设置超过cpu核数的2倍。
gbase_parallel_degree :SQL并行度,控制每个SQL的最大并行度。取值范围:2 ~ gbase_parallel_max_thread_in_pool,小于2则表示不启用并行,最大取值范围不超过线程池的线程数。
LOAD 批量加载数据到表:
LOAD之前先设置gbase全局参数:
gbase> show variables like '%load%';
gbase> set global gbase_loader_read_timeout = 10000;
gbase> set global gcluster_loader_max_data_processors = 4;
LOAD方式批量加载到数据库表:
LOAD-FTP文件加载方式(需要gbase8a数据库上安装ftp-client,并能访问远程ftp-server):
gbase> LOAD DATA INFILE 'ftp://ftpuser:user_test@192.168.100.11/var/ftp/test/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY '|';
LOAD-SFTP文件加载方式(需要gbase8a数据库能访问远程sftp-server):
gbase> LOAD DATA INFILE 'sftp://root:user_test@192.168.100.11/var/ftp/test/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY '|';
LOAD-本地文件加载方式(直接访问本地文件):
gbase> LOAD DATA INFILE 'file://192.168.142.10/opt/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY '|';
本地加载的地址也可以如下填写:
gbase> LOAD DATA INFILE 'file://127.0.0.1/opt/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY '|';
或者:
gbase> LOAD DATA INFILE '/opt/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY '|';
LOAD语法:
LOAD DATA INFILE ' file_list' INTO TABLE tbl_name [options]
tbl_name:
[database_name. ] table_name
options:
[DATA_FORMAT number [HAVING LINES SEPARATOR]]
[NULL_VALUE ' string ]
[FIELDS
[TERMINATED BY ' string ]
[ENCLOSED BY ' string ]
[PRESERVE BLANKS]
[AUTOFILL]
[{LENGTH|DEFINER} ' string ]
[TABLE_FIELDS ' string ]
]
[LINES
[TERMINATED BY ' string']
]
[MAX_BAD_RECORDS number]
[DATETIME FORMAT format]
[DATE FORMAT format]
[TIMESTAMP FORMAT format]
[TIME FORMAT format]
[TRACE number]
[TRACE_PATH ' string ]
[PARALLEL number]
[SKIP_BAD_FILE number]
[SET col_name = value[,...]]
LOAD参数说明:
DATA_FORMAT:用来指定使用哪种方式解析数据文件并加载。指定为3,表 示使用文本方式加载。指定为4表示使用定长方式加载。如果某列数据可能 包含了行分隔符,则需要在SQL中输入'HAVING LINES SEPARATOR,子句。指定 为5,表示使用文本文件宽松模式,即数据源文件为包围符中含有换行符和包 围符文本文件,或多列少列文本文件。
NULL_VALUE:用于指定空值字符,支持不超过15个任意字符的组合,参 数值以引号包围,指定方式与字段包围符一样。
FIELDS 子段:
TERMINATED BY:用于指定字段分隔符,支持不超过15个任意字符的组合, 支持任意字符,参数值以单引号包围,仅当使用文本方式加载时有效。可使用 字符本身(仅限可见字符,如:"丨”)、C风格转义字符仗口: "\a")、\xhh十六进 制仗口: "\xFF")或X''十六进制(如:"x' 09'")四种方式指定。例如:'|',表 示用|作为分隔字符。
ENCLOSED BY:用于指定字段包围符,支持任意单字符,参数值以单引号 包围,仅当使用文本方式加载时有效。可使用字符本身(仅限可见字符,如:"|")、 C风格转义字符(如:"\a")、\xhh十六进制(如:"\xFF")或x''十六进制(如: "x'09'")四种方式指定。
PRESERVE BLANKS :用于设定是否保留字段内容两端的空格,默认不保留 空格。
AUTOFILL:用于设定是否启用缺失列自动补齐功能,启用该参数后,对缺 失分割符的字段数据按照default值或者NULL值进行加载,默认不自动补齐。
LENGTH|DEFINER:在使用定长模式加载时,用于设定字段长度的参数。定 长格式数据导入时,设置每个字段的长度,有多个字段时,用逗号分隔。
TABLE_FIELDS:用于指定列加载,对于日期时间类型可以设置每一列的格 式。
SET:指定列值加载,加载系统将待加载文件和指定加载列值加载到集群 系统的表中。输入的类型应为常量,包括字符串、整数值、浮点值和NULL。
1、 支持指定所有列类型加载值;
2、 指定列值为常量值(包括NULL),包括字符串(单引号包围)、十进制 数值(1。)、浮点值(1。.9)、NULL、16进制表示的字符串(0xbac3)、 科学计数法(10e4);
3、 支持多列同时指定加载值。最多可SET表列数-1 ,如果设置的列数与 表定义中的列数一致将报错:Specified all fields .
4、 支持 format=3、format=4 以及 format=5;
使用限制说明:
1、 输入除常量值外的其他值,如列名、表达式等会报错,报错信息为 Column 'addr' should be const value;
2、 指定的列不能存在于TABLE_FIELDS中,否则报错;
3、 如果没有指定AUTOFILL,指定值的列数+数据中列数之和必须等于表 定义或者TABLE_FIELDS (若指定了 TABLE_FIELDS)中的列数,否则会 产生错误数据;如果指定了 AUTOFILL,则可以小于表定义的列数,缺 少的列会自动补全。如果TABLE_FIELDS列数+SET列数小于表定义的 列数,能够正常加载,没有涉及的列按照default值补齐;
4、 同一列在SQL中不能重复指定,否则报错。
LINES子段:
TERMINATED BY:行分隔符,支持任意单字符,参数值以引号包围。指定 方式与包围符一样。默认行分隔符为'\n'。
MAX_BAD_RECORDS :在每次加载的任务中,设定错误数据行数的上限。当 本次加载任务产生的错误数据行数大于max_bad_records设定的值时,加载任 务回滚,加载工具报错退出。此参数取值范围为:[。,4294967295] o此参数为 可选参数,默认不限制错误条数。。表示只要有错误数据就报错退出。
DATE FORMAT:用来指定date列类型的默认格式,默认为'%Y-%m-%d'。
DATETIME FORMAT :用来指定datetime列的默认格式,默认为 '%Y-%m-%d %H:%i:%s'。
TIMESTAMP FORMAT :用来指定timestamp列的默认格式,默认为 '%Y-%m-%d %H:%i:%s'。
TIME FORMAT:用来指定time列的默认格式,默认为’%H:%i:%s'。
TRACE:用来指示本次加载是否保存错误数据溯源。如果指定为0,则不溯 源。如果指定为1,则进行溯源。默认值为1。
溯源信息包括:错误数据所在的文件,所在行号。
TRACE_PATH:用来指定本次加载过程中产生的错误数据和日志存放路径。 缺省情况下,错误数据和溯源日志记录在加载机节点的 /opt/gnode/log/gbase/loader_logs下。新版支持加载错误数据和溯源信息日 志直接上传至FTP/SFTP服务器。可以由参数trace_path指定上传的路径,如: trace_path 'ftp://gbase:gbase@192.168.6.15/loadlogs'。注意上传 ftp 功 能需要在gbase_loader_logs_collect=0的情况下起效。
PARALLEL:用来控制加载并行度,取值范围[0,1024]。默认值为0,表示 并行度取值是线程池最大可用线程数。
SKIP_BAD_FILE:用来指定本次加载任务中是否忽略不存在或没有读取权 限的数据文件继续加载。如果指定为0,则加载报错终止。如果指定为1,则忽 略异常文件继续加载。默认值为0。
LOAD时,注意换行符和分隔符设置正确,如果是导出的标准csv(逗号分割、\n换行)则需要设置对应正确的的分隔符和换行符:
LOAD DATA INFILE '/opt/test.txt' into table tb_test01 DATA_FORMAT 3 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
LOAD任务的状态信息查询:
LOAD任务的状态信息表中记录正在运行的所有加载任务的状态信息,查询命令如下:
select * from information_schema.load_status;
加载错误数据与溯源信息检索:
支持语法形式对于错误数据与溯源信息检索功能,具体语法如下:
show [ gcluster ] load logs task_id LIMIT {[offset,] row_count}
举例:
gbase> show load logs 156 limit 5;
156为前面执行LOAD返回的TASK ID。
另外,也可以查看日志文件。
加载结果信息内存表查询:
加载结果信息通过 information_schema 库内的 load_result 和 cluster_load_result 表进行查询:
只查询当前coordinator节点,select查询形式,从 load_result 中查询加载信息:
select * from information_schema.load_result;
查询所有coordinator节点,select查询形式,从 cluster_load_result 中查询加载信息:
select * from information_schema.cluster_load_result;
select * from information_schema.load_result;
加载结果信息统计日志:
加载完成时将加载结果信息写入日志文件 loader_result.log 中,加载结果信息是以‘|‘为列分隔符,以‘\n‘为行分隔符存储的普通文本文件,存放在发起节点gcluster($GCLUSTER_HOM/log/gcluster/)日志目录,不支持指定存放路径。
Merge 批量合并:
GBase 8a支持Merge 合并功能,merge将临时表合并到目的表,混合了update和insert,该功能指针对Hash分布表。
语法:merge into TABLE1 using TABLE2 on ( match condition) WHEN MATCHED THEN UPDATE_CLUSE WHEN NOT MATCHED THEN INSERT_CLUSE;
参数说明:
match_condition 表关联条件
UPDATE_CLUSE 更新操作的set 部分
INSERT_CLUSE 插入操作的字段列表和values值列表部分
注意:GBase 8a的merge仅支持INSERT、UPDATE关键字,不支持DELETE关键字。GBase 8s数据库才支持DELETE关键字。
CREATE TABLE t1 (
id BIGINT NOT NULL,
info VARCHAR(100),
cnt BIGINT
);
insert into t1(id,info,cnt) values (1,'aaa',111),(2,'bbb',222),(3,'ccc',333);
select * from t1;
+----+------+------+
| id | info | cnt |
+----+------+------+
| 1 | aaa | 111 |
| 2 | bbb | 222 |
| 3 | ccc | 333 |
+----+------+------+
CREATE TABLE tmp (
id BIGINT NOT NULL,
info VARCHAR(100),
cnt BIGINT
);
insert into tmp(id,info,cnt) values (3,'cxx',300),(5,'eee',555);
select * from tmp;
+----+------+------+
| id | info | cnt |
+----+------+------+
| 3 | cxx | 300 |
| 5 | eee | 555 |
+----+------+------+
merge-目的表的关联id存在则update、不存在则insert:
merge into t1 using tmp on t1.id=tmp.id when matched then update set t1.info=tmp.info,t1.cnt=tmp.cnt when not matched then insert(id,info,cnt)values(tmp.id,tmp.info,tmp.cnt);
select * from t1;
+----+------+------+
| id | info | cnt |
+----+------+------+
| 1 | aaa | 111 |
| 2 | bbb | 222 |
| 3 | cxx | 300 |
| 5 | eee | 555 |
+----+------+------+
更新了 id=3 的数据,同时也插入了 id=5 的新id的数据;
truncate t1;
insert into t1(id,info,cnt) values (1,'aaa',111),(2,'bbb',222),(3,'ccc',333);
truncate tmp;
insert into tmp(id,info,cnt) values (3,'cxx',300),(5,'eee',555);
merge-目的表的关联id存在则update、不存在则忽略/跳过(不insert),即merge时只进行update更新:
merge into t1 using tmp on t1.id=tmp.id when matched then update set t1.info=tmp.info,t1.cnt=tmp.cnt;
select * from t1;
+----+------+------+
| id | info | cnt |
+----+------+------+
| 1 | aaa | 111 |
| 2 | bbb | 222 |
| 3 | cxx | 300 |
+----+------+------+
只更新了 id=3 的数据,忽略(跳过)了 id=5 的新数据(id=5的数据没有插入);
truncate t1;
insert into t1(id,info,cnt) values (1,'aaa',111),(2,'bbb',222),(3,'ccc',333);
truncate tmp;
insert into tmp(id,info,cnt) values (3,'cxx',300),(5,'eee',555);
merge--只插入not match的新数据(只插入新的关联id的数据、跳过update,只执行insert,不会执行update)
merge into t1 using tmp on t1.id=tmp.id when not matched then insert(id,info,cnt)values(tmp.id,tmp.info,tmp.cnt);
select * from t1;
+----+------+------+
| id | info | cnt |
+----+------+------+
| 1 | aaa | 111 |
| 2 | bbb | 222 |
| 3 | ccc | 333 |
| 5 | eee | 555 |
+----+------+------+
只插入了 id=5 的新数据,没有更新 id=3 的数据。
truncate t1;
insert into t1(id,info,cnt) values (1,'aaa',111),(2,'bbb',222),(3,'ccc',333);
truncate tmp;
insert into tmp(id,info,cnt) values (3,'cxx',300),(5,'eee',555);
merge--删除id匹配的数据(GBase 8s支持、但GBase 8a不支持)
merge into t1 using tmp on t1.id=tmp.id when matched then delete;
truncate t1;
insert into t1(id,info,cnt) values (1,'aaa',111),(2,'bbb',222),(3,'ccc',333);
truncate tmp;
insert into tmp(id,info,cnt) values (3,'cxx',300),(5,'eee',555);
merge--删除id不匹配的数据,目的表id存在但临时表id不存在,即目的表比临时表多,则删除(GBase 8s支持、但GBase 8a不支持)
merge into t1 using tmp on t1.id!=tmp.id when matched then delete;