The Automated, No-Code Data Stack
Learn how Astera Data Stack can simplify and streamline your enterprise’s data management.
Connect to the Vertica Database With Astera Centerprise
Vertica is a scalable analytics platform that can optimize both load and query performance. Vertica’s performance, high availability architecture, and parallel processing SQL query engine make it a preferred database for countless companies looking to scale up.
Due to Vertica’s popularity, Astera Centerprise has recently introduced a built-in connector enabling easy, code-free access to the database. Once the connection is established, Vertica can be configured both as the source and destination objects to read and write data. Astera Centerprise also features pushdown capabilities, allowing businesses to transform the data after being loaded onto the Vertica database.
What is Vertica Database?
Vertica is a columnar data storage platform designed to manage huge volumes of data. This allows its users fast and efficient query performance along with providing high availability and scalability on enterprise servers. The prominent features of the Vertica database are:
- Column-based storage organization
- SQL interface with built-in analytics capabilities
- Compression to cut storage costs
- Compatible with programming interfaces
- High performance and parallel data transfer
Hassle-free Data Analysis
Astera Centerprise’s code-free environment makes connecting to the Vertica database easy, 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 several other ways using the drag-and-drop transformations in Astera Centerprise.
What Can Vertica Database Be Used For?
Vertica offers features and capabilities for multiple use cases. Since businesses are dealing with data from several disparate sources, the Vertica database can be used to consolidate data, replicate entire databases, and even restore backups for quicker analysis.
Vertica as a Source Object
The following dataflow shows Vertica being used as a source to extract data and load it onto a PostgreSQL database.
Figure 1: Dataflow showing data transfer from Vertica table to a PostgreSQL table
The database table source object is dragged-and-dropped from the Toolbox to create this dataflow. Next, the database properties are configured, and the correct option is selected from the Data Provider drop-down list to connect to Vertica.
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.
Figure 3: Selecting the table and the read strategy for the Customer Export table
Next, a database table destination is dragged and dropped onto the designer window. This time, PostgreSQL is selected as the data provider.
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 onto the destination database table in a single batch.
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 configuring the Vertica database as a destination object.
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 onto a Vertica database destination object.
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.
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 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 the Vertica database and update trusted and accurate data as and when needed. Our data management platform features several 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. Thus, it reduces 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 quickly analyze big data from heterogeneous sources and make data-driven decisions to make their business grow.
Authors:
Afnan Rehan
