SELECT FROM LOGSTASH * 

Me Tal Levy Software Engineer @ Elastic 

DISCLAIMER This is not a talk about running SQL queries against Elasticsearch 

FROM DATABASE TO ELASTICSEARCH 

Elasticsearch Rivers ~~~~~ ~~~~~ * 

input { jdbc {…} } Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin Plugin PluginPluginPluginPlugin PluginPluginPluginPlugin 

MySQL Oracle Microsoft SQL Server Cassandra Elasticsearch Pagerduty Kafka Postgresql … … 

Options 

Options jdbc_driver_library 

Options jdbc_driver_library jdbc_driver_class 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string jdbc_user 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string jdbc_user statement 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string jdbc_user statement parameters 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string jdbc_user statement parameters jdbc_paging_enabled jdbc_page_size 

Options jdbc_driver_library jdbc_driver_class jdbc_connection_string jdbc_user statement parameters jdbc_paging_enabled jdbc_page_size jdbc_fetch_size 

input { jdbc { # Postgres jdbc connection string to our database, mydb jdbc_connection_string => "jdbc:postgresql://localhost:5432/mydb" # The user we wish to execute our statement as jdbc_user => "postgres" # The path to our downloaded jdbc driver jdbc_driver_library => "/path/to/postgresql-9.4-1201.jdbc41.jar" # The name of the driver class for Postgresql jdbc_driver_class => "org.postgresql.Driver" # our query statement => "SELECT * from contacts" } } output { stdout { codec => json_lines } } 

parameters 

jdbc { … parameters => { “location” => “outside” } statement => “select * from table where location=‘:location’ } 

special parameters 

special parameters O N LY O N E * maybe more to com * 

sql_last_start The last time a statement was executed 

in-sync one-time dump VS 

statement => “SELECT * FROM table” one-time dump 

schedule 

schedule “0 * * * *” “* 5 * 1-3 *” “0 6 * * * America/Chicago” => 

statement => ” SELECT * FROM table WHERE created_at >= :sql_last_start “ schedule => “*****” in-sync 

Elasticsearch Indexing Strategy 

BYO_ID 

BYOID input { jdbc { jdbc_connection_string => "jdbc:postgresql://localhost:5432/mydb" jdbc_user => "postgres" jdbc_validate_connection => true jdbc_driver_library => "/path/to/postgresql-9.4-1201.jdbc41.jar" jdbc_driver_class => "org.postgresql.Driver" statement => "SELECT * from contacts" } } output { elasticsearch { protocol => http index => "contacts" document_type => "contact" document_id => "%{uid}" host => "ES_NODE_HOST" } } 

curl ES_NODE_HOST:9200/contacts/contact/5?pretty { "_index" : "contacts", "_type" : "contact", "_id" : "5", "_version" : 1, "found" : true, "_source":{ "uid": 5, "email": "[email protected]", "first_name": "Sam", "last_name": null, "@version": "1", "@timestamp": "2015-07-29T22:12:20.146Z" } } BYOID 

UPDATE contacts SET last_name = 'Smith' WHERE email = '[email protected]'; BYOID 

curl ES_NODE_HOST:9200/contacts/contact/5?pretty { "_index" : "contacts", "_type" : "contact", "_id" : "5", "_version" : 2, "found" : true, "_source":{ "uid": 5, "email": "[email protected]", "first_name": "Sam", "last_name": "Smith", "@version": "1", "@timestamp": "2015-07-29T22:12:20.146Z" } } BYOID 

No content

Artists Albums Tracks Countries 

input { jdbc { jdbc_driver_library => "/path/to/driver.jar" jdbc_driver_class => "org.postgresql.Driver" jdbc_url => "jdbc://postgresql" jdbc_user => "musicbrainz" statement_filepath => "query.sql" } } output { elasticsearch { protocol => http } } SELECT release_group.gid AS album_i release_group.type AS album_ release_group_primary_type.n release.name AS release_name artist.name AS artist_name, artist.gid AS artist_gid, artist_credit.id AS artist_c artist.type AS artist_type_i artist_type.name AS artist_t artist.begin_date_year artis area.name AS artist_country_ release_country.date_year AS release_country.date_month A release_country.date_day AS FROM musicbrainz.artist INNER JOIN musicbrainz.artist_cr ON artist_credit_name.artist INNER JOIN musicbrainz.artist_cr ON artist_credit.id = artist INNER JOIN musicbrainz.release_g ON release_group.artist_cred INNER JOIN musicbrainz.release 

No content

No content

No content

¿Questions? 

http://discuss.elastic.co