Advanced Analytica Advanced Analytica: IBOM
← All connectors
SingleStore logo

Destination connector

SingleStore

Send governed data into SingleStore with a clear, practical setup guide.

Version 0.1.0 community alpha

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 modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Get started

Requirements

  1. A SingleStore instance
  2. Allow connections from Airbyte to your SingleStore database, if they exist in separate VPCs.
  3. 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'@'%';
  1. Before configuring the SingleStore destination in Airbyte, set the ‘local_infile’ system variable to true. Run the SET GLOBAL local_infile = true query to set it. This is required because Airbyte uses LOAD DATA LOCAL INFILE to 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 is VARCHAR(256).
  • _airbyte_extracted_at: a timestamp showing when the event was pulled from the data source. The SingleStore column type is TIMESTAMP(6).
  • _airbyte_loaded_at: a timestamp showing when the row was processed into the final table. The SingleStore column type is TIMESTAMP(6).
  • _airbyte_data: a json containing the event data. The SingleStore column type is JSON.

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 TypeSingleStore Type
stringVARCHAR(21844)
numberDECIMAL(38, 9)
integerBIGINT
booleanTINYINT(1)
objectJSON
arrayJSON
timestamp_without_timezoneTIMESTAMP(6)
timestamp_with_timezoneTIMESTAMP(6)
time_without_timezoneTIME(6)
time_with_timezoneTIME(6)
dateDATE

Naming limits

SingleStore restricts all identifiers to 63 characters or fewer. Column names longer than 63 characters are truncated to this length.

CHANGELOG

VersionDatePull RequestSubject
0.1.02024-05-2338600Implement SingleStore destination V2

Namespace support

This destination supports namespaces. The namespace maps to a SingleStore database.

Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.