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:
-
Log into your Airbyte Cloud account.
-
In the left navigation bar, click Destinations. In the top-right corner, click new destination.
-
On the Set up the destination page, name the Vertica connector. Then select Vertica from the Destination type dropdown.
-
Enter a name for your source.
-
For Host, Port, and DB Name, enter your Vertica database’s hostname, port number, and name.
-
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. :::
-
For User and Password, enter the username and password you created in Step 1.
-
For Airbyte Open Source, toggle the switch to connect using SSL. Airbyte Cloud uses SSL by default.
-
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
-
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
connectTimeoutand 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, andsslmode.:::warning This is an advanced configuration option. Use it with caution. :::
-
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. :::
-
Click Set up destination.
Supported sync modes
The Vertica destination connector supports these sync modes:
| Feature | Supported?(Yes/No) | Notes |
|---|---|---|
| Full Refresh Sync | Yes | |
| Incremental - Append Sync | No | |
| Incremental - Append + Deduped | No | |
| Namespaces | No |
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 isVARCHAR._airbyte_emitted_at: a timestamp showing when the event was pulled from the data source. Its Vertica column type isTIMESTAMP WITH TIME ZONE._airbyte_data: a json blob containing the event data. Its Vertica column type isJSONB.
Changelog
Expand to review
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 0.1.0 | 2023-05-29 | #25682 | Add Vertica Destination |