Access to sqlite3 database

[Shakthi Kannan]

Hi,

Is there a way to access an sqlite3 database from the Riemann configuration?

I tried the following code snippet:

=== BEGIN ===

(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "/tmp/test.db"})

(require '[clojure.pprint :as p]
'[clojure.java.jdbc :as j])

=== END ===

But, it throws the following exception:

java.io.FileNotFoundException: Could not locate clojure/java/jdbc__init.class

Is there a way to import the jdbc driver into Riemann? Please advise.

Thank you,

SK

--
Shakthi Kannan
http://www.shakthimaan.com

[Sanel Zukan]

Sqlite driver isn't shipped with Riemann, but you can get with something
like:

;; goes to riemann.config
(add-dependencies
:coordinates [[org.clojure/java.jdbc "0.7.12"]
[org.xerial/sqlite-jdbc "3.8.7"]]
:repositories {"clojars" "https://clojars.org/repo"})

(require '[clojure.java.jdbc :as j])

;; your code goes here...

Best,
Sanel

> --
> You received this message because you are subscribed to the Google Groups "Riemann Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to riemann-user...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/riemann-users/CABG-yt2Uc9%2BA1qDGM%2B9eAdiQ7x45ZhpoqHjAgw8DQSCbmBfiiw%40mail.gmail.com.

[Shakthi Kannan]

Hi,

On Fri, 2 Dec 2022 at 01:46, Sanel Zukan <san...@gmail.com> wrote:
| Sqlite driver isn't shipped with Riemann, but you can get with something
| like:
|
| ;; goes to riemann.config
| (add-dependencies
| :coordinates [[org.clojure/java.jdbc "0.7.12"]
| [org.xerial/sqlite-jdbc "3.8.7"]]
| :repositories {"clojars" "https://clojars.org/repo"})
|
| (require '[clojure.java.jdbc :as j])
|

\--

How do I execute the above? I am testing Riemann binaries in the bin/
folder with configuration in etc/. Using the following command for the
above throws ClassNotFoundException error:

$ ./bin/riemann ./etc/riemann.config

The following is what I am currently using:

(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "/tmp/test.db"})

(require '[clojure.pprint :as p]
'[clojure.java.jdbc :as j])

I run it with:

$ java -cp "/path/to/lib/java.jdbc-0.7.12.jar:/path/to/lib/riemann.jar:/path/to/lib/sqlite-jdbc-3.40.0.0.jar"
riemann.bin ./etc/riemann.config

Reference: https://stackoverflow.com/questions/27766730/riemann-io-add-jar-to-classpath

Please let me know.

Thanks!

[Sanel Zukan]

Shakthi Kannan <shakt...@gmail.com> writes:
> How do I execute the above? I am testing Riemann binaries in the bin/
> folder with configuration in etc/. Using the following command for the
> above throws ClassNotFoundException error:

Can you paste your full riemann.config please?

> SK
>
> --
> Shakthi Kannan
> http://www.shakthimaan.com

Best,
Sanel

[Shakthi Kannan]

Hi,

On Fri, 6 Jan 2023 at 20:36, Sanel Zukan <san...@gmail.com> wrote:
> Can you paste your full riemann.config please?

Sure.

=== BEGIN ===

;; (add-dependencies

;; :coordinates [[org.clojure/java.jdbc "0.7.12"]
;; [org.xerial/sqlite-jdbc "3.8.7"]]
;; :repositories {"clojars" "https://clojars.org/repo"})

;; (require '[clojure.java.jdbc :as j])

(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"

:subname "data.sqlite3"})

(require '[clojure.pprint :as p]
'[clojure.java.jdbc :as j])

(p/print-table (j/query db (str "select * from users;")))

(logging/init {:file "riemann.log"})

(let [host "127.0.0.1"]
(tcp-server {:host host})
(udp-server {:host host})
(ws-server {:host host}))

(let [index (index)]
(streams
...))

=== END ===

Thanks!

[Sanel Zukan]

Shakthi Kannan <shakt...@gmail.com> writes:
> === BEGIN ===
>
> ;; (add-dependencies
> ;; :coordinates [[org.clojure/java.jdbc "0.7.12"]
> ;; [org.xerial/sqlite-jdbc "3.8.7"]]
> ;; :repositories {"clojars" "https://clojars.org/repo"})

You'll need to uncomment these lines.

Best,
Sanel

[Shakthi Kannan]

Hi,

--- On Sun, 8 Jan 2023 at 00:01, Sanel Zukan <san...@gmail.com> wrote:
>> ;; (add-dependencies
>> ;; :coordinates [[org.clojure/java.jdbc "0.7.12"]
>> ;; [org.xerial/sqlite-jdbc "3.8.7"]]
>> ;; :repositories {"clojars" "https://clojars.org/repo"})
>
> You'll need to uncomment these lines.

I had shared the configuration file for reference. After uncommenting
these lines, I got the backtrace error that I had mentioned earlier. I
am posting it again for reference:

$ ./bin/riemann ./etc/riemann.config
INFO [2023-01-08 10:58:55,107] main - riemann.bin - Loading
/path/to/riemann-0.3.6/etc/riemann.config
INFO [2023-01-08 10:58:55,133] main - riemann.bin - PID 2785
ERROR [2023-01-08 10:58:55,136] main - riemann.bin - Couldn't start
clojure.lang.Compiler$CompilerException:
java.lang.ClassNotFoundException: org.clojure,
compiling:(/path/to/riemann-0.3.6/./etc/riemann.config:8:1)
at clojure.lang.Compiler.analyze(Compiler.java:6792)
at clojure.lang.Compiler.analyze(Compiler.java:6729)
at clojure.lang.Compiler$VectorExpr.parse(Compiler.java:3253)
at clojure.lang.Compiler.analyze(Compiler.java:6775)
...
Caused by: java.lang.ClassNotFoundException: org.clojure
at java.base/java.net.URLClassLoader.findClass(URLClassLoader.java:445)
at clojure.lang.DynamicClassLoader.findClass(DynamicClassLoader.java:69)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:588)
at clojure.lang.DynamicClassLoader.loadClass(DynamicClassLoader.java:77)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:521)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Class.java:488)
at java.base/java.lang.Class.forName(Class.java:467)
at clojure.lang.RT.classForName(RT.java:2204)
at clojure.lang.RT.classForNameNonLoading(RT.java:2217)
at clojure.lang.Compiler$HostExpr.maybeClass(Compiler.java:1041)
at clojure.lang.Compiler.analyzeSymbol(Compiler.java:7191)
at clojure.lang.Compiler.analyze(Compiler.java:6752)
... 31 common frames omitted

The line #8 is the (add-dependencies ...). Do I need to install some
Clojure files, or is there another way to execute Riemann?

Please let me know.

[Sanel Zukan]

Shakthi Kannan <shakt...@gmail.com> writes:
>>> ;; (add-dependencies
>>> ;; :coordinates [[org.clojure/java.jdbc "0.7.12"]
>>> ;; [org.xerial/sqlite-jdbc "3.8.7"]]
>>> ;; :repositories {"clojars" "https://clojars.org/repo"})

> ...

>
> I had shared the configuration file for reference. After uncommenting
> these lines, I got the backtrace error that I had mentioned earlier. I
> am posting it again for reference:
>

> ...

>
> The line #8 is the (add-dependencies ...). Do I need to install some
> Clojure files, or is there another way to execute Riemann?

Oh sorry, I missed the full namespace resolution. This should work:

(cemerick.pomegranate/add-dependencies

:coordinates '[[org.clojure/java.jdbc "0.7.12"]
[org.xerial/sqlite-jdbc "3.8.7"]]

:repositories (merge cemerick.pomegranate.aether/maven-central
{"clojars" "https://clojars.org/repo"}))

