南大通用数据库-Gbase-8a-学习-14-LOAD加载数据

一、测试环境

二、LOAD相关数据库参数介绍

三 、LOAD语法树

LOAD DATA INFILE 'file_list'
INTO TABLE [dbname.]tbl_name
[options]
options:
 [CHARACTER SET charset_name]
 [DATA_FORMAT number [HAVING LINES SEPARATOR]]
 [NULL_VALUE 'string']
 [FIELDS
 [TERMINATED BY 'string']
 [ENCLOSED BY 'string']
 [PRESERVE [LEADING | TRAILING] BLANKS]
 [AUTOFILL]
 [LENGTH 'string']
 [TABLE_FIELDS 'string']
 ]
 [LINES
 [TERMINATED BY 'string']
 ]
 [MAX_BAD_RECORDS number]
 [DATETIME FORMAT format]
 [DATE FORMAT format]
 [TIMESTAMP FORMAT format]
 [TIME FORMAT format]
 [TRACE number]
 [TRACE_PATH 'string']
 [NOSPLIT]
 [PARALLEL number]
 [MAX_DATA_PROCESSORS number]
 [MIN_CHUNK_SIZE number]
 [SKIP_BAD_FILE number]
 [SET col_name = value[,...]]
 [IGNORE NUM LINES]
 [FILE_FORMAT format]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

四、支持的数据源

五、部署FTP服务器

参考原文连接：Linux 上搭建 FTP 服务。

不测试LOAD的FTP加载可以不安装，跳过此步。

1-7步都在192.168.142.11节点执行。

1、安装vsftpd并配置自启

[root@xdw1 ~]# yum install -y vsftpd

[root@xdw1 ~]# systemctl enable vsftpd

[root@xdw1 ~]# systemctl start vsftpd

[root@xdw1 ~]# netstat -npl |grep ftp
tcp        0      0 0.0.0.0:21              0.0.0.0:*               LISTEN      9343/vsftpd   
1
2
3
4
5
6
7
8

2、创建FTP用户

[root@xdw1 ~]# useradd ftpuser

[root@xdw1 ~]# passwd ftpuser
1
2
3

密码我设置的是qwer1234。

3、建目录改权限

[root@xdw1 ~]# mkdir /var/ftp/test

[root@xdw1 ~]# chown -R ftpuser:ftpuser /var/ftp/test
1
2
3

4、配置vsftpd.conf

[root@xdw1 ~]# cat /etc/vsftpd/vsftpd.conf
# Example config file /etc/vsftpd/vsftpd.conf
#
# The default compiled in settings are fairly paranoid. This sample file
# loosens things up a bit, to make the ftp daemon more usable.
# Please see vsftpd.conf.5 for all compiled in defaults.
#
# READ THIS: This example file is NOT an exhaustive list of vsftpd options.
# Please read the vsftpd.conf.5 manual page to get a full idea of vsftpd's
# capabilities.
#
# Allow anonymous FTP? (Beware - allowed by default if you comment this out).
anonymous_enable=YES
#
# Uncomment this to allow local users to log in.
# When SELinux is enforcing check for SE bool ftp_home_dir
local_enable=YES
#
# Uncomment this to enable any form of FTP write command.
write_enable=YES
#
# Default umask for local users is 077. You may wish to change this to 022,
# if your users expect that (022 is used by most other ftpd's)
local_umask=022
#
# Uncomment this to allow the anonymous FTP user to upload files. This only
# has an effect if the above global write enable is activated. Also, you will
# obviously need to create a directory writable by the FTP user.
# When SELinux is enforcing check for SE bool allow_ftpd_anon_write, allow_ftpd_full_access
#anon_upload_enable=YES
#
# Uncomment this if you want the anonymous FTP user to be able to create
# new directories.
#anon_mkdir_write_enable=YES
#
# Activate directory messages - messages given to remote users when they
# go into a certain directory.
dirmessage_enable=YES
#
# Activate logging of uploads/downloads.
xferlog_enable=YES
#
# Make sure PORT transfer connections originate from port 20 (ftp-data).
connect_from_port_20=YES
#
# If you want, you can arrange for uploaded anonymous files to be owned by
# a different user. Note! Using "root" for uploaded files is not
# recommended!
#chown_uploads=YES
#chown_username=whoever
#
# You may override where the log file goes if you like. The default is shown
# below.
#xferlog_file=/var/log/xferlog
#
# If you want, you can have your log file in standard ftpd xferlog format.
# Note that the default log file location is /var/log/xferlog in this case.
xferlog_std_format=YES
#
# You may change the default value for timing out an idle session.
#idle_session_timeout=600
#
# You may change the default value for timing out a data connection.
#data_connection_timeout=120
#
# It is recommended that you define on your system a unique user which the
# ftp server can use as a totally isolated and unprivileged user.
#nopriv_user=ftpsecure
#
# Enable this and the server will recognise asynchronous ABOR requests. Not
# recommended for security (the code is non-trivial). Not enabling it,
# however, may confuse older FTP clients.
#async_abor_enable=YES
#
# By default the server will pretend to allow ASCII mode but in fact ignore
# the request. Turn on the below options to have the server actually do ASCII
# mangling on files when in ASCII mode. The vsftpd.conf(5) man page explains
# the behaviour when these options are disabled.
# Beware that on some FTP servers, ASCII support allows a denial of service
# attack (DoS) via the command "SIZE /big/file" in ASCII mode. vsftpd
# predicted this attack and has always been safe, reporting the size of the
# raw file.
# ASCII mangling is a horrible feature of the protocol.
#ascii_upload_enable=YES
#ascii_download_enable=YES
#
# You may fully customise the login banner string:
#ftpd_banner=Welcome to blah FTP service.
#
# You may specify a file of disallowed anonymous e-mail addresses. Apparently
# useful for combatting certain DoS attacks.
#deny_email_enable=YES
# (default follows)
#banned_email_file=/etc/vsftpd/banned_emails
#
# You may specify an explicit list of local users to chroot() to their home
# directory. If chroot_local_user is YES, then this list becomes a list of
# users to NOT chroot().
# (Warning! chroot'ing can be very dangerous. If using chroot, make sure that
# the user does not have write access to the top level directory within the
# chroot)
chroot_local_user=YES
chroot_list_enable=YES
# (default follows)
chroot_list_file=/etc/vsftpd/chroot_list
#
# You may activate the "-R" option to the builtin ls. This is disabled by
# default to avoid remote users being able to cause excessive I/O on large
# sites. However, some broken FTP clients such as "ncftp" and "mirror" assume
# the presence of the "-R" option, so there is a strong case for enabling it.
#ls_recurse_enable=YES
#
# When "listen" directive is enabled, vsftpd runs in standalone mode and
# listens on IPv4 sockets. This directive cannot be used in conjunction
# with the listen_ipv6 directive.
listen=YES
#
# This directive enables listening on IPv6 sockets. By default, listening
# on the IPv6 "any" address (::) will accept connections from both IPv6
# and IPv4 clients. It is not necessary to listen on *both* IPv4 and IPv6
# sockets. If you want that (perhaps because you want to listen on specific
# addresses) then you must run two copies of vsftpd with two configuration
# files.
# Make sure, that one of the listen options is commented !!
#listen_ipv6=YES

