GBase 8a 在加载时,需要匹配数据和表字段,而那些没有匹配上的字段,可以通过AUTOFILL自动填充或者SET来设置一个指定的值,本文介绍这2个方式。
目录导航
参考
GBase 8a 集群加载数据LOAD的方法 完整的LOAD语法
GBase 8a 集群加载数据LOAD数据和字段不匹配的处理方案
环境
版本
gbase> select version();
+-----------------+
| version() |
+-----------------+
| 9.5.2.36.125743 |
+-----------------+
1 row in set (Elapsed: 00:00:00.01)
表
其中name列指定了默认值'noname'
gbase> create table t(id int, name varchar(100) default 'noname',birthday date);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
gbase> desc t;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | noname | |
| birthday | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)
数据
[gbase@gbase_rh7_001 ~]$ cat t.txt
8888
数据字段不匹配处理方案
对于没有数据加载的字段,有如下几种处理方案
- 通过TABLE_FILEDS指定加载的字段,则没有加载的自动使用默认值。
- 通过SET 给非加载字段指定一个值。不一定是默认值,可以是任何一个合法的值。
- 如果字段和数据都是最前面的,没有加载的字段都在后面,则可以用AUTOFILL指示加载,非加载字段都采用默认值。注意不包括用SET指定值的。
普通加载
因为字段数量和数据列数不匹配,错误数据。
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t fields terminated by ',';
Query OK, 0 rows affected (Elapsed: 00:00:01.34)
Task 9218 finished, Loaded 0 records, Skipped 1 records
通过TABLE_FILEDS加载
指定的字段成功入库,没指定的字段采用默认值。
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t fields terminated by ',' table_fields 'id';
Query OK, 1 row affected (Elapsed: 00:00:01.29)
Task 9229 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t;
+------+--------+----------+
| id | name | birthday |
+------+--------+----------+
| 8888 | noname | NULL |
+------+--------+----------+
1 row in set (Elapsed: 00:00:00.01)
指定SET加载
指定没有加载的列name的值,加载成功。
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t fields terminated by ',' set name='load 1',birthday='2000-02-03';
Query OK, 1 row affected (Elapsed: 00:00:00.99)
Task 9224 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t;
+------+--------+------------+
| id | name | birthday |
+------+--------+------------+
| 8888 | load 1 | 2000-02-03 |
+------+--------+------------+
1 row in set (Elapsed: 00:00:00.01)
指定AUTOFILL加载
所有非加载字段,没有SET的字段,使用了默认值。注意SET的字段,不会被AUTOFILL影响。
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t fields terminated by ',' autofill;
Query OK, 1 row affected (Elapsed: 00:00:01.23)
Task 9225 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t;
+------+--------+------------+
| id | name | birthday |
+------+--------+------------+
| 8888 | load 1 | 2000-02-03 |
| 8888 | noname | NULL |
+------+--------+------------+
2 rows in set (Elapsed: 00:00:00.01)
混合使用SET和AUTOFILL
指定SET的字段本次加载默认值,其它的采用默认值。
gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table t fields terminated by ',' autofill set name='load 1';
Query OK, 1 row affected (Elapsed: 00:00:01.25)
Task 9226 finished, Loaded 1 records, Skipped 0 records
gbase> select * from t;
+------+--------+------------+
| id | name | birthday |
+------+--------+------------+
| 8888 | load 1 | 2000-02-03 |
| 8888 | noname | NULL |
| 8888 | load 1 | NULL |
+------+--------+------------+
3 rows in set (Elapsed: 00:00:00.01)