Repository navigation
Expand file tree
/
Copy pathFastLoadJSON.py
More file actions
124 lines (101 loc) · 5.18 KB
/
Copy pathFastLoadJSON.py
File metadata and controls
124 lines (101 loc) · 5.18 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# Copyright 2026 by Teradata Corporation. All rights reserved.
# This sample program demonstrates how to FastLoad a JSON file.
# It also illustrates dual treatment of a nested JSON object: the raw JSON
# string is stored verbatim in the "address" column, while the flattened
# sub-field "city" is stored in a separate column.
import json
import os
import teradatasql
with teradatasql.connect (host="whomooz", user="guest", password="please") as con:
with con.cursor () as cur:
sTableName = "FastLoadJSON"
sRequest = "DROP TABLE " + sTableName
print (sRequest)
cur.execute (sRequest, ignoreErrors=3807)
sRequest = "DROP TABLE " + sTableName + "_ERR_1"
print (sRequest)
cur.execute (sRequest, ignoreErrors=3807)
sRequest = "DROP TABLE " + sTableName + "_ERR_2"
print (sRequest)
cur.execute (sRequest, ignoreErrors=3807)
# Each record has three top-level keys:
# id -- scalar integer
# name -- scalar string
# address -- nested object; its raw JSON string maps to the "address" column
# and its sub-field "city" maps to the "city" column.
records = [
{"id": 1, "name": "Alice", "address": {"city": "Boston"}},
{"id": 2, "name": "Bob", "address": {"city": "Austin"}},
{"id": 3, "name": "Carol", "address": {"city": "Chicago"}},
{"id": 4, "name": "Dave", "address": {"city": "Denver"}},
{"id": 5, "name": "Erin", "address": {"city": "Eugene"}},
{"id": 6, "name": "Frank", "address": {"city": "Fresno"}},
{"id": 7, "name": "Grace", "address": {"city": "Houston"}},
{"id": 8, "name": "Hank", "address": {"city": "Irvine"}},
{"id": 9, "name": "Iris", "address": {"city": "Jacksonville"}},
]
jsonFileName = "dataPy.json"
print (f"Writing {jsonFileName}")
with open (jsonFileName, "w", encoding="UTF-8") as f:
json.dump (records, f)
try:
sRequest = "CREATE TABLE " + sTableName + " (id INTEGER, name VARCHAR(20), address VARCHAR(200), city VARCHAR(20)) UNIQUE PRIMARY INDEX (id)"
print (sRequest)
cur.execute (sRequest)
try:
print ("con.autocommit = False")
con.autocommit = False
try:
# The INSERT column list (id, name, address, city) drives name-based matching:
# ?1 -> id (scalar)
# ?2 -> name (scalar)
# ?3 -> address (raw JSON object string -- dual treatment)
# ?4 -> city (flattened sub-field of address)
sInsert = (
"{fn teradata_require_fastload}"
"{fn teradata_read_json(" + jsonFileName + ")}"
"INSERT INTO " + sTableName + " (id, name, address, city) VALUES (?, ?, ?, ?)"
)
print (sInsert)
cur.execute (sInsert)
# obtain the warnings and errors for transmitting the data to the database -- the acquisition phase
sRequest = "{fn teradata_nativesql}{fn teradata_get_warnings}" + sInsert
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
sRequest = "{fn teradata_nativesql}{fn teradata_get_errors}" + sInsert
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
sRequest = "{fn teradata_nativesql}{fn teradata_logon_sequence_number}" + sInsert
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
print ("con.commit()")
con.commit ()
# obtain the warnings and errors for the apply phase
sRequest = "{fn teradata_nativesql}{fn teradata_get_warnings}" + sInsert
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
sRequest = "{fn teradata_nativesql}{fn teradata_get_errors}" + sInsert
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
finally:
print ("con.autocommit = True")
con.autocommit = True
sRequest = "SELECT id, name, address, city FROM " + sTableName + " ORDER BY 1"
print (sRequest)
cur.execute (sRequest)
[ print (row) for row in cur.fetchall () ]
finally:
sRequest = "DROP TABLE " + sTableName
print (sRequest)
cur.execute (sRequest, ignoreErrors=3807)
finally:
print (f"os.remove({jsonFileName})")
try:
os.remove (jsonFileName)
except OSError as e:
print (f"os.remove failed: {e}")