Apache Sqoop 将mysql导入到Hadoop HDFS

第 21 章 Apache Sqoop

目录

  • 21.1. 安装 Sqoop
  • 21.2. sqoop2-tool
    • 21.2.1. verify
    • 21.2.2. upgrade
  • 21.3. sqoop2-shell
    • 21.3.6.1. link
    • 21.3.5.1. create job
    • 21.3.5.2. show job
    • 21.3.5.3. start job
    • 21.3.5.4. status job
    • 21.3.4.1. hdfs-connector
    • 21.3.4.2. generic-jdbc-connector
    • 21.3.2.1. server
    • 21.3.2.2. 要设置可查看具体出错信息
    • 21.3.1. show version
    • 21.3.2. set
    • 21.3.3. show connector
    • 21.3.4. link
    • 21.3.5. job
    • 21.3.6. update
  • 21.4. FAQ
    • 21.4.1. Unable to load native-hadoop library for your platform

21.1. 安装 Sqoop

OSCM 一键安装

curl -s https://raw.githubusercontent.com/oscm/shell/master/database/apache-sqoop/sqoop-1.99.7-bin-hadoop200.sh | bash		

启动 Sqoop

/srv/apache-sqoop/bin/sqoop.sh server start		

检查 Sqoop 线程

		[hadoop@netkiller ~]$ jps
2512 SecondaryNameNode
23729 SqoopJettyServer
2290 DataNode
871 ResourceManager
23885 Jps

21.2. sqoop2-tool

21.2.1. verify 配置文件检验工具

[hadoop@iZj6ciilv2rcpgauqg2uuwZ ~]$ sqoop2-tool verify
Setting conf dir: /srv/apache-sqoop/bin/../conf
Sqoop home directory: /srv/apache-sqoop
Sqoop tool executor:
	Version: 1.99.7
	Revision: 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb
	Compiled on Tue Jul 19 16:08:27 PDT 2016 by abefine
Running tool: class org.apache.sqoop.tools.tool.VerifyTool
0    [main] INFO  org.apache.sqoop.core.SqoopServer  - Initializing Sqoop server.
6    [main] INFO  org.apache.sqoop.core.PropertiesConfigurationProvider  - Starting config file poller thread
Verification was successful.
Tool class org.apache.sqoop.tools.tool.VerifyTool has finished correctly.			

21.3. sqoop2-shell

进入 sqoop2-shell

		[hadoop@netkiller ~]$ sqoop2-shell 
Setting conf dir: /srv/apache-sqoop/bin/../conf
Sqoop home directory: /srv/apache-sqoop
Sqoop Shell: Type 'help' or '\h' for help.

sqoop:000>		

Sqoop client script:

		sqoop2-shell /path/to/your/script.sqoop		

21.3.1. show version

			sqoop:000> show version
client version:
  Sqoop 1.99.7 source revision 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb 
  Compiled by abefine on Tue Jul 19 16:08:27 PDT 2016			
			sqoop:000> show version --all 
client version:
  Sqoop 1.99.7 source revision 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb 
  Compiled by abefine on Tue Jul 19 16:08:27 PDT 2016
0    [main] WARN  org.apache.hadoop.util.NativeCodeLoader  - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
server version:
  Sqoop 1.99.7 source revision 435d5e61b922a32d7bce567fe5fb1a9c0d9b1bbb 
  Compiled by abefine on Tue Jul 19 16:08:27 PDT 2016
API versions:
  [v1]			

21.3.2. set

21.3.2.1. server

				sqoop:000> set server --host master --port 12000 --webapp sqoop
Server is set successfully			

21.3.2.2. 要设置可查看具体出错信息

				sqoop:000> set option --name verbose --value true
Verbose option was changed to true				

21.3.3. show connector

sqoop:000> show connector
0    [main] WARN  org.apache.hadoop.util.NativeCodeLoader  - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
+------------------------+---------+------------------------------------------------------------+----------------------+
|          Name          | Version |                           Class                            | Supported Directions |
+------------------------+---------+------------------------------------------------------------+----------------------+
| generic-jdbc-connector | 1.99.7  | org.apache.sqoop.connector.jdbc.GenericJdbcConnector       | FROM/TO              |
| kite-connector         | 1.99.7  | org.apache.sqoop.connector.kite.KiteConnector              | FROM/TO              |
| oracle-jdbc-connector  | 1.99.7  | org.apache.sqoop.connector.jdbc.oracle.OracleJdbcConnector | FROM/TO              |
| ftp-connector          | 1.99.7  | org.apache.sqoop.connector.ftp.FtpConnector                | TO                   |
| hdfs-connector         | 1.99.7  | org.apache.sqoop.connector.hdfs.HdfsConnector              | FROM/TO              |
| kafka-connector        | 1.99.7  | org.apache.sqoop.connector.kafka.KafkaConnector            | TO                   |
| sftp-connector         | 1.99.7  | org.apache.sqoop.connector.sftp.SftpConnector              | TO                   |
+------------------------+---------+------------------------------------------------------------+----------------------+
sqoop:000>			
sqoop list-databases --connect  jdbc:mysql://192.168.1.1:3306/ --username root --password 123456			
			sqoop:000> show connector --all