;;; rest of config file...

Notice that quote in ":coordinates" value must be present, otherwise
Riemann/Clojure will see "org.clojure/java.jdbc" as a var.

> Please let me know.
>
> Thanks!
>
> SK
>
> --
> Shakthi Kannan
> http://www.shakthimaan.com

Best,
Sanel

[Shakthi Kannan]

Hi Sanel,

The data is not that big and hence I initially decided to use sqlite3.
If I store this data in ElasticSearch itself with a new index, would
it be possible to read and write to it from the Riemann configuration
file? Are there any code examples that I can refer to on how to
accomplish the same?

Thanks for the configuration change!

[Sanel Zukan]

Shakthi Kannan <shakt...@gmail.com> writes:
> The data is not that big and hence I initially decided to use sqlite3.
> If I store this data in ElasticSearch itself with a new index, would
> it be possible to read and write to it from the Riemann configuration
> file? Are there any code examples that I can refer to on how to
> accomplish the same?

For writting metrics, check ElasticSearch namespace [1]. For reading,
ElasticSearch has a simple search REST API (used by Riemann as well),
you can furthe explore.

Try with this in riemann.conf (change "some-index" to targe index and
update search query):

====
(require '[clj-http.client :as http])

;; search example, put it at the end of riemann.config.
(future
(clojure.tools.logging/info
(http/get "http://elastic-search-ip:9200/some-index/_search?q=someting-to-search" {:as :json})))
====

This will return a clojure hashmap, you can further inspect for the
data. Notice that, for Riemann to work uninterrupted, these search
queries should be run in a separate thread (I'm using future for that here).

Check online for more details on ElasticSearch REST API and clj-http.

> Thanks for the configuration change!
>
> SK
>
> --
> Shakthi Kannan
> http://www.shakthimaan.com

Best,
Sanel

[1] http://riemann.io/api/riemann.elasticsearch.html

[Shakthi Kannan]

Hi,

--- On Wed, 11 Jan 2023 at 20:03, Sanel Zukan <san...@gmail.com> wrote:
> ;; search example, put it at the end of riemann.config.
> (future
> (clojure.tools.logging/info
> (http/get "http://elastic-search-ip:9200/some-index/_search?q=someting-to-search" {:as :json})))
>

> This will return a clojure hashmap, you can further inspect for the
> data.

I was able to see the http/get info in the log, but, where do I access
the clojure hashmap? On a related note, I use the following curl
command to make a successful post to elasticsearch.

$ curl -X POST "http://elasticsearch:9200/index-name/_doc/doc-id/_update"
-H 'Content-Type: application/json' -d '{"foo" : {"bar": 50 }}'

How should I include the data for a Clojure http/post request in the
Riemann configuration?

Thanks for your help!