The Virtual DataFrame¶

The Virtual DataFrame (vDataFrame) is the core object of the VerticaPy library. Leveraging the power of Vertica and the flexibility of Python, the vDataFrame is a Python object that lets you manipulate the data representation in a Vertica database without modifying the underlying data. The data represented by a vDataFrame remains in the Vertica database, bypassing the limitations of working memory. When a vDataFrame is created or altered, VerticaPy formulates the operation as an SQL query and pushes the computation to the Vertica database, harnessing Vertica’s massive parallel processing and in-built functions. Vertica then aggregates and returns the result to VerticaPy. In essence, vDataFrames behave similar to views in the Vertica database.

For more information about Vertica’s performance advantages, including its columnar orientation and parallelization across nodes, see the Vertica documentation.

In the following tutorial, we will introduce the basic functionality of the vDataFrame and then explore the ways in which they utilize in-database processing to enhance performance.

Creating vDataFrames¶

First, run the load_titanic() function to ingest into Vertica a dataset with information about titanic passengers:

from verticapy.datasets import load_titanic

load_titanic()

	123 pclass Int 100%	...	123 survived Int 100%	Abc home.dest Varchar(100) 57%
1	1	...	0	Montevideo, Uruguay
2	1	...	0	Trenton, NJ
3	1	...	0	[null]
4	1	...	0	Montevideo, Uruguay
5	1	...	0	Los Angeles, CA
6	1	...	0	Lakewood, NJ
7	1	...	0	Montreal, PQ
8	1	...	0	Deephaven, MN / Cedar Rapids, IA
9	1	...	0	New York, NY
10	1	...	0	Scituate, MA
11	1	...	0	[null]
12	1	...	0	New York, NY
13	1	...	0	[null]
14	1	...	0	London / Middlesex
15	1	...	0	Brighton, MA
16	1	...	0	New York, NY
17	1	...	0	New York, NY
18	1	...	0	Springfield, MA
19	1	...	0	Vancouver, BC
20	1	...	0	Dorchester, MA

You can create a vDataFrame from either an existing relation or a customized relation.

To create a vDataFrame using an existing relation, in this case the Titanic dataset, provide the name of the dataset:

import verticapy as vp

vp.vDataFrame("public.titanic")

To create a vDataFrame using a customized relation, specify the SQL query for that relation as the argument:

vp.vDataFrame("SELECT pclass, AVG(survived) AS survived FROM titanic GROUP BY 1")

	123 pclass Integer 100%	123 survived Float(22) 100%
1	2	0.416988416988417
2	3	0.227752639517345
3	1	0.612179487179487

For more examples of creating vDataFrames, see vDataFrame.

In-memory vs. in-database¶

The following examples demonstrate the performance advantages of loading and processing data in-database versus in-memory.

First, we download the Expedia dataset from Kaggle and then load it into Vertica:

Note

In this example, we are only showing the steps without actually computing the results due to the size of the database. If you perform the analysis, you’ll notice a significant difference in performance. In-database processing is much faster.

vp.read_csv(
    "expedia.csv",
    schema = "public",
    parse_nrows = 20000000,
)

Once the data is loaded into the Vertica database, we can create a vDataFrame using the relation that contains the expedia dataset:

import time

start_time = time.time()
expedia = vp.vDataFrame("public.expedia")
print("elapsed time = {}".format(time.time() - start_time))

All the data—about 4GB—is stored in Vertica, requiring no in-memory data loading.

Now, to compare the above result with in-memory loading, we load about half the dataset into pandas:

Note

This process is expensive on local machines, so avoid running the following code if your computer has less than 2GB of memory.

import pandas as pd

start_time = time.time()
expedia_df = pd.read_csv(
    "expedia.csv",
)
print("elapsed time = {}".format(time.time() - start_time))

You will notice that it will take orders of magnitude more to load into memory compared with the time required to create the vDataFrame. Loading data into pandas is quite fast when the data volume is low (less than some MB), but as the size of the dataset increases, the load time can become exponentially more expensive.

