离线辅助系统

数据接入

Flume介绍

Flume组件

Flume实战案例

任务调度

调度器基础

市面上调度工具

Oozie的使用

Oozie的流程定义详解

数据导出

sqoop基础知识

sqoop实战及原理

Sqoop数据导入实战

Sqoop数据导出实战

Sqoop作业操作

Sqoop的原理

# 定义这个agent中各组件的名字

a1.sources = r1

a1.sinks = k1

a1.channels = c1

# 描述和配置source组件：r1

a1.sources.r1.type = netcat

a1.sources.r1.bind = localhost

a1.sources.r1.port = 44444

# 描述和配置sink组件：k1

a1.sinks.k1.type = logger

# 描述和配置channel组件，此处使用是内存缓存的方式

a1.channels.c1.type = memory

a1.channels.c1.capacity = 1000

a1.channels.c1.transactionCapacity = 100

# 描述和配置source  channel   sink之间的连接关系

a1.sources.r1.channels = c1

a1.sinks.k1.channel = c1

bin/flume-ng agent -c conf -f conf/netcat-logger.conf -n a1  -Dflume.root.logger=INFO,console

#定义三大组件的名称

agent1.sources = source1

agent1.sinks = sink1

agent1.channels = channel1

# 配置source组件

agent1.sources.source1.type = spooldir

agent1.sources.source1.spoolDir = /home/hadoop/logs/

agent1.sources.source1.fileHeader = false

#配置拦截器

agent1.sources.source1.interceptors = i1

agent1.sources.source1.interceptors.i1.type = host

agent1.sources.source1.interceptors.i1.hostHeader = hostname

# 配置sink组件

agent1.sinks.sink1.type = hdfs

agent1.sinks.sink1.hdfs.path =hdfs://hdp-node-01:9000/weblog/flume-collection/%y-%m-%d/%H-%M

agent1.sinks.sink1.hdfs.filePrefix = access_log

agent1.sinks.sink1.hdfs.maxOpenFiles = 5000

agent1.sinks.sink1.hdfs.batchSize= 100

agent1.sinks.sink1.hdfs.fileType = DataStream

agent1.sinks.sink1.hdfs.writeFormat =Text

agent1.sinks.sink1.hdfs.rollSize = 102400

agent1.sinks.sink1.hdfs.rollCount = 1000000

agent1.sinks.sink1.hdfs.rollInterval = 60

#agent1.sinks.sink1.hdfs.round = true

#agent1.sinks.sink1.hdfs.roundValue = 10

#agent1.sinks.sink1.hdfs.roundUnit = minute

agent1.sinks.sink1.hdfs.useLocalTimeStamp = true

# Use a channel which buffers events in memory

agent1.channels.channel1.type = memory

agent1.channels.channel1.keep-alive = 120

agent1.channels.channel1.capacity = 500000

agent1.channels.channel1.transactionCapacity = 600

# Bind the source and sink to the channel

agent1.sources.source1.channels = channel1

agent1.sinks.sink1.channel = channel1

agent1.sources = source1

agent1.sinks = sink1

agent1.channels = channel1

# Describe/configure tail -F source1

agent1.sources.source1.type = exec

agent1.sources.source1.command = tail -F /home/hadoop/logs/access_log

agent1.sources.source1.channels = channel1

#configure host for source

agent1.sources.source1.interceptors = i1

agent1.sources.source1.interceptors.i1.type = host

agent1.sources.source1.interceptors.i1.hostHeader = hostname

# Describe sink1

agent1.sinks.sink1.type = hdfs

#a1.sinks.k1.channel = c1

agent1.sinks.sink1.hdfs.path =hdfs://hdp-node-01:9000/weblog/flume-collection/%y-%m-%d/%H-%M

agent1.sinks.sink1.hdfs.filePrefix = access_log

agent1.sinks.sink1.hdfs.maxOpenFiles = 5000

agent1.sinks.sink1.hdfs.batchSize= 100

agent1.sinks.sink1.hdfs.fileType = DataStream

agent1.sinks.sink1.hdfs.writeFormat =Text

agent1.sinks.sink1.hdfs.rollSize = 102400

agent1.sinks.sink1.hdfs.rollCount = 1000000

agent1.sinks.sink1.hdfs.rollInterval = 60

