VerticaPy

Python API for Vertica Data Science at Scale

Working with Complex Data Types & VMaps¶

Setup¶

In order to work with complex data types in VerticaPy, you'll need to complete the following three setup tasks:

Import relevant libraries:

In [1]:

import verticapy as vp

Connect to Vertica:

In [ ]:

vp.new_connection({"host": "10.211.55.14", 
                   "port": "5433", 
                   "database": "testdb", 
                   "password": "XxX", 
                   "user": "dbadmin"},
                   name = "Vertica_New_Connection")

Check your VerticaPy version to make sure you have access to the right functions:

In [2]:

vp.__version__

Out[2]:

'0.11.0'

You can make it easier to keep track of your work by creating a custom schema:
Note: Because some tables are repeated in this demonstration, tables with the same names are dropped.

In [3]:

vp.drop("complex_vmap_test", method = "schema")
vp.create_schema("complex_vmap_test")

Out[3]:

True

We also set the path to our data:

In [ ]:

path= "/home/dbadmin/"

You can download the demo datasets by clicking here.

Loading Complex Data¶

There are two ways to load a nested data file:

Load directly using read_json. In this case, you will need to use an additional parameter to identify all the data types. The function loads the data using flex tables and VMaps (Native Vertica MAPS, which are flexible but not optimally performant).
Load using read_file. The function preidcts the complex data structure.

Let's try both:

In [4]:

import verticapy as vp
data = vp.read_json(path + "laliga/2008.json",
                    schema = "public",
                    use_complex_dt = True,)

