Data Ingestion#

VerticaPy supports the ingestion of data in the following formats into Vertica: - CSV - JSON - Parquet - SHP - ORC - Avro - pandas DataFrame

You can use the `read_file() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_file.html>`__ function to ingest all the above file types except pandas DataFrames and SHP, which instead use file-specific ingestion functions—`read_pandas() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_pandas.html>`__ and `read_shp() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_shp.html>`__. There are also file-specifc ingestion functions for JSON, Avro, and CSV files that use flex tables to ingest the data.

Unless you specify the columns’ data types with the dtype parameter, the ingestion functions automatically predict the data type of each column. If you provide the column data types, the function does not need to parse the file and predict data types, likely increasing ingestion speed and precision. For the read_file() function, if the file to ingest is located in the Vertica database, you must provide the column data types with the dtype parameter.

NOTE: As performance optimizations made in the Vertica database are carried over to VerticaPy, try to optimize the structure of your projections for new tables in Vertica.

In the following sections, we will explore a few of the ingestion functions and some of the options they support.

Ingest files with `read_file`#

The read_file() function inspects and ingests files in any of the following formats: - CSV - Parquet - ORC - JSON - Avro

Some of the supported function options include: - dtype: provide a dictionary of data types for the columns, where the keys are the column names and the values are the column data types. The data types in the dictionary replace the automatically predicted data types. - insert: if set to True, the data is ingested into the relation specified by the table_name and, optionally, the schema paraemters. - table_name: specifies the name of the table to create in the Vertica database, or the name of the table to which the data is inserted. - temporary_table: if set to True, creates a temporary table. - ingest_local: if set to True, the file is ingested from the local machine. In this case, the dtypes parameter is optional; if no value is provided, the function predicts the data type of each column. - genSQL: if set to True, the function generates the SQL code the creates the table but does not execute it. This is a convenient way to check the final relation types.

For a full list of supported options, see the documentation or use the help() function.

NOTE: All data files used in this tutorial are availble in the VerticaPy datasets directory. For demo purposes, the following examples ingest the data files using read_file() and other file-specific read functions. However, VerticaPy includes a set of dataset loading functions that allow you to easily ingest the data files in the datasets directory.

In the following examples, we will demonstrate how to use the read_file() function to ingest data into Vertica. Both file location options, in-database and local, will be explored.

Let’s begin with the case where the file is located in the database. We’ll ingest the iris.csv file, a popular classification dataset. First, before we ingest the file, run the function with the genSQL parameter set to True to view the SQL that will be used to create the table. Because the file is located in the database, we must specify the data types for each column with the dtypes parameter:

NOTE: For the examples in this tutorial, replace path-to-file in the path parameter with the path to the file in your Vertica database or local machine.

[2]:

import verticapy as vp

vp.read_file(
    path = "path-to-file/iris.csv",
    dtype = {
        "Id": "Integer",
        "SepalLengthCm": "Numeric",
        "SepalWidthCm": "Numeric",
        "PetalLengthCm": "Numeric",
        "PetalWidthCm": "Numeric",
        "Species": "Varchar(20)",
    },
    schema = "public",
    genSQL = True,
)

[2]:

['CREATE TABLE "public"."iris"("Id" Integer, "SepalLengthCm" Numeric, "SepalWidthCm" Numeric, "PetalLengthCm" Numeric, "PetalWidthCm" Numeric, "Species" Varchar(20))',
 'COPY "public"."iris"("Id", "SepalLengthCm", "SepalWidthCm", "PetalLengthCm", "PetalWidthCm", "Species") FROM \'/home/dbadmin/verticapy/VerticaPy/verticapy/datasets/data/iris.csv\' UNCOMPRESSED DELIMITER \',\' NULL \'\' ENCLOSED BY \'"\' ESCAPE AS \'\x17\' SKIP 1']

To ingest the file into Vertica, remove the genSQL parameter from the above command and rerun the function:

NOTE: If no table name is specified, the name of the file is used for the table name:

[3]:

vp.read_file(
    path = "path-to-file/iris.csv",
    dtype = {
        "Id": "Integer",
        "SepalLengthCm": "Numeric",
        "SepalWidthCm": "Numeric",
        "PetalLengthCm": "Numeric",
        "PetalWidthCm": "Numeric",
        "Species": "Varchar(20)",
    },
    schema = "public",
)

The table "public"."iris" has been successfully created.

[3]:

	123 Id Integer	123 SepalLengthCm Numeric(39)	123 SepalWidthCm Numeric(39)	123 PetalLengthCm Numeric(39)	123 PetalWidthCm Numeric(39)	Abc Species Varchar(20)
