sqoop 基本命令 及例子

全量抽取customer表

sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver  \
--username root \
--password ok \
--table customer \
--hive-import \
--hive-table sales_rds.customer \
--hive-overwrite \
--target-dir tmp \

全量抽取product表

sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--driver com.mysql.jdbc.Driver  \
--username root \
--password ok \
--table product \
--hive-import \
--hive-table sales_rds.product \
--hive-overwrite \
--target-dir tmp \

增量抽取

sqoop import \
--connect jdbc:mysql://localhost:3306/sales_source \
--username root \
--password ok \
--table sales_order \
--hive-import \
--hive-table sales_rds.sales_order \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--check-column entry_date \
--incremental append \
--last-value '1900-1-1'

sqoop job \
--create myjob \
--import \
--connect jdbc:mysql://localhost:3306/sales_source \
--username root \
--password ok \
--table sales_order \
--hive-import \
--hive-table sales_rds.sales_order \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--check-column entry_date \
--incremental append \
--last-value '1900-1-1'

查看job

sqoop job --list

#删除job
sqoop job  --delete myjob
#执行job
sqoop job --exec myjob


truncate


echo -n "root" > sqoopPWD.pwd
hdfs dfs -mkdir -p /sqoop/pwd
hdfs dfs -put sqoopPWD.pwd /sqoop/pwd/
hdfs dfs -chmod 400 /sqoop/pwd/sqoopPWD.pwd
	

删除job

sqoop job --delete myjob

创建job

sqoop job \
--create myjob \
-- import \
--connect jdbc:mysql://localhost:3306/sales_source \
--username root \
--password-file /sqoop/pwd/sqoopPWD.pwd  \
--table sales_order \
--hive-import \
--hive-table sales_rds.sales_order \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--check-column entry_date \
--incremental append \
--last-value '1900-1-1'

#防止在执行的时候手动输入密码

echo -n "root" > sqoopPWD.pwd
hdfs dfs -mkdir -p /sqoop/pwd
hdfs dfs -put sqoopPWD.pwd /sqoop/pwd/
hdfs dfs -chmod 400 /sqoop/pwd/sqoopPWD.pwd

删除job

sqoop job --delete myjob

创建job

sqoop job
–create myjob
– import
–connect jdbc:mysql://localhost:3306/sales_source
–username root
–password-file /sqoop/pwd/sqoopPWD.pwd
–table sales_order
–hive-import
–hive-table sales_rds.sales_order
–fields-terminated-by ‘\t’
–lines-terminated-by ‘\n’
–check-column entry_date
–incremental append
–last-value ‘1900-1-1’

#加载product
from
(
select
row_number() over(order by sp.product_code) product_sk ,
sp.product_code ,
sp.product_name,
sp.product_category,
‘1.0’,
‘2018-1-1’,
‘2050-1-1’
from sales_rds.product sp
) tmp
insert into sales_dw.dim_product
select *
;

product_sk,
product_code ,
product_name ,
product_category.
version,
effective_date,
expiry_date

加载dim_number表

from
(
select
row_number() over(order by so.order_number) order_sk,
order_number,
‘1.0’,
‘2018-1-1’,
‘2050-1-1’
from sales_rds.order so
) tmp
insert into sales_dw.dim_number
select *
;