利用ali OTTER 實時同步業務資料到資料倉庫

1.背景

     公司要求是某些業務報表達到實時标準，資料量最多的表大概400W+,報表邏輯複雜，計算一年24W資料，整個程個流程下來差不多一個小時，主要時間耗在全量資料同步跟計算上。年後大概實時需求會更多，資料量會更大，各分部資料到會到資料倉庫。用腳本增量同步有限制，主要因為業務資料每天定時批量更新，周期是分鐘，每次更新至少10W以上，腳本從下載下傳資料到導入資料之後計算，流程複雜，時間太長。是以首先要解決的是資料實時同步到資料倉庫。

2.OTTER 介紹

安裝步驟，原理介紹 參考：https://yq.aliyun.com/articles/223077?utm_content=m_32233

上面安裝會有點小問題，自己安裝研究了下：詳見下面

2.1 軟體版本表

 需要軟體：java，MYSQL5.x,manager,node,ZOOKEEPER，aria2

軟體名	版本号	備注
JAVA	1.8.0_131
mysql	5.6
manager	4.2.12	最新版是，新版下載下傳安裝後發現實際目錄結構跟啟動腳本中路徑不比對. 例如：啟動腳本配置檔案是conf目錄裡面，然而實際的目錄結構沒有conf而是resource目錄。
node	4.2.12	與manager版本保持一緻
zookeeper	3.4.10
aria2	1.15.2	下載下傳插件，manager郵件提醒用，啟動node是會檢查，沒有會報錯。

2.2 安裝

mysql java 安裝這裡不做說明，其他部落格有說明。

mysql配置：

show variables like 'binlog_format'   #binlong 格式必須是row
show variables like '%server_id%'     #必須有server_id,該參數跟資料庫複制有關，詳情看官網
show variables like '%char%'   #字元集character_set_server 必須是utf8,否則配置資料源表驗證不通過。
           

#my.ini
[mysqld]  添加以下配置
character-set-server=utf8   
collation-server=utf8_general_ci 
binlog_format=row
log-bin=mysql-bin
server_id=1
           

   初始化otter manager系統表：

[[email protected] node]$  wget https://raw.github.com/alibaba/otter/master/manager/deployer/src/main/resources/sql/otter-manager-schema.sql
[[email protected] node]$  mysql -h127.0.0.1 -uroot -p******* otter -e "source otter-manager-schema.sql";
           

2.2.1 zookeeper 安裝

下載下傳解壓zookeeper 到/usr/local/zookeeper目錄下：

[[email protected] conf]# cp zoo_sample.cfg  zoo.cfg
[[email protected] conf]# cat zoo.cfg |grep -v ^#
tickTime=2000
initLimit=10
syncLimit=5
dataDir=/var/lib/zookeeper
clientPort=2181
server.1=192.168.1.132:3887
[[email protected] conf]#
           

啟動zookeeper:

[[email protected] zookeeper-3.4.10]# ./bin/zkServer.sh  start --啟動
ZooKeeper JMX enabled by default
Using config: /usr/local/zookeeper/zookeeper-3.4.10/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[[email protected] zookeeper-3.4.10]# 
[[email protected] zookeeper-3.4.10]# ./bin/zkServer.sh  stop    --關閉
ZooKeeper JMX enabled by default
Using config: /usr/local/zookeeper/zookeeper-3.4.10/bin/../conf/zoo.cfg
Stopping zookeeper ... STOPPED
           

2.2.2 aria2 安裝

下載下傳解壓aria2 到/usr/local/aria2/aria2-1.15.2(目錄沒有統一好，因為之前裝了一個新版本1.33.1，在編譯時要GCC>4.7 )

[[email protected] aria2-1.15.2]# ./configure 
[[email protected] aria2-1.15.2]# make
[[email protected] aria2-1.15.2]# make install
#########标準源碼安裝三部曲############
[[email protected] aria2-1.15.2]# aria2c -v
aria2 version 1.15.2
Copyright (C) 2006, 2012 Tatsuhiro Tsujikawa


This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.


This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.


** Configuration **
Enabled Features: BitTorrent, GZip, HTTPS, Message Digest
Hash Algorithms: sha-1, sha-224, sha-256, sha-384, sha-512, md5


Report bugs to t-tuj[email protected]
Visit http://aria2.sourceforge.net/
           

直接安裝：

[[email protected] ~]# yum install epel-release
[[email protected] ~]# yum install aria2 -y
           

