logstash-output-jdbc 如何动态插入表，将表名设置为变量

动态插入到表中

表名filelog[变量]值，可以通过设置变量，将读到的记录插入动态表中

input {
    file {
        path => "D:/es/file_log/*.log"
        tags => ["filelogbranch"]
        start_position => "beginning"
        codec => "json"
    }
}

filter {
   ruby { 
    code => "event.set('timestamp', event.get('@timestamp').time.localtime + 8*60*60)" 
   }
   ruby {
    code => "event.set('@timestamp',event.get('timestamp'))"
   }
   mutate {
    remove_field => ["@version","message","host","path","timestamp"]
   }
}

output {
    jdbc {
            driver_jar_path => 'C:\logstash-8.1.2\sqljdbc.jar'
            connection_string => "jdbc:jtds://server:1433;databaseName=CobraDGServer;user=sa;password=123456"
            statement => [ "INSERT INTO FileLog2%{+YYYYMMdd}(id,userId) VALUES(?, ?)", "id", "userId"]
        }
        
}

Configuration options

Option	Type	Description	Required?	Default
driver_class	String	Specify a driver class if autoloading fails	No
driver_auto_commit	Boolean	If the driver does not support auto commit, you should set this to false	No	True
driver_jar_path	String	File path to jar file containing your JDBC driver. This is optional, and all JDBC jars may be placed in $LOGSTASH_HOME/vendor/jar/jdbc instead.	No
connection_string	String	JDBC connection URL	Yes
connection_test	Boolean	Run a JDBC connection test. Some drivers do not function correctly, and you may need to disable the connection test to supress an error. Cockroach with the postgres JDBC driver is such an example.	No	Yes
connection_test_query	String	Connection test and init query string, required for some JDBC drivers that don't support isValid(). Typically you'd set to this "SELECT 1"	No
username	String	JDBC username - this is optional as it may be included in the connection string, for many drivers	No
password	String	JDBC password - this is optional as it may be included in the connection string, for many drivers	No
statement	Array	An array of strings representing the SQL statement to run. Index 0 is the SQL statement that is prepared, all other array entries are passed in as parameters (in order). A parameter may either be a property of the event (i.e. "@timestamp", or "host") or a formatted string (i.e. "%{host} - %{message}" or "%{message}"). If a key is passed then it will be automatically converted as required for insertion into SQL. If it's a formatted string then it will be passed in verbatim.	Yes
unsafe_statement	Boolean	If yes, the statement is evaluated for event fields - this allows you to use dynamic table names, etc. This is highly dangerous and you should not use this unless you are 100% sure that the field(s) you are passing in are 100% safe. Failure to do so will result in possible SQL injections. Example statement: [ "insert into %{table_name_field} (column) values(?)", "fieldname" ]	No	False
max_pool_size	Number	Maximum number of connections to open to the SQL server at any 1 time	No	5
connection_timeout	Number	Number of milliseconds before a SQL connection is closed	No	10000
flush_size	Number	Maximum number of entries to buffer before sending to SQL - if this is reached before idle_flush_time	No	1000
max_flush_exceptions	Number	Number of sequential flushes which cause an exception, before the set of events are discarded. Set to a value less than 1 if you never want it to stop. This should be carefully configured with respect to retry_initial_interval and retry_max_interval, if your SQL server is not highly available	No	10
retry_initial_interval	Number	Number of seconds before the initial retry in the event of a failure. On each failure it will be doubled until it reaches retry_max_interval	No	2
retry_max_interval	Number	Maximum number of seconds between each retry	No	128
retry_sql_states	Array of strings	An array of custom SQL state codes you wish to retry until max_flush_exceptions. Useful if you're using a JDBC driver which returns retry-able, but non-standard SQL state codes in it's exceptions.	No	[]
event_as_json_keyword	String	The magic key word that the plugin looks for to convert the entire event into a JSON object. As Logstash does not support this out of the box with it's sprintf implementation, you can use whatever this field is set to in the statement parameters	No	@event
enable_event_as_json_keyword	Boolean	Enables the magic keyword set in the configuration option event_as_json_keyword. Without this enabled the plugin will not convert the event_as_json_keyword into JSON encoding of the entire event.	No	False

Example configurations