Advanced Analytica Advanced Analytica: IBOM
← All connectors
Yellowbrick logo

Destination connector

Yellowbrick

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

Version 0.0.4 community alpha

Yellowbrick

Set up the Yellowbrick destination connector using this guide.

Prerequisites

Airbyte Cloud only connects to Yellowbrick instances using SSL or TLS encryption. TLS is used by default. Otherwise, follow the open-source instructions below.

You’ll need this information to configure the Yellowbrick destination:

  • Host - The server’s host name.
  • Port - The server’s listening port. The default is the PostgreSQL™ standard port number (5432).
  • Username
  • Password
  • Default Schema Name - Specify the schema or several comma-separated schemas for the search-path. These schemas resolve unqualified object names in statements executed over this connection.
  • Database - The database name. By default, you’ll connect to a database matching the user name.
  • JDBC URL Params (optional)

Refer to this guide for more details

Configure network access

Make sure Airbyte can access your Yellowbrick database. If it’s within a VPC, allow access from the IP exposing Airbyte.

Step 1: Set up Yellowbrick

Permissions

Check whether Yellowbrick has JSON support enabled:

SHOW show enable_full_json;

If enable_full_json is off, enable it with:

ALTER SYSTEM SET enable_full_json TO TRUE;

or

ALTER USER "<user>" SET enable_full_json TO TRUE;

After either command, run:

SELECT pg_reload_conf();

Your Yellowbrick user needs these permissions:

  • Create tables and write rows.
  • Create schemas, for example:

Create this user by running:

CREATE USER airbyte_user WITH ENCRYPTED PASSWORD '<password>';
GRANT CREATE, TEMPORARY ON DATABASE <database> TO airbyte_user;

You can use an existing user, but a dedicated user for Airbyte is strongly recommended.

Step 2: Set up the Yellowbrick connector in Airbyte

Choose a target database

Choose an existing database or create one to store data synced from Airbyte.

Naming conventions

From Yellowbrick SQL Identifiers syntax

Unquoted SQL identifiers have these restrictions:

  • Unquoted SQL identifiers must begin with a letter (a-z) or underscore (). The pg prefix is reserved for system objects and isn’t allowed.
  • Later characters can be letters, digits (0-9) or underscores. Unquoted SQL identifiers aren’t case-sensitive.
  • Special characters, including # and $, aren’t allowed anywhere in an unquoted identifier.
  • Unquoted SQL identifiers aren’t case-sensitive.
  • Quoted or delimited identifiers are enclosed in double quotes (”). They’re case-sensitive and can contain uppercase letters, lowercase letters and special characters. However, object names can’t contain double quotes.
  • Database names can’t contain spaces.

:::info

The Airbyte Yellowbrick destination creates raw tables and schemas with unquoted identifiers, replacing special characters with underscores. It creates final tables and their columns with quoted identifiers, preserving case sensitivity.

:::

For Airbyte Cloud:

  1. Log into your Airbyte Cloud account.

  2. In the left navigation bar, click Destinations. Then click new destination in the top-right corner.

  3. On the Set up the destination page, name the Yellowbrick connector. Select Yellowbrick from the Destination type dropdown.

  4. Enter a name for your source.

  5. For Host, Port and DB Name, enter your Yellowbrick database’s hostname, port number and name.

  6. List the Default Schemas. :::note Schema names are case-sensitive. The ‘public’ schema is set by default. You can use multiple schemas simultaneously. If you don’t set schemas explicitly, all existing schemas will sync. :::

  7. For User and Password, enter the username and password you created in Step 1.

  8. For Airbyte Open Source, enable the switch to connect using SSL. Airbyte Cloud uses SSL by default.

  9. For SSL Modes, select:

    • disable to disable encrypted communication between Airbyte and the source
    • allow to enable encrypted communication only when the source requires it
    • prefer to allow unencrypted communication only when the source doesn’t support encryption
    • require to always require encryption. The connection will fail if the source doesn’t support encryption.
    • verify-ca to always require encryption and verify that the source has a valid SSL certificate
    • verify-full to always require encryption and verify the source’s identity
  10. To customise the JDBC connection beyond common options, enter additional supported JDBC URL parameters in the JDBC URL Parameters (Advanced) field. Separate each key-value pair with &.

    Example: key1=value1&key2=value2&key3=value3

    AirByte adds these parameters to the end of the JDBC URL used to connect to your Yellowbrick database.

    The connector now supports connectTimeout and defaults to 60 seconds. Setting connectTimeout to 0 seconds sets the longest available timeout.

    Note: Don’t use these keys in the JDBC URL Params field because Airbyte will overwrite them: currentSchema, user, password, ssl, and sslmode.

    :::warning This is an advanced configuration option. Use it carefully. :::

  11. For SSH Tunnel Method, select:

    • No Tunnel to connect directly to the database
    • SSH Key Authentication to use an RSA Private as your secret for the SSH tunnel
    • Password Authentication to use a password as your secret for the SSH tunnel

    :::warning Airbyte Cloud requires encrypted communication. Select SSH Key Authentication or Password Authentication if your SSL Mode is disable, allow or prefer. Otherwise, the connection will fail. :::

  12. Click Set up destination.

Supported sync modes

Sync modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Schema map

Raw table output schema

Each stream maps to a separate raw table in Yellowbrick. By default, raw tables are created in the airbyte_internal schema. You can override this in the configuration. Each table contains 3 columns:

  • _airbyte_raw_id: a uuid Airbyte assigns to each processed event. Its Yellowbrick column type is VARCHAR.
  • _airbyte_extracted_at: a timestamp showing when the event was pulled from the data source. Its Yellowbrick column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_loaded_at: a timestamp showing when the row was processed into the final table. Its Yellowbrick column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_data: a json blob containing the event data. Its Yellowbrick column type is JSONB.

Final table data type mapping

Airbyte TypeYellowbrick Type
stringVARCHAR
numberDECIMAL
integerBIGINT
booleanBOOLEAN
objectJSONB
arrayJSONB
timestamp_with_timezoneTIMESTAMP WITH TIME ZONE
timestamp_without_timezoneTIMESTAMP
time_with_timezoneTIME WITH TIME ZONE
time_without_timezoneTIME
dateDATE

Tutorials

  • Coming soon.

Namespace support

This destination supports namespaces. The namespace maps to a Yellowbrick schema.

Changelog

Expand to review
VersionDatePull RequestSubject
0.0.42024-03-17#48475Use JSONB functionality behind the scenes.
0.0.32024-08-06#43342Remove explicit Kotlin dependency.
0.0.22024-05-17#38329Update CDK
0.0.12024-03-02#35775Initial release
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.