# Importing data to elasticsearch from multiple dynamic databases

**URL:** <https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317>\
**Category:** Logstash\
**Created:** [November 3, 2021, 10:55am UTC](https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317 "2021-11-03T10:55:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![enricog84](https://avatars.discourse-cdn.com/v4/letter/e/8491ac/32.png) [@enricog84](https://discuss.elastic.co/u/enricog84)\
**Post date:** [November 3, 2021, 10:55am UTC](https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317/1 "2021-11-03T10:55:37Z")

</div>

Hi,

I am currently evaluating (and have no further experience so far) the ELK stack.  
I am wondering if it would be possible to import data from multiple dynamic created MariaDB databases into Elasticsearch.

Short backgrond, we have a multi-tenant application where for each of our customer the data is kept in a separate database. So whenever a new customer comes, a new database is created. The available customers are managed in a central database.

What I want to do now is, fetch the available Customer (can go into the thousands) on the input and then enrich with data from the customers respective database. The databases of the customers do all look the same.

So what I did try to achieve this was:

```auto
input {
    jdbc {
        jdbc_connection_string => "jdbc:mariadb://127.0.0.1:3306/eg_test?sessionVariables=sql_mode=ANSI_QUOTES"
        jdbc_user => "user"
        jdbc_password => "secret"
        jdbc_driver_library => "/dataservice/mariadb-java-client-2.7.4.jar"
        jdbc_driver_class => "org.mariadb.jdbc.Driver"
        jdbc_page_size => 200000
        jdbc_paging_enabled => true
        statement => "SELECT
            customer
            FROM eg_test.customers"
    }
}
filter {
    jdbc_streaming {
        jdbc_connection_string => "jdbc:mariadb://127.0.0.1:3306/db_%{[customer]}?sessionVariables=sql_mode=ANSI_QUOTES"
        jdbc_user => "user"
        jdbc_password => "secret"
        jdbc_driver_library => "/dataservice/mariadb-java-client-2.7.4.jar"
        jdbc_driver_class => "org.mariadb.jdbc.Driver"
        statement => "SELECT
                data,
                COUNT(something) AS cnt
            FROM customer_table
            WHERE ctime >= UNIX_TIMESTAMP('${START_DATE}')
                AND ctime < UNIX_TIMESTAMP('${END_DATE}')
            GROUP BY data"
        parameters => { "customer" => "[customer]"}
        target => "data"
    }
}
output {
    # Add to the elastic search index
    elasticsearch {
        "hosts" => "127.0.0.1:9200"
        "index" => "customer_data_test"
    }
}

```

Tough this fails as the jdbc\_streaming plugin is unable to resolve the field reference "%{[customer]}" in the jdbc\_connection\_string .

The error I get back is:

```auto
DatabaseConnectionError: Java::JavaSql::SQLSyntaxErrorException: Could not connect to address=(host=127.0.0.1)(port=3306)(type=master) : (conn=165) Unknown database 'db_%{[customer]}'>

```

So is there a possibility to do this?  
The other way I could think of is, create the logstash config dynamically with another script and add each database as input. But would this scale with a few thousand customers?

Thanks for any input,  
Enrico

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [November 3, 2021, 4:09pm UTC](https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317/2 "2021-11-03T16:09:17Z")

</div>

> [@enricog84](#):
>
> this fails as the jdbc\_streaming plugin is unable to resolve the field reference "%{[customer]}" in the jdbc\_connection\_string

Correct. The filter (or rather, the mixin) [does not sprintf](https://github.com/logstash-plugins/logstash-filter-jdbc_streaming/blob/2ed23b51c0b9b1a57e39bf672f6f013eae88d5fc/lib/logstash/plugin_mixins/jdbc_streaming.rb#L89) the connection string. That gets [called](https://github.com/logstash-plugins/logstash-filter-jdbc_streaming/blob/2ed23b51c0b9b1a57e39bf672f6f013eae88d5fc/lib/logstash/filters/jdbc_streaming.rb#L120) during initialization, so there is no event from which fields can be referenced.

---

<div class="post-metadata">

**Author:** ![enricog84](https://avatars.discourse-cdn.com/v4/letter/e/8491ac/32.png) [@enricog84](https://discuss.elastic.co/u/enricog84)\
**Post date:** [November 4, 2021, 8:31am UTC](https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317/3 "2021-11-04T08:31:37Z")

</div>

Thank you for the response and confirming this.  
Well then I will try to create several inputs and see how it performs 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 2, 2021, 8:32am UTC](https://discuss.elastic.co/t/importing-data-to-elasticsearch-from-multiple-dynamic-databases/288317/4 "2021-12-02T08:32:14Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.