# Connecting to Vertica Database

Vertica is one of the fastest and most scalable advanced analytics platforms, trusted by data-driven enterprises across the globe. It is a column-oriented relational database, which means that the data is stored in columns instead of rows. This reduces 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.

To learn more about Vertica and its installation, click [here](https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/InstallationGuide/Other/InstallationOverviewAndChecklist.htm?TocPath=Installing%20Vertica|Installing%20Manually|_____1).

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](https://documentation.astera.com/astera-data-stack-v9/dataflows/sources/database-table-source).

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FPl619ojuHVqQL39Uxxg5%2F0.png?alt=media)

We will use the *Aggregate* transformation object and apply the *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](https://documentation.astera.com/astera-data-stack-v9/dataflows/transformations/aggregate-transformation).

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2Fn9UnltxAU1vKnhZRIbtq%2F1.png?alt=media)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FdbC7P3m94mXRiSGrP3gJ%2F2.png?alt=media)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FthGklZn9gLaBWyH4KH7M%2F3.gif?alt=media)

We will be using the [*Database Table Destination*](https://documentation.astera.com/astera-data-stack-v9/dataflows/destinations/database-table-destination) object.

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FTCbSnE3ldV0W9DuPemk0%2F4.png?alt=media)

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

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FUcxO269rNNgdfe86Dlx6%2F5.png?alt=media)

This is where you can configure the properties for the [*Database Table Destination*](https://documentation.astera.com/astera-data-stack-v9/dataflows/destinations/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.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FsL2ZbumTuC4fVABF4LJy%2F6.png?alt=media)

3. Enter the required credentials for your Vertica data provider account:
   * *User ID*
   * *Password*
   * *Server Name*
   * *Database*
   * *Port*

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FnXJnITvgXRykWAQHYK4E%2F7.png?alt=media)

4. *Test Connection* to make sure that your database connection is successful and click *Next*.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2Fk3PQfJpObjR49B6YsCez%2F8.png?alt=media)

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

To read more on the different data writing options available for a database table, click [here](https://documentation.astera.com/astera-data-stack-v9/dataflows/sources/database-table-source#database-table-options).

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FdwqU7HRxAHNWCZfewowT%2F9.png?alt=media)

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.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FEtbBAOnFpUNns2nACSYB%2F10.png?alt=media)

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