1	45	5.1	3.8	1.9	0.4	Iris-setosa
2	57	6.3	3.3	4.7	1.6	Iris-versicolor
3	59	6.6	2.9	4.6	1.3	Iris-versicolor
4	71	5.9	3.2	4.8	1.8	Iris-versicolor
5	90	5.5	2.5	4.0	1.3	Iris-versicolor
6	102	5.8	2.7	5.1	1.9	Iris-virginica
7	103	7.1	3.0	5.9	2.1	Iris-virginica
8	115	5.8	2.8	5.1	2.4	Iris-virginica
9	119	7.7	2.6	6.9	2.3	Iris-virginica
10	122	5.6	2.8	4.9	2.0	Iris-virginica
11	132	7.9	3.8	6.4	2.0	Iris-virginica
12	141	6.7	3.1	5.6	2.4	Iris-virginica
13	150	5.9	3.0	5.1	1.8	Iris-virginica
14	1	5.1	3.5	1.4	0.2	Iris-setosa
15	2	4.9	3.0	1.4	0.2	Iris-setosa
16	5	5.0	3.6	1.4	0.2	Iris-setosa
17	7	4.6	3.4	1.4	0.3	Iris-setosa
18	8	5.0	3.4	1.5	0.2	Iris-setosa
19	10	4.9	3.1	1.5	0.1	Iris-setosa
20	15	5.8	4.0	1.2	0.2	Iris-setosa
21	16	5.7	4.4	1.5	0.4	Iris-setosa
22	27	5.0	3.4	1.6	0.4	Iris-setosa
23	32	5.4	3.4	1.5	0.4	Iris-setosa
24	49	5.3	3.7	1.5	0.2	Iris-setosa
25	63	6.0	2.2	4.0	1.0	Iris-versicolor
26	65	5.6	2.9	3.6	1.3	Iris-versicolor
27	66	6.7	3.1	4.4	1.4	Iris-versicolor
28	68	5.8	2.7	4.1	1.0	Iris-versicolor
29	85	5.4	3.0	4.5	1.5	Iris-versicolor
30	88	6.3	2.3	4.4	1.3	Iris-versicolor
31	97	5.7	2.9	4.2	1.3	Iris-versicolor
32	126	7.2	3.2	6.0	1.8	Iris-virginica
33	133	6.4	2.8	5.6	2.2	Iris-virginica
34	17	5.4	3.9	1.3	0.4	Iris-setosa
35	42	4.5	2.3	1.3	0.3	Iris-setosa
36	52	6.4	3.2	4.5	1.5	Iris-versicolor
37	64	6.1	2.9	4.7	1.4	Iris-versicolor
38	69	6.2	2.2	4.5	1.5	Iris-versicolor
39	76	6.6	3.0	4.4	1.4	Iris-versicolor
40	96	5.7	3.0	4.2	1.2	Iris-versicolor
41	104	6.3	2.9	5.6	1.8	Iris-virginica
42	109	6.7	2.5	5.8	1.8	Iris-virginica
43	112	6.4	2.7	5.3	1.9	Iris-virginica
44	114	5.7	2.5	5.0	2.0	Iris-virginica
45	123	7.7	2.8	6.7	2.0	Iris-virginica
46	125	6.7	3.3	5.7	2.1	Iris-virginica
47	145	6.7	3.3	5.7	2.5	Iris-virginica
48	147	6.3	2.5	5.0	1.9	Iris-virginica
49	14	4.3	3.0	1.1	0.1	Iris-setosa
50	23	4.6	3.6	1.0	0.2	Iris-setosa
51	24	5.1	3.3	1.7	0.5	Iris-setosa
52	30	4.7	3.2	1.6	0.2	Iris-setosa
53	47	5.1	3.8	1.6	0.2	Iris-setosa
54	48	4.6	3.2	1.4	0.2	Iris-setosa
55	67	5.6	3.0	4.5	1.5	Iris-versicolor
56	78	6.7	3.0	5.0	1.7	Iris-versicolor
57	80	5.7	2.6	3.5	1.0	Iris-versicolor
58	82	5.5	2.4	3.7	1.0	Iris-versicolor
59	143	5.8	2.7	5.1	1.9	Iris-virginica
60	6	5.4	3.9	1.7	0.4	Iris-setosa
61	12	4.8	3.4	1.6	0.2	Iris-setosa
62	20	5.1	3.8	1.5	0.3	Iris-setosa
63	38	4.9	3.1	1.5	0.1	Iris-setosa
64	40	5.1	3.4	1.5	0.2	Iris-setosa
65	44	5.0	3.5	1.6	0.6	Iris-setosa
66	53	6.9	3.1	4.9	1.5	Iris-versicolor
67	70	5.6	2.5	3.9	1.1	Iris-versicolor
68	79	6.0	2.9	4.5	1.5	Iris-versicolor
69	84	6.0	2.7	5.1	1.6	Iris-versicolor
70	86	6.0	3.4	4.5	1.6	Iris-versicolor
71	100	5.7	2.8	4.1	1.3	Iris-versicolor
72	101	6.3	3.3	6.0	2.5	Iris-virginica
73	106	7.6	3.0	6.6	2.1	Iris-virginica
74	107	4.9	2.5	4.5	1.7	Iris-virginica
75	108	7.3	2.9	6.3	1.8	Iris-virginica
76	113	6.8	3.0	5.5	2.1	Iris-virginica
77	124	6.3	2.7	4.9	1.8	Iris-virginica
78	146	6.7	3.0	5.2	2.3	Iris-virginica
79	4	4.6	3.1	1.5	0.2	Iris-setosa
80	13	4.8	3.0	1.4	0.1	Iris-setosa
81	25	4.8	3.4	1.9	0.2	Iris-setosa
82	26	5.0	3.0	1.6	0.2	Iris-setosa
83	54	5.5	2.3	4.0	1.3	Iris-versicolor
84	55	6.5	2.8	4.6	1.5	Iris-versicolor
85	56	5.7	2.8	4.5	1.3	Iris-versicolor
86	62	5.9	3.0	4.2	1.5	Iris-versicolor
87	87	6.7	3.1	4.7	1.5	Iris-versicolor
88	91	5.5	2.6	4.4	1.2	Iris-versicolor
89	94	5.0	2.3	3.3	1.0	Iris-versicolor
90	117	6.5	3.0	5.5	1.8	Iris-virginica
91	127	6.2	2.8	4.8	1.8	Iris-virginica
92	130	7.2	3.0	5.8	1.6	Iris-virginica
93	140	6.9	3.1	5.4	2.1	Iris-virginica
94	142	6.9	3.1	5.1	2.3	Iris-virginica
95	9	4.4	2.9	1.4	0.2	Iris-setosa
96	18	5.1	3.5	1.4	0.3	Iris-setosa
97	19	5.7	3.8	1.7	0.3	Iris-setosa
98	33	5.2	4.1	1.5	0.1	Iris-setosa
99	35	4.9	3.1	1.5	0.1	Iris-setosa
100	46	4.8	3.0	1.4	0.3	Iris-setosa

