does quesma support multiple clickhouse databases in backendConnectors

[marksmansj]

Hello. All:
in my sceanario. there are multiple databases in clickhouse , such as ops/dev . how can i expose tables in these databases ? can backendConnectors config multiple databases ?

[trzysiek]

Hi @marksmansj ! Are those databases administered by the same clickhouse server? If yes, then multiple databases support is in Quesma out of the box.

I can show you full walk-through how to make it work. I already did, but later thought it's probably to better to ask the question above first.

[marksmansj]

Hi @marksmansj ! Are those databases administered by the same clickhouse server? If yes, then multiple databases support is in Quesma out of the box.

I can show you full walk-through how to make it work. I already did, but later thought it's probably to ask the question above first.

thank you . yes. these databases are in the same clickhouse cluster. by the way. do you know how to get a licenseKey? I've send a email to support@quesma.com to ask a key . but no one response .

[trzysiek]

Hi. I think I can say we support multiple databases on the same cluster. Let me show you a quick example how I achieved what I think you're trying to achieve, in just a few minutes.

Please let us know in case of any issues or questions!

I run a default Quesma stack, so Quesma + Clickhouse + Kibana (with a minimalistic Quesma config that I paste at the bottom, you might need a more complex one suited for your needs. Most importantly, I say that all indexes should be queried only from Clickhouse).
First, I connect to Clickhouse and set up a couple of databases there:

Created a few tables on a few different databases, e.g.:


Inserted a few example values to the tables, then opened Kibana, at left menu: Explorer > Data Views and you can see there is some data from default.kibana_sample_data_logs table.

Then I clicked the stuff below to create a Data View in Kibana to explore my data from e.g. dev.d table, and be able to create all the dashboards that Kibana supports.

And finally I see all the data from dev.d table. I also can add all the tables from databases default and q in the same way.

My minimalistic Quesma config:

disableTelemetry: true
frontendConnectors:
  - name: elastic-ingest
    type: elasticsearch-fe-ingest
    config:
      listenPort: 8080
      disableAuth: true
  - name: elastic-query
    type: elasticsearch-fe-query
    config:
      listenPort: 8080
backendConnectors:
  - name: my-minimal-elasticsearch
    type: elasticsearch
    config:
      url: "http://elasticsearch:9200"
  - name: my-clickhouse-data-source
    type: clickhouse-os
    config:
      url: "clickhouse://clickhouse:9000"
ingestStatistics: true
internalTelemetryUrl: "https://api.quesma.com/phone-home"
logging:
  path: "logs"
  level: "info"
  remoteUrl: "https://api.quesma.com/phone-home"
  disableFileLogging: false
processors:
  - name: my-query-processor
    type: quesma-v1-processor-query
    config:
      indexes:
        "*":
          target: [ my-clickhouse-data-source ]
  - name: my-ingest-processor
    type: quesma-v1-processor-ingest
    config:
      indexes:
        "*":
          target: [ my-clickhouse-data-source ]
pipelines:
  - name: my-pipeline-elasticsearch-query-clickhouse
    frontendConnectors: [ elastic-query ]
    processors: [ my-query-processor ]
    backendConnectors: [ my-minimal-elasticsearch, my-clickhouse-data-source ]
  - name: my-pipeline-elasticsearch-ingest-to-clickhouse
    frontendConnectors: [ elastic-ingest ]
    processors: [ my-ingest-processor ]
    backendConnectors: [ my-minimal-elasticsearch, my-clickhouse-data-source ]

[marksmansj]

Hi. I think I can say we support multiple databases on the same cluster. Let me show you a quick example how I achieved what I think you're trying to achieve, in just a few minutes.

Please let us know in case of any issues or questions!

