Best practices¶

In this tutorial, we will explore some best practices and optimizations to help you get the most out of Vertica and VerticaPy.

1. Restrict objects and operations to essential columns¶

As VerticaPy is effectively an abstraction of SQL, any database-level optimizations you make in your Vertica database carry over to VerticaPy. In Vertica, optimization is centered on projections, which are collections of table columns—from one or more tables—stored on disk in a format that optimizes query execution. When you write queries in terms of the original tables, the query uses the projections to return query results. For details about creating and designing projections, see the Projections section in the Vertica documentation.

Projections are created and managed in the Vertica database, but you can leverage the power of projections in VerticaPy with features such as the vDataFrame’s usecols parameter, which specifies the columns from the input relation to include in the vDataFrame. As columnar databases perform better when there are fewer columns in the query, especially when you are working with large datasets, limiting vDataFrames and operations to essential columns can lead to a significant performance improvement. By default, most vDataFrame methods use all numerical columns in the vDataFrame, but you can restrict the operation to specific columns.

In the following examples, we’ll demonstrate how to create a vDataFrame from specific columns in the input relation, and then run methods on that vDataFrame. First, load the titanic dataset into Vertica using the load_titanic() function:

[2]:

from verticapy.datasets import load_titanic

load_titanic()

[2]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(8)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(12)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc Varchar(100)
1	1	0		male	47.0	1	0	PC 17757	227.525	C62 C64	C	[null]	124
2	1	0		male	24.0	0	1	PC 17558	247.5208	B58 B60	C	[null]	[null]
3	1	0		male	25.0	0	0	13905	26.0	[null]	C	[null]	148
4	1	0		male	42.0	0	0	110489	26.55	D22	S	[null]	[null]
5	1	0		male	45.0	0	0	113050	26.55	B38	S	[null]	[null]
6	1	0		male	46.0	1	0	W.E.P. 5734	61.175	E31	S	[null]	[null]
7	1	0		male	64.0	1	4	19950	263.0	C23 C25 C27	S	[null]	[null]
8	1	0		male	[null]	0	0	113796	42.4	[null]	S	[null]	[null]
9	1	0		male	32.5	0	0	113503	211.5	C132	C	[null]	45
10	1	0		male	55.0	0	0	113787	30.5	C30	S	[null]	[null]
11	1	0		male	37.0	0	1	PC 17596	29.7	C118	C	[null]	[null]
12	1	0		male	64.0	0	0	693	26.0	[null]	S	[null]	263
13	1	0		male	56.0	0	0	113792	26.55	[null]	S	[null]	[null]
14	1	0		male	24.0	1	0	13695	60.0	C31	S	[null]	[null]
15	1	1		male	0.92	1	2	113781	151.55	C22 C26	S	11	[null]
16	1	1		female	18.0	1	0	PC 17757	227.525	C62 C64	C	4	[null]
17	1	1		male	80.0	0	0	27042	30.0	A23	S	B	[null]
18	1	1		female	42.0	0	0	PC 17757	227.525	[null]	C	4	[null]
19	1	1		male	25.0	1	0	11967	91.0792	B49	C	7	[null]
20	1	1		female	45.0	0	0	PC 17608	262.375	[null]	C	4	[null]
21	1	1		female	22.0	0	1	113505	55.0	E33	S	6	[null]
22	1	1		female	[null]	0	0	17770	27.7208	[null]	C	5	[null]
23	1	1		female	22.0	0	0	113781	151.55	[null]	S	11	[null]
24	1	1		female	64.0	0	2	PC 17756	83.1583	E45	C	14	[null]
25	1	1		female	54.0	1	0	36947	78.2667	D20	C	4	[null]
26	1	1		male	43.0	1	0	17765	27.7208	D40	C	5	[null]
27	1	1		female	22.0	0	2	13568	49.5	B39	C	5	[null]
28	1	1		male	23.0	0	1	PC 17759	63.3583	D10 D12	C	7	[null]
29	1	1		female	35.0	1	0	113789	52.0	[null]	S	8	[null]
30	1	1		female	[null]	1	0	17464	51.8625	D21	S	8	[null]
31	1	1		male	42.0	1	0	11753	52.5542	D19	S	5	[null]
32	1	1		female	45.0	1	0	11753	52.5542	D19	S	5	[null]
33	1	1		female	16.0	0	1	PC 17592	39.4	D28	S	9	[null]
34	1	1		female	21.0	0	0	13502	77.9583	D9	S	10	[null]
35	1	1		male	36.0	0	0	PC 17473	26.2875	E25	S	7	[null]
36	1	1		male	[null]	0	0	F.C. 12998	25.7417	[null]	C	7	[null]
37	1	1		female	33.0	0	0	PC 17613	27.7208	A11	C	11	[null]
38	1	1		female	48.0	1	3	PC 17608	262.375	B57 B59 B63 B66	C	4	[null]
39	1	1		female	23.0	1	0	21228	82.2667	B45	S	7	[null]
40	1	1		female	31.0	0	2	36928	164.8667	C7	S	8	[null]
41	2	0		male	57.0	0	0	244346	13.0	[null]	S	[null]	[null]
42	2	0		female	29.0	1	0	SC/AH 29037	26.0	[null]	S	[null]	[null]
43	2	0		male	29.0	0	0	W./C. 14263	10.5	[null]	S	[null]	[null]
44	2	0		female	30.0	0	0	237249	13.0	[null]	S	[null]	[null]
45	2	0		male	24.0	0	0	248726	13.5	[null]	S	[null]	297
46	2	0		male	30.0	0	0	250646	13.0	[null]	S	[null]	305
47	2	0		male	52.0	0	0	250647	13.0	[null]	S	[null]	19
48	2	0		male	44.0	1	0	26707	26.0	[null]	S	[null]	[null]
49	2	0		male	57.0	0	0	219533	12.35	[null]	Q	[null]	[null]
50	2	0		male	32.0	0	0	237216	13.5	[null]	S	[null]	209
51	2	0		male	70.0	0	0	C.A. 24580	10.5	[null]	S	[null]	[null]
52	2	0		male	54.0	0	0	29011	14.0	[null]	S	[null]	[null]
53	2	0		male	16.0	0	0	S.O./P.P. 3	10.5	[null]	S	[null]	[null]
54	2	0		male	62.0	0	0	240276	9.6875	[null]	Q	[null]	[null]
55	2	0		male	27.0	0	0	SC/PARIS 2168	15.0333	[null]	C	[null]	[null]
56	2	0		male	36.0	0	0	C.A. 17248	10.5	[null]	S	[null]	[null]
57	2	0		female	27.0	1	0	11668	21.0	[null]	S	[null]	[null]
58	2	0		male	27.0	1	0	228414	26.0	[null]	S	[null]	293
59	2	0		male	66.0	0	0	C.A. 24579	10.5	[null]	S	[null]	[null]
60	2	1		female	48.0	0	2	C.A. 33112	36.75	[null]	S	14	[null]
61	2	1		female	30.0	1	0	SC/PARIS 2148	13.8583	[null]	C	12	[null]
62	2	1		female	34.0	0	0	243880	13.0	[null]	S	12	[null]
63	2	1		female	24.0	0	2	250649	14.5	[null]	S	4	[null]
64	2	1		female	48.0	1	2	220845	65.0	[null]	S	9	[null]
65	2	1		female	3.0	1	2	SC/Paris 2123	41.5792	[null]	C	14	[null]
66	2	1		male	3.0	1	1	230080	26.0	F2	S	D	[null]
67	2	1		female	2.0	1	1	26360	26.0	[null]	S	11	[null]
68	2	1		female	18.0	0	2	250652	13.0	[null]	S	16	[null]
69	3	0		male	30.0	0	0	C 7076	7.25	[null]	S	[null]	72
70	3	0		female	40.0	1	0	7546	9.475	[null]	S	[null]	[null]
71	3	0		male	35.0	0	0	373450	8.05	[null]	S	[null]	[null]
72	3	0		female	18.0	0	1	2691	14.4542	[null]	C	[null]	[null]
73	3	0		male	[null]	0	0	2664	7.225	[null]	C	[null]	[null]
74	3	0		female	32.0	1	1	364849	15.5	[null]	Q	[null]	[null]
75	3	0		male	22.0	0	0	350045	7.7958	[null]	S	[null]	[null]
76	3	0		male	35.0	0	0	364512	8.05	[null]	S	[null]	[null]
77	3	0		male	[null]	1	0	2689	14.4583	[null]	C	[null]	[null]
78	3	0		female	[null]	1	0	2689	14.4583	[null]	C	[null]	[null]
79	3	0		male	31.0	0	0	335097	7.75	[null]	Q	[null]	[null]
80	3	0		female	22.0	0	0	7552	10.5167	[null]	S	[null]	[null]
81	3	0		male	0.33	0	2	347080	14.4	[null]	S	[null]	[null]
82	3	0		male	34.0	1	1	347080	14.4	[null]	S	[null]	197
83	3	0		female	28.0	1	1	347080	14.4	[null]	S	[null]	[null]
84	3	0		male	25.0	0	0	349203	7.8958	[null]	S	[null]	[null]
85	3	0		male	25.0	0	0	349250	7.8958	[null]	S	[null]	[null]
86	3	0		male	[null]	0	0	349238	7.8958	[null]	S	[null]	[null]
87	3	0		male	[null]	0	0	349225	7.8958	[null]	S	[null]	[null]
88	3	0		male	22.0	0	0	A/5 21172	7.25	[null]	S	[null]	[null]
89	3	0		male	[null]	0	0	2674	7.225	[null]	C	[null]	[null]
90	3	0		male	[null]	0	0	2631	7.225	[null]	C	[null]	[null]
91	3	0		male	40.5	0	0	C.A. 6212	15.1	[null]	S	[null]	187
92	3	0		male	40.5	0	0	367232	7.75	[null]	Q	[null]	68
93	3	0		male	18.0	0	0	350036	7.7958	[null]	S	[null]	[null]
94	3	0		female	[null]	0	0	364859	7.75	[null]	Q	[null]	[null]
95	3	0		male	[null]	0	0	349254	7.8958	[null]	C	[null]	[null]
96	3	0		male	40.0	1	6	CA 2144	46.9	[null]	S	[null]	[null]
97	3	0		male	51.0	0	0	21440	8.05	[null]	S	[null]	[null]
98	3	0		male	26.0	1	0	350025	7.8542	[null]	S	[null]	[null]
99	3	0		female	[null]	0	0	382649	7.75	[null]	Q	[null]	[null]
100	3	0		male	[null]	0	0	349220	7.8958	[null]	S	[null]	[null]