Rows: 1-100 | Columns: 6

When the file to ingest is located on your local machine, you must set the ingest_local parameter to True:

NOTE: In some cases where the CSV file has a very complex structure, local ingestion might fail. If this occurs, you will have to move the file into the database and then ingest the file from that location.

[5]:

vp.read_file(
    path = "path-to-file/iris.csv",
    schema = "public",
    table_name = "iris_local",
    ingest_local = True,
)

The table "public"."iris_local" has been successfully created.

[5]:

	123 Id Integer	123 SepalLengthCm Numeric(7)	123 SepalWidthCm Numeric(7)	123 PetalLengthCm Numeric(7)	123 PetalWidthCm Numeric(7)	Abc Species Varchar(30)
1	22	5.1	3.7	1.5	0.4	Iris-setosa
2	33	5.2	4.1	1.5	0.1	Iris-setosa
3	37	5.5	3.5	1.3	0.2	Iris-setosa
4	42	4.5	2.3	1.3	0.3	Iris-setosa
5	57	6.3	3.3	4.7	1.6	Iris-versicolor
6	68	5.8	2.7	4.1	1.0	Iris-versicolor
7	72	6.1	2.8	4.0	1.3	Iris-versicolor
8	75	6.4	2.9	4.3	1.3	Iris-versicolor
9	107	4.9	2.5	4.5	1.7	Iris-virginica
10	108	7.3	2.9	6.3	1.8	Iris-virginica
11	150	5.9	3.0	5.1	1.8	Iris-virginica
12	9	4.4	2.9	1.4	0.2	Iris-setosa
13	24	5.1	3.3	1.7	0.5	Iris-setosa
14	28	5.2	3.5	1.5	0.2	Iris-setosa
15	31	4.8	3.1	1.6	0.2	Iris-setosa
16	34	5.5	4.2	1.4	0.2	Iris-setosa
17	38	4.9	3.1	1.5	0.1	Iris-setosa
18	69	6.2	2.2	4.5	1.5	Iris-versicolor
19	76	6.6	3.0	4.4	1.4	Iris-versicolor
20	82	5.5	2.4	3.7	1.0	Iris-versicolor
21	92	6.1	3.0	4.6	1.4	Iris-versicolor
22	93	5.8	2.6	4.0	1.2	Iris-versicolor
23	104	6.3	2.9	5.6	1.8	Iris-virginica
24	116	6.4	3.2	5.3	2.3	Iris-virginica
25	118	7.7	3.8	6.7	2.2	Iris-virginica
26	121	6.9	3.2	5.7	2.3	Iris-virginica
27	128	6.1	3.0	4.9	1.8	Iris-virginica
28	146	6.7	3.0	5.2	2.3	Iris-virginica
29	12	4.8	3.4	1.6	0.2	Iris-setosa
30	44	5.0	3.5	1.6	0.6	Iris-setosa
31	45	5.1	3.8	1.9	0.4	Iris-setosa
32	56	5.7	2.8	4.5	1.3	Iris-versicolor
33	60	5.2	2.7	3.9	1.4	Iris-versicolor
34	67	5.6	3.0	4.5	1.5	Iris-versicolor
35	78	6.7	3.0	5.0	1.7	Iris-versicolor
36	86	6.0	3.4	4.5	1.6	Iris-versicolor
37	95	5.6	2.7	4.2	1.3	Iris-versicolor
38	96	5.7	3.0	4.2	1.2	Iris-versicolor
39	98	6.2	2.9	4.3	1.3	Iris-versicolor
40	105	6.5	3.0	5.8	2.2	Iris-virginica
41	119	7.7	2.6	6.9	2.3	Iris-virginica
42	141	6.7	3.1	5.6	2.4	Iris-virginica
43	6	5.4	3.9	1.7	0.4	Iris-setosa
44	14	4.3	3.0	1.1	0.1	Iris-setosa
45	19	5.7	3.8	1.7	0.3	Iris-setosa
46	32	5.4	3.4	1.5	0.4	Iris-setosa
47	36	5.0	3.2	1.2	0.2	Iris-setosa
48	63	6.0	2.2	4.0	1.0	Iris-versicolor
49	83	5.8	2.7	3.9	1.2	Iris-versicolor
50	87	6.7	3.1	4.7	1.5	Iris-versicolor
51	117	6.5	3.0	5.5	1.8	Iris-virginica
52	126	7.2	3.2	6.0	1.8	Iris-virginica
53	132	7.9	3.8	6.4	2.0	Iris-virginica
54	135	6.1	2.6	5.6	1.4	Iris-virginica
55	136	7.7	3.0	6.1	2.3	Iris-virginica
56	138	6.4	3.1	5.5	1.8	Iris-virginica
57	1	5.1	3.5	1.4	0.2	Iris-setosa
58	2	4.9	3.0	1.4	0.2	Iris-setosa
59	4	4.6	3.1	1.5	0.2	Iris-setosa
60	13	4.8	3.0	1.4	0.1	Iris-setosa
61	16	5.7	4.4	1.5	0.4	Iris-setosa
62	17	5.4	3.9	1.3	0.4	Iris-setosa
63	30	4.7	3.2	1.6	0.2	Iris-setosa
64	47	5.1	3.8	1.6	0.2	Iris-setosa
65	74	6.1	2.8	4.7	1.2	Iris-versicolor
66	102	5.8	2.7	5.1	1.9	Iris-virginica
67	113	6.8	3.0	5.5	2.1	Iris-virginica
68	114	5.7	2.5	5.0	2.0	Iris-virginica
69	115	5.8	2.8	5.1	2.4	Iris-virginica
70	127	6.2	2.8	4.8	1.8	Iris-virginica
71	129	6.4	2.8	5.6	2.1	Iris-virginica
72	131	7.4	2.8	6.1	1.9	Iris-virginica
73	140	6.9	3.1	5.4	2.1	Iris-virginica
74	142	6.9	3.1	5.1	2.3	Iris-virginica
75	15	5.8	4.0	1.2	0.2	Iris-setosa
76	18	5.1	3.5	1.4	0.3	Iris-setosa
77	21	5.4	3.4	1.7	0.2	Iris-setosa
78	35	4.9	3.1	1.5	0.1	Iris-setosa
79	40	5.1	3.4	1.5	0.2	Iris-setosa
80	58	4.9	2.4	3.3	1.0	Iris-versicolor
81	59	6.6	2.9	4.6	1.3	Iris-versicolor
82	61	5.0	2.0	3.5	1.0	Iris-versicolor
83	64	6.1	2.9	4.7	1.4	Iris-versicolor
84	77	6.8	2.8	4.8	1.4	Iris-versicolor
85	80	5.7	2.6	3.5	1.0	Iris-versicolor
86	88	6.3	2.3	4.4	1.3	Iris-versicolor
87	91	5.5	2.6	4.4	1.2	Iris-versicolor
88	122	5.6	2.8	4.9	2.0	Iris-virginica
89	123	7.7	2.8	6.7	2.0	Iris-virginica
90	134	6.3	2.8	5.1	1.5	Iris-virginica
91	148	6.5	3.0	5.2	2.0	Iris-virginica
92	8	5.0	3.4	1.5	0.2	Iris-setosa
93	11	5.4	3.7	1.5	0.2	Iris-setosa
94	23	4.6	3.6	1.0	0.2	Iris-setosa
95	26	5.0	3.0	1.6	0.2	Iris-setosa
96	29	5.2	3.4	1.4	0.2	Iris-setosa
97	50	5.0	3.3	1.4	0.2	Iris-setosa
98	53	6.9	3.1	4.9	1.5	Iris-versicolor
99	54	5.5	2.3	4.0	1.3	Iris-versicolor
100	62	5.9	3.0	4.2	1.5	Iris-versicolor

