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.
-
Official comment
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
Please sign in to leave a comment.
1 comment