Rows: 1-100 | Columns: 14

Supposing we are only interested in the ‘survived’, ‘pclass’, ‘age’, ‘parch’, and ‘sibsp’ columns, we can create a vDataFrame with just those columns by specifying them in the usecols parameter:

[3]:

import verticapy as vp

vdf = vp.vDataFrame("public.titanic",
                    usecols = ["survived", "pclass", "age", "parch", "sibsp"])
display(vdf)

	123 pclass Integer	123 survived Integer	123 age Numeric(8)	123 sibsp Integer	123 parch Integer
1	1	0	71.0	0	0
2	1	0	17.0	0	0
3	1	0	27.0	1	0
4	1	0	37.0	1	1
5	1	0	[null]	0	0
6	1	0	31.0	1	0
7	1	0	[null]	0	0
8	1	0	37.0	1	0
9	1	0	24.0	0	0
10	1	0	45.0	1	0
11	1	0	42.0	0	0
12	1	0	[null]	0	0
13	1	0	41.0	1	0
14	1	0	[null]	0	0
15	1	0	29.0	0	0
16	1	0	47.0	0	0
17	1	0	58.0	0	2
18	1	0	50.0	1	0
19	1	0	57.0	1	0
20	1	0	64.0	1	0
21	1	0	51.0	0	1
22	1	1	53.0	2	0
23	1	1	36.0	0	1
24	1	1	14.0	1	2
25	1	1	[null]	0	1
26	1	1	36.0	0	2
27	1	1	[null]	0	0
28	1	1	35.0	0	0
29	1	1	22.0	0	1
30	1	1	25.0	1	0
31	1	1	35.0	1	0
32	1	1	39.0	0	0
33	1	1	37.0	1	0
34	1	1	30.0	1	0
35	1	1	31.0	1	0
36	1	1	22.0	1	0
37	1	1	43.0	0	1
38	1	1	33.0	0	0
39	1	1	35.0	0	0
40	1	1	48.0	1	0
41	1	1	60.0	1	0
42	2	0	44.0	1	0
43	2	0	29.0	1	0
44	2	0	36.0	0	0
45	2	0	30.0	0	0
46	2	0	44.0	0	0
47	2	0	21.0	0	0
48	2	0	47.0	0	0
49	2	0	22.0	2	0
50	2	0	31.0	0	0
51	2	0	[null]	0	0
52	2	0	35.0	0	0
53	2	0	41.0	0	0
54	2	1	4.0	2	1
55	2	1	0.83	0	2
56	2	1	17.0	0	0
57	2	1	24.0	1	1
58	2	1	50.0	0	1
59	2	1	33.0	0	2
60	2	1	30.0	3	0
61	2	1	31.0	0	0
62	2	1	31.0	0	0
63	2	1	12.0	0	0
64	3	0	42.0	0	0
65	3	0	26.0	0	0
66	3	0	32.0	0	0
67	3	0	22.0	0	0
68	3	0	6.0	1	1
69	3	0	9.0	1	1
70	3	0	19.0	0	0
71	3	0	28.0	0	0
72	3	0	29.0	0	0
73	3	0	20.0	0	0
74	3	0	27.0	0	0
75	3	0	30.0	0	0
76	3	0	27.0	0	0
77	3	0	22.0	0	0
78	3	0	32.0	0	0
79	3	0	23.0	1	0
80	3	0	16.0	0	0
81	3	0	18.0	2	2
82	3	0	33.0	1	1
83	3	0	41.0	0	0
84	3	0	19.0	0	0
85	3	0	21.0	0	0
86	3	0	22.0	0	0
87	3	0	29.0	0	0
88	3	0	30.0	0	0
89	3	0	22.0	2	0
90	3	0	[null]	0	0
91	3	0	36.0	1	0
92	3	0	[null]	0	0
93	3	0	[null]	0	0
94	3	0	55.5	0	0
95	3	0	21.0	0	0
96	3	0	[null]	0	0
97	3	0	22.0	0	0
98	3	0	[null]	0	0
99	3	0	28.0	0	0
100	3	0	20.0	0	0

