SingleStore
Overview
SingleStore is a distributed SQL database for high-throughput transactions, including inserts and upserts. It provides low-latency analytics and context from real-time vector data.
:::info The Airbyte SingleStore destination uses Destination V2 with Typing and Deduping support. Legacy V1 isn’t supported. :::
Supported sync modes
| Sync mode | Supported? |
|---|---|
| Full Refresh - Overwrite | Yes |
| Full Refresh - Append | Yes |
| Full Refresh - Overwrite + Deduped | Yes |
| Incremental Sync - Append | Yes |
| Incremental Sync - Append + Deduped | Yes |
Get started
Requirements
- A SingleStore instance
- Allow connections from Airbyte to your SingleStore database, if they exist in separate VPCs.
- Create a dedicated Airbyte user with minimum permissions to CRUD tables and CREATE/EXECUTE functions.
This SQL query shows how to grant an Airbyte user the minimum required permissions:
CREATE USER airbyte IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, CREATE DATABASE ON db.* TO 'airbyte'@'%';
- Before configuring the SingleStore destination in Airbyte, set the ‘local_infile’ system variable to true.
Run the
SET GLOBAL local_infile = truequery to set it. This is required because Airbyte usesLOAD DATA LOCAL INFILEto load data into the raw table.
To configure the SingleStore destination, you’ll need the following information:
- Host - The server’s host name.
- Port - The port number where the server listens. It defaults to the standard port number (3306).
- Username
- Password
- Database - The database name.
- JDBC URL Params (optional)
Schema map
Raw table
Each data stream has its own raw table in SingleStore. By default, these tables use the namespace specified by the database parameter. You can override this behaviour by configuring the raw_data_schema parameter.
:::warning
Use the same database for the raw and final tables in the SingleStore connector. SingleStore doesn’t support cross-database transactions. Using one database keeps data operations and transaction management consistent.
:::
Raw table schema:
_airbyte_raw_id: a uuid that Airbyte assigns to each processed event. The SingleStore column type isVARCHAR(256)._airbyte_extracted_at: a timestamp showing when the event was pulled from the data source. The SingleStore column type isTIMESTAMP(6)._airbyte_loaded_at: a timestamp showing when the row was processed into the final table. The SingleStore column type isTIMESTAMP(6)._airbyte_data: a json containing the event data. The SingleStore column type isJSON.
Final table
After storing the data stream in the raw table, the connector parses and normalises it into the final destination table. The connector handles data casting errors during normalisation. It tracks and records any casting errors in the _airbyte_meta column. This provides visibility into issues encountered during normalisation.
Final table schema:
data columns: normalised data columns from the raw table’s _airbyte_data JSON column._airbyte_extracted_at: a timestamp showing when the event was pulled from the data source. The SingleStore column type is TIMESTAMP(6)._airbyte_meta: a column containing warnings that occurred during data normalisation.
Final table data type mapping
| Airbyte Type | SingleStore Type |
|---|---|
| string | VARCHAR(21844) |
| number | DECIMAL(38, 9) |
| integer | BIGINT |
| boolean | TINYINT(1) |
| object | JSON |
| array | JSON |
| timestamp_without_timezone | TIMESTAMP(6) |
| timestamp_with_timezone | TIMESTAMP(6) |
| time_without_timezone | TIME(6) |
| time_with_timezone | TIME(6) |
| date | DATE |
Naming limits
SingleStore restricts all identifiers to 63 characters or fewer. Column names longer than 63 characters are truncated to this length.
CHANGELOG
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 0.1.0 | 2024-05-23 | 38600 | Implement SingleStore destination V2 |
Namespace support
This destination supports namespaces. The namespace maps to a SingleStore database.