JDBC Job
Overview
JDBC (Java Database Connectivity Technology) is an API for Java which provides methods for querying and updating a database.
Axibase Collector can be configured to query a remote database to either obtain current statistics for the purpose of accumulating their history in Axibase Time Series Database or to offload incremental statistics into ATSD for long-term retention and analytics.
Supported Databases
- Oracle Database
- Microsoft SQL Server
- IBM DB2
- MySQL
- PostgreSQL
- Sybase
- Apache Derby
- SAP HANA
- OSIsoft PI
- HP Vertica
- ATSD
- CUSTOM
Examples
Job Settings
In contrast to the Generic Job, the JDBC job has an additional field.
Data Source list allows you to select a database that to query. The list contains all databases connected as data sources.
Job Configuration
To add a new query, on the JDBC Job page click Create Query.
Use the table below to fill in the fields and configure the query.
| Field | Description |
|---|---|
| Query | SELECT query containing the entity name, time, and a list of metric columns. |
| Command Type | Type of command sent to ATSD. Possible values: SERIES, PROPERTY, MESSAGE, METRIC, ENTITY. |
| Default Entity | Default entity assigned to the collected commands. |
| Entity Column | Retrieve entity value from the specified entity column. |
| Entity Replacement Expression | FreeMarker expression to extract entity name from an input string or to retrieve the entity name from a lookup table using LOOKUP. |
| Split Condition | One or multiple WHERE conditions to copy the base query into multiple queries returning smaller resultsets. |
| Tag Columns | Columns that contain series tags. |
| Predefined Tags | Assign predefined tags to all series. |
| Time Column | Column containing the timestamp. |
| Last Time Column | Column containing last update time. |
| Time Type | Timestamp type. Possible values: TIMESTAMP, TIVOLI, TEXT, UNIX |
| Time Format | Format of the timestamp. |
| Time Round | Time is rounded before storing the series in ATSD. Possible values: MILLISECOND, SECONDS, MINUTE, HOUR, DAY |
| Time Zone | Time zone in which the data is collected and stored. |
| Check Last Time | Ignore metrics that have time set to less than the previous entry. |
| Query With Time | When executing the job, the server is set to the maximum time of the previous data. |
| Collection Start Time | Calendar expression defining the beginning of the data collection interval, for example, previous_week. |
| Max Rows | Maximum number of rows collected with each batch, -1 is unlimited. |
| Query Timeout Seconds | Number of seconds after which the query is interrupted, -1 is unlimited. |
Placeholders
Default Entity and Predefined Tags fields support the following placeholders:
| Placeholder | Description |
|---|---|
${DB_CONFIG_NAME} | Data source Name. |
${DB_SERVER} | Data source Server. |
${DB_PORT} | Data source Port. |
${DB_NAME} | Data source Database Name. |
Type-Specific options
SERIES
| Field | Description |
|---|---|
| Metric Columns | Columns containing metric values |
| Ignored Metric Columns | Metric columns that you want to skip. |
| Default Metric Name | Assign a default metric to the collected series. All series are stored in ATSD with this metric. |
| Metric Name Column | Column containing metric (series) names. |
| Metric Value Column | Column containing metric (series) values. Collect multiple value columns for the same metric if needed, which is common in aggregation queries. For example: cnt, avg, max, min, sum. Example query: SELECT st.SAMPLE_TIME, e.ENTITY_NAME, sd.NAME AS METRIC, COUNT(hs.STAT_VAL) AS "cnt", AVG(hs.STAT_VAL) AS "avg", SUM(hs.STAT_VAL) AS "sum", MAX(hs.STAT_VAL) AS "max", MIN(hs.STAT_VAL) AS "min", FROM VPX_HIST_STAT1 ... |
| Metric Prefix | Metric prefix can be assigned to easily sort and differentiate metrics in ATSD. |
| Ignore Number Parse Errors | Ignore cells that fail to parse from string into number without raising error. Default: false. |
PROPERTY
| Field | Description |
|---|---|
| Property Type | Default property type for all property commands, or column containing property type |
| Property Key Columns | Columns containing strings that are treated as property keys |
| Property Value Columns | If specified, only listed columns are considered a source of property values. By default all unused columns are treated as property values |
| Ignored Property Value Columns | Columns to be excluded from list of property values |
MESSAGE
| Field | Description |
|---|---|
| Type | Type applied to all messages, or column containing message type |
| Source | Source applied to all messages, or column containing message source |
| Severity | String representation of message severity. Either a selected value or a column containing severity |
METRIC
| Field | Description |
|---|---|
| Metric Name Column | Column containing metric names. |
| Label Column | Label Column: Column containing metric or entity label. Default Label: Label applied to all generated metrics or entities |
| Description Column | Column containing metric or entity description. |
| Data Type | Default metric data type, or column containing metric datatype. One of: short, integer, long, float, double, decimal |
| Interpolation Mode | Default interpolation mode setting or column containing interpolation mode setting. must contain a string value, case-insensitive. Possible values: LINEAR, PREVIOUS, or NONE |
| Filter Column | Column containing metric filter expression. |
| Versioning | Default versioning behavior if versioning column is not specified, or column specifying if metric is versioned. |
| Min and Max Value Columns | Minimum and Maximum Values |
| Units | Measurement Units |
| Metric Time Zone | Default Time Zone or column containing the Time Zone Identifier |
ENTITY
| Field | Description |
|---|---|
| Label Column | Label Column: Column containing metric or entity label. Default Label: Label applied to all generated metrics or entities |
| Interpolation Mode | Default interpolation mode setting or column containing interpolation mode setting. must contain a string value, case-insensitive. Possible values: LINEAR, PREVIOUS, or NONE |
| Entity Time Zone | Default Time Zone or column containing the Time Zone Identifier |
Configuration Example
The image below shows an example JDBC query configuration.
