[2021.07.22] 인턴 +143 How to create Multiple tables conf file in logstash? (+xpack elasticsearch include id & password )

[2021.07.22] 인턴 +143  How to create Multiple tables conf file in logstash? (+xpack elasticsearch include id & password )

 

 

해당 게시글은, 다중 테이블을 넣기 위한 conf 파일을 파이썬 코드로 짠 것이다.

+ Xpack을 설정하면, jdbc -> elasticsearch로 보낼 때, id & password 가 필요하다.

* 해당 게시글은 elasticsearch id & password가 포함되어 있음.

 

 

 

<소스 코드>

db_tables =  [
"Classification_diseases",
"Highblood_pressure     ",
"aged_disease           ",
"blood_pressure_sugar   ",
"blood_test             ",
"chronic_disease        ",
"diabetes               ",
"dyslipidemia           ",
"eyesight               ",
"waist                  "  
]


# db table -> for문을 통해, 양옆 공백 제거
arr = []
for i in db_tables:
    arr.append(i.strip())

#print(arr)


print("input {")
# jdbc -> plugin db table 복사 완료.
for i in range(0,len(arr)):
    print("    jdbc {")
    #print("        clean_run => true")
    print("        jdbc_driver_library => \"/usr/share/java/mysql-connector-java-8.0.25.jar\"")
    print("        jdbc_driver_class => \"com.mysql.jdbc.Driver\"")
    print("        jdbc_connection_string => \"jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root\"")
    print("        jdbc_user => \"root\"")
    print("        jdbc_password => \"root\"")
    print("        schedule => \"* * * * *\"")
    print(f"        statement => \"select * from {arr[i]}\"")
    print(f"        type => \"{arr[i]}\"")
    print(f"        tracking_column => \"table_{i+1}\"")  # 이 컬럼은 use_column_Value랑 한쌍이며, 해당 테이블의 필드를 찾는 것인데, 일일이 할 수 없으므로 대충 지어줌
    print("        use_column_value => true")
    print("        }")
    print("\n")
print("}")



print("output {")
# for 문 if 조건
for i in range(0,len(arr)):
    print(f"    if[type] == \"{arr[i]}\""  "  {")
    print("        elasticsearch {")
    print("            hosts => \"localhost:9200\"")
    print(f"            index => \"{arr[i]}\"")
    print("            user => \"admin\"")
    print("            password => \"admin12\"")
    print("            }")
    print("    }")

print("    stdout {")
print("        codec => rubydebug")
print("    }")
print("}")

<출력 결과>

input {
    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from Classification_diseases"
        type => "Classification_diseases"
        tracking_column => "table_1"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from Highblood_pressure"
        type => "Highblood_pressure"
        tracking_column => "table_2"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from aged_disease"
        type => "aged_disease"
        tracking_column => "table_3"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from blood_pressure_sugar"
        type => "blood_pressure_sugar"
        tracking_column => "table_4"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from blood_test"
        type => "blood_test"
        tracking_column => "table_5"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from chronic_disease"
        type => "chronic_disease"
        tracking_column => "table_6"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from diabetes"
        type => "diabetes"
        tracking_column => "table_7"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from dyslipidemia"
        type => "dyslipidemia"
        tracking_column => "table_8"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from eyesight"
        type => "eyesight"
        tracking_column => "table_9"
        use_column_value => true
        }


    jdbc {
        jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.25.jar"
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        jdbc_connection_string => "jdbc:mysql://localhost:3306/DATA?useSSL=false&user=root&password=root"
        jdbc_user => "root"
        jdbc_password => "root"
        schedule => "* * * * *"
        statement => "select * from waist"
        type => "waist"
        tracking_column => "table_10"
        use_column_value => true
        }


}
output {
    if[type] == "Classification_diseases"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "Classification_diseases"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "Highblood_pressure"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "Highblood_pressure"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "aged_disease"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "aged_disease"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "blood_pressure_sugar"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "blood_pressure_sugar"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "blood_test"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "blood_test"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "chronic_disease"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "chronic_disease"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "diabetes"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "diabetes"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "dyslipidemia"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "dyslipidemia"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "eyesight"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "eyesight"
            user => "admin"
            password => "admin12"
            }
    }
    if[type] == "waist"  {
        elasticsearch {
            hosts => "localhost:9200"
            index => "waist"
            user => "admin"
            password => "admin12"
            }
    }
    stdout {
        codec => rubydebug
    }
}

-> 해당 출력결과를 복사 하여, conf파일을 만들어서 사용하면 된다.

 

* 여기에는 document_id 가 없으므로 (딱 한번만 데이터를 저장하지 않음)

-> 알아서 수정하자.

 

210723_다중멀티테이블_xpack(id&amp;password).ipynb

0.01MB