非空列的数据加载
问题现象
对非空列进行加载，数据为空格时如何处理？
解决方法
数据文件：
[root@pst-red214 gbase]# cat t1.txt
aa| |bb
建表语句：
create table t1(c1 varchar(10),c2 varchar(10) not null,c3 varchar(10));
执行加载，数据跳过：
gbase> load data infile 'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into
table t1 fields terminated by '|';
Query OK, 0 rows affected (Elapsed: 00:00:00.66)
Task 126 finished, Loaded 0 records, Skipped 1 records
问题分析：
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
143
加载参数PRESERVE BLANKS：
用于设定是否保留字段内容两端的空格，
默认不
保留空格。
不保留空格的情况下将数据中的空格当做null 处理，与表列定义中not null 矛
盾，所以加载报错。
问题解决：
1.
加上参数PRESERVE BLANKS，保留空格：
gbase> load data infile
'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into table t1
fields terminated by '|' PRESERVE BLANKS;
Query OK, 1 row affected (Elapsed: 00:00:01.37)
Task 127 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t1;
+------+----+------+
| c1
| c2 | c3
|
+------+----+------+
| aa
|
| bb
|
+------+----+------+
1 row in set (Elapsed: 00:00:00.03)
2.
数据文件中增加包围符：
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
144
[root@pst-red214 gbase]# cat t1.txt
'aa'|' '|'bb'
gbase> load data infile
'sftp://gbase@192.168.105.214/home/gbase/t1.txt' into table t1
fields terminated by '|' enclosed by '''';
Query OK, 1 row affected (Elapsed: 00:00:01.22)
Task 132 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t1;
+------+----+------+
| c1
| c2 | c3
|
+------+----+------+
| aa
|
| bb
|
+------+----+------+
1 row in set (Elapsed: 00:00:00.02)
3.
修改表结构字段为允许null，default null：
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
145
gbase> create table t1(c1 varchar(10),c2 varchar(10),c3 varchar(10));
Query OK, 0 rows affected (Elapsed: 00:00:01.28)
gbase> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
| t1
| CREATE TABLE "t1" (
"c1" varchar(10) DEFAULT NULL,
"c2" varchar(10) DEFAULT NULL,
"c3" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> load data infile 'sftp://gbase@192.168.105.214
/home/gbase/t1.txt' into table t1 fields terminated by '|';
Query OK, 1 row affected (Elapsed: 00:00:01.23)
Task 135 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t1;
+------+------+------+
| c1
| c2
| c3
|
+------+------+------+
| aa
| NULL | bb
|
+------+------+------+
1 row in set (Elapsed: 00:00:00.02)
4.
修改表结构字段为允许null，default ''：
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
146
gbase> create table t1(c1 varchar(10),c2 varchar(10) default '',c3 varchar(10));
Query OK, 0 rows affected (Elapsed: 00:00:01.34)
gbase> show create table t1;
+-------+--------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------+
|
Table
|
Create
Table
|
+-------+--------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------+
| t1
| CREATE TABLE "t1" (
"c1" varchar(10) DEFAULT NULL,
"c2" varchar(10) DEFAULT '',
"c3" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+--------------------------------------------------------------------------
-----------------------------------------------------------------------------------
--------------------------+
1 row in set (Elapsed: 00:00:00.01)
gbase> load data infile 'sftp://gbase@192.168.105.214
/home/gbase/t1.txt' into table t1 fields terminated by '|';
Query OK, 1 row affected (Elapsed: 00:00:01.26)
Task 140 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t1;
+------+------+------+
| c1
| c2
| c3
|
+------+------+------+
| aa
|
| bb
|
+------+------+------+
row in set (Elapsed: 00:00:00.05)
GBase 8a MPP Cluster 最佳实践
5 FAQ
文档版本（2022-02-11）
南大通用数据技术股份有限公司
147