GBase8a导出报错ThisversionofGBasedoesn’tyetsupport‘outfileremotelyfordisExpressEnginetables

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)



https://www.gbase8.cn/5131