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:

import verticapy as vp

Connect to Vertica. This example uses an existing connection called VerticaDSN . For details on how to create a connection, see the Connection tutorial.

Note

You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

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

import verticapy as vp

vp.__version__
Out[2]: '1.1.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.

vp.drop("complex_vmap_test", method = "schema")
Out[3]: False

vp.create_schema("complex_vmap_test")
Out[4]: True

We also set the path to our data:

path= "/home/dbadmin/"

You can download the demo datasets from 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 predicts the complex data structure.

Let’s try both:

data = vp.read_json(
    path + "laliga/2008.json",
    schema = "public",
    ingest_local = False,
    use_complex_dt = True,
)

	123 away_score Int 100%	...	🛠 Row(data_version date,shot_fidelity_version int,xy_fidelity_version int) 90%	🛠 Row(season_id int,season_name varchar(80)) 100%
1	0	...
2	0	...
3	0	...
4	0	...
5	0	...
6	0	...
7	0	...
8	0	...
9	0	...
10	0	...
11	0	...
12	0	...
13	0	...
14	0	...
15	0	...
16	0	...
17	0	...
18	0	...
19	0	...
20	0	...
21	0	...
22	0	...
23	0	...
24	0	...
25	0	...
26	0	...
27	0	...
28	0	...
29	0	...
30	0	...
31	0	...
32	0	...
33	0	...
34	0	...
35	0	...
36	0	...
37	0	...
38	0	...
39	0	...
40	0	...
41	0	...
42	0	...
43	0	...
44	0	...
45	0	...
46	0	...
47	0	...
48	1	...
49	1	...
50	1	...
51	1	...
52	1	...
53	1	...
54	1	...
55	1	...
56	1	...
57	1	...
58	1	...
59	1	...
60	1	...
61	1	...
62	1	...
63	1	...
64	1	...
65	1	...
66	1	...
67	1	...
68	1	...
69	1	...
70	1	...
71	1	...
72	1	...
73	1	...
74	1	...
75	1	...
76	1	...
77	1	...
78	1	...
79	1	...
80	2	...
81	2	...
82	2	...
83	2	...
84	2	...
85	2	...
86	2	...
87	2	...
88	2	...
89	2	...
90	2	...
91	2	...
92	2	...
93	2	...
94	2	...
95	2	...
96	2	...
97	2	...
98	2	...
99	3	...
100	3	...

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

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

	123 away_score Int 100%	...	🛠 Row(data_version date,shot_fidelity_version int,xy_fidelity_version int) 90%	🛠 Row(season_id int,season_name varchar(80)) 100%
1	0	...
2	0	...
3	0	...
4	0	...
5	0	...
6	0	...
7	0	...
8	0	...
9	0	...
10	0	...
11	0	...
12	0	...
13	0	...
14	0	...
15	0	...
16	0	...
17	0	...
18	0	...
19	0	...
20	0	...
21	0	...
22	0	...
23	0	...
24	0	...
25	0	...
26	0	...
27	0	...
28	0	...
29	0	...
30	0	...
31	0	...
32	0	...
33	0	...
34	0	...
35	0	...
36	0	...
37	0	...
38	0	...
39	0	...
40	0	...
41	0	...
42	0	...
43	0	...
44	0	...
45	0	...
46	0	...
47	0	...
48	1	...
49	1	...
50	1	...
51	1	...
52	1	...
53	1	...
54	1	...
55	1	...
56	1	...
57	1	...
58	1	...
59	1	...
60	1	...
61	1	...
62	1	...
63	1	...
64	1	...
65	1	...
66	1	...
67	1	...
68	1	...
69	1	...
70	1	...
71	1	...
72	1	...
73	1	...
74	1	...
75	1	...
76	1	...
77	1	...
78	1	...
79	1	...
80	2	...
81	2	...
82	2	...
83	2	...
84	2	...
85	2	...
86	2	...
87	2	...
88	2	...
89	2	...
90	2	...
91	2	...
92	2	...
93	2	...
94	2	...
95	2	...
96	2	...
97	2	...
98	2	...
99	3	...
100	3	...

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.