I run a default Quesma stack, so Quesma + Clickhouse + Kibana (with a minimalistic Quesma config that I paste at the bottom, you might need a more complex one suited for your needs. Most importantly, I say that all indexes should be queried only from Clickhouse). First, I connect to Clickhouse and set up a couple of databases there: Created a few tables on a few different databases, e.g.: Inserted a few example values to the tables, then opened Kibana, at left menu: Explorer > Data Views and you can see there is some data from default.kibana_sample_data_logs table.

Then I clicked the stuff below to create a Data View in Kibana to explore my data from e.g. dev.d table, and be able to create all the dashboards that Kibana supports.

And finally I see all the data from dev.d table. I also can add all the tables from databases default and q in the same way.

My minimalistic Quesma config:

disableTelemetry: true
frontendConnectors:
  - name: elastic-ingest
    type: elasticsearch-fe-ingest
    config:
      listenPort: 8080
      disableAuth: true
  - name: elastic-query
    type: elasticsearch-fe-query
    config:
      listenPort: 8080
backendConnectors:
  - name: my-minimal-elasticsearch
    type: elasticsearch
    config:
      url: "http://elasticsearch:9200"
  - name: my-clickhouse-data-source
    type: clickhouse-os
    config:
      url: "clickhouse://clickhouse:9000"
ingestStatistics: true
internalTelemetryUrl: "https://api.quesma.com/phone-home"
logging:
  path: "logs"
  level: "info"
  remoteUrl: "https://api.quesma.com/phone-home"
  disableFileLogging: false
processors:
  - name: my-query-processor
    type: quesma-v1-processor-query
    config:
      indexes:
        "*":
          target: [ my-clickhouse-data-source ]
  - name: my-ingest-processor
    type: quesma-v1-processor-ingest
    config:
      indexes:
        "*":
          target: [ my-clickhouse-data-source ]
pipelines:
  - name: my-pipeline-elasticsearch-query-clickhouse
    frontendConnectors: [ elastic-query ]
    processors: [ my-query-processor ]
    backendConnectors: [ my-minimal-elasticsearch, my-clickhouse-data-source ]
  - name: my-pipeline-elasticsearch-ingest-to-clickhouse
    frontendConnectors: [ elastic-ingest ]
    processors: [ my-ingest-processor ]
    backendConnectors: [ my-minimal-elasticsearch, my-clickhouse-data-source ]

hello. my brother. I configured it based on your configuration file.but I only can find the tables in database "default" when I create the data view . is there any error in my config ? this is my quesma config and some screenshots down below:

frontendConnectors:
  - name: elastic-query
    type: elasticsearch-fe-query
    config:
      listenPort: 8080
      disableAuth: true
  - name: elastic-ingest
    type: elasticsearch-fe-ingest
    config:
      listenPort: 8080
backendConnectors:
  - name: minimal-elasticsearch
    type: elasticsearch
    config:
      url: http://192.168.60.211:9201
  - name: clickhouse-instance
    type: clickhouse-os
    config:
      url: clickhouse://192.168.60.211:9000
      user: admin
      password: 12345
processors:
  - name: query-processor
    type: quesma-v1-processor-query
    config:
      indexes:
        "*":
          target: [ clickhouse-instance ]
  - name: ingest-processor
    type: quesma-v1-processor-ingest
    config:
      indexes:
        "*":
          target: [ clickhouse-instance ]
pipelines:
  - name: elasticsearch-proxy-read
    frontendConnectors: [ elastic-query ]
    processors: [ query-processor ]
    backendConnectors: [ minimal-elasticsearch, clickhouse-instance ]
  - name: elasticsearch-proxy-write # remove if you do NOT want to write data to ClickHouse
    frontendConnectors: [ elastic-ingest ]
    processors: [ ingest-processor ]
    backendConnectors: [ minimal-elasticsearch, clickhouse-instance ]

this is the quesma log:

Quesma build info: version=[1.1.7], build hash=[e75fda2ba3bc413c5cf4864e1f6d3ffba9dc51bc], build date=[2025-03-26]
Using config file: [/config/quesma.yaml]
Initializing license module
License key not supplied in the configuration, will attempt to obtain temporary license with limited functionalities
License key obtained and set successfully, key=[ZXlKcGJu.....clE9PQ==]
Mar 26 01:38:48.102 INF platform/logger/logger.go:105 > Logger initialized with level info
Mar 26 01:38:48.103 ERR platform/clickhouse/connection.go:65 > Failed to ping database. error="tls: first record does not look like a TLS handshake"
Mar 26 01:38:48.103 WRN platform/clickhouse/connection.go:75 > Failed to connect to database with TLS. Trying without TLS at all. error="tls: first record does not look like a TLS handshake"
Mar 26 01:38:48.105 INF platform/clickhouse/connection.go:89 > Connected to database: clickhouse://192.168.60.211:9000
Mar 26 01:38:48.105 INF platform/telemetry/phone_home.go:720 > PhoneHomeAgent Started
Mar 26 01:38:48.107 INF platform/connectors/connector.go:49 > Registering connector named [injected-clickhouse-connector] of type [clickhouse-os]
Mar 26 01:38:48.108 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:38:48.108 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[]
Mar 26 01:38:48.109 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]
Mar 26 01:38:48.109 INF cmd/main.go:117 > loaded config: 
Quesma Configuration:
        Transparent proxy mode: false
        Elasticsearch URL: http://192.168.60.211:9201
        ClickhouseUrl: clickhouse://192.168.60.211:9000
      ClickHouse user: admin
      ClickHouse password: ***
        Connectors: 
        - [injected-clickhouse-connector] connector
          Type: clickhouse-os
          Url: clickhouse://192.168.60.211:9000
          User: admin
          Password: ***
        Indexes: 
        Logs Path: 
        Log Level: info
        Public TCP Port: 8080
        Ingest Statistics: false,
        Quesma Telemetry URL: https://api.quesma.com/phone-home,
        Optimizers: ,
        DisableAuth: true,
        AutodiscoveryEnabled: true,
        EnableIngest: true,
        CreateCommonTable: false,
        UseCommonTableForWildcard: false,
        DefaultIngestTarget: [clickhouse],
        DefaultQueryTarget: [clickhouse],
        MapFieldsDiscoveringEnabled: false