Rows: 1-100 | Columns: 6

To ingest multiple files of the same type, use the following syntax in the path parameter (in this case for multiple CSV files): path = "``path-to-files``/*.csv"

Ingest CSV files#

In addition to read_file(), you can also ingest CSV files with the read_csv() function, which ingests the file using flex tables. This function provides options not available in read_file(), such as: - sep: specify the column separator. - parse_nrows: the function creates a file of nrows from the data file to identify the data types. This file is then dropped and the entire data file is ingested. If your data file is large, this data type inference process should speed up the file ingestion speed. - materialize: if set to True, the flex table used to ingest the data file is materialized into a table; otherwise, the data remains in a flex table.

For a full list of supported options, see the read_csv() reference page or use the help() function.

In the following example, we will use read_csv() to ingest a subset of the Titanic dataset. To begin, load the entire Titanic dataset using the load_titanic() function:

[6]:

from verticapy.datasets import load_titanic

titanic = load_titanic()

To convert a subset of the dataset to a CSV file, select the desired rows in the dataset and use the `to_csv() <https://www.vertica.com/python/documentation/1.0.x/html/verticapy.vDataFrame.to_csv.html>`__ vDataFrame method:

[7]:

titanic[0:50].to_csv(
    path = "titanic_subset.csv"
)

Before ingesting the above CSV file, we can check its columns and their data types with the `pcsv() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.pcsv.html>`__ function:

[8]:

vp.pcsv(path = "titanic_subset.csv",
    sep = ",",
    na_rep = "",
)

[8]:

{'sibsp': 'Integer',
 'sex': 'Varchar(20)',
 'pclass': 'Integer',
 'parch': 'Integer',
 'name': 'Varchar(112)',
 'fare': 'Numeric(11,6)',
 'embarked': 'Varchar(20)',
 'body': 'Integer',
 'age': 'Numeric(8,4)',
 'boat': 'Varchar(100)',
 'ticket': 'Varchar(24)',
 'survived': 'Integer',
 'home.dest': 'Varchar(92)',
 'cabin': 'Varchar(22)'}

Now, setting the ingest_local parameter to True, ingest the CSV file into the Vertica database:

[9]:

vp.read_csv("titanic_subset.csv",
    schema = "public",
    table_name = "titanic_subset",
    sep = ",",
    ingest_local = True
)

The table "public"."titanic_subset" has been successfully created.

[9]:

	123 pclass Integer	123 survived Integer	Abc Varchar(112)	Abc sex Varchar(20)	123 age Numeric(10)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(24)	123 fare Numeric(13)	Abc cabin Varchar(22)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc Varchar(92)
