[ 
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)

Reply via email to