read_json

In [ ]:
read_json(path: str, 
          cursor = None, 
          schema: str = '', 
          table_name: str = '',
          usecols: list = [],
          new_name: dict = {},
          insert: bool = False,
          temporary_table: bool = False,
          temporary_local_table: bool = False,
          ingest_local: bool = True,)

Ingests a JSON file using flex tables.

Parameters

Name Type Optional Description
path
str
Absolute path where the JSON file is located.
cursor
DBcursor
Vertica DB cursor.
schema
str
Schema where the JSON file will be ingested.
table_name
str
Final relation name.
usecols
list
List of the JSON parameters to ingest. The other ones will be ignored. If empty all the JSON parameters will be ingested.
new_name
dict
Dictionary of the new columns name. If the JSON file is nested, it is advised to change the final names as special characters will be included. For example, {"param": {"age": 3, "name": Badr}, "date": 1993-03-11} will create 3 columns: "param.age", "param.name" and "date". You can rename these columns using the 'new_name' parameter with the following dictionary: {"param.age": "age", "param.name": "name"}
insert
bool
If set to True, the data will be ingested to the input relation. The JSON parameters must be the same than the input relation otherwise they will not be ingested.
temporary_table
bool
If set to True, a temporary table will be created.
temporary_local_table
bool
If set to True, a temporary local table will be created and the parameter 'schema' is ignored.
ingest_local
bool
If set to True, the file will be ingested from the local machine.

Returns

vDataFrame : The vDataFrame of the relation.

Example

In [9]:
from verticapy.utilities import *
# Ingesting the JSON file
read_json("titanic.json", 
          table_name = "titanic_dataset",
          schema = "public")