2.2.3 otter 安裝

OTTER安裝在otter使用者下，家目錄結構如下： manager,node分别解壓到otter-manager和otter-node目錄

[[email protected] ~]$ ll
總用量 41092
-rw-r--r--. 1 otter otter 24385075 2月   4 15:03 manager.deployer-4.2.12.tar.gz
-rw-r--r--. 1 otter otter 17663670 2月   4 15:03 node.deployer-4.2.12.tar.gz
drwxrwxr-x. 7 otter otter     4096 2月   4 15:13 otter-manager
-rw-rw-r--. 1 otter otter    11298 2月   4 15:02 otter-manager-schema.sql
drwxr-xr-x. 6 otter otter     4096 12月  5 19:48 otter-master     #之前下的最新版，裡面包含manager node 包，但是目錄不對
drwxrwxr-x. 9 otter otter     4096 2月   4 17:05 otter-node
[[email protected] ~]$ 
           

otter manager 配置：

[[email protected] conf]$ cd /home/otter/otter-manager/conf
[[email protected] conf]$ cat otter.properties |grep -v ^#|grep -v ^$
otter.domainName = 127.0.0.1    #修改成實際IP
otter.port = 8080
otter.jetty = jetty.xml
otter.database.driver.class.name = com.mysql.jdbc.Driver
otter.database.driver.url = jdbc:mysql://127.0.0.1:3306/otter   #資料庫配置 需要手動初始化系統表！！！。
otter.database.driver.username = root
otter.database.driver.password = 123456
otter.communication.manager.port = 1099
otter.communication.pool.size = 10
otter.zookeeper.cluster.default = 127.0.0.1:2181
otter.zookeeper.sessionTimeout = 60000
otter.manager.address = ${otter.domainName}:${otter.communication.manager.port}
otter.manager.productionMode = true
otter.manager.monitor.self.enable = true
otter.manager.monitor.self.interval = 120
otter.manager.monitor.recovery.paused = true
otter.manager.monitor.email.host = smtp.gmail.com
otter.manager.monitor.email.username = 
otter.manager.monitor.email.password = 
otter.manager.monitor.email.stmp.port = 465
           

啟動manager:

[[email protected] otter-manager]$ ./bin/startup.sh
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=96m; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: UseCMSCompactAtFullCollection is deprecated and will likely be removed in a future release.
2018-02-04 23:45:53.306 [] INFO  com.alibaba.otter.manager.deployer.OtterManagerLauncher - ## start the manager server.
2018-02-04 23:46:07.858 [] INFO  com.alibaba.otter.manager.deployer.JettyEmbedServer - ##Jetty Embed Server is startup!
2018-02-04 23:46:07.858 [] INFO  com.alibaba.otter.manager.deployer.OtterManagerLauncher - ## the manager server is running now ......
           

otter node 配置：

[[email protected] conf]$ cd /home/otter/otter-node/conf
[[email protected] conf]$ cat otter.properties |grep -v ^# |grep -v ^$
otter.nodeHome = ${user.dir}/../
otter.htdocs.dir = ${otter.nodeHome}/htdocs
otter.download.dir = ${otter.nodeHome}/download
otter.extend.dir= ${otter.nodeHome}/extend
otter.zookeeper.sessionTimeout = 60000
otter.communication.pool.size = 10
otter.manager.address = 127.0.0.1:1099  #修改成實際ip
           

otter node啟動：

[[email protected] otter-node]$ ./bin/startup.sh
[[email protected] otter-node]$ ./bin/stop.sh
           

2.2.4 驗證

manager WEB登入：http://192.168.1.132:8080/login.htm 登入預設是匿名登入，可用admin使用者登入建立流程（admin/admin）:

附錄：

manger啟停：

[[email protected] bin]$ pwd
/home/otter/otter-manager/bin
[ot[email protected] bin]$ ls
otter.pid  startup.bat  startup.sh  stop.sh
           

node啟停：

/home/otter/otter-node/bin
[[email protected] bin]$ ll
總用量 36
-rw-rw-r--. 1 otter otter 16589 2月   4 16:35 hs_err_pid18360.log  #報錯日志
-rw-rw-r--. 1 otter otter     6 2月   4 17:05 otter.pid
-rwxr-xr-x. 1 otter otter  1144 6月  30 2015 startup.bat
-rwxr-xr-x. 1 otter otter  3578 6月  30 2015 startup.sh
-rwxr-xr-x. 1 otter otter  1361 6月  30 2015 stop.sh
[[email protected] bin]$ 
           