agent1.sinks.sink1.hdfs.round = true

agent1.sinks.sink1.hdfs.roundValue = 10

agent1.sinks.sink1.hdfs.roundUnit = minute

agent1.sinks.sink1.hdfs.useLocalTimeStamp = true

# Use a channel which buffers events in memory

agent1.channels.channel1.type = memory

agent1.channels.channel1.keep-alive = 120

agent1.channels.channel1.capacity = 500000

agent1.channels.channel1.transactionCapacity = 600

# Bind the source and sink to the channel

agent1.sources.source1.channels = channel1

agent1.sinks.sink1.channel = channel1

特性

Hamake

Oozie

Azkaban

Cascading

工作流描述语言

XML

XML (xPDL based)

text file with key/value pairs

Java API

依赖机制

data-driven

explicit

explicit

explicit

是否要web容器

No

Yes

Yes

No

进度跟踪

console/log messages

web page

web page

Java API

Hadoop job调度支持

no

yes

yes

yes

运行模式

command line utility

daemon

daemon

API

Pig支持

yes

yes

yes

yes

事件通知

no

no

no

yes

需要安装

no

yes

yes

no

支持的hadoop版本

0.18+

0.20+

currently unknown

0.18+

重试支持

no

workflownode evel

yes

yes

运行任意命令

yes

yes

yes

yes

Amazon EMR支持

yes

no

currently unknown

yes

#Azkaban Personalization Settings

azkaban.name=Test                           #服务器UI名称,用于服务器上方显示的名字

azkaban.label=My Local Azkaban                               #描述

azkaban.color=#FF3601                                                 #UI颜色

azkaban.default.servlet.path=/index                         #

web.resource.dir=web/                                                 #默认根web目录

default.timezone.id=Asia/Shanghai                           #默认时区,已改为亚洲/上海 默认为美国

#Azkaban UserManager class

user.manager.class=azkaban.user.XmlUserManager   #用户权限管理默认类

user.manager.xml.file=conf/azkaban-users.xml              #用户配置,具体配置参加下文

#Loader for projects

executor.global.properties=conf/global.properties    # global配置文件所在位置

azkaban.project.dir=projects                                                #

database.type=mysql                                                              #数据库类型

mysql.port=3306                                                                       #端口号

mysql.host=hadoop03                                                      #数据库连接IP

mysql.database=azkaban                                                       #数据库实例名

mysql.user=root                                                                 #数据库用户名

mysql.password=root                                                          #数据库密码

mysql.numconnections=100                                                  #最大连接数

# Velocity dev mode

velocity.dev.mode=false

# Jetty服务器属性.

jetty.maxThreads=25                                                               #最大线程数

jetty.ssl.port=8443                                                                   #Jetty SSL端口

jetty.port=8081                                                                         #Jetty端口

jetty.keystore=keystore                                                          #SSL文件名

jetty.password=123456                                                             #SSL文件密码

jetty.keypassword=123456                                                      #Jetty主密码 与 keystore文件相同

jetty.truststore=keystore                                                                #SSL文件名

jetty.trustpassword=123456                                                   # SSL文件密码

# 执行服务器属性

executor.port=12321                                                               #执行服务器端口

# 邮件设置

mail.sender=xxxxxxxx@163.com                                       #发送邮箱

mail.host=smtp.163.com                                                       #发送邮箱smtp地址

mail.user=xxxxxxxx                                       #发送邮件时显示的名称

mail.password=**********                                                 #邮箱密码

job.failure.email=xxxxxxxx@163.com                              #任务失败时发送邮件的地址

job.success.email=xxxxxxxx@163.com                            #任务成功时发送邮件的地址

lockdown.create.projects=false                                           #

cache.directory=cache                                                            #缓存目录

#Azkaban

default.timezone.id=Asia/Shanghai                                              #时区

# Azkaban JobTypes 插件配置

azkaban.jobtype.plugin.dir=plugins/jobtypes                   #jobtype 插件所在位置

#Loader for projects

executor.global.properties=conf/global.properties

azkaban.project.dir=projects

#数据库设置

database.type=mysql                                                                       #数据库类型(目前只支持mysql)

mysql.port=3306                                                                                #数据库端口号

mysql.host=192.168.20.200                                                           #数据库IP地址

