Running the same query against multiple databases with the same schema

I have a database per client and would like to run the same query against all clients (thousands of them), pool the results together and drive dashboards from them.

1 comment

Official comment

Jay June 05, 2018 18:17
The agent supports a way to query multiple databases using a wildcard token.

In the agent distribution, under examples folder, see datasource_example_mysql.json and query_example_mysql.json for an example.

Datasource syntax:
```
/* Wildcard token to connect to multiple databases with the same schema */
{
  "name":"demoMySQLGroup",
  "url":"localhost:3306/app_${c9_wildcard}_somepostfix",
  "datasource":"mysql",
  "userId":"a",
  "password":"b"
}
```
In this example, we will pick up any database that contains app_XXX_somepostfix in the database name.

Query Example:
```
{
  "entityName":"Group Run",
  "dsName":"demoMySQLGroup",
  /* Executes against multiple databases and combines the result */
  "queryStr":"select * from sometable",
  /* Optional c9QL that runs on the combined query results.*/
  "c9QLFilter":"select count(*) as Total count, \"MySQL Counts\" as MySQL",
  "overrideVals":{
    "replaceAll":true
  }
}
```
The results can be queried, visualized and analyzed as needed.

Note: This is currently supported on SQL databases and MongoDB datasources only. Contact us if you need support for another database.
Comment actions Permalink

Please sign in to leave a comment.