zookeeper:

[[email protected] bin]$ ./zkServer.sh  --help
ZooKeeper JMX enabled by default
Using config: /usr/local/zookeeper/zookeeper-3.4.10/bin/../conf/zoo.cfg
Usage: ./zkServer.sh {start|start-foreground|stop|restart|status|upgrade|print-cmd}
           

報錯解決1：

[[email protected] node]$ cat node.log
# There is insufficient memory for the Java Runtime Environment to continue.
# Native memory allocation (mmap) failed to map 1073741824 bytes for committing reserved memory.
# An error report file with more information is saved as:
# /home/otter/otter-node/bin/hs_err_pid18312.log
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=96m; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=256m; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: UseCMSCompactAtFullCollection is deprecated and will likely be removed in a future release.
Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000700000000, 1073741824, 0) failed; error='Cannot allocate memory' (errno=12)
           

解決虛拟機記憶體不夠具體記憶體資訊檢視 /home/otter/otter-node/bin/hs_err_pid18312.log，加大記憶體，或減少其他應用記憶體使用。

報錯解決2：

Exception in thread "main" java.lang.ExceptionInInitializerError
        at com.alibaba.otter.node.deployer.OtterLauncher.main(OtterLauncher.java:39)
Caused by: com.alibaba.otter.shared.common.model.config.ConfigException: ERROR ## 
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'configClientService' defined in URL [jar:file:/home/otter/otter-node/lib/node.common-4.2.12.jar!/
spring/otter-node-config.xml]: Invocation of init method failed; nested exception is com.alibaba.otter.shared.common.model.config.ConfigException: nid is not set!
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1455)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:284)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:609)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:469)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
        at com.alibaba.otter.node.etl.OtterContextLocator$1.<init>(OtterContextLocator.java:39)
        at com.alibaba.otter.node.etl.OtterContextLocator.<clinit>(OtterContextLocator.java:39)
        at com.alibaba.otter.node.deployer.OtterLauncher.main(OtterLauncher.java:39)
Caused by: com.alibaba.otter.shared.common.model.config.ConfigException: nid is not set!
           

解決 執行：[[email protected] conf]$ echo 1>conf/nid #在conf目錄下建立檔案nid,内容為1，跟manager界面錄入的node資訊産生的序号一緻

報錯解決3：

這個錯誤有點奇怪，前端知識配置了canal 名稱（沒有強制不為空，但是一旦為空，則canal界面便一直不能通路），背景otter庫CANAL表插入了一條name空記錄，是以記住這個名稱一定要填寫，一定要填寫！！！！！！：

Problem accessing /canal_list.htm. Reason:

    Failed to invoke Valve[#2/3, level 3]: com.alibaba.citrus.turbine.pipeline.valve.PerformTemplateScreenValve#4270b142:PerformTemplateScreenValve
           

記錄 類似

2018-02-04 22:03:34.800 [/canal_list.htm] ERROR com.alibaba.citrus.webx.impl.WebxRootControllerImpl - Full stack trace of the error IllegalArgumentException: [Assertion failed] - the argument is r
equired; it must not be null
com.alibaba.citrus.service.pipeline.PipelineException: Failed to invoke Valve[#2/3, level 3]: com.alibaba.citrus.turbine.pipeline.valve.PerformTemplateScreenValve#7a0ab480:PerformTemplateScreenVal
ve
。。。。。。。
Caused by: com.alibaba.citrus.webx.WebxException: Failed to execute screen: CanalList
        at com.alibaba.citrus.turbine.pipeline.valve.PerformScreenValve.performScreenModule(PerformScreenValve.java:126) ~[citrus-webx-all-3.2.0.jar:3.2.0]
        at com.alibaba.citrus.turbine.pipeline.valve.PerformScreenValve.invoke(PerformScreenValve.java:74) ~[citrus-webx-all-3.2.0.jar:3.2.0]
        at com.alibaba.citrus.service.pipeline.impl.PipelineImpl$PipelineContextImpl.invokeNext(PipelineImpl.java:157) ~[citrus-webx-all-3.2.0.jar:3.2.0]
        ... 51 common frames omitted
           

解決：手動删除該條記錄，暫時解決方案，永久解決要修改前段該字段為必要字段，不為空。

報錯4：點選zookeeper 重新整理按鈕時報錯：

點選檢視時進去有發現錯誤：

解決：安裝NC指令： yum install nc 即可！