mysql.database=azkaban                                                                #数据库实例名

mysql.user=azkaban                                                                         #数据库用户名

mysql.password=oracle                                                                   #数据库密码

mysql.numconnections=100                                                           #最大连接数

# 执行服务器配置

executor.maxThreads=50                                                                #最大线程数

executor.port=12321                                                               #端口号(如修改,请与web服务中一致)

executor.flow.threads=30                                                                #线程数

<azkaban-users>

        <user username="azkaban" password="azkaban" roles="admin" groups="azkaban" />

        <user username="metrics" password="metrics" roles="metrics"/>

        <user username="admin" password="admin" roles="admin,metrics" />

        <role name="admin" permissions="ADMIN" />

        <role name="metrics" permissions="METRICS"/>

</azkaban-users>

#command.job

type=command                                                   

command=echo 'hello'

# foo.job

type=command

command=echo foo

# bar.job

type=command

dependencies=foo

command=echo bar

# fs.job

type=command

command=/home/hadoop/apps/hadoop-2.6.1/bin/hadoop fs -mkdir /azaz

# mrwc.job

type=command

command=/home/hadoop/apps/hadoop-2.6.1/bin/hadoop jar hadoop-mapreduce-examples-2.6.1.jar wordcount  /wordcount/input  /wordcount/azout

use default;

drop table aztest;

create table aztest(id int,name string) row format delimited fields terminated by ',';

load data inpath '/aztest/hiveinput' into table aztest;

create table azres as select * from aztest;

insert overwrite directory '/aztest/hiveoutput' select count(1) from aztest;

# hivef.job

type=command

command=/home/hadoop/apps/hive/bin/hive -f 'test.sql'

$ sqoop import (generic-args) (import-args)

id

name

deg

salary

dept

1201

gopal

manager

50,000

TP

1202

manisha

Proof reader

50,000

TP

1203

khalil

php dev

30,000

AC

1204

prasanth

php dev

30,000

AC

1205

kranthi

admin

20,000

TP

id

hno

street

city

1201

288A

vgiri

jublee

1202

108I

aoc

sec-bad

1203

144Z

pgutta

hyd

1204

78B

old city

sec-bad

1205

720X

hitec

sec-bad

id

phno

email

1201

2356742

gopal@tp.com

1202

1661663

manisha@tp.com

1203

8887776

khalil@ac.com

1204

9988774

prasanth@ac.com

1205

1231231

kranthi@tp.com

$bin/sqoop import \

--connect jdbc:mysql://hdp-node-01:3306/test \

--username root \

--password root \

--table emp

--m 1

14/12/22 15:24:54 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5

14/12/22 15:24:56 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.

INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/cebe706d23ebb1fd99c1f063ad51ebd7/emp.jar

-----------------------------------------------------

O mapreduce.Job: map 0% reduce 0%

14/12/22 15:28:08 INFO mapreduce.Job: map 100% reduce 0%

14/12/22 15:28:16 INFO mapreduce.Job: Job job_1419242001831_0001 completed successfully

-----------------------------------------------------

-----------------------------------------------------

14/12/22 15:28:17 INFO mapreduce.ImportJobBase: Transferred 145 bytes in 177.5849 seconds (0.8165 bytes/sec)

14/12/22 15:28:17 INFO mapreduce.ImportJobBase: Retrieved 5 records.

$ $HADOOP_HOME/bin/hadoop fs -cat /user/hadoop/emp/part-m-00000

1201, gopal,    manager, 50000, TP

1202, manisha,  preader, 50000, TP

1203, kalil,    php dev, 30000, AC

1204, prasanth, php dev, 30000, AC

1205, kranthi,  admin,   20000, TP

bin/sqoop import

--connect jdbc:mysql://hdp-node-01:3306/test

--username root

--password root

--table emp

--hive-import

--m 1

--target-dir <new or exist directory in HDFS>

bin/sqoop import \

--connect jdbc:mysql://hdp-node-01:3306/test \

--username root \

--password root \

--target-dir /queryresult \

--table emp --m 1

 $HADOOP_HOME/bin/hadoop fs -cat /queryresult/part-m-*

1201, 288A, vgiri,   jublee

1202, 108I, aoc,     sec-bad

1203, 144Z, pgutta,  hyd

1204, 78B,  oldcity, sec-bad