Even after the data is loaded into memory, the performance is very slow.

The following example removes non-numeric columns from the dataset, then computes a correlation matrix:

columns_to_drop = ["date_time", "srch_ci", "srch_co"]
expedia_df = expedia_df.drop(columns_to_drop, axis = 1)
start_time = time.time()
expedia_df.corr()
print(f"elapsed time = {time.time() - start_time}")

Let’s compare the performance in-database using a vDataFrame to compute the correlation matrix of the entire dataset:

# Remove non-numeric columns
expedia.drop(columns = ["date_time", "srch_ci", "srch_co"])
start_time = time.time()
expedia.corr(show = False)
print(f"elapsed time = {time.time() - start_time}")

VerticaPy also caches the computed aggregations. With this cache available, we can repeat the correlation matrix computation almost instantaneously:

Note

If necessary, you can deactivate the cache by calling the set_option() function with the cache parameter set to False.

start_time = time.time()
expedia.corr(show = False);
print(f"elapsed time = {time.time() - start_time}")

You will notice that the result can re-fetched instantaneously.

Memory usage¶

Now, we will examine how the memory usage compares between in-memory and in-database.

First, use the pandas info() method to explore the DataFrame’s memory usage:

expedia_df.info()

You should observe that the size is the same is that of the original file.

Compare this with vDataFrame - The vDataFrame only uses about 37KB! By storing the data in the Vertica database, and only recording the user’s data modifications in memory, the memory usage is reduced to a minimum.

With VerticaPy, we can take advantage of Vertica’s structure and scalability, providing fast queries without ever loading the data into memory. In the above examples, we’ve seen that in-memory processing is much more expensive in both computation and memory usage. This often leads to the decesion to downsample the data, which sacrfices the possibility of further data insights.

The `vDataFrame` structure¶

Now that we’ve seen the performance and memory benefits of the vDataFrame , let’s dig into some of the underlying structures and methods that produce these great results.

vDataFrame are composed of columns called vDataColumn. To view all vDataColumn in a vDataFrame , use the get_columns() method:

expedia.get_columns()
Out[1]: 
['"date_time"',
 '"site_name"',
 '"posa_continent"',
 '"user_location_country"',
 '"user_location_region"',
 '"user_location_city"',
 '"orig_destination_distance"',
 '"user_id"',
 '"is_mobile"',
 '"is_package"',
 '"channel"',
 '"srch_ci"',
 '"srch_co"',
 '"srch_adults_cnt"',
 '"srch_children_cnt"',
 '"srch_rm_cnt"',
 '"srch_destination_id"',
 '"srch_destination_type_id"',
 '"is_booking"',
 '"cnt"',
 '"hotel_continent"',
 '"hotel_country"',
 '"hotel_market"',
 '"hotel_cluster"']

To access a vDataColumn, specify the column name in square brackets, for example:

Note

VerticaPy saves computed aggregations to avoid unncessary recomputations.

expedia["is_booking"].describe()

	value
name	"is_booking"
dtype	int
unique	2.0
count	149814.0
0	138566
1	11248

Each vDataColumn has its own catalog to save user modifications. In the previous example, we computed some aggregations for the is_booking column. Let’s look at the catalog for that vDataColumn:

expedia["is_booking"]._catalog
Out[2]: 
{'cov': {},
 'pearson': {},
 'spearman': {},
 'spearmand': {},
 'kendall': {},
 'cramer': {},
 'biserial': {},
 'regr_avgx': {},
 'regr_avgy': {},
 'regr_count': {},
 'regr_intercept': {},
 'regr_r2': {},
 'regr_slope': {},
 'regr_sxx': {},
 'regr_sxy': {},
 'regr_syy': {},
 'approx_unique': 2,
 'count': 149814}

The catalog is updated whenever major changes are made to the data.

