The
export
tool exports a set of files from HDFS back to an RDBMS. The target table must already exist in the database. The input files are read and parsed into a set of records according to the user-specified delimiters.
table
必须是已经存在的$ sqoop export (generic-args) (export-args)
$ sqoop-export (generic-args) (export-args)
对上面的表格的几个重要参数解释:
—columns
:没有包含在其后面的字段类型,要么具有默认参数,要么允许插入空值By default, all columns within a table are selected for export. You can select a subset of columns and control their ordering by using the
--columns
argument. Note that columns that are not included in the--columns
parameter need to have either defined default value or allowNULL
values.
—export-dir
:导出目录,必须指定;参数必须配合—table
或者—call
The
--export-dir
argument and one of--table
or--call
are required.
—input-null-string
和—input-null-string
The
--input-null-string
and--input-null-non-string
arguments are optional. If--input-null-string
is not specified, then the string “null” will be interpreted as null for string-type columns. (1) If--input-null-non-string
is not specified, then both the string “null” and the empty string will be interpreted as null for non-string columns. (2)
NULL
被翻译成空值NULL
值还是空字符串都被翻译成空值By default,
sqoop-export
appends new rows to a table; each input record is transformed into anINSERT
statement that adds a row to the target database table. If you specify the--update-key
argument, Sqoop will instead modify an existing dataset in the database. Each input record is treated as anUPDATE
statement that modifies an existing row.
CREATE TABLE foo(
id INT NOT NULL PRIMARY KEY,
msg VARCHAR(32),
bar INT);
# HDFS中的数据表现为
0,this is a test,42
1,some more data,100
...
# SQL语句表现形式
UPDATE foo SET msg='this is a test', bar=42 WHERE id=0;
UPDATE foo SET msg='some more data', bar=100 WHERE id=1;
...
两个更新的模式updatemod
updateonly
:默认模式,更新已经存在的记录,不插入新数据allowinsert
:允许插入新值,相当于是append
根据update-key
中指定的字段是否为主键
$ sqoop export
--connect jdbc:mysql://ubuntu:3306/sqooptest \
--username root \
--password 123456 \
--table bigdata \
--export-dir /usr/root/bigdata2 # 从bigdata导出bigdata2中
$ sqoop export
--connect jdbc:mysql://ubuntu:3306/sqooptest \
--username root \
--password 123456 \
--table bigdata \
--export-dir /usr/root/bigdata2 \ # 从bigdata导出bigdata2中
--update-key class_id \ # 没有主键
--update-mode updateonly # 只更新update
--update-mode allowinsert # 相当于是append,会有数据冗余
$ sqoop export
--connect jdbc:mysql://ubuntu:3306/sqooptest \
--username root \
--password 123456 \
--table bigdata \
--export-dir /usr/root/bigdata2 \ # 从bigdata导出bigdata2中
--update-key class_id \ # 有主键
--update-mode updateonly # 只更新update
--update-mode allowinsert # 更新和追加