1205, 720C, hitech,  sec-bad

--where <condition>

bin/sqoop import \

--connect jdbc:mysql://hdp-node-01:3306/test \

--username root \

--password root \

--where "city ='sec-bad'" \

--target-dir /wherequery \

--table emp_add --m 1

$HADOOP_HOME/bin/hadoop fs -cat /wherequery/part-m-*

1202, 108I, aoc, sec-bad

1204, 78B, oldcity, sec-bad

1205, 720C, hitech, sec-bad

--incremental <mode>

--check-column <column name>

--last value <last check column value>

bin/sqoop import \

--connect jdbc:mysql://hdp-node-01:3306/test \

--username root \

--password root \

--table emp --m 1 \

--incremental append \

--check-column id \

--last-value 1205

$ $HADOOP_HOME/bin/hadoop fs -cat /user/hadoop/emp/part-m-*

它用逗号（，）分隔 emp_add表数据和字段。

1201, gopal,    manager, 50000, TP

1202, manisha,  preader, 50000, TP

1203, kalil,    php dev, 30000, AC

1204, prasanth, php dev, 30000, AC

1205, kranthi,  admin,   20000, TP

1206, satish p, grp des, 20000, GR

$ $HADOOP_HOME/bin/hadoop fs -cat /emp/part-m-*1

这表示新添加的行用逗号（，）分隔emp表的字段。

1206, satish p, grp des, 20000, GR

$ sqoop export (generic-args) (export-args)

1201, gopal,     manager, 50000, TP

1202, manisha,   preader, 50000, TP

1203, kalil,     php dev, 30000, AC

1204, prasanth,  php dev, 30000, AC

1205, kranthi,   admin,   20000, TP

1206, satish p,  grp des, 20000, GR

$ mysql

mysql> USE db;

mysql> CREATE TABLE employee (

   id INT NOT NULL PRIMARY KEY,

   name VARCHAR(20),

   deg VARCHAR(20),

   salary INT,

   dept VARCHAR(10));

bin/sqoop export \

--connect jdbc:mysql://hdp-node-01:3306/test \

--username root \

--password root \

--table emp2 \

--export-dir /user/hadoop/emp/

mysql>select * from employee;

如果给定的数据存储成功，那么可以找到数据在如下的employee表。

+------+--------------+-------------+-------------------+--------+

| Id   | Name         | Designation | Salary            | Dept   |

+------+--------------+-------------+-------------------+--------+

| 1201 | gopal        | manager     | 50000             | TP     |

| 1202 | manisha      | preader     | 50000             | TP     |

| 1203 | kalil        | php dev     | 30000             | AC     |

| 1204 | prasanth     | php dev     | 30000             | AC     |

| 1205 | kranthi      | admin       | 20000             | TP     |

| 1206 | satish p     | grp des     | 20000             | GR     |

+------+--------------+-------------+-------------------+--------+

$ sqoop job (generic-args) (job-args)

   [-- [subtool-name] (subtool-args)]

$ sqoop-job (generic-args) (job-args)

   [-- [subtool-name] (subtool-args)]

bin/sqoop job

--create myimportjob

-- import

--connect jdbc:mysql://hdp-node-01:3306/test

--username root

--password root

--table emp

--m 1

Job: myjob

 Tool: import Options:

 ----------------------------

 direct.import = true

 codegen.input.delimiters.record = 0

 hdfs.append.dir = false

 db.table = employee

 ...

 incremental.last.value = 1206

 ...

$ sqoop job --exec myjob

它会显示下面的输出。

10/08/19 13:08:45 INFO tool.CodeGenTool: Beginning code generation

...

$ sqoop-codegen (generic-args) (codegen-args)

$ sqoop-codegen (generic-args) (codegen-args)

$ sqoop-codegen \

--import

--connect jdbc:mysql://localhost/userdb \

--username root \

--table emp

14/12/23 02:34:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5

14/12/23 02:34:41 INFO tool.CodeGenTool: Beginning code generation

……………….

14/12/23 02:34:42 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop

Note: /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/emp.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

14/12/23 02:34:47 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/emp.jar

$ cd /tmp/sqoop-hadoop/compile/9a300a1f94899df4a9b10f9935ed9f91/

$ ls

emp.class

emp.jar

emp.java