data = vp.read_json(
    path + "laliga/2008.json",
    schema = "public",
    ingest_local = False,
    use_complex_dt = True,
    genSQL = True,
)

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:

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

	123 away_score Int 100%	...	🛠 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 100%	🛠 Row(season_id int,season_name varchar(80)) 100%
1	0	...
2	0	...
3	0	...
4	0	...
5	0	...
6	0	...
7	0	...
8	0	...
9	0	...
10	0	...
11	0	...
12	0	...
13	0	...
14	0	...
15	0	...
16	0	...
17	0	...
18	0	...
19	0	...
20	0	...

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

data["away_team"]["away_team_gender"]

	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
18	male
19	male
20	male

We can view any nested data structure by index:

ddata["competition"]["competition_id"]

	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
18	11
19	11
20	11

These nested structures can be used to create features:

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:

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

	🛠 Vmap(378) 54%	...	123 competition.competition_id Int 100%	Abc away_team.country.name Varchar(20) 100%
1		...	11	Spain
2		...	11	Spain
3		...	11	Spain
4		...	11	Spain
5		...	11	Spain
6		...	11	Spain
7		...	11	Spain
8		...	11	Spain
9		...	11	Spain
10		...	11	Spain
11		...	11	Spain
12		...	11	Spain
13		...	11	Spain
14		...	11	Spain
15		...	11	Spain
16		...	11	Spain
17		...	11	Spain
18		...	11	Spain
19		...	11	Spain
20		...	11	Spain
21		...	11	Spain
22		...	11	Spain
23		...	11	Spain
24		...	11	Spain
25		...	11	Spain
26		...	11	Spain
27		...	11	Spain
28		...	11	Spain
29		...	11	Spain
30		...	11	Spain
31		...	11	Spain

We can see that all the columns from the JSON file have been flattened and multiple columns have been created for each sub-column. This causes some loss in data structure, but makes it easy to see the data and to use it for model building.

It is important to note that the data type of certain columns (home_team.managers) is now VMap, and not the ROW type that we saw in the above cells. Even though both are used to capture nested data, there is in a subtile difference between the two.

VMap: More flexible as it stores the data as a string of maps, allowing the ingestion of data in varying shapes. The shape is not fixed and new keys can easily be handled. This is a great option when we don’t know the structure in advance, or if the structure changes over time.
Row: More rigid because the dictionaries, including all the data types, are fixed when they are defined. Newly parsed keys are ignored. But because of it’s rigid structure, it is much more performant than VMaps. They are best used when the file structure is known in advance.

To deconvolve the nested structure, we can use the flatten_arrays parameter in order to make the output strictly formatted. However, it can be an expensive process.

data = vp.read_json(
    path = path + "laliga/2008.json",
    table_name = "laliga_flat",
    schema = "complex_vmap_test",
    ingest_local = False,
    flatten_arrays=True,
)
data.head(100)

	Abc home_team.managers.0.nickname Varchar(42) 54%	...	Abc away_team.away_team_group Varchar(20) 0%	123 away_score Int 100%
1	Manuel Pellegrini	...	[null]	2
2	Pep Guardiola	...	[null]	1
3	Pep Guardiola	...	[null]	1
4	Pep Guardiola	...	[null]	0
5	Juande Ramos	...	[null]	6
6	Lucas Alcaraz	...	[null]	2
7	Manolo Jiménez	...	[null]	3
8	Pep Guardiola	...	[null]	0
9	Pep Guardiola	...	[null]	2
10	Pep Guardiola	...	[null]	0
11	[null]	...	[null]	6
12	[null]	...	[null]	3
13	José Luis Mendilibar	...	[null]	1
14	Juan Muñiz	...	[null]	2
15	Pep Guardiola	...	[null]	0
16	Pep Guardiola	...	[null]	0
17	Pep Guardiola	...	[null]	3
18	[null]	...	[null]	3
19	[null]	...	[null]	0
20	[null]	...	[null]	0
21	[null]	...	[null]	1
22	Pep Guardiola	...	[null]	0
23	Unai Emery	...	[null]	2
24	[null]	...	[null]	2
25	[null]	...	[null]	2
26	[null]	...	[null]	2
27	[null]	...	[null]	1
28	[null]	...	[null]	1
29	[null]	...	[null]	4
30	[null]	...	[null]	0
31	[null]	...	[null]	2

