|Share this article:|
Vertica Integration with Incorta: Connection Guide
For: Vertica 7.x
About Vertica Connection Guides
Vertica connection guides provide basic information about setting up connections to Vertica from software that third-party vendors create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor's software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested.
Incorta is a seamless, end-to-end analytical warehouse solution engineered for simple, powerful, real-time analysis of massive volumes of data.
This document describes how to:
- Connect to Vertica from Incorta.
- Load data from an Vertica database into Incorta.
- Create a dashboard that displays real-time analysis of the data.
This document assumes that the reader is familiar with both Incorta and Vertica.
This document is based on the result of testing Vertica 7.x with Incorta 2.1.
Before You Begin
Before you can connect Incorta to Vertica, you must install the Incorta software. Incorta provides the Vertica 6.1.3 JDBC drivers (
vertica-jdk5-6.1.3-0.jar) with the installation package. You do not need to download the drivers separately. After you install Incorta you need to:
- Log in using the Sign In page.
- Create a connection to your Vertica database.
- Load data from Vertica into Incorta.
- Build your dashboard using that data.
Connecting to Vertica Using Incorta
Before you can connect to Vertica, define the data source using the following steps:
- To create a new connection, click the database icon on the left side of the window. Then click + on the top right of the window. The Add New Data Source window opens.
- Select Vertica from the Database dropdown list:
- Specify a name for this data source. This example names the Vertica data source as testdb. Specify the Vertica database username and password, and the JDBC connection string that Incorta needs to connect to your Vertica instance.
- To register your Vertica database with Incorta and create the connection, click Add Data Source.
- After Incorta has registered the connection successfully, click Test Connection to test the connection.
Loading Vertica Data into Incorta
Incorta provides end-to-end analytics platform capabilities. You load data from your Vertica database into the Incorta engine. Incorta keeps this data in memory for fast performance.
Take the following steps:
- Once you have established a connection from Incorta to your Vertica database, click the Schemas and Session Variables icon on the left side of the window. This window displays all the schemas you have currently defined in Incorta.
- To create a new schema in Incorta for the data you want to load from your Vertica instance, in the top right of the window, click Schema Wizard.
- Using the Schema Wizard, enter the name of your Incorta schema to contain the Vertica data. This example names the Incorta schema VMart. Enter the name of the data source, which is the name you assigned when you created the connection, in this example, testdb. Optionally, you can add a description of the schema. This example loads data into Incorta from the VMart database that ships with Vertica. Click Next.
- The following window lists the Vertica schemas and tables. Select the schemas and tables that you want to load and click Next.
The Schema Wizard displays information about the tables that you have selected. Incorta displays information about the columns in the tables, including name, label, data type, mapping type, and function. Incorta has not loaded the data yet.
- The Schema Wizard also gives you the option to modify the query that selects exactly this data. To do so, click Play, the blue arrow inside a blue circle next to the table name.
- You might edit this query if you want to omit a table column from the data you load from Vertica. To do so, edit the query to omit that column name from the SELECT statement, click Execute to run the modified query.
- To save the results, without the omitted column, click Save.
- After you have the data you want, to tell Incorta to verify the schema that you have created, in the Schema Wizard, click Next.
- To save your new schema, click Finish.
After Incorta has verified the schema, it displays detailed information about the schema and tables you selected. Note that Incorta has not yet loaded the Vertica data.
- When you load data from the Vertica schemas and tables, Incorta stores that data into memory to perform the analytics. Currently in this example, there is no data loaded into memory; no value displays for the number of rows and memory consumed. To tell Incorta to load the data from Vertica, click Load.
There are three options for data load:
Full—First-time load into Incorta memory.
Incremental—Load only data that has been generated in the data source since data was last loaded into Incorta. Incorta uses a SQL query to fetch the new data from the server.
Snapshot—Load data into the schema from Incorta’s data snapshot instance, rather than directly from the data source. This snapshot is updated each time a full or incremental load occurs.
- After loading the data, Incorta displays updated information about each table, such as the number of rows and columns, memory usage, and other information.
Note Incorta compresses the data on ingestion, which depends on the cardinality of the data, and loads the data in memory. If memory is insufficient to load the data, “cold data” is removed from the memory and is only loaded from the disk if it is required again.
- To define the missing joins manually in Incorta, click + at the top right of the window and specify the columns on which to join multiple tables to create a new table.
Designing Dashboards with Incorta
Now that you have loaded the Vertica data into your Incorta schema, you are ready to design a dashboard.
- To create a new dashboard, click the Content icon on the left side of the window (it looks like a cloud).
- Click + on the top right of the window.
- Enter a name for your new dashboard and click Create. Incorta opens the dashboard design page where you can begin to design your dashboard.
- The following dashboard uses data from the VMart database that ships with Vertica. That data now resides in memory as part of your Incorta schema. The dashboard displays three pieces of information about sales: Sales per Year, Sales by Geographic Region, and Sales by Product Category.
In the Sales per Year graph, to view more detailed data, such as quarterly sales or monthly sales, click a data point for a given year, and the sales per quarter display. Click a data point for a given quarter, and the sales for the three months of that quarter display.
Similarly, on the Sales by Geographical Region, click a data point for a region. For example, if you click the East category, Incorta displays detailed results for all states in the East region.
To cancel the filter and return to the high-level data, click the button next to Remote All. For the Sales by Geographic Region graph, that button reads Customer Region > East.
For More Information
For more information about Incorta, see www.Incorta.com.