Logstash \uc640 jdbc \ub97c \uc774\uc6a9\ud574 Elasticsearch RDBMS \uc0ac\uc774\uc758 \ub370\uc774\ud0c0\ub97c \ub3d9\uae30\ud654 \ud569\ub2c8\ub2e4.<\/p>\n
\uc6b4\uc601\ud658\uacbd\uc5d0\uc11c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub610, JDK, ES, Logstash, MySQL \uc758 \uc124\uce58\ub294 \uc0dd\ub7b5\ud569\ub2c8\ub2e4.<\/p>\n \uc778\ub371\uc2a4\ub294 \uc0dd\uc131\ud574 \uc8fc\uc9c0 \uc54a\uc544\ub3c4, Logstash \uac00 \uc790\ub3d9\uc0dd\uc131\ud558\uc9c0\ub9cc \uc218\uae30\ub85c \uc0dd\uc131\ud574\uc8fc\ub294 \uac83\uc774 \uc548\uc804\ud569\ub2c8\ub2e4.<\/p>\n \ub85c\uadf8\uc2a4\ud0dc\uc2dc \ub370\ubaac\uc774 \uc2a4\ucf00\uc904\uc5d0 \ub9de\ucdb0 jdbc \ub97c \ud638\ucd9c\ud569\ub2c8\ub2e4.<\/p>\n \ub3d9\uae30\ud654 \ud574\uc57c\ud560 \uc778\ub371\uc2a4\uac00 \uc5ec\ub7ec \uac1c\uc77c \uacbd\uc6b0 JDBC \ub97c \uc774\uc6a9\ud55c Elasticsearch RDBMS \uc5f0\ub3d9 Logstash \uc640 jdbc \ub97c \uc774\uc6a9\ud574 Elasticsearch RDBMS \uc0ac\uc774\uc758 \ub370\uc774\ud0c0\ub97c \ub3d9\uae30\ud654 \ud569\ub2c8\ub2e4. \uc6b4\uc601\ud658\uacbd\uc5d0\uc11c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 Bulk Insert \ub97c \ubcd1\ud589\ud574\uc11c \uc0ac\uc6a9\ud574\uc57c \ud558\uc9c0\ub9cc \ubb38\uc11c\uc758 \uc591\uc744 \uc904\uc774\uae30 \uc704\ud574 \uc0dd\ub7b5\ud569\ub2c8\ub2e4. \ub610, JDK, ES, Logstash, MySQL \uc758 \uc124\uce58\ub294 \uc0dd\ub7b5\ud569\ub2c8\ub2e4. \ucc38\uc870 \uc0ac\uc774\ud2b8 https:\/\/peung.tistory.com\/m\/13 https:\/\/www.elastic.co\/kr\/blog\/how-to-keep-elasticsearch-synchronized-with-a-relational-database-using-logstash MySQL \uc124\uc815 CREATE DATABASE db_test DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; USE\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-935","post","type-post","status-publish","format-standard","hentry","category-elasticsearch"],"_links":{"self":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/935","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=935"}],"version-history":[{"count":22,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/935\/revisions"}],"predecessor-version":[{"id":1062,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/935\/revisions\/1062"}],"wp:attachment":[{"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skyer9.pe.kr\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Bulk Insert<\/code> \ub97c \ubcd1\ud589\ud574\uc11c \uc0ac\uc6a9\ud574\uc57c \ud558\uc9c0\ub9cc \ubb38\uc11c\uc758 \uc591\uc744 \uc904\uc774\uae30 \uc704\ud574 \uc0dd\ub7b5\ud569\ub2c8\ub2e4.<\/p>\n\ucc38\uc870 \uc0ac\uc774\ud2b8<\/h2>\n
\n
MySQL \uc124\uc815<\/h2>\n
CREATE DATABASE db_test DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;\n\nUSE db_test;\n\nDROP TABLE IF EXISTS items;\n\nCREATE TABLE items (\n itemid BIGINT(20) UNSIGNED NOT NULL,\n PRIMARY KEY (itemid),\n UNIQUE KEY unique_id (itemid),\n itemname VARCHAR(512) NOT NULL,\n category VARCHAR(128) NULL,\n price DECIMAL(13, 4) NULL,\n lastupdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n regdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP\n);\n\nINSERT INTO items (itemid, itemname) VALUES (1, '\ube14\ub799 \uc544\uc774\ud3f0 \ucf00\uc774\uc2a4');<\/code><\/pre>\nitemid<\/code> \uac00 PK \ub85c ES \uc758 document id<\/code> \uc640 \ub9e4\uce6d\ub429\ub2c8\ub2e4. lastupdate \uac00 \ubcc0\uacbd\ub41c \ub0b4\uc5ed\ub9cc \ub3d9\uae30\ud654\ub429\ub2c8\ub2e4.<\/p>\nElasticsearch \uc124\uc815<\/h2>\n
curl -XDELETE http:\/\/localhost:9200\/items?pretty\n\ncurl -XPUT http:\/\/localhost:9200\/items?pretty -H 'Content-Type: application\/json' -d '{\n "settings" : {\n "number_of_shards": "5",\n "number_of_replicas": "1",\n "index":{\n "analysis":{\n "analyzer":{\n "korean":{\n "type":"custom",\n "tokenizer":"seunjeon_default_tokenizer"\n }\n },\n "tokenizer": {\n "seunjeon_default_tokenizer": {\n "index_eojeol": "true",\n "user_words": [\n "\ud150\ubc14\uc774\ud150", "\uc5e0\ub514", "\uc5d0\uc5b4\ud31f", "\ub7ec\ube0c\ud50c\ub77c\ubcf4", "\uc530\uc2a4\ud2f0\ucee4",\n "1+1"\n ],\n "index_poses": [\n "UNK", "EP", "I", "J", "M",\n "N", "SL", "SH", "SN", "VCP",\n "XP", "XS", "XR"\n ],\n "decompound": "true",\n "type": "seunjeon_tokenizer"\n }\n }\n }\n }\n },\n "mappings" : {\n "_doc" : {\n "properties" : {\n "@timestamp" : {\n "type" : "date"\n },\n "itemid" : {\n "type" : "integer"\n },\n "itemname" : {\n "type" : "text",\n "analyzer": "korean"\n },\n "category" : {\n "type" : "text",\n "analyzer": "korean"\n },\n "price" : {\n "type": "scaled_float",\n "scaling_factor": 10000\n },\n "lastupdate" : {\n "type" : "date"\n },\n "regdate" : {\n "type" : "date"\n }\n }\n }\n }\n}'<\/code><\/pre>\nLogstash \uc124\uc815<\/h2>\n
sudo mkdir \/last_run\nsudo chown logstash:logstash \/last_run\nsudo vi \/etc\/logstash\/conf.d\/logstash-jdbc-es.conf<\/code><\/pre>\nlast_run_metadata_path<\/code> \ub97c \uc9c0\uc815\ud574 \uc2a4\ucf00\uc904\ub9c8\ub2e4 \uac01\uac01 sql_last_value<\/code> \ub97c \uc800\uc7a5\ud560 \uc218 \uc788\uac8c \ud574\uc8fc\uc5b4\uc57c \ud569\ub2c8\ub2e4.<\/p>\ninput {\n jdbc {\n jdbc_driver_library => "\/home\/ubuntu\/mysql-connector-java-8.0.20.jar"\n jdbc_driver_class => "com.mysql.jdbc.Driver"\n jdbc_connection_string => "jdbc:mysql:\/\/localhost:3306\/db_test?zeroDateTimeBehavior=convertToNull&serverTimezone=Asia\/Seoul"\n jdbc_user => "testuser"\n jdbc_password => "test1234"\n jdbc_paging_enabled => true\n jdbc_page_size => 100000\n tracking_column => "unix_ts_in_secs"\n use_column_value => true\n tracking_column_type => "numeric"\n schedule => "*\/5 * * * * *"\n statement => "SELECT *, UNIX_TIMESTAMP(lastupdate) AS unix_ts_in_secs FROM items WHERE (UNIX_TIMESTAMP(lastupdate) > :sql_last_value AND lastupdate < NOW()) ORDER BY lastupdate ASC"\n last_run_metadata_path => "\/last_run\/logstash-jdbc-es"\n }\n}\n\nfilter {\n mutate {\n copy => { "itemid" => "[@metadata][_id]"}\n remove_field => ["@version", "unix_ts_in_secs"]\n }\n}\n\noutput {\n # stdout { codec => "rubydebug"}\n elasticsearch {\n hosts => ["localhost:9200"]\n index => "items"\n document_id => "%{[@metadata][_id]}"\n document_type => "_doc"\n }\n}<\/code><\/pre>\n\ud14c\uc2a4\ud2b8\ud558\uae30<\/h2>\n
use db_test;\nupdate items set itemname = '\ube14\ub799 \uc544\uc774\ud3f0 \ucf00\uc774\uc2a41' where itemid = 1;<\/code><\/pre>\ncurl -XGET http:\/\/localhost:9200\/items\/_search?pretty\ncurl -XGET http:\/\/localhost:9200\/items\/doc\/1?pretty\n\ncurl -XGET http:\/\/localhost:9200\/items\/_search?pretty -H 'Content-Type: application\/json' -d '{\n "query": {\n "match": {\n "itemname": "\uc544\uc774\ud3f0"\n }\n }\n}'<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"