We can even convert columns into other formats, such as string:

data["home_team.managers.0.nickname"].astype(str)

	Abc home_team.managers.0.nickname Varchar 54%	...	Abc home_team.managers.0.name Varchar(64) 54%	123 away_score Int 100%
1	Manuel Pellegrini	...	Manuel Luis Pellegrini Ripamonti	2
2	Pep Guardiola	...	Josep Guardiola i Sala	1
3	Pep Guardiola	...	Josep Guardiola i Sala	1
4	Pep Guardiola	...	Josep Guardiola i Sala	0
5	Juande Ramos	...	Juan de la Cruz Ramos Cano	6
6	Lucas Alcaraz	...	Luis Lucas Alcaraz González	2
7	Manolo Jiménez	...	Manuel Enrique Jiménez Jiménez	3
8	Pep Guardiola	...	Josep Guardiola i Sala	0
9	Pep Guardiola	...	Josep Guardiola i Sala	2
10	Pep Guardiola	...	Josep Guardiola i Sala	0
11	[null]	...	[null]	6
12	[null]	...	[null]	3
13	José Luis Mendilibar	...	José Luis Mendilibar Etxebarria	1
14	Juan Muñiz	...	Juan Ramón López Muñiz	2
15	Pep Guardiola	...	Josep Guardiola i Sala	0
16	Pep Guardiola	...	Josep Guardiola i Sala	0
17	Pep Guardiola	...	Josep Guardiola i Sala	3
18	[null]	...	[null]	3
19	[null]	...	[null]	0
20	[null]	...	[null]	0

Or integer:

data["match_week"].astype(int)

	Abc home_team.managers.0.nickname Varchar 54%	...	Abc home_team.managers.0.name Varchar(64) 54%	123 away_score Int 100%
1	Manuel Pellegrini	...	Manuel Luis Pellegrini Ripamonti	2
2	Pep Guardiola	...	Josep Guardiola i Sala	1
3	Pep Guardiola	...	Josep Guardiola i Sala	1
4	Pep Guardiola	...	Josep Guardiola i Sala	0
5	José Luis Mendilibar	...	José Luis Mendilibar Etxebarria	1
6	Juan Muñiz	...	Juan Ramón López Muñiz	2
7	Pep Guardiola	...	Josep Guardiola i Sala	0
8	Pep Guardiola	...	Josep Guardiola i Sala	0
9	Pep Guardiola	...	Josep Guardiola i Sala	3
10	[null]	...	[null]	3
11	[null]	...	[null]	0
12	[null]	...	[null]	0
13	[null]	...	[null]	1
14	Juande Ramos	...	Juan de la Cruz Ramos Cano	6
15	Lucas Alcaraz	...	Luis Lucas Alcaraz González	2
16	Manolo Jiménez	...	Manuel Enrique Jiménez Jiménez	3
17	Pep Guardiola	...	Josep Guardiola i Sala	0
18	Pep Guardiola	...	Josep Guardiola i Sala	2
19	Pep Guardiola	...	Josep Guardiola i Sala	0
20	[null]	...	[null]	6

It is also possible to:

Cast str to array.
Cast complex data types to json str.
Cast str to VMAP
And much more…

Multiple File Ingestion¶

If we have multiple files with the same extension, we can easily ingest them using the * operator:

data = vp.read_file(
    path = path + "laliga/*.json",
    table_name = "laliga_all",
    ingest_local = False,
    schema = "complex_vmap_test",
)

We can also do this for other file types. For example, CSV:

data = vp.read_csv(
    path = path + "*.csv",
    table_name = "cities_all",
    schema = "complex_vmap_test",
    ingest_local = False,
    insert = True,
)

Materialize¶

When we do not materialize a table, it automatically becomes a flextable:

data = vp.read_json(
    path = path + "laliga/*.json",
    table_name = "laliga_verticapy_test_json",
    schema = "complex_vmap_test",
    ingest_local = False,
    materialize = False,
)
data.head(100)

	123 referee.country.id Integer 22%	...	Abc competition.country_name Varchar(20) 100%	123 away_team.away_team_id Integer 100%
1	112	...	Spain	217
2	112	...	Spain	215
3	214	...	Spain	217
4	112	...	Spain	322
5	112	...	Spain	217
6	112	...	Spain	216
7	214	...	Spain	212
8	112	...	Spain	208
9	112	...	Spain	217
10	112	...	Spain	222
11	112	...	Spain	217
12	112	...	Spain	207
13	112	...	Spain	217
14	112	...	Spain	217
15	112	...	Spain	206
16	214	...	Spain	214
17	214	...	Spain	219
18	112	...	Spain	220
19	112	...	Spain	217
20	112	...	Spain	210
21	112	...	Spain	217
22	112	...	Spain	217
23	112	...	Spain	211
24	112	...	Spain	217
25	112	...	Spain	223
26	112	...	Spain	213
27	112	...	Spain	217
28	112	...	Spain	217
29	112	...	Spain	217
30	112	...	Spain	221
31	112	...	Spain	209
32	112	...	Spain	217
33	112	...	Spain	205
34	214	...	Spain	217
35	112	...	Spain	218
36	214	...	Spain	217
37	[null]	...	Spain	217
38	[null]	...	Spain	216
39	[null]	...	Spain	210
40	[null]	...	Spain	217
41	[null]	...	Spain	222
42	[null]	...	Spain	220
43	[null]	...	Spain	395
44	[null]	...	Spain	1217
45	[null]	...	Spain	217
46	[null]	...	Spain	217
47	[null]	...	Spain	213
48	[null]	...	Spain	217
49	[null]	...	Spain	1218
50	[null]	...	Spain	217
51	[null]	...	Spain	217
52	[null]	...	Spain	215
53	[null]	...	Spain	223
54	[null]	...	Spain	217
55	[null]	...	Spain	217
56	[null]	...	Spain	217
57	[null]	...	Spain	217
58	[null]	...	Spain	217
59	[null]	...	Spain	207
60	[null]	...	Spain	217
61	[null]	...	Spain	217
62	[null]	...	Spain	214
63	[null]	...	Spain	1043
64	[null]	...	Spain	217
65	[null]	...	Spain	422
66	[null]	...	Spain	403
67	[null]	...	Spain	217
68	[null]	...	Spain	212
69	[null]	...	Spain	217
70	[null]	...	Spain	219
71	[null]	...	Spain	217
72	[null]	...	Spain	217
73	[null]	...	Spain	217
74	[null]	...	Spain	217
75	[null]	...	Spain	220
76	[null]	...	Spain	207
77	[null]	...	Spain	422
78	[null]	...	Spain	217
79	[null]	...	Spain	395
80	[null]	...	Spain	213
81	[null]	...	Spain	217
82	[null]	...	Spain	217
83	[null]	...	Spain	217
84	[null]	...	Spain	215
85	[null]	...	Spain	217
86	[null]	...	Spain	214
87	[null]	...	Spain	218
88	[null]	...	Spain	217
89	[null]	...	Spain	221
90	[null]	...	Spain	1043
91	[null]	...	Spain	216
92	[null]	...	Spain	217
93	[null]	...	Spain	1217
94	[null]	...	Spain	217
95	[null]	...	Spain	217
96	[null]	...	Spain	360
97	[null]	...	Spain	217
98	[null]	...	Spain	210
99	[null]	...	Spain	217
100	[null]	...	Spain	215

Some of the columns are VMAPs:

managers = ["away_team.managers", "home_team.managers"]

for m in managers:
    print(data[m].isvmap())

True
True

We can easily flatten the VMaps virtual columns by using the flat_vmap() method:

data.flat_vmap(managers).drop(managers)

	123 referee.country.id Integer 22%	...	Abc referee.country.name Varchar(20) 22%	123 away_team.away_team_id Integer 100%
