Connect to Vertica Using Astera Centerprise’s Built-in Connector

By |2020-06-30T10:18:00+00:00June 23rd, 2020|

Vertica is a scalable analytics platform that can be optimized for both load and query performance. The high availability architecture and parallel-processing SQL query engine make Vertica the preferred database for countless companies looking to scale up.

Owing to Vertica’s popularity, Astera Centerprise has recently introduced a built-in connector that enables easy, code-free access to the database. Once the connection is established, Vertica can be configured both as source and destination objects to read and write data. Astera Centerprise also features pushdown capabilities, allowing businesses to transform the data after it has been loaded on to the Vertica database.

Analyze Data without Hassle

Astera Centerprise’s code-free environment makes connecting to Vertica database quick and hassle-free. Its out-of-the-box connectivity enables Vertica database integration with various file formats, databases, and REST APIs.

Users can extract and load data from/to Vertica simply by configuring the database connection. Source data can also be cleaned, filtered, checked for integrity and inaccuracies, and modified in a number of other ways using the drag-and-drop transformations in Astera Centerprise.

Vertica as a Source Object

The following dataflow shows Vertica being used as a source to extract data and load it on to a PostgreSQL database.

Transferring data from Vertica to Postgres

Figure 1: Dataflow showing data transfer from Vertica table to a PostgreSQL table

To create this dataflow, the database table source object is dragged-and-dropped from the Toolbox. Next, the database properties are configured, and the correct option is selected from the Data Provider drop-down list to connect to Vertica.

Vertica source object properties

Figure 2: A Vertica database table being set up as a source object

In this example, we will point the source object to the Customer Export table in the Properties page and select Full Load as the Read Strategy.

Picking the right Vertica database table

Figure 3: Selecting the table and the read strategy for the Customer Export table

Next, a database table destination is dragged and dropped on to the designer window. This time, PostgreSQL is selected as the data provider.

Configuring the Postgres destination table

Figure 4: Properties of the PostgreSQL destination table

A new table is created and a data loading option is selected to write all the data on to the destination database table in a single batch.

Selecting the load strategy for Postgres

Figure 5: Selecting the load option for the Postgres destination table

Using Vertica as a Destination Object

Astera Centerprise can also be used to transform data from another source and load it to a Vertica database table. The following example illustrates how to configure Vertica database as a destination object.

Transferring data from a mySQL table to a Vertica table

Figure 6: Filter transformation being applied to data from a mySQL Customer table and loaded to the Vertica FilteredbyID table

In this case, our source object is a MySQL table named Customer. A Filter transformation is applied to the data in the Customer table to filter the records with the required Store ID.

The output of the Filter transformation is then mapped on to a Vertica database destination object.

Configuring the Vertica destination table

Figure 7: Properties of the Vertica destination table

Vertica is selected as the Data Provider and a new table named FilteredbyID is created. This is done to load all the data from the Filter transformation to a new table instead of existing ones.

Creating a new Vertica database table

Figure 8 Selecting the data load option for the FilteredbyID Vertica destination table

How Does the Vertica Connector Help with Data Integration?

The Vertica connector for Astera Centerprise allows easy integration with numerous on-premise and online platforms and file formats. Centerprise can then be used to process raw data using database lookups, aggregators, expressions, and more to extract value from data.

Leverage Code-free Connection

Using Vertica as a source or destination and leveraging its powerful analytical and storage capabilities is as simple as selecting the option from a drop-down list of supported platforms.

Ensure Data Accuracy

With Astera Centerprise, users can connect to Vertica database and update trusted and accurate data as and when needed. Centerprise features a number of data cleansing and validation transformations that can be used to maximize accuracy and relevance.

Reduce Time-to-Insights

Astera Centerprise’s automation capability removes multiple unnecessary steps, such as manually running dataflows and workflows every time the data is updated, from the data integration process. This reduces the time-to-insight and makes the entire process more efficient.

 

Vertica is the database of choice for countless enterprises owing to its flexibility, scalability, and performance. With Centerprise’s Vertica connector, business users can perform quick analysis of data from heterogeneous sources and make data-driven decisions to make their business grow.

Download the free Astera Centerprise trial today to experience connectivity to a wide range of platforms and integrate your data easily.