DBLINK in VerticaPy¶

Introduction¶

Starting with VerticaPy 0.12.0, you can work with other databases, such as PostgresQL and mySQL, using DBLINK functionality. DBLINK is a Vertica User Defined Transform Function coded in C++ that runs SQL against other databases. To setup and learn more about DBLINK in Vertica, please view the github repo.

In order to use this new functionality, we first need to install the ODBC driver and manager, as well as configure DBLINK on all nodes of the cluster. Configuration entails three files:

• dblink.cids
• odbc.ini
• odbcinst.ini

For more information about requirements, see the github repo.

These files provide the host server address, username, and password, as well as the database name that we want to access. In future versions, we are planning to simplify this process and automate the creation of these files.

In the next section, let's work through an example of a database in PostgreSQL.

Connecting to an External Database¶

We first need to provide the connection information that we have set up in the Connection Identifier Database file (dblink.cids). We can select a special character symbol to identify this connection.

Let's try to set up a connection with an external PostgreSQL database, which we name "pgdb". The connection details for "pgdb", including server name, user name etc., are in the configuration files mentioned in the introduction section.

Creating a vDataFrame¶

We can create a vDataFrame from a table stored in an external database by setting the 'external' parameter to True. SQL can be used to fetch required data, and we can provide an identifying symbol that can be used for fetching perform queries with SQL.

All vDataFrame functions are available for this imported table. For example, we can get all the column names:

Or the column data types:

Or the count of the datapoints:

Note: Every time we perform these calculations or call the vDataFrame, it runs the SQL query to fetch all the data from the external database. After retrieving the entire table, the operations are computed by Vertica. In order to push the queries to a remote database, we can use the option "sql_push_ext". When we create a vDataFrame with this option activated, all the aggregations are done on the external database using SQL.

If we look at the SQL generated in background, we can see that it pushes the aggregation query to the database.

Let's look at the count query again, and see how VerticaPy is pushing it to the external database.

Let's also look at the "min" method:

For the above examples, the queries were pushed to the external database.

If the function is unique to Vertica, it automatically fetches the data from the external database to compute on the Vertica server. Let's try an example with the describe function, which is a unique Vertica function.

We can see that the data was fetched from the external database to be computed in Vertica.

(Now we can turn off SQL display).

Using SQL Magic Cells¶

We can use magic cells to call external tables using special characters like "$$$" and "%%%". If we have multiple external databases, we can specify special characters for each.

This makes writing queries a lot more convenient and visually appealing!

Now we will try to get fetch data from our external database "pgdb, whose special character is "&".

To perform all regular queries, all we need to do is call the table with its name inside three special characters.

We'll now try out some queries:

Count the elements inside the table:

Find the IATA_CODE where CITY is "Allentown":

Note: Any query that we write inside the "&&&" signs is also sent to the external database to be run.

So, instead of just calling the whole table, we can query it using the same special character padding.

For example, let's select all elements inside the airports table:

Now we'll run a search query to find a particular id:

We can also insert a new entry into the airports table, which is placed in the postgreSQL database represented by "&":

Connecte Multiple Databases¶

You can connect and use multiple datasets from different databases.

In this example we will get:

• Airline data from PostgreSQL
• Airport data from MySQL
• Flights data from Vertica

The datasets can be found here.

Airline Data in PostgreSQL¶

We can set up a new connection in just one line by referencing the alias inside the connection files. As before, we will provide the special character symbol that is used to invoke the connection.

Let's look at the airline table that we have in our postgreSQL database.

Airports Data in MySQL¶

We can create another new connection by providing the cid reference for our MySQL database. We'll also provide a unique special character, which is not used for any other connection.

Let's take a look at the airports table that we have in our MySQL database.

Flights Data Vertica¶

We'll now read a locally stored CSV file with the flights data and materialize it in Vertica.