[ https://issues.apache.org/jira/browse/KARAF-5976?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16674667#comment-16674667 ]
ASF GitHub Bot commented on KARAF-5976: --------------------------------------- jbonofre closed pull request #58: [KARAF-5976] Add TimescaleDB appender URL: https://github.com/apache/karaf-decanter/pull/58 This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/appender/pom.xml b/appender/pom.xml index 42cef43..6546526 100644 --- a/appender/pom.xml +++ b/appender/pom.xml @@ -52,6 +52,7 @@ <module>redis</module> <module>rest</module> <module>socket</module> + <module>timescaledb</module> </modules> </project> \ No newline at end of file diff --git a/appender/timescaledb/pom.xml b/appender/timescaledb/pom.xml new file mode 100644 index 0000000..7738326 --- /dev/null +++ b/appender/timescaledb/pom.xml @@ -0,0 +1,72 @@ +<?xml version="1.0" encoding="UTF-8"?> +<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> + + <!-- + + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. + --> + + <modelVersion>4.0.0</modelVersion> + + <parent> + <groupId>org.apache.karaf.decanter</groupId> + <artifactId>appender</artifactId> + <version>2.2.0-SNAPSHOT</version> + <relativePath>../pom.xml</relativePath> + </parent> + + <groupId>org.apache.karaf.decanter.appender</groupId> + <artifactId>org.apache.karaf.decanter.appender.timescaledb</artifactId> + <packaging>bundle</packaging> + <name>Apache Karaf :: Decanter :: Appender :: TimescaleDB</name> + + <dependencies> + <dependency> + <groupId>javax.json</groupId> + <artifactId>javax.json-api</artifactId> + </dependency> + <dependency> + <groupId>org.apache.karaf.decanter</groupId> + <artifactId>org.apache.karaf.decanter.api</artifactId> + </dependency> + </dependencies> + + <build> + <plugins> + <plugin> + <groupId>org.codehaus.mojo</groupId> + <artifactId>build-helper-maven-plugin</artifactId> + <executions> + <execution> + <phase>package</phase> + <goals> + <goal>attach-artifact</goal> + </goals> + <configuration> + <artifacts> + <artifact> + <file>src/main/cfg/org.apache.karaf.decanter.appender.timescaledb.cfg</file> + <type>cfg</type> + </artifact> + </artifacts> + </configuration> + </execution> + </executions> + </plugin> + </plugins> + </build> + +</project> \ No newline at end of file diff --git a/appender/timescaledb/src/main/cfg/org.apache.karaf.decanter.appender.timescaledb.cfg b/appender/timescaledb/src/main/cfg/org.apache.karaf.decanter.appender.timescaledb.cfg new file mode 100644 index 0000000..3178b5c --- /dev/null +++ b/appender/timescaledb/src/main/cfg/org.apache.karaf.decanter.appender.timescaledb.cfg @@ -0,0 +1,31 @@ +################################################################################ +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +################################################################################ + +####################################### +# Decanter TimescaleDB Appender Configuration +####################################### + +# DataSource to use +dataSource.target=(osgi.jndi.service.name=jdbc/decanter-timescaledb) + +# Name of the table storing the collected data +table.name=decanter + +# Marshaller to use (json is recommended) +marshaller.target=(dataFormat=json) \ No newline at end of file diff --git a/appender/timescaledb/src/main/java/org/apache/karaf/decanter/appender/timescaledb/TimescaleDbAppender.java b/appender/timescaledb/src/main/java/org/apache/karaf/decanter/appender/timescaledb/TimescaleDbAppender.java new file mode 100644 index 0000000..ef95c25 --- /dev/null +++ b/appender/timescaledb/src/main/java/org/apache/karaf/decanter/appender/timescaledb/TimescaleDbAppender.java @@ -0,0 +1,122 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.karaf.decanter.appender.timescaledb; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.Dictionary; +import javax.sql.DataSource; +import org.apache.karaf.decanter.api.marshaller.Marshaller; +import org.osgi.service.component.ComponentContext; +import org.osgi.service.component.annotations.Activate; +import org.osgi.service.component.annotations.Component; +import org.osgi.service.component.annotations.Reference; +import org.osgi.service.event.Event; +import org.osgi.service.event.EventConstants; +import org.osgi.service.event.EventHandler; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +@Component( + name = "org.apache.karaf.decanter.appender.timescaledb", + immediate = true, + property = EventConstants.EVENT_TOPIC + "=decanter/collect/*" +) +public class TimescaleDbAppender implements EventHandler { + + @Reference + public Marshaller marshaller; + + @Reference + public DataSource dataSource; + + private final static Logger LOGGER = LoggerFactory.getLogger(TimescaleDbAppender.class); + + private final static String createExtensionTemplate = + "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE"; + private final static String createTableQueryTemplate = + "CREATE TABLE TABLENAME(timestamp BIGINT, content text)"; + private final static String convertHyperTableQueryTemplate = + "SELECT * FROM create_hypertable('TABLENAME', 'timestamp', chunk_time_interval=>_timescaledb_internal.interval_to_usec('1 day'), migrate_data => true);"; + + private final static String insertQueryTemplate = + "INSERT INTO TABLENAME(timestamp, content) VALUES(?,?)"; + + String tableName; + + @SuppressWarnings("unchecked") + @Activate + public void activate(ComponentContext context) { + open(context.getProperties()); + } + + public void open(Dictionary<String, Object> config) { + this.tableName = getValue(config, "table.name", "decanter"); + try (Connection connection = dataSource.getConnection()) { + createStructure(connection); + } catch (Exception e) { + LOGGER.debug("Error creating table " + tableName, e); + } + } + + private String getValue(Dictionary<String, Object> config, String key, String defaultValue) { + String value = (String)config.get(key); + return (value != null) ? value : defaultValue; + } + + @Override + public void handleEvent(Event event) { + try (Connection connection = dataSource.getConnection()) { + String jsonSt = marshaller.marshal(event); + String insertQuery = insertQueryTemplate.replaceAll("TABLENAME", tableName); + Long timestamp = (Long)event.getProperty(EventConstants.TIMESTAMP); + if (timestamp == null) { + timestamp = System.currentTimeMillis(); + } + try (PreparedStatement insertStatement = connection.prepareStatement(insertQuery)) { + insertStatement.setLong(1, timestamp); + insertStatement.setString(2, jsonSt); + insertStatement.executeUpdate(); + LOGGER.trace("Data inserted into {} table", tableName); + } + } catch (Exception e) { + LOGGER.error("Can't store in the database", e); + } + } + + private void createStructure(Connection connection) { + String createTemplate = createTableQueryTemplate; + String createTableQuery = createTemplate.replaceAll("TABLENAME", tableName); + + String convertTemplate = convertHyperTableQueryTemplate; + String convertTableQuery = convertTemplate.replaceAll("TABLENAME", tableName); + + try (Statement createStatement = connection.createStatement()) { + createStatement.executeUpdate(createExtensionTemplate); + LOGGER.debug("Extension has been created", tableName); + createStatement.executeUpdate(createTableQuery); + LOGGER.debug("Table {} has been created", tableName); + createStatement.execute(convertTableQuery); + LOGGER.debug("Table {} has been converted to hypertable", tableName); + } catch (SQLException e) { + LOGGER.error("Can't create table {}", e); + } + } + +} diff --git a/assembly/src/main/feature/feature.xml b/assembly/src/main/feature/feature.xml index 0d3fba9..67ad591 100644 --- a/assembly/src/main/feature/feature.xml +++ b/assembly/src/main/feature/feature.xml @@ -398,6 +398,17 @@ <bundle>mvn:org.apache.karaf.decanter.appender/org.apache.karaf.decanter.appender.dropwizard/${project.version}</bundle> </feature> + <feature name="decanter-appender-timescaledb" version="${project.version}" description="Karaf Decanter TimescaleDB Appender"> + <feature>decanter-common</feature> + <feature>jndi</feature> + <feature>pax-jdbc-config</feature> + <feature>pax-jdbc-postgresql</feature> + <feature>pax-jdbc-pool-hikaricp</feature> + <feature>jdbc</feature> + <bundle>mvn:org.apache.karaf.decanter.appender/org.apache.karaf.decanter.appender.timescaledb/${project.version}</bundle> + <configfile finalname="/etc/org.apache.karaf.decanter.appender.timescaledb.cfg">mvn:org.apache.karaf.decanter.appender/org.apache.karaf.decanter.appender.timescaledb/${project.version}/cfg</configfile> + </feature> + <feature name="decanter-alerting-core" version="${project.version}" description="Karaf Decanter Alerting core"> <feature>decanter-common</feature> <bundle>mvn:org.apache.karaf.decanter.alerting/org.apache.karaf.decanter.alerting.checker/${project.version}</bundle> diff --git a/manual/src/main/asciidoc/user-guide/appenders.adoc b/manual/src/main/asciidoc/user-guide/appenders.adoc index c6aa6b7..10829c8 100644 --- a/manual/src/main/asciidoc/user-guide/appenders.adoc +++ b/manual/src/main/asciidoc/user-guide/appenders.adoc @@ -1372,3 +1372,51 @@ The `decanter-appender-dropwizard` feature provides the Decanter event handler r ---- karaf@root()> feature:install decanter-appender-dropwizard ---- + +==== TimescaleDB + +The Decanter TimescaleDB appender stores the collected data into TimescaleDB database. + +You have to install a TimescaleDB before using the appender. + +You can install a test database with Docker for dev: + +``` +docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=decanter -e POSTGRES_USER=decanter -e POSTGRES_DATABASE=decanter timescale/timescaledb +``` + +===== TimescaleDB appender + +The `decanter-appender-timescaledb` feature installs the TimescaleDB appender. + +As TimescaleDB is a PostgreSQL database extension, the *timescaledb* feature will install all required features to configure +your datasource (jdbc, jndi, postgreSQL driver, pool datasource). + +This feature installs the `etc/org.apache.karaf.decanter.appender.timescaledb.cfg` configuration file allowing you to setup the location +of the TimescaleDB database to use: + +---- +################################# +# Decanter TimescaleDB Configuration +################################# + +# DataSource to use +dataSource.target=(osgi.jndi.service.name=jdbc/decanter-timescaledb) + +# Name of the table storing the collected data +table.name=decanter + +# Marshaller to use (json is recommended) +marshaller.target=(dataFormat=json) +---- + +where: + +* `datasource.target` property contains the name of the JDBC datasource to use to connect to the database. You can +create this datasource using the Karaf `jdbc:create` command (provided by the `jdbc` feature). +* `table.name` property contains the table name in the database. The Decanter JDBC appender automatically +activates the Timescale extenssion, creates the table for you and migrates the table to a TimescaleDB hypertable. +The table is simple and contains just two column: +** `timestamp` as BIGINT +** `content` as TEXT +* `marshaller.target` is the marshaller used to serialize data into the table. ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org > Add TimescaleDB appender > ------------------------ > > Key: KARAF-5976 > URL: https://issues.apache.org/jira/browse/KARAF-5976 > Project: Karaf > Issue Type: Improvement > Components: decanter > Reporter: Francois Papon > Assignee: Francois Papon > Priority: Major > Fix For: decanter-2.2.0 > > > TimescaleDB is an open source time-series database powered by PostgreSQL. > We could add an appender to store collected data into a TimescaleDB database. -- This message was sent by Atlassian JIRA (v7.6.3#76005)