Connecting to Vertica Database

Vertica is one of the fastest and scalable advanced analytics platforms, trusted by data-driven enterprise across the globe. It is a column-oriented relational database, which means that the data is stored in columns instead of rows. This reduces the memory consumption and disk I/O. Vertica databases are designed to manage large and growing volumes of data and provide fast query performance when used for data warehouses and other query-intensive applications.

Astera offers native connectivity to Vertica databases at both source and destination points. The connectivity also extends to other objects such as Database Lookup, SQL Statement Lookup in transformations, SQL Script Task in a workflow, and Database Diff Processor in Database Write Strategies.

In this article, we will cover:

1. A use case where Vertica Database is being used in Astera.

2. Connecting to a Vertica database in Astera.

3. Different data writing options for a database destination in Astera.

Use Case

In this use case, we will be connecting to a Vertica Database and integrating data from it in a dataflow.

We have Sales by Category data stored in a Database Table Source and we want to calculate the total amount of sales closed for each category.

To learn more about how a Database Table Source works in Astera, click here.

We will use the Aggregate transformation object and apply Sum function to the ProductSales field while grouping the dataset by CategoryID.

To learn more about how the Aggregate transformation works in Astera, click here.

If we preview the output, we can see the total ProductSales for each category.

Next, we can write this data to a database table using the Database Table Destination in Astera.

We will be using the Database Table Destination object.

1. To configure the Database Table Destination object, right-click on its header and select Properties from the context menu.

As soon as you have selected the Properties option from the context menu, a new window will open.

This is where you can configure the properties for the Database Table Destination object.

2. The first step is to specify the connection in the Database Connection window for the source object.

Select Vertica as your Data Provider from the drop-down list.

• Enter the required credentials for your Vertica data provider account.

  1. User ID

  2. Password

  3. Server Name

  4. Database

  5. Port

• Test Connection to make sure that your database connection is successful, and click Next.

Once the connection is established, you can Pick Table or Create Table and modify Writing Options for Vertica Database on the Properties screen.

You have successfully configured Vertica as your database provider for the Database Table Destination object. The data will now be written to the Vertica Database once the dataflow is run.

We have successfully connected to Vertica Database and completed our use case in Astera.