QueryHub Data Source Configuration and Query Examples¶
This article describes the connection configuration, native query, and DQL Like query examples for different data sources, as well as how to view the queryable tables, metrics, indexes, collections, and fields.
- MySQL · PostgreSQL · Prometheus · Elasticsearch · MongoDB
- For the data source ID, name, and description, as well as the common steps for testing connections, permissions, and saving, see Overview and Configuration. The data source ID must be entered manually.
- For the actual query entry point and the steps to refresh results, see Query and Application. QueryHub supports native queries and DQL Like; both are entered manually, and the query modes actually enabled for the selected instance take precedence.
The authentication fields in each table indicate the specific required conditions. After you select the corresponding authentication method, fill in the matching credentials. Fill in the certificate fields according to the TLS requirements. Addresses, accounts, passwords, tokens, and certificates are provided by the data source administrator or operations personnel.
The datasource in a DQL Like expression must match the ID of the currently selected instance, and source must match the data source type. Fill in the query objects and fields according to the actual data. The following examples are read-only, and the account also needs read permission on the target data.
Common TLS Configuration¶
All five data sources provide TLS settings. When you create a connection, disable is used by default and subfields such as certificates are hidden. After you select a mode other than disable, fill in the corresponding fields according to the server requirements. For the fields specific to each type, see the corresponding sections below.
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| TLS mode | No | Defaults to disable. When TLS is required, select the corresponding mode according to the server requirements, as confirmed by the data source administrator |
| CA certificate | No | Content of the CA certificate used to verify the server certificate; provided by the administrator when needed. Paste the certificate text |
| Client certificate | Required depending on authentication method | Fill in when the server requires client certificate authentication. Issued by the administrator and must be filled in as a pair with the client private key |
| Client private key | Required depending on authentication method | Private key content that matches the client certificate, provided by the administrator and must be filled in as a pair with the client certificate |
| Server name | No | Server name used for TLS verification, usually the domain name in the certificate, as confirmed by the administrator |
| Skip certificate verification | No | When selected, server certificate verification is skipped. It cannot be configured together with a CA certificate, nor used together with the verify or verify-full modes; usually left unselected |
MySQL¶
Connection Fields¶
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| Host address | Yes | Domain name or IP of the MySQL service, obtained from the database administrator or the cloud database connection information. Do not enter the management console URL |
| Port | No | Prefilled with 3306 on the page; enter the actual value when using a custom port |
| Default database | No | Name of the database used by default for the connection, not a table name. Table names without a database prefix in queries use this as the default database, as confirmed by the database administrator |
| Username | No | Enter a database account with read permission on the target databases and tables, according to the service authentication requirements |
| Password | No | Password of the above database account, provided by the account administrator |
| Character set | No | Character encoding used by the connection. Select a character set from the drop-down list that matches the database requirements, as confirmed by the database administrator |
| Parse datetime | No | Controls whether database datetime values are parsed as time types; selected by default on the page. It does not set the time range of the query |
Query Examples¶
The examples use the id, total, and status fields in the orders table. Replace them with the actual table name and fields.
Native query
Query up to 10 paid orders, sorted by order ID:
DQL Like
queryhub(source="mysql", datasource="mysql-orders")::orders:(id,total) {status = "paid"} ORDER BY id LIMIT 10
View Tables and Fields¶
The following are all executed manually in Native query, one statement per run.
View the tables that the account has permission to see in the current database:
View the column names, types, and other definitions of orders:
These queries return metadata tables; they do not mean that the page automatically scans or generates all fields. The visible scope is affected by the database account permissions.
Aggregation Query Example¶
Enter the following SQL in Native query to aggregate amounts by order status, then click Refresh in the upper right corner to view the results:
SELECT status, SUM(total) AS total_amount
FROM orders
GROUP BY status
ORDER BY total_amount DESC
LIMIT 10
When you need to limit the time range, write the condition in the SQL using a time field that actually exists; adjusting the page time range does not automatically add a time filter to arbitrary SQL.
PostgreSQL¶
Connection Fields¶
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| Host | Yes | Domain name or IP of the PostgreSQL service, provided by the database administrator or the cloud database connection information |
| Port | No | Prefilled with 5432 on the page; enter the actual value when using a custom port |
| Database | Yes | Name of the database to be accessed by this connection, not a Schema or table name |
| Username | Yes | Database account with read permission on the target Schema and tables, provided by the database administrator |
| Password | Required depending on authentication method | Required when password authentication is used; it can be left empty when the server allows client certificate authentication and a paired client certificate and private key have been filled in |
| Schema | No | Namespace where the table resides; prefilled with public on the page. As confirmed by the database administrator, use the same scope in queries |
Query Examples¶
The examples use the orders table in the public Schema. Replace the Schema, table name, and fields according to the actual configuration. Database and Schema are two different levels of scope.
Native query
DQL Like
View Tables and Fields¶
In Native query, execute one statement at a time, and replace public with the configured Schema. The results are affected by the visible scope of the database account.
View table names:
View the field names and types of orders:
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position LIMIT 50
When the table names are the same but the Schemas differ, use the full schema.table in the query, consistent with the scope allowed for the selected instance.
Prometheus¶
Connection Fields¶
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| Service address | Yes | Base address of the Prometheus query service, for example http://prometheus.example.com:9090. This HTTP example corresponds to TLS mode disable; when using an HTTPS address, you must select a TLS mode other than disable. Provided by the service administrator. Do not enter the management page address or the complete /api/v1/query path |
| Authentication method | No | No authentication by default. Basic or Bearer can be selected; the two authentication methods are mutually exclusive |
| Username | Required depending on authentication method | Required when Basic authentication is selected, provided by the service or gateway administrator. It is not a Workspace login account |
| Password | Required depending on authentication method | Required when Basic authentication is selected, corresponding to the above username |
| Bearer token | Required depending on authentication method | Required when Bearer authentication is selected, issued by the administrator. Do not include the Bearer prefix; it is not a Workspace API Key |
Query Examples¶
The query object is a metric, and job, instance, and others are sample labels. Replace the examples with the actual metrics and labels.
Native query
Enter the complete JSON in the native query input box, specify the query operation via operation, and write the PromQL in statement.
Instant query:
Range query:
{"operation":"query_range","statement":"up{job=\"fixture\"}","arguments":{"start":"#{startTime}","end":"#{endTime}","step":15}}
#{startTime} and #{endTime} reference the chart's time range, and the frontend automatically converts the time to seconds; the unit of step is also seconds, which is 15 seconds in the example. These parameters are filled in arguments of the JSON.
DQL Like
Query Results¶
In the Studio table, "Name" and "Labels" come from the series' name and tags, respectively. The time column is time; the API uses Unix millisecond timestamps, and the page displays them in a formatted way. Value column names vary with the metric name or expression.
View Metrics and Labels¶
Enter the following JSON in the Native query input box, and put the PromQL for viewing metrics in statement. Use known names or a narrow-range regular expression to view the metrics that currently have samples:
The example matches only up and http_requests_total. After execution, view the names and labels of the matched series in the table.
To view labels such as job and instance, run the instant query JSON above and check "Labels" in the result table. If there are no samples, check the label conditions or adjust the query time.
These results only reflect the currently matched samples; they do not represent the complete set of labels or the metric catalog, and they cannot be used to determine that other metrics do not exist.
Elasticsearch¶
Connection Fields¶
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| Service address | Yes | Base address of the Elasticsearch query service, for example https://es.example.com:9200. This HTTPS example requires a TLS mode other than disable; if an HTTP address is used, disable must be selected. Provided by the administrator. Do not enter the Kibana address or the complete _search path |
| Authentication method | No | No authentication by default. Basic or Bearer can be selected; the two authentication methods are mutually exclusive |
| Username | Required depending on authentication method | Required when Basic authentication is selected; it needs read permission on the target index |
| Password | Required depending on authentication method | Required when Basic authentication is selected, corresponding to the above username, provided by the account administrator |
| Bearer token | Required depending on authentication method | Required when Bearer authentication is selected, issued by the administrator, without the Bearer prefix; do not enter an API Key as a Bearer token |
| Index | Yes | Index queried by this instance, for example orders, as confirmed by the data administrator. The objects in DQL Like must be consistent with the scope configured for the instance |
Query Examples¶
The examples assume the index configured for the instance is orders. When querying documents, use a table chart to view the results.
Native query
Enter the complete JSON in the native query input box, use search for operation, and put the request body in arguments.body:
DQL Like
In the table chart, expand the document JSON to view the document. Projected fields remain within the JSON and do not automatically become separate table columns.
View Indexes and Fields¶
The queryable indexes are subject to the current instance configuration. Enter the following JSON in the Native query input box, then read the index column of the matched results in the table:
When the index has no matching documents, rely on the instance configuration and do not use an empty result to determine whether the index exists.
To view document samples and their keys, fill in the native query as follows:
In the table chart, expand the document JSON of the result to view the fields. Documents may contain sparse fields, and you can read more samples within a limited scope; this is not the complete mapping, and the complete definition must be viewed on the Elasticsearch side.
MongoDB¶
Connection Fields¶
| Configuration Item | Required | Meaning and How to Fill In |
|---|---|---|
| Host list | Yes | In practice, only 1 hostname or IP can be entered, provided by the database administrator; the port is entered separately |
| Port | No | Prefilled with 27017 on the page; enter the actual value when using a custom port |
| Authentication method | No | No authentication by default. After password authentication is selected, the username, password, and authentication database fields are displayed |
| Username | Required depending on authentication method | Required when password authentication is selected. Use a MongoDB account with permission to read the target database and collection |
| Password | Required depending on authentication method | Required when password authentication is selected, corresponding to the above username, provided by the account administrator |
| Authentication database | No | Optional for password authentication. Indicates the database where the account identity information resides, which can be admin, as confirmed by the administrator |
| Query database | Yes | Name of the database from which business documents are read. admin, config, and local cannot be used |
| Collection | Yes | Enter only the collection name, for example items, without a database name prefix. Native find queries use this collection |
Query Examples¶
The examples assume the instance is configured with the items collection, and the documents contain the business fields id and status. The query database and the authentication database may differ; use a table chart to view document results.
Native query
Enter the complete JSON in the native query input box, use find for operation, and write the filter, projection, and return count in arguments. The database and collection follow the current instance configuration:
{"operation":"find","arguments":{"filter":{"status":"paid"},"projection":{"id":1,"status":1},"limit":10}}
filter defines the filter conditions, projection specifies the document fields to read, and limit limits the number of returned documents. The id in the example is a business field and is not the same as MongoDB's default _id; adjust it according to the actual document structure.
DQL Like
In the table chart, expand the document JSON to view the document. Projected fields remain in the JSON and are not automatically expanded into separate columns.
View Collections and Fields¶
The queryable databases and collections are subject to the current instance configuration. The Studio entry point does not provide listCollections.
Enter the following JSON in the Native query input box to read one sample document:
In the table chart, expand the document JSON to view the document keys. Different documents may have different structures; you can change the filter conditions or appropriately increase the limited number of samples. The sample content does not represent the complete Schema and does not grant additional account permissions.