21.3.4. link

21.3.4.1. hdfs-connector

				sqoop:000> create link -connector hdfs-connector
0    [main] WARN  org.apache.hadoop.util.NativeCodeLoader  - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Creating link for connector with name hdfs-connector
Please fill following values to create new link object
Name: hdfs

HDFS cluster

URI: hdfs://127.0.0.1:9000
Conf directory: 
Additional configs:: 
There are currently 0 values in the map:
entry# 
New link was successfully created with validation status OK and name hdfs
sqoop:000> 
				sqoop:000> show link
+------+----------------+---------+
| Name | Connector Name | Enabled |
+------+----------------+---------+
| hdfs | hdfs-connector | true    |
+------+----------------+---------+			

21.3.4.2. generic-jdbc-connector

				sqoop:000> create link -connector generic-jdbc-connector
Creating link for connector with name generic-jdbc-connector
Please fill following values to create new link object
Name: mysql

Database connection

Driver class: com.mysql.jdbc.Driver
Connection String: jdbc:mysql://127.0.0.1:3306/test
Username: test
Password: ****
Fetch Size: 
Connection Properties: 
There are currently 0 values in the map:
entry# 

SQL Dialect

Identifier enclose: 
New link was successfully created with validation status OK and name mysql		
				sqoop:000> show link
+-------+------------------------+---------+
| Name  |     Connector Name     | Enabled |
+-------+------------------------+---------+
| mysql | generic-jdbc-connector | true    |
| hdfs  | hdfs-connector         | true    |
+-------+------------------------+---------+			

21.3.5. job

21.3.5.1. create job

				sqoop:000> create job -f "mysql" -t "hdfs"
Creating job for links with from name mysql and to name hdfs
Please fill following values to create new job object
Name: from-mysql-to-hdfs

Database source

Schema name: test
Table name: member
SQL statement: 
Column names: 
There are currently 0 values in the list:
element# 
Partition column: 
Partition column nullable: 
Boundary query: 

Incremental read

Check column: 
Last value: 

Target configuration

Override null value: 
Null value: 
File format: 
  0 : TEXT_FILE
  1 : SEQUENCE_FILE
  2 : PARQUET_FILE
Choose: 0
Compression codec: 
  0 : NONE
  1 : DEFAULT
  2 : DEFLATE
  3 : GZIP
  4 : BZIP2
  5 : LZO
  6 : LZ4
  7 : SNAPPY
  8 : CUSTOM
Choose: 0
Custom codec: 
Output directory: /sqoop/member
Append mode: 

Throttling resources

Extractors: 
Loaders: 

Classpath configuration

Extra mapper jars: 
There are currently 0 values in the list:
element# 
New job was successfully created with validation status OK  and name from-mysql-to-hdfs				

21.3.5.2. show job

				sqoop:000> show job
+----+--------------------+--------------------------------+-----------------------+---------+
| Id |        Name        |         From Connector         |     To Connector      | Enabled |
+----+--------------------+--------------------------------+-----------------------+---------+
| 1  | from-mysql-to-hdfs | mysql (generic-jdbc-connector) | hdfs (hdfs-connector) | true    |
+----+--------------------+--------------------------------+-----------------------+---------+				

21.3.5.3. start job

				sqoop:000> start job -n from-mysql-to-hdfs		

sqoop:000> start job -n from-mysql-to-hdfs
Submission details
Job Name: from-mysql-to-hdfs
Server URL: http://localhost:12000/sqoop/
Created by: hadoop
Creation date: 2017-07-22 23:18:02 CST
Lastly updated by: hadoop
External ID: job_1499236611045_0001
	http://iZj6ciilv2rcpgauqg2uuwZ:8088/proxy/application_1499236611045_0001/
2017-07-22 23:18:02 CST: BOOTING  - Progress is not available				