Rows: 1-100 | Columns: 5

If we run the avg() method without specifying columns, all numerical vDataFrame columns are included in the operation:

NOTE: To examine the generated SQL for each command, turn on the “sql_on” option.

[4]:

vp.set_option("sql_on", True)
vdf.avg()

Computing the different aggregations.

SELECT
/*+LABEL('vDataframe.aggregate')*/ AVG("pclass"),
AVG("survived"),
AVG("age"),
AVG("sibsp"),
AVG("parch")
FROM
(
SELECT
"pclass",
"survived",
"age",
"sibsp",
"parch"
FROM
"public"."titanic")
VERTICAPY_SUBTABLE
LIMIT 1

[4]:

	avg
"pclass"	2.28444084278768
"survived"	0.364667747163695
"age"	30.1524573721163
"sibsp"	0.504051863857374
"parch"	0.378444084278768

Rows: 1-5 | Columns: 2

To restrict the operation to specific columns in the vDataFrame, provide the column names in the columns parameter:

[5]:

vdf.avg(columns = ["age", "survived"])

[5]:

	avg
"age"	30.1524573721163
"survived"	0.364667747163695

Rows: 1-2 | Columns: 2

As we are working with a small dataset, the perfomance impact of excluding unncessary columns is not very significant. However, with large datasets (e.g. greater than a TB), the impact is much greater, and choosing essential columns becomes a key step in improving performance.

