QuestDB
The QuestDB connection reads from and writes to a QuestDB server using QWP (QuestDB Wire Protocol). The connection supports SQL queries, table creation, and data ingestion, including support for ingesting parquet files.
Connection Settings
Host
The hostname or IP address of the QuestDB server.
Port
The port the QuestDB server listens on. The default is 9000.
Authentication Method
Specifies how the connection authenticates to the QuestDB server. Options are None, Token, and Basic.
- None connects without credentials.
- Token authenticates using a bearer token.
- Basic authenticates using HTTP basic authentication with a username and password.
Token
The bearer token used to authenticate to the QuestDB server.
Username
The username used to authenticate to the QuestDB server.
Password
The password used to authenticate to the QuestDB server.
Use TLS
When enabled, the connection uses a secure WebSocket (wss) instead of a plain WebSocket (ws).
Ignore Self-Signed Certificate
When enabled, TLS certificate validation is disabled. This is only intended for development against a server using a self-signed certificate. This setting is shown when Use TLS is enabled.
Compression
The wire compression used for traffic from the QuestDB server. Options are Raw (no compression), Zstd, and Auto. Compression only applies to reads. Writes are not compressed.
Input Settings
SQL Query
The SQL query used to read data from QuestDB. See the QuestDB documentation for supported SQL syntax.
The query results are returned as an array of rows, where each row is an object with an attribute for each column. The table below shows how QuestDB column types are mapped to Intelligence Hub data types. Columns with other types are omitted from the results.
| QuestDB Type | Data Type |
|---|---|
| BOOLEAN | Boolean |
| BYTE | Int8 |
| SHORT | Int16 |
| CHAR | String |
| INT, IPV4 | Int32 |
| FLOAT | Real32 |
| DOUBLE | Real64 |
| LONG, GEOHASH | Int64 |
| TIMESTAMP, TIMESTAMP_NS, DATE | DateTime |
| SYMBOL, VARCHAR, UUID | String |
Output Settings
The QuestDB connection only writes complex (object) values, written as one row per object. Arrays of objects are written as multiple rows. Nested objects are flattened, so a child attribute is written to a column named using the parent and child attribute names separated by an underscore (for example, motor_speed).
Attributes are matched to table columns by name, ignoring case. Attributes that don’t match a column in the table are ignored, and null values are not written.
Table
The QuestDB table to write data to.
Create Table
Controls how the table definition is managed. Options are Off, Create, and Create & Update.
- When Off is used, it’s assumed that the table exists.
- When Create is used, it’s assumed the connection manages the table and it’s created once. If the table doesn’t exist it’s created using the schema of the payload.
- When Create & Update is used, it’s assumed the connection manages the table and updates the table on schema change.
Tables are created as WAL tables with a _timestamp column as the designated timestamp, partitioned according to Partition By. The table below shows how Intelligence Hub data types are mapped to QuestDB column types when creating or updating a table.
| Data Type | QuestDB Type |
|---|---|
| Boolean | BOOLEAN |
| Int8 | BYTE |
| UInt8 | SHORT |
| Int16 | SHORT |
| UInt16 | INT |
| Int32 | INT |
| UInt32 | LONG |
| Int64 | LONG |
| UInt64 | LONG |
| Real32 | FLOAT |
| Real64 | DOUBLE |
| DateTime | TIMESTAMP |
| String | VARCHAR |
Partition By
The partitioning strategy used when the connection creates the table. Options are Hour, Day, Week, Month, and Year. The default is Day.
Partitioning only applies when the table is created.
Timestamp
The case-sensitive name of a payload attribute to use as the table’s designated timestamp. The attribute must hold an ISO-8601 UTC string value or epoch value in milliseconds, and it is not written as a column of its own.
When left blank, or when the attribute is missing or doesn’t hold a usable value, the payload’s _timestamp attribute is used. If neither is available, QuestDB assigns the time the row is written.
Ingesting Parquet Files
QuestDB can import parquet files using the read_parquet() function. The file must be accessible to the QuestDB server. For example, the following query appends the rows in asset_values.parquet to the asset_values table each time the input is read.
INSERT INTO asset_values
SELECT asset, attribute, value, quality, timestamp
FROM read_parquet('asset_values.parquet');