Advanced Analytica Advanced Analytica: IBOM
← All connectors
Vertica logo

Destination connector

Vertica

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

Version 0.1.0 archived alpha

Vertica

Set up the Vertica destination connector using this guide.

Prerequisites

To use the Vertica destination, you’ll need:

  • A Vertica server running version 11.0 or later

Airbyte Cloud only supports SSL or TLS encrypted connections to your Vertica instances. TLS is used by default. You can follow the open-source instructions below for everything else.

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

  • Host - The server’s host name.
  • Port - The port where the server listens. The default is the standard VSQL™ port number (5433).
  • 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 through this connection.
  • Database - The database name. By default, the connection uses 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 Vertica database. If your database is within a VPC, allow access from the IP exposing Airbyte.

Step 1: Set up Vertica

Permissions

You need a Vertica user with these permissions:

  • Can create tables and write rows.
  • Can create schemas, for example:

Create a user with these permissions by running:

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

You can use an existing user. However, creating a dedicated user for Airbyte is highly recommended.

Step 2: Set up the Vertica connector in Airbyte

Choose a target database

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

Naming conventions

From Vertica SQL Identifiers syntax:

  • SQL identifiers and key words must start with a letter or underscore (_). Letters include a-z, letters with diacritical marks and non-Latin letters.

  • Later characters can be letters, underscores, digits (0-9) or dollar signs ($).

    The SQL standard doesn’t allow dollar signs in identifiers, so using them may make applications less portable. The SQL standard won’t define a key word containing digits or starting or ending with an underscore. Identifiers in these forms are therefore safe from possible conflicts with future standard extensions.

  • The system uses no more than NAMEDATALEN-1 bytes of an identifier. You can write longer names in commands, but the system truncates them. By default, NAMEDATALEN is 64, so identifiers can contain up to 63 bytes.

  • Quoted identifiers can contain any character except the character with code zero. (To include a double quote, write two double quotes.) This supports otherwise invalid table or column names, including names with spaces or ampersands. The length limit still applies.

  • Quoted identifiers are case-sensitive, while unquoted names are always converted to lower case.

  • Use consistent quoting for each name to make your applications more portable and reduce errors. Either always quote a name or never quote it.

The Airbyte Vertica destination creates tables and schemas with Unquoted identifiers where possible. It uses Quoted Identifiers when names contain special characters.

For Airbyte Cloud:

  1. Log into your Airbyte Cloud account.

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

  3. On the Set up the destination page, name the Vertica connector. Then select Vertica from the Destination type dropdown.

  4. Enter a name for your source.

  5. For Host, Port, and DB Name, enter your Vertica 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 several schemas at once. If you don’t specify schemas, 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, toggle 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. Use key-value pairs separated by the symbol &.

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

    These parameters are added to the end of the JDBC URL that the AirByte uses to connect to your Vertica database.

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

    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 with caution. :::

  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 you selected disable, allow, or prefer as the SSL Mode. Otherwise, the connection will fail. :::

  12. Click Set up destination.

Supported sync modes

The Vertica destination connector supports these sync modes:

FeatureSupported?(Yes/No)Notes
Full Refresh SyncYes
Incremental - Append SyncNo
Incremental - Append + DedupedNo
NamespacesNo

Schema map

Output schema

Each stream maps to a separate table in Vertica. Each table contains three columns:

  • _airbyte_ab_id: a uuid that Airbyte assigns to each processed event. Its Vertica column type is VARCHAR.
  • _airbyte_emitted_at: a timestamp showing when the event was pulled from the data source. Its Vertica column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_data: a json blob containing the event data. Its Vertica column type is JSONB.

Changelog

Expand to review
VersionDatePull RequestSubject
0.1.02023-05-29#25682Add Vertica Destination
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.