The Generic SQL Driver provides connectivity between IOTA and SQL Servers. The driver is state-less, i.e. it doesn't maintain source connections and data caches. The single Generic SQL Driver service can support multiple connections to different SQL servers.
Supported Database Types
- Oracle DB
- PostgreSQL
- MSSQL server
- MySQL
- DataBricks
- Snowflake
- Azure Synapse Analytics SQL Pool
Supported Functions
| IOTA Function | Supported? | Type | Description |
|---|---|---|---|
| tagsearch | 🟢 | SEARCH | Tag search |
| elementsearch | 🟢 | SEARCH | Asset search (not for tabular table schemas) |
| timeframesearch | ⚫ | SEARCH | Timeframe search |
| snapshot | 🟢 | DATA | Retrieve snapshot value |
| snapshot with a global time control | 🟢 | DATA | Retrieve snapshot value for specified time |
| eventpipepoll | 🟢 | DATA | Signup for server side event polling for snapshot |
| plotted | 🟢 | DATAHISTORY | Retrieve plotted (raw) values |
| interpolated | ⚫ | DATAHISTORY | Retrieve interpolated values |
| discrete | ⚫ | DATAHISTORY | Retrieve discrete values |
| writedata | 🟢 | GENERAL | Write values to specified FQNs |
| version | 🟢 | GENERAL | Get driver's version |
| hosts | 🟢 | GENERAL | Register hosts |
| functions | 🟢 | GENERAL | List supported functions |
| workbooks | ⚫ | GENERAL | Get existing workbooks |
| datasources | ⚫ | GENERAL | Get existing data sources |
Supported Object Types
| IOTA Type | Supported? | Source Type |
|---|---|---|
| Tag | 🟢 | Data table |
| Asset | 🟢 | Data table |
| Timeframe | ⚫ |
Diagram
Technical Specification
| Description | Value |
|---|---|
| Latest Version | 1.2.23.0 |
| Development Language | GoLang |
| Processor Architecture | 64-bit |
| Supported Operating Systems | Windows/Linux/OSX |
| Deployment Size | 34.3 Mb |
| Data Modes | Read/Write* |
| Request/Response Pattern | Asynchronous |
| Source Communication | Database Clients for each DB type |
| Back-end Communication | NATs message bus |
| Message Bus Driver Type | generic-sql |
| Near Real-Time Data Updates | Yes |
| Multiple Database Connections | Yes |
* writes only timeseries data
Dependencies
The Generic SQL driver is installed alongside the main IOTA Vue service within a containerized environment such as Kubernetes, Docker or Podman.
Security
Source Security
The Generic SQL Driver current version only supports explicit user/password authentication mechanism for each configured server connection.
IOTA API (back-end) Security
The Generic SQL Driver uses NATs message bus to communicate with the back-end IOTA API. The two-way data traffic is encrypted using Transport Layer Security (TLS) on port 443. In addition, the message bus communication security model uses a public-key signature system based on Ed25519 called NKeys. With NKeys, the server can verify identities without ever storing or seeing private keys. The authentication system works by requiring a connecting client to provide its public key and digitally sign a challenge with its private key. The server generates a random challenge with every connection request, making it immune to playback attacks. The generated signature is validated against the provided public key, thus proving the client's identity. If the public key is known to the server, authentication succeeds.