Instead of specifying essential columns to include, some methods allow you to list the columns to exclude with the exclude_columns parameter:

[6]:

vdf.numcol(exclude_columns = ["parch", "sibsp"])

[6]:

['"pclass"', '"survived"', '"age"']

NOTE: To list all columns in a vDataFrame, including non-numerical columns, use the get_columns() method.

You can then use this truncated list of columns in another method call; for instance, to compute a correlation matrix:

[7]:

vdf.corr(columns = vdf.numcol(exclude_columns = ["parch", "sibsp"]))

Computing the pearson Corr Matrix.

SELECT
/*+LABEL('vDataframe._aggregate_matrix')*/ CORR_MATRIX("pclass", "survived", "age") OVER ()
FROM
(
SELECT
"pclass",
"survived",
"age",
"sibsp",
"parch"
FROM
"public"."titanic")
VERTICAPY_SUBTABLE

../../../_images/notebooks_introduction_best-practices_index_17_2.png

To turn off the SQL code generation option:

[8]:

vp.set_option("sql_on", False)

2. Save the current relation¶

The vDataFrame works like a view, a stored query that encapsulates one or more SELECT statements. If the generated relation uses many different functions, the computation time for each method call is greatly increased.

Small transformations don’t drastically slow down computation, but heavy transformations (multiple joins, frequent use of advanced analytical funcions, moving windows, etc.) can result in noticeable slowdown. When performing computationally expensive operations, you can aid performance by saving the vDataFrame structure as a table in the Vertica database. We will demonstrate this process in the following example.

