编程知识 cdmana.com

Ambari component status is synchronized from Mysql to SQL server

Catalog

One 、Ambari Table information

1.1.hoststate( Host status table )

1.2.hostcomponentstate( Host component status table )

1.3. hosts( Host table )

 1.4.clusters( Cluster table )

Two 、Ambari Component information synchronization

2.1. To write table.ini Database configuration file for

2.2.Shell Realize the synchronization of table information


One 、Ambari Table information

1.1.hoststate( Host status table )

mysql> desc hoststate;
+-------------------+--------------+------+-----+---------+
| Field             | Type         | Null | Key | Default |
+-------------------+--------------+------+-----+---------+
| agent_version     | varchar(255) | NO   |     | NULL    | agent edition  
| available_mem     | bigint(20)   | NO   |     | NULL    |  Available memory 
| current_state     | varchar(255) | NO   |     | NULL    |  current state 
| health_status     | varchar(255) | YES  |     | NULL    |  A healthy state 
| host_id           | bigint(20)   | NO   | PRI | NULL    |  host id
| time_in_state     | bigint(20)   | NO   |     | NULL    |
| maintenance_state | varchar(512) | YES  |     | NULL    |  Maintenance mode status 
+-------------------+--------------+------+-----+---------+

1.2.hostcomponentstate( Host component status table )

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    |  Component name 
| version         | varchar(32)  | NO   |     | UNKNOWN |  edition 
| current_state   | varchar(255) | NO   |     | NULL    | 
| last_live_state | varchar(255) | NO   |     | UNKNOWN |  Last active state 
| host_id         | bigint(20)   | NO   | MUL | NULL    | 
| service_name    | varchar(100) | NO   |     | NULL    |  The component corresponds to the service name 
| upgrade_state   | varchar(32)  | NO   |     | NONE    | 
+-----------------+--------------+------+-----+---------+

1.3. hosts( Host table )

mysql> desc hosts;
+------------------------+---------------+------+-----+---------+
| Field                  | Type          | Null | Key | Default |
+------------------------+---------------+------+-----+---------+
| host_id                | bigint(20)    | NO   | PRI | NULL    |
| host_name              | varchar(255)  | NO   | UNI | NULL    |  Host name 
| cpu_count              | int(11)       | NO   |     | NULL    | cpu Number 
| cpu_info               | varchar(255)  | NO   |     | NULL    | cpu Information 
| discovery_status       | varchar(2000) | NO   |     | NULL    | 
| host_attributes        | longtext      | NO   |     | NULL    |  Host properties 
| ipv4                   | varchar(255)  | YES  |     | NULL    | IP Address 
| ipv6                   | varchar(255)  | YES  |     | NULL    | IP Address 
| last_registration_time | bigint(20)    | NO   |     | NULL    |  Last registration time 
| os_arch                | varchar(255)  | NO   |     | NULL    |  Operating system architecture 
| 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    |  Host name 
| rack_info              | varchar(255)  | NO   |     | NULL    |  Rack information 
| total_mem              | bigint(20)    | NO   |     | NULL    |  Total memory 
+------------------------+---------------+------+-----+---------+

 1.4.clusters( Cluster table )

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| cluster_id            | bigint(20)   | NO   | PRI | NULL    |       |
| resource_id           | bigint(20)   | NO   | MUL | NULL    |       |  resources id
| upgrade_id            | bigint(20)   | YES  | MUL | NULL    |       |
| cluster_info          | varchar(255) | NO   |     | NULL    |       |
| cluster_name          | varchar(100) | NO   | UNI | NULL    |       |  Cluster name 
| provisioning_state    | varchar(255) | NO   |     | INIT    |       |  Configuration status 
| security_type         | varchar(32)  | NO   |     | NONE    |       |  Security policy type 
| desired_cluster_state | varchar(255) | NO   |     | NULL    |       |  Expected cluster state 
| desired_stack_id      | bigint(20)   | NO   | MUL | NULL    |       |  Expectation Library id
+-----------------------+--------------+------+-----+---------+-------+

 1.5. Table Association query

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'

  We will synchronize the information to SQLServer surface


Two 、Ambari Component information synchronization

take MySQL in Ambari in 4 The information of the tables is related and the query information is synchronized to SqlServer, Task scheduling frequency 5 One minute , If you find a problem, you will give an alarm

2.1. To write table.ini Database configuration file for

xxx It's different customers, different databases You can read the configuration of multiple databases and write them to different SQLServer database

​[kangll@client ~]$ cat /hadoop/datadir/script/hadoop/table.ini
[xxx_CONNECT]
url=xxx
port=1433
username=PCS.Support
password=321@win#
dbname=HDP_TEST
customer=xxx_

2.2.Shell Realize the synchronization of table information

​
​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 monitor 
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

# Query component status 
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]);"

# perform SQL  Run successful and failed tasks   write file 
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

 

版权声明
本文为[osc_ vo0yi5f8]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224095149194M.html

Scroll to Top