read_csv | Teradata Package for Python - read_csv - Teradata Package for Python

The read_csv() function loads data from CSV file into Teradata Vantage. This function is used to quickly load large amounts of data in a table on Vantage using FastLoadCSV protocol.

Teradata recommends using FastLoad protocol when number of rows to be loaded is at least 100,000. FastLoad opens multiple data transfer connections to the database, when the argument use_fastload is set to 'True'. The number of data transfer sessions can be set using argument open_sessions. If this argument is not set, by default, data transfer sessions opened by teradataml is the smaller of 8 and the number of available AMPs in Vantage.

See the CSV BATCH INSERTS section and FastLoad section of https://pypi.org/project/teradatasql/ for more information.

Returns

Example Setup

>>> import csv
>>> from collections import OrderedDict
>>> from teradataml.dataframe.data_transfer import read_csv
>>> from teradatasqlalchemy.types import *

>>> csv_filename = "test_file.csv"
>>> table_name = "read_csv_table"
>>> record = [
        'id,fname,lname,marks',
        '101,abc,def,200',
        '102,lmn,pqr,105',
        '103,def,xyz,492',
        '101,abc,def,300'
    ]

>>> # Function to write list data into csv file.
>>> def write_data_into_csv(filename, record):
        with open(filename, 'w', newline='') as csvFile:
            csvWriter = csv.writer(csvFile, delimiter='\n')
            csvWriter.writerow(record)
>>>
>>> write_data_into_csv(csv_filename, record)

Example 1: Default execution with types passed as OrderedDict

This example loads the data from CSV file into a table, with argument types passed as OrderedDict.

>>> read_csv('test_file.csv', 'my_first_table', types)>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT)

Example 2: Catch all errors and warnings, and store in the table

This example loads the data from CSV file into a table using fastload CSV protocol, and catch all errors and warnings and store those in the table.

>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table1', 
             types=types,
             save_errors=True, 
             catch_errors_warnings=True)

Example 3: Load data into an existing table, replace the table

This example loads the data from CSV file into a table using fastload CSV protocol. If table exists, then replace the table.

>>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             types=types, 
             if_exists='replace')

Example 4: Load data into an existing table, append the table

This example loads the data from CSV file into a table using fastload CSV protocol. If table exists in specified schema, then append the table.

>>> # Create new table in Vantage.
>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             types=types, 
             if_exists='fail')

>>> # Write different data into csv file.
>>> record = [
        'id,fname,lname,marks',
        '501,orl,mal,740'
    ]

>>> write_data_into_csv(csv_filename, record)
>>>
>>> # Append the existing table.
>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             if_exists='append')

Example 5: Load data into a SET table, catch all errors and warnings

This example loads the data from CSV file into a SET table using fastload CSV protocol. Catch all errors and warnings as well as store those in the table.

>>> # Write duplicate rows in csv file.
>>> record = [
           'id,fname,lname,marks',
            '101,abc,def,200',
            '102,lmn,pqr,105',
            '103,def,xyz,492',
            '101,abc,def,200'
        ]
>>> write_data_into_csv(csv_filename, record)

>>> # Create SET table.
>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             types=types, 
             if_exists='replace',
             set_table=True, 
             primary_index='id',
             save_errors=True, 
             catch_errors_warnings=True)

Example 6: Load data into a table without FastLoad protocol

This example loads the data from CSV file with DATE and TIMESTAMP columns into a table without FastLoad protocol. If table exists in specified schema, then append to the table.

>>> # Write date and timestamp in csv file.
>>> record = [
            'id,fname,lname,marks,admission_date,admission_time',
            '101,abc,def,200,2009-07-29,2009-07-29 20:17:59',
            '102,lmn,pqr,105,2009-07-29,2009-07-29 20:17:59',
            '103,def,xyz,492,2009-07-29,2009-07-29 20:17:59',
            '101,abc,def,200,2009-07-29,2009-07-29 20:17:59'
        ]
>>> write_data_into_csv(csv_filename, record)

>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT, admission_date=DATE, admission_time=TIMESTAMP)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             types=types, 
             if_exists='replace',
             use_fastload=False)

>>> # Append the existing table.
>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             if_exists='append',
             use_fastload=False)

Example 7: Load data into a temporary table without FastLoad protocol

This example loads the data from CSV file into a temporary table without FastLoad protocol. If table exists in specified schema, then append to the table.

>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT, admission_date=DATE, admission_time=TIMESTAMP)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             types=types, 
             if_exists='replace',
             temporary=True)

>>> # Append the existing table.
>>> read_csv(filepath='test_file.csv',
             table_name='my_first_table',
             if_exists='append',
             temporary=True)

Example 8: Load data into a PTI table

This example loads data from CSV file with TIMESTAMP columns into a PTI table. If specified table exists, then append to the table; otherwise, creates new table.

>>> types = OrderedDict(partition_id=INTEGER, adid=INTEGER, productid=INTEGER, event=VARCHAR, clicktime=TIMESTAMP)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_read_csv_pti_table',
             types=types, 
             if_exists='append',
             timecode_column='clicktime',
             columns_list='event',
             use_fastload=False)

Example 9: Load data into a SET PTI table

This example loads data from CSV file with TIMESTAMP columns into a SET PTI table. If specified table exists, then append to the table; otherwise, creates new table.

>>> types = OrderedDict(partition_id=INTEGER, adid=INTEGER, productid=INTEGER, event=VARCHAR, clicktime=TIMESTAMP)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_read_csv_pti_table',
             types=types, 
             if_exists='append',
             timecode_column='clicktime',
             columns_list='event',
             set_table=True)

Example 10: Load data into a temporary PTI table

This example loads data from CSV file with TIMESTAMP columns into a temporary PTI table. If specified table exists, then append to the table; otherwise, creates new table.

>>> types = OrderedDict(partition_id=INTEGER, adid=INTEGER, productid=INTEGER, event=VARCHAR, clicktime=TIMESTAMP)

>>> read_csv(filepath='test_file.csv',
             table_name='my_first_read_csv_pti_table',
             types=types, 
             if_exists='append',
             timecode_column='clicktime',
             columns_list='event',
             temporary=True)

Example 11: Load data into a table by opening given number of Teradata data transfer sessions

This example loads the data from CSV file into a Vantage table by opening two Teradata data transfer sessions.

>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT)

>>> read_csv(filepath='test_file.csv',  
             table_name='my_first_table_with_open_sessions',
             types=types, 
             open_sessions=2)

Example 12: Load data into table and set primary index

This example loads data from CSV file into a Vantage table and set primary index provided through the primary_index argument.

>>> types = OrderedDict(id=BIGINT, fname=VARCHAR, lname=VARCHAR, marks=FLOAT)

>>> read_csv(filepath='test_file.csv',  
             table_name='my_first_table_with_primary_index',
             types=types, 
             primary_index = ['fname'])