First, create a vDataFrame, then perform some operations on that vDataFrame:

[9]:

vdf = vp.vDataFrame("public.titanic")
vdf["sex"].label_encode()["boat"].fillna(method = "0ifnull")["name"].str_extract(
    ' ([A-Za-z]+)\.').eval("family_size", expr = "parch + sibsp + 1").drop(
    columns = ["cabin", "body", "ticket", "home.dest"])["fare"].fill_outliers().fillna()
print(vdf.current_relation())

795 elements were filled.
(
   SELECT
     "pclass",
     "survived",
     "name",
     "sex",
     "age",
     "sibsp",
     "parch",
     COALESCE("fare", 32.9113074018842) AS "fare",
     "embarked",
     "boat",
     "family_size"
   FROM
 (
   SELECT
     "pclass",
     "survived",
     REGEXP_SUBSTR("name", ' ([A-Za-z]+)\.') AS "name",
     DECODE("sex", 'female', 0, 'male', 1, 2) AS "sex",
     COALESCE("age", 30.1524573721163) AS "age",
     "sibsp",
     "parch",
     (CASE WHEN "fare" < -176.6204982585513 THEN -176.6204982585513 WHEN "fare" > 244.5480856064831 THEN 244.5480856064831 ELSE "fare" END) AS "fare",
     COALESCE("embarked", 'S') AS "embarked",
     DECODE("boat", NULL, 0, 1) AS "boat",
     parch + sibsp + 1 AS "family_size"
   FROM
 (

   SELECT

                    "pclass",
     "survived",
     "name",
     "sex",
     "age",
     "sibsp",
     "parch",
     "fare",
     "embarked",
     "boat"

   FROM
 "public"."titanic")
VERTICAPY_SUBTABLE)
VERTICAPY_SUBTABLE)
VERTICAPY_SUBTABLE

To understand how Vertica executes the different aggregations in the above relation, let’s take a look at the query plan:

NOTE: Query plans can be hard to interpret if you don’t know how to parse them. For more information, see query plan information and structure.

[10]:

print(vdf.explain())

------------------------------
QUERY PLAN DESCRIPTION:

EXPLAIN SELECT /*+LABEL('vDataframe.explain')*/ * FROM (SELECT "pclass", "survived", "name", "sex", "age", "sibsp", "parch", COALESCE("fare", 32.9113074018842) AS "fare", "embarked", "boat", "family_size" FROM (SELECT "pclass", "survived", REGEXP_SUBSTR("name", ' ([A-Za-z]+)\.') AS "name", DECODE("sex", 'female', 0, 'male', 1, 2) AS "sex", COALESCE("age", 30.1524573721163) AS "age", "sibsp", "parch", (CASE WHEN "fare" < -176.6204982585513 THEN -176.6204982585513 WHEN "fare" > 244.5480856064831 THEN 244.5480856064831 ELSE "fare" END) AS "fare", COALESCE("embarked", 'S') AS "embarked", DECODE("boat", NULL, 0, 1) AS "boat", parch + sibsp + 1 AS "family_size" FROM ( SELECT "pclass", "survived", "name", "sex", "age", "sibsp", "parch", "fare", "embarked", "boat" FROM "public"."titanic") VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE

Access Path:
+-STORAGE ACCESS for titanic [Cost: 129, Rows: 1K (NO STATISTICS)] (PATH ID: 1)
|  Projection: public.titanic_super
|  Materialize: titanic.pclass, titanic.survived, titanic.name, titanic.sex, titanic.age, titanic.sibsp, titanic.parch, titanic.fare, titanic.embarked, titanic.boat
|  Execute on: v_vertica_eon_node0001, v_vertica_eon_node0002, v_vertica_eon_node0003


-----------------------------------------------
PLAN: BASE QUERY PLAN (GraphViz Format)
-----------------------------------------------
digraph G {
graph [rankdir=BT, label = "BASE QUERY PLAN
        Query: EXPLAIN SELECT /*+LABEL(\'vDataframe.explain\')*/ * FROM (SELECT \"pclass\", \"survived\", \"name\", \"sex\", \"age\", \"sibsp\", \"parch\", COALESCE(\"fare\", 32.9113074018842) AS \"fare\", \"embarked\", \"boat\", \"family_size\" FROM (SELECT \"pclass\", \"survived\", REGEXP_SUBSTR(\"name\", \' ([A-Za-z]+)\.\') AS \"name\", DECODE(\"sex\", \'female\', 0, \'male\', 1, 2) AS \"sex\", COALESCE(\"age\", 30.1524573721163) AS \"age\", \"sibsp\", \"parch\", (CASE WHEN \"fare\" \< -176.6204982585513 THEN -176.6204982585513 WHEN \"fare\" \> 244.5480856064831 THEN 244.5480856064831 ELSE \"fare\" END) AS \"fare\", COALESCE(\"embarked\", \'S\') AS \"embarked\", DECODE(\"boat\", NULL, 0, 1) AS \"boat\", parch + sibsp + 1 AS \"family_size\" FROM ( SELECT \"pclass\", \"survived\", \"name\", \"sex\", \"age\", \"sibsp\", \"parch\", \"fare\", \"embarked\", \"boat\" FROM \"public\".\"titanic\") VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE

        All Nodes Vector:

          node[0]=v_vertica_eon_node0001 (initiator) Up
          node[1]=v_vertica_eon_node0002 (executor) Up
          node[2]=v_vertica_eon_node0003 (executor) Up
          node[3]=v_vertica_eon_node0004 (executor) Down
          node[4]=v_vertica_eon_node0005 (executor) Down
          node[5]=v_vertica_eon_node0006 (executor) Down
          node[6]=v_vertica_eon_node0007 (executor) Down
          node[7]=v_vertica_eon_node0008 (executor) Down
          node[8]=v_vertica_eon_node0009 (executor) Down

        Participating subscriptions:

          replica: [<1, 2, 0>]
          segment0001: [<0>]
          segment0002: [<1>]
          segment0003: [<2>]
          segment0004: [<0>]
          segment0005: [<1>]
          segment0006: [<2>]
          segment0007: [<0>]
          segment0008: [<1>]
          segment0009: [<2>]
        ", labelloc=t, labeljust=l ordering=out]
0[label = "Root
        OutBlk=[UncTuple(11)]", color = "green", shape = "house"];
1[label = "NewEENode
        OutBlk=[UncTuple(11)]", color = "green", shape = "box"];
2[label = "Recv
        Recv from: v_vertica_eon_node0001, v_vertica_eon_node0002, v_vertica_eon_node0003
        Net id: 1000

        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Varchar(164)
        Unc: Integer(8)
        Unc: Numeric(16, 13)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Numeric(18, 13)
        Unc: Varchar(20)
        Unc: Integer(8)
        Unc: Integer(8)", color = "green", shape = "box"];
3[label = "Send
        Send to: v_vertica_eon_node0001
        Net id: 1000

        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Varchar(164)
        Unc: Integer(8)
        Unc: Numeric(16, 13)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Numeric(18, 13)
        Unc: Varchar(20)
        Unc: Integer(8)
        Unc: Integer(8)", color = "green", shape = "box"];
4[label = "StorageUnionStep: titanic_super
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Varchar(164)
        Unc: Integer(8)
        Unc: Numeric(16, 13)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Numeric(18, 13)
        Unc: Varchar(20)
        Unc: Integer(8)
        Unc: Integer(8)", color = "purple", shape = "box"];
5[label = "ExprEval:
          titanic.pclass
          titanic.survived
          regexp_substr(titanic.name, E\' ([A-Za-z]+)\\.\', 1, 1, \'\', 0)
          CASE titanic.sex WHEN NULLSEQUAL \'female\' THEN 0 WHEN NULLSEQUAL \'male\' THEN 1 ELSE 2 END
          coalesce(titanic.age, 30.1524573721163)
          titanic.sibsp
          titanic.parch
          coalesce(CASE WHEN (titanic.fare \< (-176.6204982585513)) THEN (-176.6204982585513) WHEN (titanic.fare \> 244.5480856064831) THEN 244.5480856064831 ELSE titanic.fare END, 32.9113074018842)
          coalesce(titanic.embarked, \'S\')
          CASE titanic.boat WHEN NULLSEQUAL NULL THEN 0 ELSE 1 END
          ((titanic.parch + titanic.sibsp) + 1)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Varchar(164)
        Unc: Integer(8)
        Unc: Numeric(16, 13)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Numeric(18, 13)
        Unc: Varchar(20)
        Unc: Integer(8)
        Unc: Integer(8)", color = "brown", shape = "box"];
6[label = "ScanStep: titanic_super
        pclass
        survived
        name
        sex
        age
        sibsp
        parch
        fare
        embarked
        boat
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Varchar(164)
        Unc: Varchar(20)
        Unc: Numeric(6, 3)
        Unc: Integer(8)
        Unc: Integer(8)
        Unc: Numeric(10, 5)
        Unc: Varchar(20)
        Unc: Varchar(100)", color = "brown", shape = "box"];
1->0 [label = "V[0] C=11", color = "black", style="bold", arrowtail="inv"];
2->1 [label = "0", color = "blue"];
3->2 [label = "0", color = "blue"];
4->3 [label = "0", color = "blue"];
5->4 [label = "0", color = "blue"];
6->5 [label = "0", color = "blue"];}

Looking at the plan and its associated relation, it’s clear that the transformations we applied to the vDataFrame result in a complicated relation. Each method call to the vDataFrame must use this relation for computation.

NOTE: To better understand your queries, check out the `QueryProfiler <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.performance.vertica.qprof.QueryProfiler.html>`__ function.

To save the relation as a table in the Vertica and replace the current relation in VerticaPy with the new table relation, use the `to_db() <https://www.vertica.com/python/documentation/1.0.x/html/verticapy.vDataFrame.to_db.html>`__ method with the inplace parameter set to True:

[11]:

vp.drop("public.titanic_clean", method = "table") # drops any existing table with the same schema and name
vdf.to_db("public.titanic_clean",
          relation_type = "table",
          inplace = True)
print(vdf.current_relation())

"public"."titanic_clean"

When dealing with very large datasets, it’s best to take caution before saving relations with complicated transformations. Ideally, you will perform a thorough data exploration, and only execute heavy transformations when essential.

3. Use the help function¶

For a quick and convenient way to view information about an object or function, use the help() function:

[12]:

help(vp.connect)

Help on function connect in module verticapy.connection.connect:

connect(section: str, dsn: Optional[str] = None) -> None
    Connects to the database.

    Parameters
    ----------
    section: str
        Name of the section in the
        configuration file.
    dsn: str, optional
        Path to the file containing
        the credentials. If empty,
        the Connection File will be
        used.

    Examples
    --------
    Display all available connections:

    .. code-block:: python

        from verticapy.connection import available_connections

        available_connections()

    ``['VML', 'VerticaDSN', 'VerticaDSN_test']``

    Connect using the VerticaDSN connection:

    .. code-block:: python

        from verticapy.connection import connect

        connect("VerticaDSN")

    .. seealso::

        | :py:func:`~verticapy.connection.available_connections` :
            Displays all available connections.
        | :py:func:`~verticapy.connection.get_connection_file` :
            Gets the VerticaPy connection file.
        | :py:func:`~verticapy.connection.new_connection` :
            Creates a new VerticaPy connection.
        | :py:func:`~verticapy.connection.set_connection` :
            Sets the VerticaPy connection.

4. Close your connections¶

Each connection to the database increases the concurrency on the system, so try to close connections when you’re done with them. VerticaPy simplifies the connection process by allowing the user to create an auto-connection, but the closing of connections must be done manually with the close_connection() function.

To demonstrate, create a database connection:

[ ]:

vp.connect("VerticaDSN")

When you are done making changes, close the connection with the close_connection() function:

[ ]:

vp.close_connection()

It is especially important to close connections when you are working in an environment with mutliple users.

5. Consider a method’s time complexity¶

Some techniques are significantly more computationally expensive than others. For example, a Kendall correlation is very expensive compared to a Pearson correlation because, unlike Pearson, Kendall correlations use a cross join, resulting in a time complexity of O(n*n) (where n is the number of rows). Let’s compare the time needed to compute these two correlations on the ‘titanic’ dataset:

[13]:

import time

vdf = vp.vDataFrame("public.titanic")

start_time = time.time()
x = vdf.corr(method = "pearson", show = False)
print("Pearson, time: {0}".format(time.time() - start_time))

start_time = time.time()
x = vdf.corr(method = "kendall", show = False)
print("Kendall, time: {0}".format(time.time() - start_time))

Pearson, time: 0.17660832405090332

100%|██████████| 6/6 [00:00<00:00, 2943.03it/s]

Kendall, time: 0.857501745223999

The Kendall correlation matrix takes longer to compute than the Pearson matrix. As we are using a relatively small dataset, this difference is not very large, but it increases in magntiude with the size of the dataset. Taking into account the time complexity of operations is a key step in optimzing performance, esepcially with large datasets.

6. Limit plot elements¶

Graphics are an essential tool to understand your data, but they can become difficult to parse if they contain too many elements. VerticaPy provides options that restrict plots to specified elements. To demonstrate, let’s first draw a multi-histogram with a categorical column with thousands of categories:

[3]:

vdf.bar(["name", "survived"])

VerticaPy outputs the bar chart, but the number of categories makes the graph basically incomprehensible. Instead, whenever possible, try to create graphics with as few categories as possible for your use case:

[4]:

vdf.hist(["pclass", "survived"])

To view the cardinality of your variables, use the nunique() method:

[17]:

vdf.nunique()

[17]:

	approx_unique
"pclass"	3.0
"survived"	2.0
"name"	1233.0
"sex"	2.0
"age"	96.0
"sibsp"	7.0
"parch"	8.0
"ticket"	888.0
"fare"	275.0
"cabin"	181.0
"embarked"	3.0
"boat"	26.0
"body"	118.0
"home.dest"	355.0

Rows: 1-14 | Columns: 2

7. Filter unnecessary data¶

Filtering your data is a crucial step in data preparation. Proper filtering avoids unnecessary computations and greatly improves the performance of each method call. While the performance impact can be minimal for small datasets, filtering large datasets is key to improving performance.

For example, if we are only interested in analyzing Titanic passengers who didn’t have a lifeboat, we can filter on this requirement using the `filter() <https://www.vertica.com/python/documentation/1.0.x/html/verticapy.vDataFrame.filter.html>`__ method:

[18]:

vdf.filter("boat IS NOT NULL")

795 elements were filtered.

[18]:

	123 pclass Integer	123 survived Integer	Abc Varchar(164)	Abc sex Varchar(20)	123 age Numeric(8)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(36)	123 fare Numeric(12)	Abc cabin Varchar(30)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc Varchar(100)
1	1	1		male	37.0	1	1	11751	52.5542	D35	S	5	[null]
2	1	1		male	26.0	0	0	111369	30.0	C148	C	5	[null]
3	1	1		female	35.0	0	0	PC 17760	135.6333	C99	S	8	[null]
4	1	1		female	60.0	0	0	11813	76.2917	D15	C	8	[null]
5	1	1		male	45.0	0	0	PC 17594	29.7	A9	C	7	[null]
6	1	1		female	27.0	1	1	PC 17558	247.5208	B58 B60	C	6	[null]
7	1	1		female	35.0	1	0	113803	53.1	C123	S	D	[null]
8	1	1		male	53.0	0	0	113780	28.5	C51	C	B	[null]
9	1	1		female	19.0	0	0	112053	30.0	B42	S	3	[null]
10	1	1		female	58.0	0	1	PC 17582	153.4625	C125	S