We can also view the vDataFrame’s backend SQL code generation by setting the sql_on parameter to True with the set_option() function:

vp.set_option("sql_on", True)
expedia["cnt"].describe()

-- Computing the different aggregations
SELECT
    /*+LABEL('vDataframe.aggregate')*/
    APPROXIMATE_COUNT_DISTINCT("cnt")
FROM (
    SELECT
        "site_name",
        "posa_continent",
        "user_location_country",
        "user_location_region",
        "user_location_city",
        "orig_destination_distance",
        "user_id",
        "is_mobile",
        "is_package",
        "channel",
        "srch_adults_cnt",
        "srch_children_cnt",
        "srch_rm_cnt",
        "srch_destination_id",
        "srch_destination_type_id",
        "is_booking",
        "cnt",
        "hotel_continent",
        "hotel_country",
        "hotel_market",
        "hotel_cluster"
    FROM "public"."expedia"
) VERTICAPY_SUBTABLE
LIMIT 1;

-- Computing the descriptive statistics of all numerical columns using SUMMARIZE_NUMCOL
SELECT
    /*+LABEL('vDataframe.describe')*/
    SUMMARIZE_NUMCOL("cnt") OVER ()
FROM (
    SELECT
        "site_name",
        "posa_continent",
        "user_location_country",
        "user_location_region",
        "user_location_city",
        "orig_destination_distance",
        "user_id",
        "is_mobile",
        "is_package",
        "channel",
        "srch_adults_cnt",
        "srch_children_cnt",
        "srch_rm_cnt",
        "srch_destination_id",
        "srch_destination_type_id",
        "is_booking",
        "cnt",
        "hotel_continent",
        "hotel_country",
        "hotel_market",
        "hotel_cluster"
    FROM "public"."expedia"
) VERTICAPY_SUBTABLE;

	value
name	"cnt"
dtype	int
unique	30.0
count	149814
mean	1.49385237694741
std	1.22996676120092
min	1.0
approx_25%	1.0
approx_50%	1.0
approx_75%	2.0
max	47.0

To control whether each query outputs its elasped time, use the time_on parameter of the set_option() function:

vp.set_option("sql_on", False)

expedia = vp.vDataFrame("public.expedia") # creating a new vDataFrame to delete the catalog

vp.set_option("time_on", True)

expedia.corr()
<IPython.core.display.HTML object>
Out[6]: <Axes: >

The aggregation’s for each vDataColumn are saved to its catalog. If we again call the corr() method, it’ll complete in a couple seconds—the time needed to draw the graphic—because the aggregations have already been computed and saved during the last call:

import time

start_time = time.time()

expedia.corr();

print("elapsed time = {}".format(time.time() - start_time))
elapsed time = 0.9719889163970947

To turn off the elapsed time and the SQL code generation options:

vp.set_option("sql_on", False)

vp.set_option("time_on", False)

You can obtain the current vDataFrame relation with the current_relation() method:

print(expedia.current_relation())
"public"."expedia"

The generated SQL for the relation changes according to the user’s modifications. For example, if we impute the missing values of the orig_destination_distance vDataColumn by its average and then drop the is_package vDataColumn, these changes are reflected in the relation:

expedia["orig_destination_distance"].fillna(method = "avg");

expedia["is_package"].drop();

