Several problems with External Datasources (JDBC, REST)

Hello,

I am trying Siren Community Edition. I downloaded the latest version 10.4.
I am facing some unexpected behaviors when it comes to configuring external datasources, both JDBC and REST.

First with JDBC:
I am able to create a JDBC connection to an external SQL server, and I am able to create a virtual index and import the data into Siren investigate, so no problem with that. However, the problem is that when I create a JDBC datasource (in my case SQL server), I save it and everything, but I can’t find it in the Saved Objects/ Datasources, and therefore I can’t find it if I want to create queries. That is, when I create a new Query, and when I want to select an external datasource, I can’t find my JDBC datasource in order to select it.

Second with REST:
I can create a rest datasource, and contrary to the JDBC datasource, the REST datasource is saved in Saved Objects/Datasources, and therefore I can select it when I create a query, however I can’t select the entity to do a preview, even when I try to create a Query Viewer Visualization, it doesn’t show me any options for the type, in order to select it, You can check the image below.
image

So is there a bug somewhere, or is it something from my machine?
Thanks in advance for the help

1 Like

Hi Raef, sorry for the delay.

there is a bit of misleading UI there and we’re addressing that in 10.5. Specifically, 10.5 launches the “Web services” infrastructure which is the replacement for the old REST datasources. Those datasources can only produce data which can be visualized in the very specific “templates” and not in the general visualizations (histograms/piecharts etc) . 10.5 services index the data in Elasticsearch so then you can see the results in normal visualizations.

Similarly “Queries” referred to queries that worked with the old type of db connector (discontinued now) as it has been replaced by Virtual Indexes, Datasources.

To see the data coming from a JDBC query you have 2 ways now.

  1. First of all create a Data Source to your database. This requires installing the JDBC driver, see the documentation. Once you tested it works you can either

a) use the “virtual indexes” - these are direct connections to table (or views i believe) in the remote backend. See the documentation. You’ll be able to immediately create visualizations and dashboards on a virtual index without any copying

b) go in “reflections” (there is a side icon ) and from the reflections you can chose a datasource you configured. From there you can input a SQL query and import the results (also on a scheduled basis) into an Elasticsearch index… which in turn you can visualize in visualizations.

Next version of Siren removes this ambiguity and, like i said, introduces the new web service infrastructure. I’ll be posting a blog in a few days or watch for the announcement.

1 Like

Hello @jccq, thank you for the reply and for the clarification.
Ok fine for the REST, I will wait next version.

For the JDBC DB, what I wanted to achieve is to configure the query to the DB in a way that when I select something on the dashboard, then this will be the parameter to the query. I know that I can use the @doc[…]@ syntax to give a parameter to the query but the problem is that I can’t find the JDBC datasource when I want to build the DB query.

Anyway, do you have a date to when you will release the next version?
Thanks again.

Raef hi this will be possible in 10.5 with the new Scripting API (@Dara). WIth this new API you can add a script on the dashboard so that when the doc table is clicked then you can fire a query to a datasource (With your own parameter taken from the document) and you can then render the result in a panel or do other operations based on this.

Will require a bit of Javascript (Scripting is in javascript and is attached to dashboard actions and components) but the possibilities are very extensive - hard to be more flexible than this. 10.5 is expected early May.
cheers

Ok thank you very much for your answers