1	1	0		male	58.0	0	2	35273	113.275	D48	C	[null]	122
2	1	0		male	51.0	0	1	PC 17597	61.3792	[null]	C	[null]	[null]
3	1	1		female	14.0	1	2	113760	120.0	B96 B98	S	4	[null]
4	1	1		female	[null]	0	0	17421	110.8833	[null]	C	4	[null]
5	1	1		male	48.0	1	0	19996	52.0	C126	S	5 7	[null]
6	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]
7	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]
8	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]
9	1	0		male	57.0	1	0	PC 17569	146.5208	B78	C	[null]	[null]
10	1	0		male	64.0	1	0	110813	75.25	D37	C	[null]	[null]
11	1	1		female	35.0	0	0	113503	211.5	C130	C	4	[null]
12	1	1		female	33.0	0	0	110152	86.5	B77	S	8	[null]
13	2	0		male	36.0	0	0	229236	13.0	[null]	S	[null]	236
14	2	0		male	31.0	0	0	C.A. 18723	10.5	[null]	S	[null]	165
15	1	0		male	24.0	0	0	PC 17593	79.2	B86	C	[null]	[null]
16	1	0		male	47.0	0	0	113796	42.4	[null]	S	[null]	[null]
17	1	1		female	43.0	0	1	24160	211.3375	B3	S	2	[null]
18	1	1		male	35.0	0	0	PC 17475	26.2875	E24	S	5	[null]
19	2	0		male	44.0	0	0	248746	13.0	[null]	S	[null]	35
20	2	0		male	22.0	2	0	C.A. 31029	31.5	[null]	S	[null]	[null]
21	2	0		male	30.0	0	0	250653	13.0	[null]	S	[null]	75
22	1	0		male	[null]	0	0	PC 17600	30.6958	[null]	C	14	[null]
23	1	1		female	53.0	2	0	11769	51.4792	C101	S	D	[null]
24	1	1		male	36.0	0	1	PC 17755	512.3292	B51 B53 B55	C	3	[null]
25	1	1		female	39.0	0	0	24160	211.3375	[null]	S	2	[null]
26	1	1		female	60.0	1	0	110813	75.25	D37	C	5	[null]
27	2	0		male	21.0	0	0	S.O.C. 14879	73.5	[null]	S	[null]	[null]
28	2	0		male	47.0	0	0	237565	15.0	[null]	S	[null]	[null]
29	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]
30	1	0		male	37.0	1	0	113803	53.1	C123	S	[null]	[null]
31	1	1		female	35.0	1	0	36973	83.475	C83	S	D	[null]
32	1	1		male	30.0	1	0	13236	57.75	C78	C	11	[null]
33	2	0		female	44.0	1	0	244252	26.0	[null]	S	[null]	[null]
34	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]
35	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]
36	1	0		male	45.0	1	0	36973	83.475	C83	S	[null]	[null]
37	1	0		male	42.0	0	0	113038	42.5	B11	S	[null]	[null]
38	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]
39	1	1		female	36.0	0	2	WE/P 5735	71.0	B22	S	7	[null]
40	1	1		female	25.0	1	0	11765	55.4417	E50	C	5	[null]
41	1	1		female	22.0	1	0	113776	66.6	C2	S	8	[null]
42	2	0		male	29.0	1	0	2003	26.0	[null]	S	[null]	[null]
43	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22
44	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126
45	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]
46	1	1		female	[null]	0	1	113505	55.0	E33	S	6	[null]
47	1	1		female	22.0	0	1	112378	59.4	[null]	C	7	[null]
48	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]
49	1	1		female	37.0	1	0	19928	90.0	C78	Q	14	[null]
50	1	1		female	31.0	1	0	35273	113.275	D36	C	6	[null]

Rows: 1-50 | Columns: 14

If we want to insert additional data from the original Titanic dataset into the public.titanic_subset table, we can do so by setting the insert parameter of the read_csv() function to True:

TIP: You can also insert data into an existing Vertica table with the `insert_into() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.insert_into.html>`__ function.

[10]:

titanic[50:100].to_csv(
    path = "titanic_more_data.csv"
)

vp.read_csv("titanic_more_data.csv",
    schema = "public",
    table_name = "titanic_subset",
    sep = ",",
    insert = True
)

[10]:

	123 pclass Integer	123 survived Integer	Abc Varchar(112)	Abc sex Varchar(20)	123 age Numeric(10)	123 sibsp Integer	123 parch Integer	Abc ticket Varchar(24)	123 fare Numeric(13)	Abc cabin Varchar(22)	Abc embarked Varchar(20)	Abc boat Varchar(100)	123 body Integer	Abc Varchar(92)