Mar 26 01:38:48.109 INF platform/ab_testing/sender/coordinator.go:121 > A/B Testing Controller is disabled
Mar 26 01:38:48.109 INF platform/v2/core/quesma_builder.go:93 > BasicHTTPFrontendConnector::8080, index: 0, pipeline:0
Mar 26 01:38:48.109 INF platform/v2/core/quesma_builder.go:93 > BasicHTTPFrontendConnector::8080, index: 0, pipeline:1
Mar 26 01:38:48.109 INF platform/v2/core/quesma_builder.go:145 > Sharing frontend connector 0xc000122208 with 0xc000122200
Mar 26 01:38:48.109 INF platform/v2/core/quesma_builder.go:145 > Sharing frontend connector 0xc000122208 with 0xc000122208
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.Quesma :SKIP - No dependencies to inject. It doesn't implement DependenciesSetter interface.
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.Pipeline :Injecting dependencies intopipeline(0xc00028b980)
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.Pipeline :OK - Injected Dependencies
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.ElasticHttpIngestFrontendConnector :Injecting dependencies intoBasicHTTPFrontendConnector
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.ElasticHttpIngestFrontendConnector :OK - Injected Dependencies
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.PathRouter :SKIP - No dependencies to inject. It doesn't implement DependenciesSetter interface.
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.Dispatcher :OK - Injected Dependencies
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.NoopBackendConnector :SKIP - No dependencies to inject. It doesn't implement DependenciesSetter interface.
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.Pipeline :Injecting dependencies intopipeline(0xc00028b920)
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.Pipeline :OK - Injected Dependencies
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.ElasticHttpIngestFrontendConnector :Injecting dependencies intoBasicHTTPFrontendConnector
Mar 26 01:38:48.109 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.ElasticHttpIngestFrontendConnector :OK - Injected Dependencies
Mar 26 01:38:48.110 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.PathRouter :SKIP - No dependencies to inject. It doesn't implement DependenciesSetter interface.
Mar 26 01:38:48.110 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *frontend_connectors.Dispatcher :OK - Injected Dependencies
Mar 26 01:38:48.110 INF platform/v2/core/dependency_injection.go:159 > Dependency injection into *quesma_api.NoopBackendConnector :SKIP - No dependencies to inject. It doesn't implement DependenciesSetter interface.
Mar 26 01:38:48.110 INF cmd/quesma.go:41 > starting quesma, transparent proxy mode: false
Mar 26 01:38:48.110 WRN platform/ui/console_routes.go:83 > admin console authentication is disabled
Mar 26 01:38:48.110 INF platform/table_resolver/table_resolver.go:216 > Table resolve started.
Mar 26 01:38:48.114 INF platform/clickhouse/table_discovery.go:350 > Index configuration empty, running table auto-discovery
Mar 26 01:38:48.144 INF platform/clickhouse/table_discovery.go:367 > Table auto-discovery results -> 3 tables found: [{table: log, timestampField: time}, {table: rlog, timestampField: time}, {table: table_name, timestampField: }]
Mar 26 01:38:48.144 WRN platform/clickhouse/table_discovery.go:231 > common table not found
Mar 26 01:38:48.144 INF platform/clickhouse/table_discovery.go:451 > discovered new tables: [log rlog table_name]
Mar 26 01:38:48.144 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc00020a2a0
Mar 26 01:38:48.144 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc000280770
Mar 26 01:38:48.144 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:38:48.144 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[log:{log false} rlog:{rlog false} table_name:{table_name false}]
Mar 26 01:38:48.146 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]
Mar 26 01:38:48.147 INF platform/clickhouse/table_discovery.go:350 > Index configuration empty, running table auto-discovery
Mar 26 01:38:48.179 INF platform/clickhouse/table_discovery.go:367 > Table auto-discovery results -> 3 tables found: [{table: log, timestampField: time}, {table: rlog, timestampField: time}, {table: table_name, timestampField: }]
Mar 26 01:38:48.179 WRN platform/clickhouse/table_discovery.go:231 > common table not found
Mar 26 01:38:48.179 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc00020a2a0
Mar 26 01:38:48.179 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc000280770
Mar 26 01:38:48.180 INF platform/clickhouse/clickhouse.go:95 > schemas loaded: [rlog log table_name]
Mar 26 01:38:48.180 INF platform/v2/core/quesma_builder.go:44 > Starting pipeline &{ [0xc000122208] [] map[0:0x1766840] 0xc0002be500}
Mar 26 01:38:48.180 INF platform/v2/core/quesma_pipeline.go:87 > Starting frontend connector :8080 component=pipeline(0xc00028b980)
Mar 26 01:38:48.180 INF platform/v2/core/quesma_builder.go:44 > Starting pipeline &{ [0xc000122208] [] map[0:0x1766840] 0xc0002be600}
Mar 26 01:38:48.180 INF platform/v2/core/quesma_pipeline.go:87 > Starting frontend connector :8080 component=pipeline(0xc00028b920)
Mar 26 01:38:48.180 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:38:48.180 INF platform/frontend_connectors/basic_http_frontend_connector.go:145 > HTTP server started on :8080 component=BasicHTTPFrontendConnector
Mar 26 01:38:48.180 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[log:{log false} rlog:{rlog false} table_name:{table_name false}]
Mar 26 01:38:48.182 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]
Mar 26 01:39:18.123 INF platform/telemetry/phone_home.go:561 > [USAGE REPORT] dababase=[41b9f11a8baaf768] billable_size_in_Mbs=[0] top_table_sizes=map[]
Mar 26 01:39:18.922 INF platform/telemetry/phone_home.go:659 > Phone Home succeded.
Mar 26 01:39:36.980 INF platform/v2/core/quesma_logger.go:174 > Ingesting via _bulk API, batch size=4 lines
Mar 26 01:39:48.184 INF platform/clickhouse/table_discovery.go:350 > Index configuration empty, running table auto-discovery
Mar 26 01:39:48.208 INF platform/clickhouse/table_discovery.go:367 > Table auto-discovery results -> 3 tables found: [{table: log, timestampField: time}, {table: rlog, timestampField: time}, {table: table_name, timestampField: }]
Mar 26 01:39:48.208 WRN platform/clickhouse/table_discovery.go:231 > common table not found
Mar 26 01:39:48.208 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc00020a2a0
Mar 26 01:39:48.208 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc000280770
Mar 26 01:39:48.208 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:39:48.208 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[log:{log false} rlog:{rlog false} table_name:{table_name false}]
Mar 26 01:39:48.210 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]
Mar 26 01:40:43.028 INF platform/v2/core/quesma_logger.go:174 > Ingesting via _bulk API, batch size=2 lines
Mar 26 01:40:48.213 INF platform/clickhouse/table_discovery.go:350 > Index configuration empty, running table auto-discovery
Mar 26 01:40:48.244 INF platform/clickhouse/table_discovery.go:367 > Table auto-discovery results -> 3 tables found: [{table: log, timestampField: time}, {table: rlog, timestampField: time}, {table: table_name, timestampField: }]
Mar 26 01:40:48.245 WRN platform/clickhouse/table_discovery.go:231 > common table not found
Mar 26 01:40:48.245 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc00020a2a0
Mar 26 01:40:48.245 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc000280770
Mar 26 01:40:48.245 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:40:48.245 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[log:{log false} rlog:{rlog false} table_name:{table_name false}]
Mar 26 01:40:48.247 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]
Mar 26 01:41:48.250 INF platform/clickhouse/table_discovery.go:350 > Index configuration empty, running table auto-discovery
Mar 26 01:41:48.274 INF platform/clickhouse/table_discovery.go:367 > Table auto-discovery results -> 3 tables found: [{table: rlog, timestampField: time}, {table: table_name, timestampField: }, {table: log, timestampField: time}]
Mar 26 01:41:48.274 WRN platform/clickhouse/table_discovery.go:231 > common table not found
Mar 26 01:41:48.275 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc00020a2a0
Mar 26 01:41:48.275 INF platform/clickhouse/table_discovery.go:161 > Sending message to observer 0xc000280770
Mar 26 01:41:48.275 INF platform/table_resolver/table_resolver.go:151 > Index registry updating state.
Mar 26 01:41:48.275 INF platform/table_resolver/table_resolver.go:165 > Clickhouse tables updated: map[log:{log false} rlog:{rlog false} table_name:{table_name false}]
Mar 26 01:41:48.276 INF platform/table_resolver/table_resolver.go:185 > Elastic tables updated: map[]

[trzysiek]

Have you tried to wait 1 minute (at most)? Quesma refreshes indexes from Clickhouse every 1min by default, so you should see the index in Kibana at most 1min after adding the table to Clickhouse.

[marksmansj]

Have you tried to wait 1 minute (at most)? Quesma refreshes indexes from Clickhouse every 1min by default, so you should see the index in Kibana at most 1min after adding the table to Clickhouse.

The problem still exists. if I specify the database name in the config . such as ops .

  - name: clickhouse-instance
    type: clickhouse-os
    config:
      url: clickhouse://192.168.60.211:9000
      user: admin
      password: 12345
      database: ops

it can find the tables in this database . but when I remove the database. it only can discover the tables in database "default"

[trzysiek]

Thanks, I'll take a look into that right now.
I'm pretty sure we haven't supported such a setup yet and starting to do so isn't that trivial. I'll try to come up with some solution for you today.

[pdelewski]

There is already PR for that, however we have to validate if it works