Skip to content

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:

SELECT id, total FROM orders WHERE status = 'paid' ORDER BY id LIMIT 10

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:

SHOW TABLES

View the column names, types, and other definitions of orders:

SHOW COLUMNS FROM 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

SELECT id, total FROM public.orders WHERE id = 1 ORDER BY id LIMIT 10

DQL Like

queryhub(source="postgres", datasource="pg-orders")::public.orders:(id,total) {id = 1} LIMIT 10

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:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'
LIMIT 20

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:

{"operation":"query","statement":"up{job=\"fixture\"}"}

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

queryhub(source="prometheus", datasource="metrics")::up:(*) {job = "fixture"}

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:

{"operation":"query","statement":"count by (__name__) ({__name__=~\"up|http_requests_total\"})"}

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:

{"operation":"search","arguments":{"body":{"query":{"match_all":{}},"size":10}}}

DQL Like

queryhub(source="elasticsearch", datasource="es-orders")::orders:(id,status) LIMIT 10

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:

{"operation":"search","arguments":{"body":{"query":{"match_all":{}},"_source":false,"size":1}}}

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:

{"operation":"search","arguments":{"body":{"query":{"match_all":{}},"size":1}}}

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

queryhub(source="mongodb", datasource="mongo-orders")::items:(id,status) {status = "paid"} LIMIT 10

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:

{"operation":"find","arguments":{"filter":{},"limit":1}}

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.