1	1	0		male	[null]	0	0	113791	26.55	[null]	S	[null]	[null]
2	1	0		male	[null]	0	0	113778	26.55	D34	S	[null]	[null]
3	1	0		male	45.0	1	0	36973	83.475	C83	S	[null]	[null]
4	1	0		male	42.0	0	0	113038	42.5	B11	S	[null]	[null]
5	1	0		male	[null]	0	0	113028	26.55	C124	S	[null]	[null]
6	1	1		female	36.0	0	2	WE/P 5735	71.0	B22	S	7	[null]
7	1	1		female	25.0	1	0	11765	55.4417	E50	C	5	[null]
8	1	1		female	22.0	1	0	113776	66.6	C2	S	8	[null]
9	2	0		male	29.0	1	0	2003	26.0	[null]	S	[null]	[null]
10	1	0		male	71.0	0	0	PC 17609	49.5042	[null]	C	[null]	22
11	1	0		male	29.0	0	0	113501	30.0	D6	S	[null]	126
12	1	0		male	50.0	1	0	13507	55.9	E44	S	[null]	[null]
13	1	1		female	[null]	0	1	113505	55.0	E33	S	6	[null]
14	1	1		female	22.0	0	1	112378	59.4	[null]	C	7	[null]
15	1	0		male	37.0	1	1	PC 17756	83.1583	E52	C	[null]	[null]
16	1	1		female	37.0	1	0	19928	90.0	C78	Q	14	[null]
17	1	1		female	31.0	1	0	35273	113.275	D36	C	6	[null]
18	2	0		male	27.0	1	0	228414	26.0	[null]	S	[null]	293
19	3	0		female	18.0	0	1	2691	14.4542	[null]	C	[null]	[null]
20	3	0		male	[null]	0	0	2664	7.225	[null]	C	[null]	[null]
21	3	0		male	31.0	0	0	335097	7.75	[null]	Q	[null]	[null]
22	3	0		male	[null]	0	0	2674	7.225	[null]	C	[null]	[null]
23	3	0		male	[null]	0	0	2631	7.225	[null]	C	[null]	[null]
24	3	0		male	40.5	0	0	C.A. 6212	15.1	[null]	S	[null]	187
25	3	0		male	26.0	1	0	350025	7.8542	[null]	S	[null]	[null]
26	2	1		male	3.0	1	1	230080	26.0	F2	S	D	[null]
27	2	1		female	18.0	0	2	250652	13.0	[null]	S	16	[null]
28	3	0		male	0.33	0	2	347080	14.4	[null]	S	[null]	[null]
29	3	0		male	30.0	0	0	C 7076	7.25	[null]	S	[null]	72
30	3	0		female	[null]	0	0	364859	7.75	[null]	Q	[null]	[null]
31	3	0		female	[null]	0	0	382649	7.75	[null]	Q	[null]	[null]
32	2	0		male	30.0	0	0	250653	13.0	[null]	S	[null]	75
33	1	0		male	[null]	0	0	PC 17600	30.6958	[null]	C	14	[null]
34	1	1		female	53.0	2	0	11769	51.4792	C101	S	D	[null]
35	1	1		male	36.0	0	1	PC 17755	512.3292	B51 B53 B55	C	3	[null]
36	1	1		female	39.0	0	0	24160	211.3375	[null]	S	2	[null]
37	1	1		female	60.0	1	0	110813	75.25	D37	C	5	[null]
38	2	0		male	21.0	0	0	S.O.C. 14879	73.5	[null]	S	[null]	[null]
39	2	0		male	47.0	0	0	237565	15.0	[null]	S	[null]	[null]
40	1	0		male	27.0	1	0	13508	136.7792	C89	C	[null]	[null]
41	1	0		male	37.0	1	0	113803	53.1	C123	S	[null]	[null]
42	1	1		female	35.0	1	0	36973	83.475	C83	S	D	[null]
43	1	1		male	30.0	1	0	13236	57.75	C78	C	11	[null]
44	2	0		female	44.0	1	0	244252	26.0	[null]	S	[null]	[null]
45	2	1		female	34.0	0	0	243880	13.0	[null]	S	12	[null]
46	3	0		female	40.0	1	0	7546	9.475	[null]	S	[null]	[null]
47	3	0		female	28.0	1	1	347080	14.4	[null]	S	[null]	[null]
48	3	0		male	25.0	0	0	349203	7.8958	[null]	S	[null]	[null]
49	3	0		male	25.0	0	0	349250	7.8958	[null]	S	[null]	[null]
50	3	0		male	40.0	1	6	CA 2144	46.9	[null]	S	[null]	[null]
51	3	0		male	51.0	0	0	21440	8.05	[null]	S	[null]	[null]
52	2	0		male	36.0	0	0	C.A. 17248	10.5	[null]	S	[null]	[null]
53	2	1		female	48.0	1	2	220845	65.0	[null]	S	9	[null]
54	3	0		male	35.0	0	0	373450	8.05	[null]	S	[null]	[null]
55	3	0		male	35.0	0	0	364512	8.05	[null]	S	[null]	[null]
56	3	0		male	34.0	1	1	347080	14.4	[null]	S	[null]	197
57	2	0		male	62.0	0	0	240276	9.6875	[null]	Q	[null]	[null]
58	2	0		male	27.0	0	0	SC/PARIS 2168	15.0333	[null]	C	[null]	[null]
59	2	1		female	24.0	0	2	250649	14.5	[null]	S	4	[null]
60	3	0		female	22.0	0	0	7552	10.5167	[null]	S	[null]	[null]
61	3	0		male	40.5	0	0	367232	7.75	[null]	Q	[null]	68
62	1	0		male	58.0	0	2	35273	113.275	D48	C	[null]	122
63	1	0		male	51.0	0	1	PC 17597	61.3792	[null]	C	[null]	[null]
64	1	1		female	14.0	1	2	113760	120.0	B96 B98	S	4	[null]
65	1	1		female	[null]	0	0	17421	110.8833	[null]	C	4	[null]
66	1	1		male	48.0	1	0	19996	52.0	C126	S	5 7	[null]
67	1	0		male	17.0	0	0	113059	47.1	[null]	S	[null]	[null]
68	1	0		male	31.0	1	0	F.C. 12750	52.0	B71	S	[null]	[null]
69	1	0		male	41.0	1	0	17464	51.8625	D21	S	[null]	[null]
70	1	0		male	57.0	1	0	PC 17569	146.5208	B78	C	[null]	[null]
71	1	0		male	64.0	1	0	110813	75.25	D37	C	[null]	[null]
72	1	1		female	35.0	0	0	113503	211.5	C130	C	4	[null]
73	1	1		female	33.0	0	0	110152	86.5	B77	S	8	[null]
74	2	0		male	36.0	0	0	229236	13.0	[null]	S	[null]	236
75	2	0		male	31.0	0	0	C.A. 18723	10.5	[null]	S	[null]	165
76	1	0		male	24.0	0	0	PC 17593	79.2	B86	C	[null]	[null]
77	1	0		male	47.0	0	0	113796	42.4	[null]	S	[null]	[null]
78	1	1		female	43.0	0	1	24160	211.3375	B3	S	2	[null]
79	1	1		male	35.0	0	0	PC 17475	26.2875	E24	S	5	[null]
80	2	0		male	44.0	0	0	248746	13.0	[null]	S	[null]	35
81	2	0		male	22.0	2	0	C.A. 31029	31.5	[null]	S	[null]	[null]
82	2	0		male	70.0	0	0	C.A. 24580	10.5	[null]	S	[null]	[null]
83	2	0		female	27.0	1	0	11668	21.0	[null]	S	[null]	[null]
84	2	1		female	48.0	0	2	C.A. 33112	36.75	[null]	S	14	[null]
85	2	1		female	3.0	1	2	SC/Paris 2123	41.5792	[null]	C	14	[null]
86	3	0		female	32.0	1	1	364849	15.5	[null]	Q	[null]	[null]
87	3	0		male	22.0	0	0	350045	7.7958	[null]	S	[null]	[null]
88	3	0		male	[null]	1	0	2689	14.4583	[null]	C	[null]	[null]
89	3	0		male	22.0	0	0	A/5 21172	7.25	[null]	S	[null]	[null]
90	3	0		male	18.0	0	0	350036	7.7958	[null]	S	[null]	[null]
91	2	0		male	16.0	0	0	S.O./P.P. 3	10.5	[null]	S	[null]	[null]
92	2	0		male	66.0	0	0	C.A. 24579	10.5	[null]	S	[null]	[null]
93	2	1		female	30.0	1	0	SC/PARIS 2148	13.8583	[null]	C	12	[null]
94	3	0		male	[null]	0	0	349238	7.8958	[null]	S	[null]	[null]
95	3	0		male	[null]	0	0	349225	7.8958	[null]	S	[null]	[null]
96	3	0		male	[null]	0	0	349254	7.8958	[null]	C	[null]	[null]
97	3	0		male	[null]	0	0	349220	7.8958	[null]	S	[null]	[null]
98	2	0		male	54.0	0	0	29011	14.0	[null]	S	[null]	[null]
99	2	1		female	2.0	1	1	26360	26.0	[null]	S	11	[null]
100	3	0		female	[null]	1	0	2689	14.4583	[null]	C	[null]	[null]

