Ambari元件狀态從MySQL同步到SQLServer或者MySQL一、Ambari表資訊二、Ambari元件資訊同步實作
目錄
一、Ambari表資訊
1.1.hoststate（主機狀态表）
1.2.hostcomponentstate（主機元件狀态表）
1.3. hosts（主機表）
1.4.clusters(叢集表)
二、Ambari元件資訊同步實作
2.1.編寫table.ini 的資料庫配置檔案
2.2.Shell 實作表資訊的同步
一、Ambari表資訊
1.1.hoststate（主機狀态表）
mysql> desc hoststate;
+-------------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------------+--------------+------+-----+---------+
| agent_version | varchar(255) | NO | | NULL | agent版本
| available_mem | bigint(20) | NO | | NULL | 可用記憶體
| current_state | varchar(255) | NO | | NULL | 目前狀态
| health_status | varchar(255) | YES | | NULL | 健康狀态
| host_id | bigint(20) | NO | PRI | NULL | 主機id
| time_in_state | bigint(20) | NO | | NULL |
| maintenance_state | varchar(512) | YES | | NULL | 維護模式狀态
+-------------------+--------------+------+-----+---------+
1.2.hostcomponentstate（主機元件狀态表）
mysql> desc hostcomponentstate;
+-----------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------+--------------+------+-----+---------+
| id | bigint(20) | NO | PRI | NULL |
| cluster_id | bigint(20) | NO | | NULL |
| component_name | varchar(100) | NO | MUL | NULL | 元件名稱
| version | varchar(32) | NO | | UNKNOWN | 版本
| current_state | varchar(255) | NO | | NULL |
| last_live_state | varchar(255) | NO | | UNKNOWN | 上一次活躍狀态
| host_id | bigint(20) | NO | MUL | NULL |
| service_name | varchar(100) | NO | | NULL | 元件對應服務名稱
| upgrade_state | varchar(32) | NO | | NONE |
+-----------------+--------------+------+-----+---------+
1.3. hosts（主機表）
mysql> desc hosts;
+------------------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------------------+---------------+------+-----+---------+
| host_id | bigint(20) | NO | PRI | NULL |
| host_name | varchar(255) | NO | UNI | NULL | 主機名稱
| cpu_count | int(11) | NO | | NULL | cpu數量
| cpu_info | varchar(255) | NO | | NULL | cpu資訊
| discovery_status | varchar(2000) | NO | | NULL |
| host_attributes | longtext | NO | | NULL | 主機屬性
| ipv4 | varchar(255) | YES | | NULL | IP位址
| ipv6 | varchar(255) | YES | | NULL | IP位址
| last_registration_time | bigint(20) | NO | | NULL | 最後登記時間
| os_arch | varchar(255) | NO | | NULL | 作業系統架構
| os_info | varchar(1000) | NO | | NULL |
| os_type | varchar(255) | NO | | NULL |
| ph_cpu_count | int(11) | YES | | NULL |
| public_host_name | varchar(255) | YES | | NULL | 主機名
| rack_info | varchar(255) | NO | | NULL | 機架資訊
| total_mem | bigint(20) | NO | | NULL | 總記憶體
+------------------------+---------------+------+-----+---------+
1.4.clusters(叢集表)
+-----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| cluster_id | bigint(20) | NO | PRI | NULL | |
| resource_id | bigint(20) | NO | MUL | NULL | | 資源id
| upgrade_id | bigint(20) | YES | MUL | NULL | |
| cluster_info | varchar(255) | NO | | NULL | |
| cluster_name | varchar(100) | NO | UNI | NULL | | 叢集名稱
| provisioning_state | varchar(255) | NO | | INIT | | 配置狀态
| security_type | varchar(32) | NO | | NONE | | 安全政策類型
| desired_cluster_state | varchar(255) | NO | | NULL | | 預期叢集狀态
| desired_stack_id | bigint(20) | NO | MUL | NULL | | 預期庫id
+-----------------------+--------------+------+-----+---------+-------+
1.5.表的關聯查詢
SELECT cluster_name,host_name,ipv4,service_name,a.current_state,component_name
FROM hostcomponentstate a
INNER JOIN hoststate b
on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED'
查詢出的資訊我們将同步到 SQLServer或者MySQL 表，說明一點，上面這個SQL語句查詢出來是 STARTED 的狀态，也就是元件的最新狀态，當第一次查詢時，我們要将元件全部最新狀态同步到 要查詢的資料庫中，如果哪個元件停掉，上面的 SQL的 就不會查詢出來，查詢出來的還是 STARTED 狀态，我們隻需要 将 最新狀态的臨時表和 目标表關聯 ，将關聯不到的 STATED的 狀态的元件 狀态更新為 STOPED 即可，就可以實作叢集監控資訊 的同步。
二、Ambari元件資訊同步實作
将MySQL中Ambari 中 4個表的資訊關聯查詢出的資訊同步到SqlServer，任務排程頻次5分鐘一趟，發現問題就告警
2.1.編寫table.ini 的資料庫配置檔案
xxx 是不同的客戶不同的資料庫 可以讀取多個資料庫的配置寫到不同的SQLServer資料庫
[[email protected] ~]$ cat /hadoop/datadir/script/hadoop/table.ini
[xxx_CONNECT]
url=xxx
port=1433
username=PCS.Support
[email protected]#
dbname=HDP_TEST
customer=xxx_
2.2.Shell 實作表資訊的同步到SQLServer
set -x
HOSTNAME="xxx"
USER="root"
PASSWD="@001"
PORT="3306"
DBNAME="ambari"
function ReadConnect(){
ReadINI=`awk -F '=' '/\['$2'\]/{a=1}a==1&&$1~/^'$3'$/{print $2;exit}' $1`
}
batchCustomer=xxx_
table_ini=/hadoop/datadir/script/hadoop/ipvaSum/table.ini
ReadConnect $table_ini "${batchCustomer}CONNECT" url
server=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" port
port=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" dbname
database=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" username
user=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" password
paw=$ReadINI
ambari_tmp_file=/hadoop/datadir/temp/monitor/component_tmp_file.txt
mysql_cmd="mysql -h${HOSTNAME} -P${PORT} -u${USER} -p${PASSWD} ${DBNAME} -e"
sqlserver_cmd="/opt/mssql-tools/bin/sqlcmd -S $server -U $user -P $paw -d ${database} -Q "
datebatch=`date +'%Y-%m-%d %H:%M:%S'`
# AmbariServer監控
ambariServerCount=`ps -ef | grep AmbariServer | grep -v "grep" | wc -l`
if [ 0 == $ambariServerCount ];then
${sqlserver_cmd} "INSERT into task_monitor (flowId,taskId,status,startTime,endTime) VALUES(DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()),'ambari-server-heartbeat',90,GETDATE(),GETDATE())"
else
${sqlserver_cmd} "INSERT into task_monitor (flowId,taskId,status,startTime,endTime) VALUES(DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()),'ambari-server-heartbeat',80,GETDATE(),GETDATE())"
fi
#查詢元件狀态
select_ambari_sql="SELECT cluster_name , host_name , ipv4 , service_name ,a. current_state,component_name FROM hostcomponentstate a INNER JOIN hoststate b on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED' into outfile \"${ambari_tmp_file}\" fields terminated by \",\" ;"
# meger
task_merge_sql="MERGE ${database}.[dbo].[component_monitor] AS a USING (SELECT cluster_name,host_name,ipv4,service_name,current_state,component_name FROM ${database}.[dbo].[component_monitor_tmp]) AS b ON a.host_name = b.host_name AND a.service_name=b.service_name AND a.component_name = b.component_name WHEN MATCHED THEN UPDATE SET a.current_state = b.current_state ,a.modifyTime='${datebatch}' WHEN NOT MATCHED THEN INSERT (cluster_name , host_name , ipv4 , service_name ,current_state , component_name,modifyTime,createTime) VALUES(b.cluster_name , b.host_name , b.ipv4 , b.service_name ,b.current_state , b.component_name,'${datebatch}','${datebatch}');"
update_stop_sql="UPDATE ${database}.[dbo].[component_monitor] SET current_state = 'STOPED' where modifyTime < (select max(modifyTime) from ${database}.[dbo].[component_monitor]);"
#執行SQL 運作成功和失敗的任務 寫入檔案
rm -rf ${azkaban_exec_tmp_file}
${mysql_cmd} "${select_ambari_sql}"
if [ -f ${azkaban_exec_tmp_file} ];then
${sqlserver_cmd} "truncate table ${database}.[dbo].[component_monitor_tmp]"
/opt/mssql-tools/bin/bcp ${database}.dbo.component_monitor_tmp in ${azkaban_exec_tmp_file} -S${server} -U${user} -P${paw} -c -t, -r'\n' -b 1000
${sqlserver_cmd} "${task_merge_sql}"
${sqlserver_cmd} "${update_stop_sql}"
else
echo file ${azkaban_exec_tmp_file} not exist!
fi
2.3.Shell 實作表資訊的同步到MySQL
兩個MySQL 是不同的資料庫 或者不同的伺服器上的資料庫。
set -x
# Ambari 中繼資料庫
HOSTNAME="xxx"
USER="root"
PASSWD="@001"
PORT="3306"
DBNAME="ambari"
# 告警目标庫
WARN_HOSTNAME="xxx"
WARN_USER="root"
WARN_PASSWD="@001"
WARN_PORT="3306"
WARN_DBNAME="kangll"
# 查詢元件狀态寫入到本地的臨時檔案
hdp_tmp_file=/hadoop/datadir/temp/monitor/component_tmp_file.txt
mysql_cmd="mysql -h${HOSTNAME} -P${PORT} -u${USER} -p${PASSWD} ${DBNAME} -e "
mysql_cmd_two="mysql -h${WARN_HOSTNAME} -P${WARN_PORT} -u${WARN_USER} -p${WARN_PASSWD} ${WARN_DBNAME} -e "
#查詢元件狀态,查詢出來是 STATED 狀态的元件，如果元件停掉則查不到
select_ambari_sql="SELECT cluster_name , host_name , ipv4 , service_name ,a. current_state,component_name FROM hostcomponentstate a INNER JOIN hoststate b on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED' into outfile \"${hdp_tmp_file}\" fields terminated by \",\" ;"
# 狀态字段的更新，元件停掉則臨時表查不到，臨時表和目标表關聯，如果哪個元件停掉則，目标表會多一條，則更新STARED 狀态的元件
update_state_sql="UPDATE component_monitor cm LEFT JOIN component_monitor_tmp t ON
cm.host_name = t.host_name AND cm.service_name = t.service_name AND cm.component_name = t.component_name
SET cm.current_state = if(t.component_name is null, 'STOPED',t.current_state);"
# 查詞插入目标表資料檢查
insert_check_sql="select count(*) as cnt from component_monitor;"
# 首次查詢叢集元件狀态
select_insert_sql="insert into component_monitor(cluster_name,host_name,ipv4,service_name,current_state,component_name) select cluster_name,host_name,ipv4,service_name,current_state,component_name from component_monitor_tmp"
# load檔案到臨時表
load_file_sql="load data local infile \"${hdp_tmp_file}\" into table component_monitor_tmp fields terminated by \",\" (cluster_name,host_name,ipv4,service_name,current_state,component_name)"
# 删除臨時檔案
rm -rf ${hdp_tmp_file}
# 查詢元件狀态寫入臨時檔案
${mysql_cmd} "${select_ambari_sql}"
# 判斷臨時檔案是否存在
if [ -f ${hdp_tmp_file} ];then
${mysql_cmd_two} "truncate table component_monitor_tmp"
# load 本地資料到臨時表
${mysql_cmd_two} "${load_file_sql}"
# 判斷首次目标表資料為 0，則查詢插入，否則直接更新狀态
dataCount=$(mysql -h${WARN_HOSTNAME} -P${WARN_PORT} -u${WARN_USER} -p${WARN_PASSWD} ${WARN_DBNAME} --skip-column-names -e "${insert_check_sql}")
echo $dataCount
if [ 0 -eq $dataCount ];then
# 叢集狀态首次寫入表
${mysql_cmd_two} "${select_insert_sql}"
else
# 叢集元件狀态更新
${mysql_cmd_two} "${update_state_sql}"
fi
else
echo ${hdp_tmp_file}"not exist"!
fi