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 条评论
登录 后参与评论

相关文章

来自专栏linux驱动个人学习

高通 sensor 从native到HAL

前几篇sensor相关的文章介绍了sensor的hal的知识,以press_sensor实时显示气压坐标来分析,app层数据获取的过程,其实实现数据监控非常简单...

1902
来自专栏happyJared

Spring Boot 1.0 && 2.0 + Mybatis 多数据源配置与使用

mysql 对应的数据源配置中,定义了实体 Boy 和对应的数据层接口 BoyMapper:

683
来自专栏java闲聊

第二篇 SSM运行Demo

1915
来自专栏Ken的杂谈

Spring Boot入门教程2-1、使用Spring Boot+MyBatis访问数据库(CURD)注解版

本项目构建基于:https://ken.io/note/springboot-course-basic-helloworld

1403
来自专栏乐沙弥的世界

PL/SQL Profiler 剖析报告生成html

    使用图形化界面工具实施PL/SQL Profiler 易用直观,但是并不是所有的环境都可以使用图形界面软件连接到数据库。对于只能在命令行下进行剖析又需要...

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

外部表的导入导出问题 (41天)

今天尝试了一下用exp导出外部表,碰到了一些问题。 ----导出 今天导出的时候发现一个严重的问题,导出一个很小的外部表花了很长时间,最后还是报错,而且生成的d...

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

redo日志文件学习(22天)

关于redo日志文件,今天因为要做redo日志的在线迁移,所以特意做了个简单的总结。 1. 如果要把redo的切换信息显示到alert日志中,需要设置个参数。...

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

关于exp statistics的问题和简单测试(82天)

在数据导出的时候,可能会碰到EXP-00091: Exporting questionable statistics.的问题,有时候会让人有点摸不到头脑,不知道...

2138
来自专栏闵开慧

通过多种方式将数据导入hive表

hive官方手册 http://slaytanic.blog.51cto.com/2057708/939950 通过多种方式将数据导入hive表 1.通过外部...

3517
来自专栏Kubernetes

Controlling Access to the Kubernetes API

? API Server Ports and IPs By default the Kubernetes API server serves HTTP o...

2708

扫码关注云+社区