The table "public"."titanic_dataset" has been successfully created.
123
fields.parch
Int
Abc
Varchar(164)
Abc
fields.sex
Varchar(20)
010
fields.survived
Boolean
Abc
fields.ticket
Varchar(36)
Abc
fields.embarked
Varchar(20)
123
fields.sibsp
Int
📅
record_timestamp
Timestamp
123
fields.passengerid
Int
Abc
Varchar(100)
123
fields.age
Float
123
fields.fare
Float
Abc
fields.cabin
Varchar(30)
123
fields.pclass
Int
Abc
datasetid
Varchar(36)
10male
13049C02016-09-20 15:34:51.31358436.040.125A101titanic-passengers
20male
110465S02016-09-20 15:34:51.313476[null]52.0A141titanic-passengers
30female
11755C12016-09-20 15:34:51.31355748.039.6A161titanic-passengers
40male
113056S02016-09-20 15:34:51.313285[null]26.0A191titanic-passengers
50male
PC 17485C12016-09-20 15:34:51.31360049.056.9292A201titanic-passengers
60male
27042S02016-09-20 15:34:51.31363180.030.0A231titanic-passengers
70male
PC 17590S02016-09-20 15:34:51.31386831.050.4958A241titanic-passengers
80male
13213C02016-09-20 15:34:51.31364856.035.5A261titanic-passengers
90male
112277C02016-09-20 15:34:51.31321040.031.0A311titanic-passengers
100male
113767S02016-09-20 15:34:51.313186[null]50.0A321titanic-passengers
112male
33638S02016-09-20 15:34:51.3134464.081.8583A341titanic-passengers
120male
112050S02016-09-20 15:34:51.31380739.00.0A361titanic-passengers
130male
PC 17754C02016-09-20 15:34:51.3139771.034.6542A51titanic-passengers
140male
113788S02016-09-20 15:34:51.3132428.035.5A61titanic-passengers
150male
17764C02016-09-20 15:34:51.31317556.030.6958A71titanic-passengers
160male
PC 17755C02016-09-20 15:34:51.31373835.0512.3292B1011titanic-passengers
170male
112058S02016-09-20 15:34:51.313816[null]0.0B1021titanic-passengers
181female
111361C02016-09-20 15:34:51.31333016.057.9792B181titanic-passengers
191female
111361C02016-09-20 15:34:51.31352444.057.9792B181titanic-passengers
200male
111240S02016-09-20 15:34:51.31317161.033.5B191titanic-passengers
210female
17474S12016-09-20 15:34:51.31378217.057.0B201titanic-passengers
220male
17474S12016-09-20 15:34:51.31369131.057.0B201titanic-passengers
232female
WE/P 5735S02016-09-20 15:34:51.31354136.071.0B221titanic-passengers
241male
WE/P 5735S12016-09-20 15:34:51.31374670.071.0B221titanic-passengers
250female
113572[null]02016-09-20 15:34:51.3136238.080.0B281titanic-passengers
260female
113572[null]02016-09-20 15:34:51.31383062.080.0B281titanic-passengers
271female
24160S02016-09-20 15:34:51.31378043.0211.3375B31titanic-passengers
281male
113509C02016-09-20 15:34:51.3135565.061.9792B301titanic-passengers
290female
PC 17477C02016-09-20 15:34:51.31337024.069.3B351titanic-passengers
300female
PC 17477C02016-09-20 15:34:51.31364224.069.3B351titanic-passengers
310male
11771C02016-09-20 15:34:51.31348858.029.7B371titanic-passengers
320male
113050S02016-09-20 15:34:51.31353745.026.55B381titanic-passengers
332female
13568C02016-09-20 15:34:51.31354022.049.5B391titanic-passengers
340female
PC 17610C02016-09-20 15:34:51.31319544.027.7208B41titanic-passengers
351male
13567C12016-09-20 15:34:51.31358860.079.2B411titanic-passengers
360female
112053S02016-09-20 15:34:51.31388819.030.0B421titanic-passengers
370female
11967C12016-09-20 15:34:51.31329219.091.0792B491titanic-passengers
380male
11967C12016-09-20 15:34:51.31348525.091.0792B491titanic-passengers
391female
24160S02016-09-20 15:34:51.31369015.0211.3375B51titanic-passengers
400female
24160S02016-09-20 15:34:51.31373129.0211.3375B51titanic-passengers
410male
13214C02016-09-20 15:34:51.31363332.030.5B501titanic-passengers
420male
695S02016-09-20 15:34:51.31387333.05.0B51 B53 B551titanic-passengers
431male
PC 17755C02016-09-20 15:34:51.31368036.0512.3292B51 B53 B551titanic-passengers
442female
PC 17608C22016-09-20 15:34:51.31331218.0262.375B57 B59 B63 B661titanic-passengers
452female
PC 17608C22016-09-20 15:34:51.31374321.0262.375B57 B59 B63 B661titanic-passengers
461male
PC 17558C02016-09-20 15:34:51.31311924.0247.5208B58 B601titanic-passengers
471female
PC 17558C02016-09-20 15:34:51.31330050.0247.5208B58 B601titanic-passengers
481female
12749S12016-09-20 15:34:51.31382152.093.5B691titanic-passengers
490male
F.C. 12750S12016-09-20 15:34:51.31367231.052.0B711titanic-passengers
500female
12749S02016-09-20 15:34:51.31352130.093.5B731titanic-passengers
510female
110152S02016-09-20 15:34:51.31325830.086.5B771titanic-passengers
520female
110152S02016-09-20 15:34:51.31376033.086.5B771titanic-passengers
530female
PC 17569C12016-09-20 15:34:51.31332[null]146.5208B781titanic-passengers
540female
110152S02016-09-20 15:34:51.31350516.086.5B791titanic-passengers
550female
PC 17569C02016-09-20 15:34:51.31319658.0146.5208B801titanic-passengers
560male
PC 17593C02016-09-20 15:34:51.31379046.079.2B82 B841titanic-passengers
570male
PC 17593C02016-09-20 15:34:51.31314024.079.2B861titanic-passengers
580male
112059S02016-09-20 15:34:51.31326440.00.0B941titanic-passengers
592male
113760S12016-09-20 15:34:51.31380311.0120.0B96 B981titanic-passengers
602female
113760S12016-09-20 15:34:51.31343614.0120.0B96 B981titanic-passengers
612male
113760S12016-09-20 15:34:51.31339136.0120.0B96 B981titanic-passengers
622female
113760S12016-09-20 15:34:51.31376436.0120.0B96 B981titanic-passengers
630female
11769S22016-09-20 15:34:51.31357253.051.4792C1011titanic-passengers
640female
113783S02016-09-20 15:34:51.3131258.026.55C1031titanic-passengers
650male
113786S02016-09-20 15:34:51.31345052.030.5C1041titanic-passengers
660male
19988S02016-09-20 15:34:51.313299[null]30.5C1061titanic-passengers
670male
110465S02016-09-20 15:34:51.31311147.052.0C1101titanic-passengers
680male
113051C02016-09-20 15:34:51.31345330.027.75C1111titanic-passengers
691male
PC 17596C02016-09-20 15:34:51.31327437.029.7C1181titanic-passengers
700female
113803S12016-09-20 15:34:51.313435.053.1C1231titanic-passengers
710male
113803S12016-09-20 15:34:51.31313837.053.1C1231titanic-passengers
720male
113043S02016-09-20 15:34:51.31333245.528.5C1241titanic-passengers
730male
113028S02016-09-20 15:34:51.313712[null]26.55C1241titanic-passengers
740female
PC 17582S02016-09-20 15:34:51.31361040.0153.4625C1251titanic-passengers
751female
PC 17582S02016-09-20 15:34:51.31326958.0153.4625C1251titanic-passengers
760male
19996S12016-09-20 15:34:51.31371348.052.0C1261titanic-passengers
770female
19996S12016-09-20 15:34:51.313670[null]52.0C1261titanic-passengers
780male
113510S02016-09-20 15:34:51.313352[null]35.0C1281titanic-passengers
790male
111369C02016-09-20 15:34:51.31389026.030.0C1481titanic-passengers
800female
113776S12016-09-20 15:34:51.31315222.066.6C21titanic-passengers
810male
113776S12016-09-20 15:34:51.31333729.066.6C21titanic-passengers
822male
113781S12016-09-20 15:34:51.3133060.92151.55C22 C261titanic-passengers
832female
113781S12016-09-20 15:34:51.3132982.0151.55C22 C261titanic-passengers
842female
113781S12016-09-20 15:34:51.31349925.0151.55C22 C261titanic-passengers
852male
19950S32016-09-20 15:34:51.3132819.0263.0C23 C25 C271titanic-passengers
862female
19950S32016-09-20 15:34:51.3138923.0263.0C23 C25 C271titanic-passengers
872female
19950S32016-09-20 15:34:51.31334224.0263.0C23 C25 C271titanic-passengers
884male
19950S12016-09-20 15:34:51.31343964.0263.0C23 C25 C271titanic-passengers
890male
113787S02016-09-20 15:34:51.31349355.030.5C301titanic-passengers
900female
PC 17760C02016-09-20 15:34:51.31332636.0135.6333C321titanic-passengers
910female
PC 17757C02016-09-20 15:34:51.31371738.0227.525C451titanic-passengers
920male
19877S12016-09-20 15:34:51.31374236.078.85C461titanic-passengers
930male
11774C02016-09-20 15:34:51.313840[null]29.7C471titanic-passengers
940female
PC 17595C02016-09-20 15:34:51.31317850.028.7125C491titanic-passengers
951female
11767C02016-09-20 15:34:51.31388056.083.1583C501titanic-passengers
960male
110564S02016-09-20 15:34:51.31343128.026.55C521titanic-passengers
970male
19947S02016-09-20 15:34:51.31356[null]35.5C521titanic-passengers
980female
11767C02016-09-20 15:34:51.31331124.083.1583C541titanic-passengers
990female
PC 17757C12016-09-20 15:34:51.31370118.0227.525C62 C641titanic-passengers
1000female
PC 17758C12016-09-20 15:34:51.31330817.0108.9C651titanic-passengers
Out[9]:
Rows: 1-100 of 891 | Columns: 15
In [11]:
# Ingesting the JSON file and renaming some columns
read_json("titanic.json", 
          table_name = "titanic_dataset",
          schema = "public",
          new_name = {"fields.fare": "fare",
                      "fields.sex": "sex"})