print(expedia.current_relation())
(
   SELECT
     "date_time",
     "site_name",
     "posa_continent",
     "user_location_country",
     "user_location_region",
     "user_location_city",
     COALESCE("orig_destination_distance", 2081.97741638588) AS "orig_destination_distance",
     "user_id",
     "is_mobile",
     "channel",
     "srch_ci",
     "srch_co",
     "srch_adults_cnt",
     "srch_children_cnt",
     "srch_rm_cnt",
     "srch_destination_id",
     "srch_destination_type_id",
     "is_booking",
     "cnt",
     "hotel_continent",
     "hotel_country",
     "hotel_market",
     "hotel_cluster" 
   FROM
 (
                
   SELECT
     
                    "date_time",
     "site_name",
     "posa_continent",
     "user_location_country",
     "user_location_region",
     "user_location_city",
     "orig_destination_distance",
     "user_id",
     "is_mobile",
     "channel",
     "srch_ci",
     "srch_co",
     "srch_adults_cnt",
     "srch_children_cnt",
     "srch_rm_cnt",
     "srch_destination_id",
     "srch_destination_type_id",
     "is_booking",
     "cnt",
     "hotel_continent",
     "hotel_country",
     "hotel_market",
     "hotel_cluster" 
                
   FROM
 "public"."expedia") 
VERTICAPY_SUBTABLE) 
VERTICAPY_SUBTABLE

Notice that the is_package column has been removed from the SELECT statement and the orig_destination_distance is now using a COALESCE SQL function.

vDataFrame attributes and management¶

The vDataFrame has many attributes and methods, some of which were demonstrated in the above examples. vDataFrame have two types of attributes:

Virtual Columns (vDataColumn)
Main attributes ( columns , main_relation …)

The vDataFrame main attributes are stored in the _vars dictionary:

Note

You should never change these attributes manually.

expedia._vars
Out[17]: 
{'allcols_ind': 24,
 'count': 149814,
 'clean_query': True,
 'exclude_columns': [],
 'history': ['{Thu Oct 31 19:27:40 2024} [Fillna]: 94077 "orig_destination_distance" missing values were  filled.',
  '{Thu Oct 31 19:27:41 2024} [Drop]: vDataColumn "is_package" was deleted from the vDataFrame.'],
 'isflex': False,
 'max_columns': -1,
 'max_rows': -1,
 'order_by': {},
 'saving': [],
 'sql_push_ext': False,
 'sql_magic_result': 0,
 'symbol': '$',
 'where': [],
 'has_dpnames': False,
 'columns': ['"date_time"',
  '"site_name"',
  '"posa_continent"',
  '"user_location_country"',
  '"user_location_region"',
  '"user_location_city"',
  '"orig_destination_distance"',
  '"user_id"',
  '"is_mobile"',
  '"channel"',
  '"srch_ci"',
  '"srch_co"',
  '"srch_adults_cnt"',
  '"srch_children_cnt"',
  '"srch_rm_cnt"',
  '"srch_destination_id"',
  '"srch_destination_type_id"',
  '"is_booking"',
  '"cnt"',
  '"hotel_continent"',
  '"hotel_country"',
  '"hotel_market"',
  '"hotel_cluster"'],
 'main_relation': '"public"."expedia"'}

Data types¶

vDataFrame use the data types of its vDataColumn. The behavior of some vDataFrame methods depend on the data type of the columns.

For example, computing a histogram for a numerical data type is not the same as computing a histogram for a categorical data type.

The vDataFrame identifies four main data types:

int: integers are treated like categorical data types
when their cardinality is low; otherwise, they are considered numeric
float: numeric data types
date: date-like data types (including timestamp)
text: categorical data types

Data types not included in the above list are automatically treated as categorical. You can examine the data types of the vDataColumns in a vDataFrame using the dtypes() method:

expedia.dtypes()

	dtype
"date_time"	timestamp
"site_name"	int
"posa_continent"	int
"user_location_country"	int
"user_location_region"	int
"user_location_city"	int
"orig_destination_distance"	float
"user_id"	int
"is_mobile"	int
"channel"	int
"srch_ci"	date
"srch_co"	date
"srch_adults_cnt"	int
"srch_children_cnt"	int
"srch_rm_cnt"	int
"srch_destination_id"	int
"srch_destination_type_id"	int
"is_booking"	int
"cnt"	int
"hotel_continent"	int
"hotel_country"	int
"hotel_market"	int
"hotel_cluster"	int

To convert the data type of a vDataColumn, use the astype() method:

expedia["hotel_market"].astype("varchar");

