南大通用GBase 8a导出报错This version of GBase doesn't yet support 'outfile remotely for dis-Express Engine tables

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)

参考