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

发表于 作者 laozizhu

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)

参考

GBase 8a集群导出数据到文件，支持ftp,sftp,hadoop等
Post Views: 1,414

相关文章:

  1. 南大通用GBase 8a本地导出select outfile 报错，Can't open file Permisson denied
  2. 南大通用GBase 8a 导出时报错FileSystem Error : Permission denied
  3. 南大通用GBase 8a导出报错File XXX already exists和强制覆盖写解决方案writemode by overwrites
  4. 南大通用GBase 8a导出本地文件时多了目录，gbase_export_directory参数用处
  5. 南大通用GBase 8a导出加载报错FIELDS ESCAPED STRING must be only one byte
  6. 南大通用GBase 8a 集群加载数据LOAD的错误数据分析
  7. 南大通用GBase 8a集群命令行报错bash: gccli: command not found...
  8. 南大通用GBase 8a集群打补丁时需要关闭文件检查功能的参数skip_file_check
  9. 南大通用GBase 8a集群常见故障DataState状态为1，查看同步进度
  10. 南大通用GBase 8a集群LOCK,其它节点都是CLOSE/OFFLINE,只能看到自己OPEN