路径:
/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/logstash-integration-jdbc-5.0.5/docs/filter-jdbc_static.asciidoc
:plugin: jdbc_static
:type: filter
///////////////////////////////////////////
START - GENERATED VARIABLES, DO NOT EDIT!
开始 - 生成的变量,不要编辑修改
///////////////////////////////////////////
:version: %VERSION%
:release_date: %RELEASE_DATE%
:changelog_url: %CHANGELOG_URL%
:include_path: ../../../../logstash/docs/include
///////////////////////////////////////////
END - GENERATED VARIABLES, DO NOT EDIT!
///////////////////////////////////////////
[id="plugins-{type}s-{plugin}"]
=== Jdbc_static filter plugin
include::{include_path}/plugin_header.asciidoc[]
==== Description描述
This filter enriches events with data pre-loaded from a remote database.
这个过滤器用于从远程数据库预加载数据,以充实事件内容
This filter is best suited for enriching events with reference data that is
static or does not change very often, such as environments, users, and products.
最适合静态或不是经常改变的数据
This filter works by fetching data from a remote database, caching it in a
local, in-memory https://db.apache.org/derby/manuals/#docs_10.14[Apache Derby]
database, and using lookups to enrich events with data cached in the local
database. You can set up the filter to load the remote data once (for static
data), or you can schedule remote loading to run periodically (for data that
needs to be refreshed).
filter从远程数据库提取数据,缓存在本地内存中的Apache Derby数据库中。可以设置filter一次性加载静态数据,
或周期性远程加载需要刷新的数据
To define the filter, you specify three main sections: local_db_objects, loaders,
and lookups.
为了定义这个filter,需要说明三个主要部分:local_db_objects,loaders,和lookups
*local_db_objects*:: 本地数据对象
Define the columns, types, and indexes used to build the local database
structure. The column names and types should match the external database.
Define as many of these objects as needed to build the local database
structure.
创建本地数据库结构,定义列,类型和索引。列名和类型必须与外部数据库相匹配(一致)。可以按需要的数量定义本地数据库结构
*loaders*::加载器
Query the external database to fetch the dataset that will be cached locally.
Define as many loaders as needed to fetch the remote data. Each
loader should fill a table defined by `local_db_objects`. Make sure
the column names and datatypes in the loader SQL statement match the
columns defined under `local_db_objects`. Each loader has an independent remote
database connection.
查询外部数据库提取需要缓存在本地的数据集。按需要的数量定义加载器以提取远程数据。
每一个加载器应该填写一张由‘local_db_objects’定义的表。要保证loader里的SQL语句的列名和数据类型与'local_db_objects'
里定义匹配。每个loader有一个独立的远程数据库连接。
*lookups*::查询
Perform lookup queries on the local database to enrich the events.
Define as many lookups as needed to enrich the event from all
lookup tables in one pass. Ideally the SQL statement should only
return one row. Any rows are converted to Hash objects and are
stored in a target field that is an Array.
在本地数据库执行查询以充实事件。一次性按需要的数量定义查询表。理想情况下SQL语句应该仅返回一行。
所有返回行都别转换为Hash对象,并内存储在一个target field里,类型是列表(Array)
+
The following example config fetches data from a remote database, caches it in a
local database, and uses lookups to enrich events with data cached in the local
database.
下面是个列子
+
["source","json",subs="callouts"]
-----
filter {
jdbc_static {
loaders => [ 注释说明
{
id => "remote-servers"
query => "select ip, descr from ref.local_ips order by ip"
local_table => "servers"
},
{
id => "remote-users"
query => "select firstname, lastname, userid from ref.local_users order by userid"
local_table => "users"
}
]
local_db_objects => [ 注释说明
{
name => "servers"
index_columns => ["ip"]
columns => [
["ip", "varchar(15)"],
["descr", "varchar(255)"]
]
},
{
name => "users"
index_columns => ["userid"]
columns => [
["firstname", "varchar(255)"],
["lastname", "varchar(255)"],
["userid", "int"]
]
}
]
local_lookups => [ 注释说明
{
id => "local-servers"
query => "SELECT descr as description FROM servers WHERE ip = :ip"
parameters => {ip => "[from_ip]"}
target => "server"
},
{
id => "local-users"
query => "SELECT firstname, lastname FROM users WHERE userid = ? AND country = ?"
prepared_parameters => ["[loggedin_userid]", "[user_nation]"] 注释说明
target => "user" 注释说明
}
]
# using add_field here to add & rename values to the event root
add_field => { server_name => "%{[server][0][description]}" } 注释说明
add_field => { user_firstname => "%{[user][0][firstname]}" }
add_field => { user_lastname => "%{[user][0][lastname]}" }
remove_field => ["server", "user"]
staging_directory => "/tmp/logstash/jdbc_static/import_data"
loader_schedule => "* */2 * * *" # run loaders every 2 hours
jdbc_user => "logstash"
jdbc_password => "example"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_driver_library => "/tmp/logstash/vendor/postgresql-42.1.4.jar"
jdbc_connection_string => "jdbc:postgresql://remotedb:5432/ls_test_2"
}
}
-----
注释说明
Queries an external database to fetch the dataset that will be cached
locally.
Defines the columns, types, and indexes used to build the local database
structure. The column names and types should match the external database.
The order of table definitions is significant and should match that of the loader queries.
See >.
Performs lookup queries on the local database to enrich the events.
Local lookup queries can also use prepared statements where the parameters
follow the positional ordering.
Specifies the event field that will store the looked-up data. If the lookup
returns multiple columns, the data is stored as a JSON object within the field.
Takes data from the JSON object and stores it in top-level event fields for
easier analysis in Kibana.
Here's a full example:
一个完整的例子:
[source,json]
-----
input {
generator {
lines => [
'{"from_ip": "10.2.3.20", "app": "foobar", "amount": 32.95}',
'{"from_ip": "10.2.3.30", "app": "barfoo", "amount": 82.95}',
'{"from_ip": "10.2.3.40", "app": "bazfoo", "amount": 22.95}'
]
count => 200
}
}
filter {
json {
source => "message"
}
jdbc_static {
loaders => [
{
id => "servers"
query => "select ip, descr from ref.local_ips order by ip"
local_table => "servers"
}
]
local_db_objects => [
{
name => "servers"
index_columns => ["ip"]
columns => [
["ip", "varchar(15)"],
["descr", "varchar(255)"]
]
}
]
local_lookups => [
{
query => "select descr as description from servers WHERE ip = :ip"
parameters => {ip => "[from_ip]"}
target => "server"
}
]
staging_directory => "/tmp/logstash/jdbc_static/import_data"
loader_schedule => "*/30 * * * *"
jdbc_user => "logstash"
jdbc_password => "logstash??"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_driver_library => "/Users/guy/tmp/logstash-6.0.0/vendor/postgresql-42.1.4.jar"
jdbc_connection_string => "jdbc:postgresql://localhost:5432/ls_test_2"
}
}
output {
stdout {
codec => rubydebug {metadata => true}
}
}
-----
Assuming the loader fetches the following data from a Postgres database:
[source,shell]
select * from ref.local_ips order by ip;
ip | descr
-----------+-----------------------
10.2.3.10 | Authentication Server
10.2.3.20 | Payments Server
10.2.3.30 | Events Server
10.2.3.40 | Payroll Server
10.2.3.50 | Uploads Server
The events are enriched with a description of the server based on the value of
the IP:
[source,shell]
{
"app" => "bazfoo",
"sequence" => 0,
"server" => [
[0] {
"description" => "Payroll Server"
}
],
"amount" => 22.95,
"@timestamp" => 2017-11-30T18:08:15.694Z,
"@version" => "1",
"host" => "Elastics-MacBook-Pro.local",
"message" => "{"from_ip": "10.2.3.40", "app": "bazfoo", "amount": 22.95}",
"from_ip" => "10.2.3.40"
}
==== Using this plugin with multiple pipelines
[IMPORTANT]
===============================
Logstash uses a single, in-memory Apache Derby instance as the lookup database
engine for the entire JVM. Because each plugin instance uses a unique database
inside the shared Derby engine, there should be no conflicts with plugins
attempting to create and populate the same tables. This is true regardless of
whether the plugins are defined in a single pipeline, or multiple pipelines.
However, after setting up the filter, you should watch the lookup results and
view the logs to verify correct operation.
===============================
[id="plugins-{type}s-{plugin}-object_order"]
==== Loader column and local_db_object order dependency
[IMPORTANT]
===============================
For loader performance reasons, the loading mechanism uses a CSV style file with an inbuilt
Derby file import procedure to add the remote data to the local db. The retrieved columns
are written to the CSV file as is and the file import procedure expects a 1 to 1 correspondence
to the order of the columns specified in the local_db_object settings. Please ensure that this
order is in place.
为了加载器的效率原因,加载机制使用一个CSV类型的文件和一个内置的Derby文件导入程序去加载远程数据到本地数据库。
列的顺序必须一致。
===============================
[id="plugins-{type}s-{plugin}-options"]
==== Jdbc_static Filter Configuration Options
This plugin supports the following configuration options plus the > described later.
[cols="> |>|Yes
| > |>|Yes
| > |a valid filesystem path|No
| > |>|No
| > |>|No
| > |>|No
| > |>|No
| > |>|No
| >|>|No
| >|>|No
| >|>|No
| >|>|No
|=======================================================================
Also see > for a list of options supported by all
filter plugins.
[id="plugins-{type}s-{plugin}-jdbc_connection_string"]
===== `jdbc_connection_string`
* This is a required setting.
* Value type is >
* There is no default value for this setting.
JDBC connection string.
[id="plugins-{type}s-{plugin}-jdbc_driver_class"]
===== `jdbc_driver_class`
* This is a required setting.
* Value type is >
* There is no default value for this setting.
JDBC driver class to load, for example, "org.apache.derby.jdbc.ClientDriver".
NOTE: According to https://github.com/logstash-plugins/logstash-input-jdbc/issues/43[Issue 43],
if you are using the Oracle JDBC driver (ojdbc6.jar), the correct
`jdbc_driver_class` is `"Java::oracle.jdbc.driver.OracleDriver"`.
[id="plugins-{type}s-{plugin}-jdbc_driver_library"]
===== `jdbc_driver_library`
* Value type is >
* There is no default value for this setting.
JDBC driver library path to third-party driver library. Use comma separated paths
in one string if you need multiple libraries.
If the driver class is not provided, the plugin looks for it in the Logstash
Java classpath.
[id="plugins-{type}s-{plugin}-jdbc_password"]
===== `jdbc_password`
* Value type is >
* There is no default value for this setting.
JDBC password.
[id="plugins-{type}s-{plugin}-jdbc_user"]
===== `jdbc_user`
* This is a required setting.
* Value type is >
* There is no default value for this setting.
JDBC user.
[id="plugins-{type}s-{plugin}-tag_on_default_use"]
===== `tag_on_default_use`
* Value type is >
* Default value is `["_jdbcstaticdefaultsused"]`
Append values to the `tags` field if no record was found and default values were used.
[id="plugins-{type}s-{plugin}-tag_on_failure"]
===== `tag_on_failure`
* Value type is >
* Default value is `["_jdbcstaticfailure"]`
Append values to the `tags` field if a SQL error occurred.
[id="plugins-{type}s-{plugin}-staging_directory"]
===== `staging_directory`
* Value type is >
* Default value is derived from the Ruby temp directory + plugin_name + "import_data"
* e.g. `"/tmp/logstash/jdbc_static/import_data"`
The directory used stage the data for bulk loading, there should be sufficient
disk space to handle the data you wish to use to enrich events.
Previous versions of this plugin did not handle loading datasets of more than
several thousand rows well due to an open bug in Apache Derby. This setting
introduces an alternative way of loading large recordsets. As each row is
received it is spooled to file and then that file is imported using a
system 'import table' system call.
Append values to the `tags` field if a SQL error occurred.
[id="plugins-{type}s-{plugin}-loader_schedule"]
===== `loader_schedule`
* Value type is >
* There is no default value for this setting.
You can schedule remote loading to run periodically according to a
specific schedule. This scheduling syntax is powered by
https://github.com/jmettraux/rufus-scheduler[rufus-scheduler]. The
syntax is cron-like with some extensions specific to Rufus
(for example, timezone support). For more about this syntax, see
https://github.com/jmettraux/rufus-scheduler#parsing-cronlines-and-time-strings[parsing cronlines and time strings].
Examples:
|==========================================================
| `*/30 * * * *` | will execute on the 0th and 30th minute of every hour every day.
| `* 5 * 1-3 *` | will execute every minute of 5am every day of January through March.
| `0 * * * *` | will execute on the 0th minute of every hour every day.
| `0 6 * * * America/Chicago` | will execute at 6:00am (UTC/GMT -5) every day.
|==========================================================
Debugging using the Logstash interactive shell:
[source,shell]
bin/logstash -i irb
irb(main):001:0> require 'rufus-scheduler'
=> true
irb(main):002:0> Rufus::Scheduler.parse('*/10 * * * *')
=> #<:scheduler::cronline:0x230f8709>
irb(main):003:0> exit
The object returned by the above call, an instance of `Rufus::Scheduler::CronLine` shows the seconds, minutes etc. of execution.
[id="plugins-{type}s-{plugin}-loaders"]
===== `loaders`
* Value type is >
* Default value is `[]`
The array should contain one or more Hashes. Each Hash is validated
according to the table below.
[cols=">
* Default value is `[]`
The array should contain one or more Hashes. Each Hash represents a table
schema for the local lookups database. Each Hash is validated
according to the table below.
[cols=">
* Default value is `[]`
The array should contain one or more Hashes. Each Hash represents a lookup
enrichment. Each Hash is validated according to the table below.
[cols=" { "p1" => "ref" }`. Quoting is
automatic - you do not need to put quotes in the statement.
Only use the field interpolation syntax on the RHS if you need to
add a prefix/suffix or join two event field values together to build
the substitution value. For example, imagine an IOT message that has
an id and a location, and you have a table of sensors that have a
column of `id-loc_id`. In this case your parameter hash would look
like this: `parameters => { "p1" => "%{[id]}-%{[loc_id]}" }`.
prepared_parameters::
An Array, where the position is related to the position of the `?` in
the query syntax. The values of array follow the same semantic of `parameters`.
If `prepared_parameters` is valorized the filter is forced to use JDBC's
prepared statement to query the local database.
Prepared statements provides two benefits: one on the performance side, because
avoid the DBMS to parse and compile the SQL expression for every call;
the other benefit is on the security side, using prepared statements
avoid SQL-injection attacks based on query string concatenation.
target::
An optional name for the field that will receive the looked-up data.
If you omit this setting then the `id` setting (or the default id) is
used. The looked-up data, an array of results converted to Hashes, is
never added to the root of the event. If you want to do this, you
should use the `add_field` setting. This means that
you are in full control of how the fields/values are put in the root
of the event, for example,
`add_field => { user_firstname => "%{[user][0][firstname]}" }` -
where `[user]` is the target field, `[0]` is the first result in the
array, and `[firstname]` is the key in the result hash.
default_hash::
An optional hash that will be put in the target field array when the
lookup returns no results. Use this setting if you need to ensure that later
references in other parts of the config actually refer to something.
tag_on_failure::
An optional string that overrides the plugin-level setting. This is
useful when defining multiple lookups.
tag_on_default_use::
An optional string that overrides the plugin-level setting. This is
useful when defining multiple lookups.
[id="plugins-{type}s-{plugin}-common-options"]
include::{include_path}/{type}.asciidoc[]
服务器租用托管,机房租用托管,主机租用托管,https://www.e1idc.com