expedia["hotel_market"].ctype()
Out[19]: 'varchar'

To view the category of a specific vDataColumn, specify the vDataColumn and use the category() method:

expedia["hotel_market"].category()
Out[20]: 'text'

Exporting, saving, and loading¶

The save() and load() functions allow you to save and load vDataFrames:

expedia.save()
expedia.filter("is_booking = 1")

	📅 date_time Timestamp 100%	...	Abc hotel_market Varchar 100%	123 hotel_cluster Int 100%
1	2013-01-07 13:31:16	...	4	33
2	2013-01-07 16:13:38	...	47	57
3	2013-01-08 09:24:27	...	55	90
4	2013-01-08 14:23:35	...	46	29
5	2013-01-08 23:52:37	...	46	12
6	2013-01-09 08:38:38	...	628	79
7	2013-01-10 14:17:40	...	91	29
8	2013-01-10 17:09:07	...	1032	83
9	2013-01-10 18:30:08	...	1776	43
10	2013-01-10 18:48:14	...	1846	67
11	2013-01-11 10:56:42	...	46	97
12	2013-01-11 15:17:38	...	152	58
13	2013-01-11 16:17:20	...	1722	29
14	2013-01-11 21:01:41	...	1230	68
15	2013-01-12 11:57:38	...	212	48
16	2013-01-12 19:56:18	...	502	22
17	2013-01-13 09:14:34	...	24	64
18	2013-01-13 10:59:20	...	24	64
19	2013-01-13 19:05:49	...	126	92
20	2013-01-14 02:37:51	...	46	58

To return a vDataFrame to a previously saved structure, use the load() function:

expedia = expedia.load();

print(expedia.shape())
(149814, 23)

Because vDataFrame are views of data stored in the connected Vertica database, any modifications made to the vDataFrame are not reflected in the underlying data in the database. To save a vDataFrame relation to the database, use the to_db() method.

It’s good practice to examine the expected disk usage of the vDataFrame before exporting it to the database:

expedia.expected_store_usage(unit = "Gb")

	...	max_size (Gb)	type
"date_time"	...	0.0011162012815475464	timestamp
"site_name"	...	0.0011162012815475464	int
"posa_continent"	...	0.0011162012815475464	int
"user_location_country"	...	0.0011162012815475464	int
"user_location_region"	...	0.0011162012815475464	int
"user_location_city"	...	0.0011162012815475464	int
"orig_destination_distance"	...	0.0011162012815475464	float
"user_id"	...	0.0011162012815475464	int
"is_mobile"	...	0.0011162012815475464	int
"channel"	...	0.0011162012815475464	int
"srch_ci"	...	0.0011138394474983215	date
"srch_co"	...	0.0011138394474983215	date
"srch_adults_cnt"	...	0.0011162012815475464	int
"srch_children_cnt"	...	0.0011162012815475464	int
"srch_rm_cnt"	...	0.0011162012815475464	int
"srch_destination_id"	...	0.0011162012815475464	int
"srch_destination_type_id"	...	0.0011162012815475464	int
"is_booking"	...	0.0011162012815475464	int
"cnt"	...	0.0011162012815475464	int
"hotel_continent"	...	0.0011162012815475464	int
"hotel_country"	...	0.0011162012815475464	int
"hotel_market"	...	0.011162012815475464	varchar
"hotel_cluster"	...	0.0011162012815475464	int
separator	...	0.003209078684449196
header	...	3.4831464290618896e-07
rawsize	...	0.03892314434051514

If you decide that there is sufficient space to store the vDataFrame in the database, run the to_db() method:

expedia.to_db(
    "public.expedia_clean",
    relation_type = "table",
)

The Virtual DataFrame¶

Creating vDataFrames¶

In-memory vs. in-database¶

Memory usage¶

The vDataFrame structure¶

vDataFrame attributes and management¶

Data types¶

Exporting, saving, and loading¶

The `vDataFrame` structure¶