启动后进入HDFS查看导入情况

				[hadoop@netkiller ~]$ hdfs dfs -ls /sqoop	

[hadoop@netkiller ~]$ hdfs dfs -ls /member
Found 10 items
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/310af608-5533-4bc2-bfb8-eaa45470b04d.txt
-rw-r--r--   3 hadoop supergroup         48 2017-07-22 23:18 /member/36bc39a5-bc73-4065-a361-ff2d61c4922c.txt
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/3e855400-84a9-422d-b50c-1baa9666a719.txt
-rw-r--r--   3 hadoop supergroup        140 2017-07-22 23:18 /member/3e8dad92-e0f1-4a74-a337-642cf4e6d634.txt
-rw-r--r--   3 hadoop supergroup         55 2017-07-22 23:18 /member/4a9f47f1-0413-4149-a93a-ed8b51efbc87.txt
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/4dc5bfe7-1cd9-4d9b-96a8-07e82ed79a71.txt
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/60dbcc60-61f2-4433-af39-1dfdfc048940.txt
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/6d02ed89-94d9-4d4b-87ed-d5da9d2bf9fe.txt
-rw-r--r--   3 hadoop supergroup        209 2017-07-22 23:18 /member/cf7b7185-3ab6-4077-943a-26228b769c57.txt
-rw-r--r--   3 hadoop supergroup          0 2017-07-22 23:18 /member/f2e0780d-ad33-4b35-a1c7-b3fbc23e303d.txt	

21.3.5.4. status job

				sqoop:000> status job -n from-mysql-to-hdfs		

21.3.6. update

21.3.6.1. link

				sqoop:000> update link -n  mysql
Updating link with name mysql
Please update link:
Name: mysql

Database connection

Driver class: com.mysql.jdbc.Driver
Connection String: jdbc:mysql://127.0.0.1:3306/test
Username: test
Password: ****
Fetch Size: 
Connection Properties: 
There are currently 0 values in the map:
entry# 

SQL Dialect

Identifier enclose:  
link was successfully updated with status OK				

原文发布于微信公众号 - Netkiller(netkiller-ebook)

原文发表时间:2017-07-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏ASP.NET MVC5 后台权限管理系统

构建ASP.NET MVC4+EF5+EasyUI+Unity2.x注入的后台管理系统(23)-权限管理系统-角色组模块

接下来我们的目标是 角色组管理 角色组权限设置 用户管理 把角色组授权给用户 给用户分配角色组 所以最少我们还要讲多5讲才能结束这个管理系统,经过之前的样例程序...

2449
来自专栏杨建荣的学习笔记

由一条日志警告所做的调优分析(r3笔记第40天)

这个案例发生有段时间了,但是今天无意中看到当时的邮件,感觉还是收益匪浅,看来还是细节决定成败啊。从一些日志或trace 文件中的警告信息中我们可以发掘出潜在的问...

3193
来自专栏菩提树下的杨过

基于sliverlight + wcf的web 文字版IM 示例

演示地址: http://task.24city.com/default.html 预览界面: ? 一、布局 采用Grid布局,5行2列 第一行:为登录/注册信...

3116
来自专栏吴伟祥

Linux命令缩写英文对照记忆(〇) 转

602
来自专栏技术博文

Linux命令英文全称

su:Swith user  切换用户,切换到root用户 cat: Concatenate  串联 uname: Unix name  系统名称 df: Di...

3365
来自专栏散尽浮华

服务器磁盘监控脚本分享(含报警邮件)

在日常的运维工作中,我们经常会对服务器的磁盘使用情况进行巡检,以防止磁盘爆满导致的业务故障. 如果能编写一个合理完善的监控脚本,当磁盘使用率达到我们设置的阀值时...

1958
来自专栏Golang语言社区

Golang 通过 smtp 发送邮件

package main import( "fmt" "net/smtp" "encoding/base64" ) //html,plain func Send...

3326
来自专栏乐沙弥的世界

Failed to upgrade Oracle Cluster Registry configuration(root.sh)

    最近在给客户基于Suse 11 sp3安装Oracle 10g RAC,在安装完clusterware执行/u01/app/crs/root.sh时收...

511
来自专栏java沉淀

centos7安装jdk,tomcat,nginx,redis,fastDFS的步骤

3087
来自专栏乐沙弥的世界

slave have equal MySQL server UUIDs

    最近在部署MySQL主从复制架构的时候,碰到了"Last_IO_Error: Fatal error: The slave I/O thread sto...

651

扫描关注云+社区