CODEWITHSUNDEEP
sqlpowershellcsvexportteradata
Souhail Ouabi
Souhail Ouabi

Reputation: 125

Export result of a query to a text file by Teradata Parallel transporter

I need to automate an export of a large amount of data (from TERADATA by an sql query) to a pipe delimited text file. I used PowerShell (ConvertTo-CSV cmdlet) but it was very slow. I was advised to use TPT to export but I have never used this Tool and all I have found is how to export one table to a flat file and not a complex query with more than a table. Does someone know how to proceed with TPT or has a sample of a script for that?

Edit :

I am using this script for TPT but it has I think a lot of errors 

DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a delimited file'
(
DEFINE SCHEMA select EXPORT_DELIMITED_FILE from DELIMITED OF OPERATOR SQL_SELECTOR  
DEFINE OPERATOR SQL_SELECTOR
TYPE SELECTOR
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'selector_log',
VARCHAR TdpId = 'Server',
VARCHAR UserName = 'user',
VARCHAR UserPassword = 'password',
VARCHAR SelectStmt = 'E:\PowerShell\SQL\file.sql',
VARCHAR ReportModeOn
);
DEFINE OPERATOR FILE_WRITER
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = 'E:\PowerShell\Output',
VARCHAR FileName = 'test_file.txt',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Write',
VARCHAR TextDelimiter = '|'
);
APPLY TO OPERATOR (FILE_WRITER)

SELECT * FROM OPERATOR (SQL_SELECTOR);
);

Upvotes: 0

Views: 7782

Answers (1)

xenodevil
xenodevil

Reputation: 604

Your schema definition is wrong. Schema defines how your data set looks like in terms of field names and data types.

Producer operators (in your case SQL Selector) always need to have a defined schema. Deferred schema (SCHEMA *) can only be used with consumer operators (in your case Data Connector), which allows consumers to have the same schema as the related producer.

Schema definitions looks something like this:

DEFINE SCHEMA FILE_SCHEMA
(
    Column1 VARCHAR(255),
    Column2 VARCHAR(255),
    Column3 VARCHAR(255),
    Column4 VARCHAR(255)
);

Remember that data connector operator accepts only character data in the schema. If you specify any other data type, it will result in error.

Also, SelectStmt must contain an actual SQL query, not the path to file containing query. For exporting data to a flat file through data connector operator, you will need to cast everything to VARCHAR type in your query

SelectStmt = 'SELECT CAST(ColumnA AS VARCHAR(100), CAST(ColumnB AS VARCHAR(100), CAST(ColumnC AS VARCHAR(100), CAST(COUNT(*) AS VARCHAR(100)) FROM MyTable GROUP BY 1,2,3,4;'

Note that number of columns returned by the SelectStmt are same as the define schema.

Also set ReportModeOn = 'Y' instead of leaving it to default value.

Always remember to indent the code. With indentation, the script now looks like:

DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a delimited file'
(
    DEFINE SCHEMA FILE_SCHEMA
    (
        Column1 VARCHAR(100),
        Column2 VARCHAR(100),
        Column3 VARCHAR(100),
        Column4 VARCHAR(100)
    );

    DEFINE OPERATOR SQL_SELECTOR
    TYPE SELECTOR
    SCHEMA FILE_SCHEMA
    ATTRIBUTES
    (
        VARCHAR PrivateLogName = 'selector_log',
        VARCHAR TdpId = 'Server',
        VARCHAR UserName = 'user',
        VARCHAR UserPassword = 'password',
        VARCHAR SelectStmt = 'SELECT CAST(ColumnA AS VARCHAR(100), CAST(ColumnB AS VARCHAR(100), CAST(ColumnC AS VARCHAR(100), CAST(COUNT(*) AS VARCHAR(100)) FROM MyTable GROUP BY 1,2,3;',
        VARCHAR ReportModeOn = 'Y'
    );

    DEFINE OPERATOR FILE_WRITER
    TYPE DATACONNECTOR CONSUMER
    SCHEMA *
    ATTRIBUTES
    (
        VARCHAR PrivateLogName = 'dataconnector_log',
        VARCHAR DirectoryPath = 'E:\PowerShell\Output',
        VARCHAR FileName = 'test_file.txt',
        VARCHAR Format = 'Delimited',
        VARCHAR OpenMode = 'Write',
        VARCHAR TextDelimiter = '|'
    );

    APPLY TO OPERATOR (FILE_WRITER)
    SELECT * FROM OPERATOR (SQL_SELECTOR);
);

Upvotes: 2

Related Questions