The table "public"."titanic_dataset" has been successfully created.
123
fields.parch
Int
Abc
Varchar(164)
Abc
sex
Varchar(20)
010
fields.survived
Boolean
Abc
fields.ticket
Varchar(36)
Abc
fields.embarked
Varchar(20)
123
fields.sibsp
Int
📅
record_timestamp
Timestamp
123
fields.passengerid
Int
Abc
Varchar(100)
123
fields.age
Float
Abc
fields.cabin
Varchar(30)
123
fields.pclass
Int
Abc
datasetid
Varchar(36)
123
fare
Float
10male
13049C02016-09-20 15:34:51.31358436.0A101titanic-passengers40.125
20male
110465S02016-09-20 15:34:51.313476[null]A141titanic-passengers52.0
30female
11755C12016-09-20 15:34:51.31355748.0A161titanic-passengers39.6
40male
113056S02016-09-20 15:34:51.313285[null]A191titanic-passengers26.0
50male
PC 17485C12016-09-20 15:34:51.31360049.0A201titanic-passengers56.9292
60male
27042S02016-09-20 15:34:51.31363180.0A231titanic-passengers30.0
70male
PC 17590S02016-09-20 15:34:51.31386831.0A241titanic-passengers50.4958
80male
13213C02016-09-20 15:34:51.31364856.0A261titanic-passengers35.5
90male
112277C02016-09-20 15:34:51.31321040.0A311titanic-passengers31.0
100male
113767S02016-09-20 15:34:51.313186[null]A321titanic-passengers50.0
112male
33638S02016-09-20 15:34:51.3134464.0A341titanic-passengers81.8583
120male
112050S02016-09-20 15:34:51.31380739.0A361titanic-passengers0.0
130male
PC 17754C02016-09-20 15:34:51.3139771.0A51titanic-passengers34.6542
140male
113788S02016-09-20 15:34:51.3132428.0A61titanic-passengers35.5
150male
17764C02016-09-20 15:34:51.31317556.0A71titanic-passengers30.6958
160male
PC 17755C02016-09-20 15:34:51.31373835.0B1011titanic-passengers512.3292
170male
112058S02016-09-20 15:34:51.313816[null]B1021titanic-passengers0.0
181female
111361C02016-09-20 15:34:51.31333016.0B181titanic-passengers57.9792
191female
111361C02016-09-20 15:34:51.31352444.0B181titanic-passengers57.9792
200male
111240S02016-09-20 15:34:51.31317161.0B191titanic-passengers33.5
210female
17474S12016-09-20 15:34:51.31378217.0B201titanic-passengers57.0
220male
17474S12016-09-20 15:34:51.31369131.0B201titanic-passengers57.0
232female
WE/P 5735S02016-09-20 15:34:51.31354136.0B221titanic-passengers71.0
241male
WE/P 5735S12016-09-20 15:34:51.31374670.0B221titanic-passengers71.0
250female
113572[null]02016-09-20 15:34:51.3136238.0B281titanic-passengers80.0
260female
113572[null]02016-09-20 15:34:51.31383062.0B281titanic-passengers80.0
271female
24160S02016-09-20 15:34:51.31378043.0B31titanic-passengers211.3375
281male
113509C02016-09-20 15:34:51.3135565.0B301titanic-passengers61.9792
290female
PC 17477C02016-09-20 15:34:51.31337024.0B351titanic-passengers69.3
300female
PC 17477C02016-09-20 15:34:51.31364224.0B351titanic-passengers69.3
310male
11771C02016-09-20 15:34:51.31348858.0B371titanic-passengers29.7
320male
113050S02016-09-20 15:34:51.31353745.0B381titanic-passengers26.55
332female
13568C02016-09-20 15:34:51.31354022.0B391titanic-passengers49.5
340female
PC 17610C02016-09-20 15:34:51.31319544.0B41titanic-passengers27.7208
351male
13567C12016-09-20 15:34:51.31358860.0B411titanic-passengers79.2
360female
112053S02016-09-20 15:34:51.31388819.0B421titanic-passengers30.0
370female
11967C12016-09-20 15:34:51.31329219.0B491titanic-passengers91.0792
380male
11967C12016-09-20 15:34:51.31348525.0B491titanic-passengers91.0792
391female
24160S02016-09-20 15:34:51.31369015.0B51titanic-passengers211.3375
400female
24160S02016-09-20 15:34:51.31373129.0B51titanic-passengers211.3375
410male
13214C02016-09-20 15:34:51.31363332.0B501titanic-passengers30.5
420male
695S02016-09-20 15:34:51.31387333.0B51 B53 B551titanic-passengers5.0
431male
PC 17755C02016-09-20 15:34:51.31368036.0B51 B53 B551titanic-passengers512.3292
442female
PC 17608C22016-09-20 15:34:51.31331218.0B57 B59 B63 B661titanic-passengers262.375
452female
PC 17608C22016-09-20 15:34:51.31374321.0B57 B59 B63 B661titanic-passengers262.375
461male
PC 17558C02016-09-20 15:34:51.31311924.0B58 B601titanic-passengers247.5208
471female
PC 17558C02016-09-20 15:34:51.31330050.0B58 B601titanic-passengers247.5208
481female
12749S12016-09-20 15:34:51.31382152.0B691titanic-passengers93.5
490male
F.C. 12750S12016-09-20 15:34:51.31367231.0B711titanic-passengers52.0
500female
12749S02016-09-20 15:34:51.31352130.0B731titanic-passengers93.5
510female
110152S02016-09-20 15:34:51.31325830.0B771titanic-passengers86.5
520female
110152S02016-09-20 15:34:51.31376033.0B771titanic-passengers86.5
530female
PC 17569C12016-09-20 15:34:51.31332[null]B781titanic-passengers146.5208
540female
110152S02016-09-20 15:34:51.31350516.0B791titanic-passengers86.5
550female
PC 17569C02016-09-20 15:34:51.31319658.0B801titanic-passengers146.5208
560male
PC 17593C02016-09-20 15:34:51.31379046.0B82 B841titanic-passengers79.2
570male
PC 17593C02016-09-20 15:34:51.31314024.0B861titanic-passengers79.2
580male
112059S02016-09-20 15:34:51.31326440.0B941titanic-passengers0.0
592male
113760S12016-09-20 15:34:51.31380311.0B96 B981titanic-passengers120.0
602female
113760S12016-09-20 15:34:51.31343614.0B96 B981titanic-passengers120.0
612male
113760S12016-09-20 15:34:51.31339136.0B96 B981titanic-passengers120.0
622female
113760S12016-09-20 15:34:51.31376436.0B96 B981titanic-passengers120.0
630female
11769S22016-09-20 15:34:51.31357253.0C1011titanic-passengers51.4792
640female
113783S02016-09-20 15:34:51.3131258.0C1031titanic-passengers26.55
650male
113786S02016-09-20 15:34:51.31345052.0C1041titanic-passengers30.5
660male
19988S02016-09-20 15:34:51.313299[null]C1061titanic-passengers30.5
670male
110465S02016-09-20 15:34:51.31311147.0C1101titanic-passengers52.0
680male
113051C02016-09-20 15:34:51.31345330.0C1111titanic-passengers27.75
691male
PC 17596C02016-09-20 15:34:51.31327437.0C1181titanic-passengers29.7
700female
113803S12016-09-20 15:34:51.313435.0C1231titanic-passengers53.1
710male
113803S12016-09-20 15:34:51.31313837.0C1231titanic-passengers53.1
720male
113043S02016-09-20 15:34:51.31333245.5C1241titanic-passengers28.5
730male
113028S02016-09-20 15:34:51.313712[null]C1241titanic-passengers26.55
740female
PC 17582S02016-09-20 15:34:51.31361040.0C1251titanic-passengers153.4625
751female
PC 17582S02016-09-20 15:34:51.31326958.0C1251titanic-passengers153.4625
760male
19996S12016-09-20 15:34:51.31371348.0C1261titanic-passengers52.0
770female
19996S12016-09-20 15:34:51.313670[null]C1261titanic-passengers52.0
780male
113510S02016-09-20 15:34:51.313352[null]C1281titanic-passengers35.0
790male
111369C02016-09-20 15:34:51.31389026.0C1481titanic-passengers30.0
800female
113776S12016-09-20 15:34:51.31315222.0C21titanic-passengers66.6
810male
113776S12016-09-20 15:34:51.31333729.0C21titanic-passengers66.6
822male
113781S12016-09-20 15:34:51.3133060.92C22 C261titanic-passengers151.55
832female
113781S12016-09-20 15:34:51.3132982.0C22 C261titanic-passengers151.55
842female
113781S12016-09-20 15:34:51.31349925.0C22 C261titanic-passengers151.55
852male
19950S32016-09-20 15:34:51.3132819.0C23 C25 C271titanic-passengers263.0
862female
19950S32016-09-20 15:34:51.3138923.0C23 C25 C271titanic-passengers263.0
872female
19950S32016-09-20 15:34:51.31334224.0C23 C25 C271titanic-passengers263.0
884male
19950S12016-09-20 15:34:51.31343964.0C23 C25 C271titanic-passengers263.0
890male
113787S02016-09-20 15:34:51.31349355.0C301titanic-passengers30.5
900female
PC 17760C02016-09-20 15:34:51.31332636.0C321titanic-passengers135.6333
910female
PC 17757C02016-09-20 15:34:51.31371738.0C451titanic-passengers227.525
920male
19877S12016-09-20 15:34:51.31374236.0C461titanic-passengers78.85
930male
11774C02016-09-20 15:34:51.313840[null]C471titanic-passengers29.7
940female
PC 17595C02016-09-20 15:34:51.31317850.0C491titanic-passengers28.7125
951female
11767C02016-09-20 15:34:51.31388056.0C501titanic-passengers83.1583
960male
110564S02016-09-20 15:34:51.31343128.0C521titanic-passengers26.55
970male
19947S02016-09-20 15:34:51.31356[null]C521titanic-passengers35.5
980female
11767C02016-09-20 15:34:51.31331124.0C541titanic-passengers83.1583
990female
PC 17757C12016-09-20 15:34:51.31370118.0C62 C641titanic-passengers227.525
1000female
PC 17758C12016-09-20 15:34:51.31330817.0C651titanic-passengers108.9
Out[11]:
Rows: 1-100 of 891 | Columns: 15
In [14]:
# Ingesting the JSON file, using some columns and
# renaming some columns
read_json("titanic.json", 
          table_name = "titanic_dataset",
          schema = "public",
          usecols = ["fields.fare", "fields.sex"],
          new_name = {"fields.fare": "fare",
                      "fields.sex": "sex"})
The table "public"."titanic_dataset" has been successfully created.