GBase 8a数据库集群支持导出数据到本地和远端服务,比如sftp,ftp,hadoop等,但要求必须是express引擎表。对其它引擎类型的表,比如元数据表为memory 或者 gssys引擎,当前版本尚不支持导出到远端的功能,只能先导出到本地,否则就会报This version of GBase doesn't yet support 'outfile remotely for dis-Express Engine tables的错误。
目录导航
报错样例
gbase> select * from information_schema.tables into outfile 'sftp://gbase:gbase1234@10.0.2.201:/home/gbase/tables.txt';
ERROR 1235 (42000): This version of GBase doesn't yet support 'outfile remotely for dis-Express Engine tables'
解决方法
可以通过如下2种方式,将数据导出。
先导出到本地
先将数据导出到本地,再通过其它手段发送到远端。
导出语句样例如下
select * from information_schema.tables into outfile '/home/gbase/tables.txt';
注意写入目录,必须是操作系统gbase用户有写权限的目录。
将文件,发送到远端的方法就不写了。
转储到express表再导出
创建experss表
根据表的结构,创建一个express引擎表。如下的tables元数据表,引擎为MEMORY。
gbase> show create table information_schema.tables;
+--------+--+
| Table | Create Table |
+--------+------+
| TABLES | CREATE TABLE "tables" (
"TABLE_CATALOG" varchar(512) DEFAULT NULL,
"TABLE_SCHEMA" varchar(64) NOT NULL DEFAULT '',
"TABLE_NAME" varchar(64) NOT NULL DEFAULT '',
"TABLE_TYPE" varchar(64) NOT NULL DEFAULT '',
"ENGINE" varchar(64) DEFAULT NULL,
"VERSION" bigint(21) unsigned DEFAULT NULL,
"ROW_FORMAT" varchar(10) DEFAULT NULL,
"TABLE_ROWS" bigint(21) unsigned DEFAULT NULL,
"AVG_ROW_LENGTH" bigint(21) unsigned DEFAULT NULL,
"DATA_LENGTH" bigint(21) unsigned DEFAULT NULL,
"MAX_DATA_LENGTH" bigint(21) unsigned DEFAULT NULL,
"INDEX_LENGTH" bigint(21) unsigned DEFAULT NULL,
"DATA_FREE" bigint(21) unsigned DEFAULT NULL,
"AUTO_INCREMENT" bigint(21) unsigned DEFAULT NULL,
"CREATE_TIME" datetime DEFAULT NULL,
"UPDATE_TIME" datetime DEFAULT NULL,
"CHECK_TIME" datetime DEFAULT NULL,
"TABLE_COLLATION" varchar(32) DEFAULT NULL,
"CHECKSUM" bigint(21) unsigned DEFAULT NULL,
"CREATE_OPTIONS" varchar(255) DEFAULT NULL,
"TABLE_LIMIT_STORAGE_SIZE" bigint(21) unsigned DEFAULT NULL,
"TABLE_STORAGE_SIZE" bigint(21) unsigned DEFAULT NULL,
"TABLE_DATA_SIZE" bigint(21) unsigned DEFAULT NULL,
"TABLE_COMMENT" varchar(2000) NOT NULL DEFAULT '',
"LOCAL_HASH_INDEX_FILE_SIZE" bigint(21) unsigned DEFAULT NULL,
"GLOBAL_HASH_INDEX_FILE_SIZE" bigint(21) unsigned DEFAULT NULL,
"SCN" bigint(21) unsigned DEFAULT NULL,
"TABLE_ID" bigint(21) unsigned DEFAULT NULL,
"OWNER_UID" bigint(20) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 |
+--------+-----+
1 row in set (Elapsed: 00:00:00.00)
在用户库里,创建一个新表,比如testdb.my_tables; 如果不指定引擎,默认是express。
注意一些属性,在express表里是不支持的,比如unsigned。
create table testdb.my_tables(
"TABLE_CATALOG" varchar(512) DEFAULT NULL,
"TABLE_SCHEMA" varchar(64) NOT NULL DEFAULT '',
"TABLE_NAME" varchar(64) NOT NULL DEFAULT '',
"TABLE_TYPE" varchar(64) NOT NULL DEFAULT '',
"ENGINE" varchar(64) DEFAULT NULL,
"VERSION" bigint(21) DEFAULT NULL,
"ROW_FORMAT" varchar(10) DEFAULT NULL,
"TABLE_ROWS" bigint(21) DEFAULT NULL,
"AVG_ROW_LENGTH" bigint(21) DEFAULT NULL,
"DATA_LENGTH" bigint(21) DEFAULT NULL,
"MAX_DATA_LENGTH" bigint(21) DEFAULT NULL,
"INDEX_LENGTH" bigint(21) DEFAULT NULL,
"DATA_FREE" bigint(21) DEFAULT NULL,
"AUTO_INCREMENT" bigint(21) DEFAULT NULL,
"CREATE_TIME" datetime DEFAULT NULL,
"UPDATE_TIME" datetime DEFAULT NULL,
"CHECK_TIME" datetime DEFAULT NULL,
"TABLE_COLLATION" varchar(32) DEFAULT NULL,
"CHECKSUM" bigint(21) DEFAULT NULL,
"CREATE_OPTIONS" varchar(255) DEFAULT NULL,
"TABLE_LIMIT_STORAGE_SIZE" bigint(21) DEFAULT NULL,
"TABLE_STORAGE_SIZE" bigint(21) DEFAULT NULL,
"TABLE_DATA_SIZE" bigint(21) DEFAULT NULL,
"TABLE_COMMENT" varchar(2000) NOT NULL DEFAULT '',
"LOCAL_HASH_INDEX_FILE_SIZE" bigint(21) DEFAULT NULL,
"GLOBAL_HASH_INDEX_FILE_SIZE" bigint(21) DEFAULT NULL,
"SCN" bigint(21) DEFAULT NULL,
"TABLE_ID" bigint(21) DEFAULT NULL,
"OWNER_UID" bigint(20) NOT NULL DEFAULT '0'
)
执行结果
gbase> create table testdb.my_tables(
-> "TABLE_CATALOG" varchar(512) DEFAULT NULL,
-> "TABLE_SCHEMA" varchar(64) NOT NULL DEFAULT '',
-> "TABLE_NAME" varchar(64) NOT NULL DEFAULT '',
-> "TABLE_TYPE" varchar(64) NOT NULL DEFAULT '',
-> "ENGINE" varchar(64) DEFAULT NULL,
-> "VERSION" bigint(21) DEFAULT NULL,
-> "ROW_FORMAT" varchar(10) DEFAULT NULL,
-> "TABLE_ROWS" bigint(21) DEFAULT NULL,
-> "AVG_ROW_LENGTH" bigint(21) DEFAULT NULL,
-> "DATA_LENGTH" bigint(21) DEFAULT NULL,
-> "MAX_DATA_LENGTH" bigint(21) DEFAULT NULL,
-> "INDEX_LENGTH" bigint(21) DEFAULT NULL,
-> "DATA_FREE" bigint(21) DEFAULT NULL,
-> "AUTO_INCREMENT" bigint(21) DEFAULT NULL,
-> "CREATE_TIME" datetime DEFAULT NULL,
-> "UPDATE_TIME" datetime DEFAULT NULL,
-> "CHECK_TIME" datetime DEFAULT NULL,
-> "TABLE_COLLATION" varchar(32) DEFAULT NULL,
-> "CHECKSUM" bigint(21) DEFAULT NULL,
-> "CREATE_OPTIONS" varchar(255) DEFAULT NULL,
-> "TABLE_LIMIT_STORAGE_SIZE" bigint(21) DEFAULT NULL,
-> "TABLE_STORAGE_SIZE" bigint(21) DEFAULT NULL,
-> "TABLE_DATA_SIZE" bigint(21) DEFAULT NULL,
-> "TABLE_COMMENT" varchar(2000) NOT NULL DEFAULT '',
-> "LOCAL_HASH_INDEX_FILE_SIZE" bigint(21) DEFAULT NULL,
-> "GLOBAL_HASH_INDEX_FILE_SIZE" bigint(21) DEFAULT NULL,
-> "SCN" bigint(21) DEFAULT NULL,
-> "TABLE_ID" bigint(21) DEFAULT NULL,
-> "OWNER_UID" bigint(20) NOT NULL DEFAULT '0'
-> );
Query OK, 0 rows affected (Elapsed: 00:00:01.15)
将数据转储
因为跨引擎,需要设置个参数。
gbase> set _gbase_query_path=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> insert into testdb.my_tables select * from information_schema.tables;
Query OK, 166 rows affected (Elapsed: 00:00:01.73)
Records: 166 Duplicates: 0 Warnings: 0
导出
gbase> select * from testdb.my_tables into outfile 'sftp://gbase:gbase1234@10.0.2.201/home/gbase/tables.txt';
Query OK, 166 rows affected (Elapsed: 00:00:00.21)