Rows: 1-100 | Columns: 14

Ingest JSON files#

As with CSV files, VerticaPy provides a file-specific ingestion function for JSON files, read_json(), which supports additional options, including: - usecols: provide a list of JSON parameters to ingest. Other JSON parameters are ignored. - start_point: name the key in the JSON load data at which to begin parsing - flatten_maps: set whether sub-maps within the JSON data are flattened. - materialize: if set to True, the flex table used to ingest the data is materialized into a table.

For a full list of supported options, see the read_json() reference page or use the help() function.

VerticaPy also provides a `pjson() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.pjson.html>`__ function to parse JSON files to identify columns and their respective data types.

In the following example, we load the iris dataset using the `load_iris() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.datasets.load_iris.html>`__ dataset, convert the vDataFrame to JSON format with the `to_json() <https://www.vertica.com/python/documentation/1.0.x/html/verticapy.vDataFrame.to_json.html>`__ method, then ingest the JSON file into Vetica:

[11]:

from verticapy.datasets import load_iris

iris = load_iris()
iris.to_json(
    path = "iris_local.json",
)
vp.read_json(
    path = "iris_local.json",
    table_name = "iris_ingest",
    schema = "public",
)

The table "public"."iris_ingest" has been successfully created.

[11]:

	123 PetalWidthCm Float(22)	123 SepalWidthCm Float(22)	Abc Species Varchar(30)	123 PetalLengthCm Float(22)	123 SepalLengthCm Float(22)	123 Id Integer