pam_service_name=vsftpd
userlist_enable=YES
#tcp_wrappers=YES
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129

5、配置chroot_list

[root@xdw1 ~]# cat /etc/vsftpd/chroot_list
ftpuser
1
2

6、重启FTP服务

[root@xdw1 ~]# systemctl restart vsftpd
1

7、生成测试文件

[root@xdw1 ~]# cat /var/ftp/test/test.txt 
1
2
1
2
3

8、验证FTP服务器（可做可不做）

192.168.142.10节点执行如下命令。

（1）安装FTP命令

yum install ftp
1

（2）登录FTP拉去文件

[root@xdw0 src]# ftp

ftp> open 192.168.142.11
Connected to 192.168.142.11 (192.168.142.11).
220 (vsFTPd 3.0.2)
Name (192.168.142.11:root): ftpuser
331 Please specify the password.
Password:
230 Login successful.
Remote system type is UNIX.
Using binary mode to transfer files.

ftp> get /var/ftp/test/test.txt /opt/test.txt
local: /opt/test.txt remote: /var/ftp/test/test.txt
227 Entering Passive Mode (192,168,142,11,22,160).
150 Opening BINARY mode data connection for /var/ftp/test/test.txt (4 bytes).
226 Transfer complete.
4 bytes received in 0.000162 secs (24.69 Kbytes/sec)

ftp> quit
221 Goodbye.

[root@xdw0 src]# cat /opt/test.txt 
1
2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

六、测试LOAD

0、加载之前建议配置参数（全局设置）

set global gbase_loader_read_timeout = 10000;

set global gcluster_loader_max_data_processors = 4;
1
2
3

1、FTP

gbase> desc sun;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> LOAD DATA INFILE 'ftp://ftpuser:qwer1234@192.168.142.11//var/ftp/test/test.txt' into table sun DATA_FORMAT 3 FIELDS TERMINATED BY '|';
Query OK, 2 rows affected (Elapsed: 00:00:00.22)
Task 1310724 finished, Loaded 2 records, Skipped 0 records

gbase> select * from sun;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (Elapsed: 00:00:00.00)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2、SFTP

gbase> LOAD DATA INFILE 'sftp://root:qwer1234@192.168.142.11//var/ftp/test/test.txt' into table sun DATA_FORMAT 3 FIELDS TERMINATED BY '|';
Query OK, 2 rows affected (Elapsed: 00:00:00.77)
Task 1310725 finished, Loaded 2 records, Skipped 0 records

gbase> select * from sun;
+------+
| a    |
+------+
|    1 |
|    2 |
|    1 |
|    2 |
+------+
4 rows in set (Elapsed: 00:00:00.01)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3、本地

gbase> LOAD DATA INFILE 'file://192.168.142.10//opt/test.txt' into table sun DATA_FORMAT 3 FIELDS TERMINATED BY '|';
Query OK, 2 rows affected (Elapsed: 00:00:00.04)
Task 1310728 finished, Loaded 2 records, Skipped 0 records

gbase> select * from sun;
+------+
| a    |
+------+
|    1 |
|    2 |
|    1 |
|    2 |
|    1 |
|    2 |
+------+
6 rows in set (Elapsed: 00:00:00.00)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16