Create the target table once in a dedicated test schema and leave it empty before loading. The function does not drop an existing table. Use credentials from your environment and validate source widths before FastLoad. The audit tested the loading protocol and stored values with a small deterministic fixture; the 100,000-row random generator below is not a measured throughput benchmark.
This article assumes prior basic knowledge of Python and demonstrates the easy process of loading data using Python and the Teradata SQL Driver for Python with Fastload.
If you are using Windows, we recommend using WinPython if you don’t have Python installed yet. Because a Jupyter Notebook is included, we use it to show how easy it is to load data into Teradata.
Download the latest WinPython version from here.
Download the latest version of WinPython
The table definition is provided below:
CREATE MULTISET TABLE DWHPRO.PythonFastload
(
PK INTEGER,
TheDate DATE FORMAT 'YY/MM/DD',
TheInteger INTEGER,
TheDecimal DECIMAL(10,2),
TheVarchar VARCHAR(34) CHARACTER SET LATIN NOT CASESPECIFIC
) PRIMARY INDEX ( PK );The Required Packages
The following packages are necessary to access Teradata:
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book- teradatasql
- pandas
- NumPy
- faker
The pandas, NumPy, and faker packages serve solely to generate fictitious random data for import.
import pandas as pd
import numpy as np
import teradatasqlimport faker.providers as f
from faker import Faker
fake=Faker()
from datetime import datetimeNext, we establish a connection to Teradata and initiate a cursor.
import os
con = teradatasql.connect(host=os.environ["TD_HOST"],
user=os.environ["TD_USER"],
password=os.environ["TD_PASSWORD"],
database="DWHPRO")
cur = con.cursor()We’ll create a helper function for generating random dates to insert:
def random_dates(start, end, size):
divide_by = 24 * 60 * 60 * 10**9
start_u = start.value // divide_by
end_u = end.value // divide_by
return pd.to_datetime(np.random.randint(start_u, end_u, size), unit="D").dateFunction executing the Fastload
This function utilizes a data frame to load the simulated data.
def fload(size_tran = 100000):
"""Uses Teradata FastLoad method to load data
"""
if size_tran <= 0:
raise ValueError("size_tran must be positive")
# Create the empty target using the DDL above before calling this function.
sRequest = "{fn teradata_nativesql}{fn teradata_autocommit_off}"
print (sRequest)
cur.execute (sRequest)
size = 100000
start = datetime.now()
tran = pd.DataFrame( )
tran["PK"] = [x+1 for x in range(size_tran)]
tran["TheDate"] = random_dates(start=pd.to_datetime('2021-01-01'), end=pd.to_datetime('2021-12-31'), size=size_tran)
tran["TheInteger"] = np.random.choice([x+1 for x in range(size)] , size =size_tran)
tran["TheDecimal"]= [ np.random.randint(10, 100000) / 100 for x in range(size_tran)]
tran["TheVarChar"]= np.random.choice([fake.iban() for _ in range(1000)] , size = size_tran)
tran_list = tran.values.tolist()
parameter = ",?"*len(tran_list[0])
parameter = parameter[1:]
aaoValues= tran.values.tolist()
if any(len(row[4]) > 34 for row in aaoValues):
raise ValueError("IBAN exceeds target width")
sInsert = "{fn teradata_require_fastload}INSERT INTO DWHPRO.PythonFastload ("+parameter+")"
print (sInsert)
try:
cur.execute(sInsert, aaoValues)
con.commit()
except Exception:
con.rollback()
raise
print(f"transaction: data generated {datetime.now()-start}")
print(f"transaction: data inserted {datetime.now()-start}")We execute the Fastload by calling the function, then close the cursor and terminate the connection to Teradata.
fload(size_tran = 100000)cur.close()
con.close()If the load-slot limit is reached, inspect the exact load job and its owned sessions with the DBA. Wait, reduce concurrency or cancel only an identified job under the normal operational procedure. Do not abort every session belonging to an administrator account.
The broad administrator-session abort command has been removed because it can terminate unrelated work.
Check the driver’s FastLoad errors and warnings and verify the loaded row count and values. The require_fastload escape reports failure instead of silently falling back to another protocol. In the tested driver, two 22-character inputs were truncated in VARCHAR(20) but preserved in VARCHAR(34); warning text alone did not expose that truncation.
Building or improving a data pipeline?
Discuss ingestion, transformations, orchestration, quality checks and operational handover.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Can you please same for FastExport using python. Also, what is the approach to extract dml/ddl statement from the teradata using python ?
Great article!