1	0.1	3.0	Iris-setosa	1.1	4.3	14
2	0.2	3.2	Iris-setosa	1.2	5.0	36
3	0.2	3.2	Iris-setosa	1.3	4.7	3
4	0.2	3.2	Iris-setosa	1.6	4.7	30
5	0.2	3.6	Iris-setosa	1.0	4.6	23
6	0.2	3.6	Iris-setosa	1.4	5.0	5
7	1.2	2.8	Iris-versicolor	4.7	6.1	74
8	1.2	3.0	Iris-versicolor	4.2	5.7	96
9	1.3	2.8	Iris-versicolor	4.5	5.7	56
10	1.4	2.6	Iris-virginica	5.6	6.1	135
11	1.5	2.9	Iris-versicolor	4.5	6.0	79
12	1.8	2.8	Iris-virginica	4.8	6.2	127
13	1.8	2.9	Iris-virginica	5.6	6.3	104
14	1.8	3.0	Iris-virginica	4.8	6.0	139
15	2.0	3.0	Iris-virginica	5.2	6.5	148
16	2.0	3.2	Iris-virginica	5.1	6.5	111
17	2.1	3.3	Iris-virginica	5.7	6.7	125
18	0.2	2.9	Iris-setosa	1.4	4.4	9
19	0.2	3.4	Iris-setosa	1.4	5.2	29
20	0.2	3.4	Iris-setosa	1.9	4.8	25
21	0.2	3.7	Iris-setosa	1.5	5.3	49
22	0.2	3.7	Iris-setosa	1.5	5.4	11
23	0.2	4.2	Iris-setosa	1.4	5.5	34
24	0.4	3.8	Iris-setosa	1.9	5.1	45
25	1.0	2.7	Iris-versicolor	4.1	5.8	68
26	1.3	2.7	Iris-versicolor	4.2	5.6	95
27	1.3	2.9	Iris-versicolor	4.3	6.2	98
28	1.4	2.8	Iris-versicolor	4.8	6.8	77
29	1.4	3.0	Iris-versicolor	4.4	6.6	76
30	1.4	3.2	Iris-versicolor	4.7	7.0	51
31	1.5	2.5	Iris-versicolor	4.9	6.3	73
32	1.5	2.8	Iris-virginica	5.1	6.3	134
33	1.5	3.0	Iris-versicolor	4.5	5.6	67
34	1.9	2.5	Iris-virginica	5.0	6.3	147
35	2.1	3.0	Iris-virginica	5.5	6.8	113
36	2.3	3.2	Iris-virginica	5.3	6.4	116
37	0.1	3.1	Iris-setosa	1.5	4.9	38
38	0.2	3.0	Iris-setosa	1.6	5.0	26
39	0.2	3.4	Iris-setosa	1.5	5.0	8
40	0.2	3.4	Iris-setosa	1.7	5.4	21
41	0.3	3.0	Iris-setosa	1.4	4.8	46
42	1.0	2.3	Iris-versicolor	3.3	5.0	94
43	1.0	2.6	Iris-versicolor	3.5	5.7	80
44	1.1	2.4	Iris-versicolor	3.8	5.5	81
45	1.3	2.3	Iris-versicolor	4.0	5.5	54
46	1.4	3.0	Iris-versicolor	4.6	6.1	92
47	1.4	3.1	Iris-versicolor	4.4	6.7	66
48	1.5	2.8	Iris-versicolor	4.6	6.5	55
49	1.5	3.2	Iris-versicolor	4.5	6.4	52
50	1.6	3.0	Iris-virginica	5.8	7.2	130
51	1.8	2.5	Iris-virginica	5.8	6.7	109
52	2.0	2.8	Iris-virginica	6.7	7.7	123
53	2.1	3.0	Iris-virginica	6.6	7.6	106
54	2.3	3.0	Iris-virginica	6.1	7.7	136
55	2.3	3.1	Iris-virginica	5.1	6.9	142
56	2.4	3.1	Iris-virginica	5.6	6.7	141
57	0.1	3.1	Iris-setosa	1.5	4.9	35
58	0.2	3.4	Iris-setosa	1.6	4.8	12
59	0.3	3.4	Iris-setosa	1.4	4.6	7
60	0.3	3.8	Iris-setosa	1.7	5.7	19
61	0.4	3.4	Iris-setosa	1.6	5.0	27
62	0.4	3.7	Iris-setosa	1.5	5.1	22
63	0.4	4.4	Iris-setosa	1.5	5.7	16
64	0.6	3.5	Iris-setosa	1.6	5.0	44
65	1.0	2.0	Iris-versicolor	3.5	5.0	61
66	1.0	2.2	Iris-versicolor	4.0	6.0	63
67	1.3	2.9	Iris-versicolor	4.3	6.4	75
68	1.4	2.7	Iris-versicolor	3.9	5.2	60
69	1.6	3.3	Iris-versicolor	4.7	6.3	57
70	1.8	3.2	Iris-versicolor	4.8	5.9	71
71	1.9	2.7	Iris-virginica	5.1	5.8	102
72	2.0	2.5	Iris-virginica	5.0	5.7	114
73	2.2	2.8	Iris-virginica	5.6	6.4	133
74	2.5	3.3	Iris-virginica	6.0	6.3	101
75	0.2	3.0	Iris-setosa	1.4	4.9	2
76	0.2	3.5	Iris-setosa	1.4	5.1	1
77	0.3	3.5	Iris-setosa	1.4	5.1	18
78	0.3	3.8	Iris-setosa	1.5	5.1	20
79	0.4	3.4	Iris-setosa	1.5	5.4	32
80	1.1	2.5	Iris-versicolor	3.9	5.6	70
81	1.3	2.9	Iris-versicolor	3.6	5.6	65
82	1.6	2.7	Iris-versicolor	5.1	6.0	84
83	1.6	3.4	Iris-versicolor	4.5	6.0	86
84	1.9	2.8	Iris-virginica	6.1	7.4	131
85	2.1	2.8	Iris-virginica	5.6	6.4	129
86	2.1	3.1	Iris-virginica	5.4	6.9	140
87	2.2	3.8	Iris-virginica	6.7	7.7	118
88	2.3	3.0	Iris-virginica	5.2	6.7	146
89	2.3	3.4	Iris-virginica	5.4	6.2	149
90	2.4	2.8	Iris-virginica	5.1	5.8	115
91	2.5	3.6	Iris-virginica	6.1	7.2	110
92	0.2	3.1	Iris-setosa	1.6	4.8	31
93	0.2	3.3	Iris-setosa	1.4	5.0	50
94	0.2	4.0	Iris-setosa	1.2	5.8	15
95	0.5	3.3	Iris-setosa	1.7	5.1	24
96	1.1	2.5	Iris-versicolor	3.0	5.1	99
97	1.3	2.8	Iris-versicolor	4.1	5.7	100
98	1.3	2.9	Iris-versicolor	4.2	5.7	97
99	1.4	2.9	Iris-versicolor	4.7	6.1	64
100	1.5	3.1	Iris-versicolor	4.7	6.7	87

Rows: 1-100 | Columns: 6

Other file types#

For more information about other file-specific ingestion functions, see the following reference pages, which include examples: - pandas DataFrames: `read_pandas() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_pandas.html>`__ and `pandas_to_vertica() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.pandas_to_vertica.html>`__ - Avro: `read_avro() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_avro.html>`__ - SHP: `read_shp() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.read_shp.html>`__

Data Ingestion#

Ingest files with read_file#

Ingest CSV files#

Ingest JSON files#

Other file types#

Ingest files with `read_file`#