Out[4]:

	123 away_score Int	🛠 Row(away_team_gender varchar(80),away_team_group varchar(80),away_team_id int,away_team_name varchar(80),country row(id int,name	🛠 Row(competition_id int,competition_name varchar(80),country_name varchar(80))	🛠 Row(id int,name varchar(80))	123 home_score Int	🛠 Row(country row(id int,name varchar(80)),home_team_gender varchar(80),home_team_group varchar(80),home_team_id int,home_team_nam	📅 kick_off Time	📅 last_updated Date	📅 match_date Date	123 match_id Int	Abc match_status Varchar(80)	123 match_week Int	🛠 Row(data_version date,shot_fidelity_version int,xy_fidelity_version int)	🛠 Row(season_id int,season_name varchar(80))
1	0				1		19:00:00	2019-12-16	2008-08-31	69147	available	1
2	0				2		20:00:00	2019-12-16	2009-03-07	69216	available	26
3	0				2		20:00:00	2019-12-16	2009-04-11	69222	available	30
4	0				5		20:00:00	2019-12-16	2009-01-17	69183	available	19
5	0				5		22:00:00	2019-12-16	2008-10-25	69232	available	8
6	0				6		19:00:00	2019-12-16	2009-03-22	69142	available	28
7	1				0		20:00:00	2019-12-16	2009-04-04	69273	available	29
8	2				0		21:00:00	2019-12-16	2008-11-16	69285	available	11
9	2				1		17:00:00	2019-12-16	2009-02-01	69209	available	21
10	4				1		20:00:00	2019-12-16	2008-11-01	69235	available	9
11	6				1		21:00:00	2019-12-16	2008-09-21	69138	available	3
12	1				4		22:00:00	2019-12-16	2009-01-24	69189	available	20
13	1				6		22:00:00	2019-12-16	2008-10-04	69212	available	6
14	2				0		21:03:00	2019-12-16	2009-03-15	69177	available	27
15	2				3		20:00:00	2019-12-16	2008-09-24	69207	available	4
16	3				2		21:00:00	2019-12-16	2009-01-11	69230	available	18
17	0				2		22:00:00	2019-12-16	2008-12-13	69225	available	15
18	0				6		22:00:00	2019-12-16	2008-11-08	69279	available	10
19	1				1		20:00:00	2019-12-16	2008-09-13	69215	available	2
20	1				3		19:00:00	2019-12-16	2009-02-08	69231	available	22
21	2				1		20:00:00	2019-12-16	2009-02-21	69139	available	24
22	2				2		20:00:00	2019-12-16	2009-02-14	69195	available	23
23	2				2		22:00:00	2019-12-16	2009-04-25	69251	available	33
24	3				0		22:00:00	2019-12-16	2008-11-29	69171	available	13
25	3				3		19:03:00	2019-12-16	2009-05-10	69217	available	35
26	3				4		19:00:00	2019-12-16	2009-03-01	69185	available	25
27	0				4		22:00:00	2019-12-16	2008-12-06	69149	available	14
28	1				0		20:00:00	2019-12-16	2009-04-18	69223	available	31
29	2				1		19:00:00	2019-12-16	2008-12-21	69228	available	16
30	2				1		22:00:00	2019-12-16	2008-09-27	69211	available	5
31	6				2		20:00:00	2019-12-16	2009-05-02	69249	available	34

Rows: 1-31 | Columns: 14

Similar to the use of read_json above, we can use read_file to ingest the complex data directly:

In [6]:

data = vp.read_file(path = path + "laliga/2005.json",
                    schema = "complex_vmap_test",)
data

Out[6]:

	123 away_score Int	🛠 Row(away_team_gender varchar(80),away_team_group varchar(80),away_team_id int,away_team_name varchar(80),country row(id int,name	🛠 Row(competition_id int,competition_name varchar(80),country_name varchar(80))	🛠 Row(id int,name varchar(80))	123 home_score Int	🛠 Row(country row(id int,name varchar(80)),home_team_gender varchar(80),home_team_group varchar(80),home_team_id int,home_team_nam	📅 kick_off Time	📅 last_updated Date	📅 match_date Date	123 match_id Int	Abc match_status Varchar(80)	123 match_week Int	🛠 Row(data_version date,shot_fidelity_version int,xy_fidelity_version int)	🛠 Row(season_id int,season_name varchar(80))
1	0				3		22:00:00	2019-12-16	2005-10-22	68318	available	8
2	1				2		19:00:00	2019-12-16	2006-01-15	68342	available	19
3	1				5		22:00:00	2019-12-16	2006-02-18	69172	available	24
4	3				1		21:00:00	2019-12-16	2005-11-06	68339	available	10
5	0				2		19:00:00	2019-12-16	2006-01-22	68324	available	20
6	0				2		20:30:00	2019-12-16	2005-10-26	69164	available	11
7	1				2		21:00:00	2020-02-27	2005-12-11	68321	available	15
8	2				1		22:00:00	2019-12-16	2006-01-07	68348	available	18
9	2				2		20:00:00	2019-12-16	2005-10-01	68317	available	6
10	3				1		22:00:00	2019-12-16	2005-12-17	68322	available	16
11	0				2		21:00:00	2019-12-16	2005-12-20	68323	available	17
12	2				0		22:00:00	2019-12-16	2006-02-25	68351	available	25
13	3				0		20:00:00	2019-12-16	2005-11-19	68319	available	12
14	3				1		19:00:00	2019-12-16	2006-02-05	68350	available	22
15	1				4		21:00:00	2019-12-16	2005-11-27	68354	available	13
16	2				0		19:00:00	2019-12-16	2005-12-04	68320	available	14
17	3				0		19:00:00	2019-12-16	2006-01-29	68325	available	21

Rows: 1-17 | Columns: 14

We can also use the handy 'genSQL' parameter to generate (but not execute) the SQL needed to create the final relation:
Note: This is a great way to customize the data ingestion or alter the final relation types.

In [9]:

sql = vp.read_file(path = path + "laliga/2005.json",
                   schema = "complex_vmap_test",
                   table_name = "laliga_2005",
                   genSQL = True,
                   varchar_varbinary_length = 60,
                   dtype = {"away_score": "float"})
for query in sql:
    print(query)

CREATE TABLE "complex_vmap_test"."laliga_2005"("away_score" float, "away_team" Row("away_team_gender" varchar(60), "away_team_group" varchar(60), "away_team_id" int, "away_team_name" varchar(60), "country" Row("id" int, "name" varchar(60))), "competition" Row("competition_id" int, "competition_name" varchar(60), "country_name" varchar(60)), "competition_stage" Row("id" int, "name" varchar(60)), "home_score" int, "home_team" Row("country" Row("id" int, "name" varchar(60)), "home_team_gender" varchar(60), "home_team_group" varchar(60), "home_team_id" int, "home_team_name" varchar(60)), "kick_off" time, "last_updated" date, "match_date" date, "match_id" int, "match_status" varchar(60), "match_week" int, "metadata" Row("data_version" date, "shot_fidelity_version" int, "xy_fidelity_version" int), "season" Row("season_id" int, "season_name" varchar(60)))
copy "complex_vmap_test"."laliga_2005" from '/scratch_b/qa/ericsson/laliga/2005.json' PARSER FJsonParser()

Feature Exploration¶

In the generated SQL from the above example, we can see that the away_team column is a ROW type with a complex structure consisting of many sub-columns. We can convert this column into a JSON and view its contents:

In [7]:

data["competition_stage"].astype("json")

Out[7]:

	123 away_score Int	🛠 Row(away_team_gender varchar(80),away_team_group varchar(80),away_team_id int,away_team_name varchar(80),country row(id int,name	🛠 Row(competition_id int,competition_name varchar(80),country_name varchar(80))	Abc competition_stage Varchar	123 home_score Int	🛠 Row(country row(id int,name varchar(80)),home_team_gender varchar(80),home_team_group varchar(80),home_team_id int,home_team_nam	📅 kick_off Time	📅 last_updated Date	📅 match_date Date	123 match_id Int	Abc match_status Varchar(80)	123 match_week Int	🛠 Row(data_version date,shot_fidelity_version int,xy_fidelity_version int)	🛠 Row(season_id int,season_name varchar(80))
1	0			{"id":1,"name":"Regular Season"}	2		19:00:00	2019-12-16	2006-01-22	68324	available	20
2	0			{"id":1,"name":"Regular Season"}	2		20:30:00	2019-12-16	2005-10-26	69164	available	11
3	0			{"id":1,"name":"Regular Season"}	2		21:00:00	2019-12-16	2005-12-20	68323	available	17
4	0			{"id":1,"name":"Regular Season"}	3		22:00:00	2019-12-16	2005-10-22	68318	available	8
5	1			{"id":1,"name":"Regular Season"}	2		19:00:00	2019-12-16	2006-01-15	68342	available	19
6	1			{"id":1,"name":"Regular Season"}	2		21:00:00	2020-02-27	2005-12-11	68321	available	15
7	1			{"id":1,"name":"Regular Season"}	4		21:00:00	2019-12-16	2005-11-27	68354	available	13
8	1			{"id":1,"name":"Regular Season"}	5		22:00:00	2019-12-16	2006-02-18	69172	available	24
9	2			{"id":1,"name":"Regular Season"}	0		19:00:00	2019-12-16	2005-12-04	68320	available	14
10	2			{"id":1,"name":"Regular Season"}	0		22:00:00	2019-12-16	2006-02-25	68351	available	25
11	2			{"id":1,"name":"Regular Season"}	1		22:00:00	2019-12-16	2006-01-07	68348	available	18
12	2			{"id":1,"name":"Regular Season"}	2		20:00:00	2019-12-16	2005-10-01	68317	available	6
13	3			{"id":1,"name":"Regular Season"}	0		19:00:00	2019-12-16	2006-01-29	68325	available	21
14	3			{"id":1,"name":"Regular Season"}	0		20:00:00	2019-12-16	2005-11-19	68319	available	12
15	3			{"id":1,"name":"Regular Season"}	1		19:00:00	2019-12-16	2006-02-05	68350	available	22
16	3			{"id":1,"name":"Regular Season"}	1		21:00:00	2019-12-16	2005-11-06	68339	available	10
17	3			{"id":1,"name":"Regular Season"}	1		22:00:00	2019-12-16	2005-12-17	68322	available	16

Rows: 1-17 | Columns: 14

As with a normal vDataFrame, we can easily extract the values from the sub-columns:

In [8]:

data["away_team"]["away_team_gender"]

Out[8]:

	Abc away_team_gender Varchar(80)
1	male
2	male
3	male
4	male
5	male
6	male
7	male
8	male
9	male
10	male
11	male
12	male
13	male
14	male
15	male
16	male
17	male

Rows: 17 | Column: away_team_gender | Type: Varchar(80)

We can view any nested data structure by index:

In [9]:

data["competition"]["competition_id"]

Out[9]:

	123 competition_id Integer
1	11
2	11
3	11
4	11
5	11
6	11
7	11
8	11
9	11
10	11
11	11
12	11
13	11
14	11
15	11
16	11
17	11

Rows: 17 | Column: competition_id | Type: Integer

These nested structures can be used to create features:

In [10]:

data["name_home"] = data["home_team"]["home_team_name"]

We can even flatten the nested structure inside a json file, either flattening the entire file or just particular columns:

In [17]:

data = vp.read_json(path = path + "laliga/2008.json",
                    table_name = "laliga_flat",
                    schema = "complex_vmap_test",
                    flatten_maps = True,)
data

The table "complex_vmap_test"."laliga_flat" has been successfully created.

Out[17]:

	Abc Vmap(368)	Abc Vmap(378)	Abc season.season_name Varchar(20)	123 season.season_id Int	123 metadata.xy_fidelity_version Int	123 metadata.shot_fidelity_version Int	📅 metadata.data_version Date	123 match_week Int	Abc match_status Varchar(20)	123 match_id Int	📅 match_date Date	📅 last_updated Timestamp	📅 kick_off Time	Abc home_team.home_team_name Varchar(34)	123 home_team.home_team_id Int	Abc home_team.home_team_group Varchar(20)	Abc home_team.home_team_gender Varchar(20)	Abc home_team.country.name Varchar(20)	123 home_team.country.id Int	123 home_score Int	Abc competition_stage.name Varchar(28)	123 competition_stage.id Int	Abc competition.country_name Varchar(20)	Abc competition.competition_name Varchar(20)	123 competition.competition_id Int	Abc away_team.country.name Varchar(20)	123 away_team.country.id Int	Abc away_team.away_team_name Varchar(38)	123 away_team.away_team_id Int	Abc away_team.away_team_group Varchar(20)	Abc away_team.away_team_gender Varchar(20)	123 away_score Int
1			2008/2009	41	2	2	2000-01-01	1	available	69147	2008-08-31	2019-12-16 23:09:16.168756	19:00:00	Numancia	444	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	0
2			2008/2009	41	2	2	2000-01-01	2	available	69215	2008-09-13	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Racing Santander	1217	[null]	male	1
3			2008/2009	41	2	2	2000-01-01	3	available	69138	2008-09-21	2019-12-16 23:09:16.168756	21:00:00	Sporting Gijón	1041	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	6
4			2008/2009	41	2	2	2000-01-01	4	available	69207	2008-09-24	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	3	Regular Season	1	Spain	La Liga	11	Spain	214	Real Betis	218	[null]	male	2
5			2008/2009	41	2	2	2000-01-01	5	available	69211	2008-09-27	2019-12-16 23:09:16.168756	22:00:00	Espanyol	214	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
6			2008/2009	41	2	2	2000-01-01	6	available	69212	2008-10-04	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	6	Regular Season	1	Spain	La Liga	11	Spain	214	Atlético Madrid	212	[null]	male	1
7			2008/2009	41	2	2	2000-01-01	8	available	69232	2008-10-25	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	5	Regular Season	1	Spain	La Liga	11	Spain	214	Almería	403	[null]	male	0
8			2008/2009	41	2	2	2000-01-01	9	available	69235	2008-11-01	2019-12-16 23:09:16.168756	20:00:00	Málaga	223	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	4
9			2008/2009	41	2	2	2000-01-01	10	available	69279	2008-11-08	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	6	Regular Season	1	Spain	La Liga	11	Spain	214	Real Valladolid	901	[null]	male	0
10			2008/2009	41	2	2	2000-01-01	11	available	69285	2008-11-16	2019-12-16 23:09:16.168756	21:00:00	Recreativo Huelva	1220	[null]	male	Spain	214	0	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
11			2008/2009	41	2	2	2000-01-01	13	available	69171	2008-11-29	2019-12-16 23:09:16.168756	22:00:00	Sevilla	213	[null]	male	Spain	214	0	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	3
12			2008/2009	41	2	2	2000-01-01	14	available	69149	2008-12-06	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	4	Regular Season	1	Spain	La Liga	11	Spain	214	Valencia	207	[null]	male	0
13			2008/2009	41	2	2	2000-01-01	15	available	69225	2008-12-13	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	2	Regular Season	1	Spain	La Liga	11	Spain	214	Real Madrid	220	[null]	male	0
14			2008/2009	41	2	2	2000-01-01	16	available	69228	2008-12-21	2019-12-16 23:09:16.168756	19:00:00	Villarreal	222	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
15			2008/2009	41	2	2	2000-01-01	18	available	69230	2009-01-11	2019-12-16 23:09:16.168756	21:00:00	Osasuna	422	[null]	male	Spain	214	2	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	3
16			2008/2009	41	2	2	2000-01-01	19	available	69183	2009-01-17	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	5	Regular Season	1	Spain	La Liga	11	Spain	214	Deportivo La Coruna	219	[null]	male	0
17			2008/2009	41	2	2	2000-01-01	20	available	69189	2009-01-24	2019-12-16 23:09:16.168756	22:00:00	Barcelona	217	[null]	male	Spain	214	4	Regular Season	1	Spain	La Liga	11	Spain	214	Numancia	444	[null]	male	1
18			2008/2009	41	2	2	2000-01-01	21	available	69209	2009-02-01	2019-12-16 23:09:16.168756	17:00:00	Racing Santander	1217	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
19			2008/2009	41	2	2	2000-01-01	22	available	69231	2009-02-08	2019-12-16 23:09:16.168756	19:00:00	Barcelona	217	[null]	male	Spain	214	3	Regular Season	1	Spain	La Liga	11	Spain	214	Sporting Gijón	1041	[null]	male	1
20			2008/2009	41	2	2	2000-01-01	23	available	69195	2009-02-14	2019-12-16 23:09:16.168756	20:00:00	Real Betis	218	[null]	male	Spain	214	2	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
21			2008/2009	41	2	2	2000-01-01	24	available	69139	2009-02-21	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	1	Regular Season	1	Spain	La Liga	11	Spain	214	Espanyol	214	[null]	male	2
22			2008/2009	41	2	2	2000-01-01	25	available	69185	2009-03-01	2019-12-16 23:09:16.168756	19:00:00	Atlético Madrid	212	[null]	male	Spain	214	4	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	3
23			2008/2009	41	2	2	2000-01-01	26	available	69216	2009-03-07	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	2	Regular Season	1	Spain	La Liga	11	Spain	214	Athletic Bilbao	215	[null]	male	0
24			2008/2009	41	2	2	2000-01-01	27	available	69177	2009-03-15	2019-12-16 23:09:16.168756	21:03:00	Almería	403	[null]	male	Spain	214	0	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	2
25			2008/2009	41	2	2	2000-01-01	28	available	69142	2009-03-22	2019-12-16 23:09:16.168756	19:00:00	Barcelona	217	[null]	male	Spain	214	6	Regular Season	1	Spain	La Liga	11	Spain	214	Málaga	223	[null]	male	0
26			2008/2009	41	2	2	2000-01-01	29	available	69273	2009-04-04	2019-12-16 23:09:16.168756	20:00:00	Real Valladolid	901	[null]	male	Spain	214	0	Regular Season	1	Spain	La Liga	11	Spain	214	Barcelona	217	[null]	male	1
27			2008/2009	41	2	2	2000-01-01	30	available	69222	2009-04-11	2019-12-16 23:09:16.168756	20:00:00	Barcelona	217	[null]	male	Spain	214	2	Regular Season	1	Spain	La Liga	11	Spain	214	Recreativo Huelva	1220	[null]	male	0
28			2008/2009	41	2