1	112	...	Italy	217
2	112	...	Italy	215
3	214	...	Spain	217
4	112	...	Italy	322
5	112	...	Italy	217
6	112	...	Italy	216
7	214	...	Spain	212
8	112	...	Italy	208
9	112	...	Italy	217
10	112	...	Italy	222
11	112	...	Italy	217
12	112	...	Italy	207
13	112	...	Italy	217
14	112	...	Italy	217
15	112	...	Italy	206
16	214	...	Spain	214
17	214	...	Spain	219
18	112	...	Italy	220
19	112	...	Italy	217
20	112	...	Italy	210

To check for a flex table, we can use the following function:

from verticapy.sql import isflextable

isflextable(table_name = "laliga_verticapy_test_json", schema = "complex_vmap_test")
Out[9]: True

We can then manually materialize the flextable using the convenient to_db() method:

data.to_db("complex_vmap_test.laliga_to_db");

Once we have stored the database, we can easily create a vDataFrame of the relation:

data_new = vp.vDataFrame("complex_vmap_test.laliga_to_db")

Transformations¶

First, we load the dataset.

from verticapy.datasets import load_amazon

data = load_amazon()

	📅 date Date 100%	...	Abc state Varchar(32) 100%	123 number Int 100%
1	1998-01-01	...	AMAPÁ	0
2	1998-01-01	...	AMAZONAS	0
3	1998-01-01	...	DISTRITO FEDERAL	0
4	1998-01-01	...	ESPÍRITO SANTO	0
5	1998-01-01	...	MARANHÃO	0
6	1998-01-01	...	PARANÁ	0
7	1998-01-01	...	PIAUÍ	0
8	1998-01-01	...	RORAIMA	0
9	1998-01-01	...	SERGIPE	0
10	1998-01-01	...	SÃO PAULO	0
11	1998-02-01	...	GOIÁS	0
12	1998-02-01	...	MATO GROSSO DO SUL	0
13	1998-02-01	...	MINAS GERAIS	0
14	1998-02-01	...	PARAÍBA	0
15	1998-02-01	...	SANTA CATARINA	0
16	1998-02-01	...	SÃO PAULO	0
17	1998-03-01	...	AMAPÁ	0
18	1998-03-01	...	BAHIA	0
19	1998-03-01	...	MATO GROSSO DO SUL	0
20	1998-03-01	...	PARÁ	0

Once we have data in the form of vDataFrame, we can readily convert it to a JSON file:

data.to_json(path = "amazon_json.json")

Now we can load the new JSON file and see the contents:

data = read_json(
    path = "amazon_json.json",
    schema = "complex_vmap_test",
    table_name = "cities_transf_test",
    ingest_local = False,
)

We can even extract the JSON as string and edit it before saving it as a json file:

json_str = data.to_json();

Let’s look at the begining portion of the string:

json_str[0:100]
Out[14]: '[\n{"date": "1998-02-01", "state": "ALAGOAS", "number": 0},\n{"date": "1998-02-01", "state": "BAHIA", '

We can edit a portion of the string and save it again. We’ll change the name of the first State from ACRE to XXXX:

json_str = json_str[:35] + 'XXXX' + json_str[39:];

Now we can save this edited strings file:

out_file = open(path + "amazon_edited.json", "w")

out_file.write(json_str)
Out[17]: 391613

out_file.close()

If we look at the new file, we can see the updated changes:

data = vp.read_json(
    path = path + "amazon_edited.json",
    schema = "complex_vmap_test",
    table_name = "amazon_edit",
    ingest_local = True,
);

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

Let’s search for the changed name:

data[data["state"] == "XXXX"]

	123 number Integer 100%	...	Abc state Varchar(38) 100%	📅 date Date 100%
1	0	...	XXXXOAS	1998-02-01

Now to clean everything up, we can drop our temporary schema:

vp.drop("complex_vmap_test", method = "schema")
Out[20]: True

Conclusion¶

This new functionality not only make it easy to ingest complex data types in different formats, but it enables data wrangling like never before.

The new features provide increased flexibility while keeping the process and syntax simple. You can do all of the following in VerticaPy:

Ingest complex datasets.
Perform convenient column operations.
Switch data types.
Flatten columns and maps into array like structures.