Can anyone explain me whether TPT Stream Operator(TPUMP) can be used to load data from file to table and table to file. If it is possible could you kindly provide the script for the same.
In my view, TPT Stream Operator is best suitable for mini batch loads. Could you confirm this also please.
The Stream operator just sends data to TD; in a TPT job the Data Connector Producer operator reads from files. Stream does not read from tables. See the manuals for examples.
I would say there is some overlap between use cases for mini-batch and use cases for Stream, but would stop well short of saying that's the best use for Stream, let alone that Stream was necessarily the best choice for mini-batch.
The idea of mini-batch is to load into a staging table, and then perform an INS-SEL or MERGE-INTO into the target table.
For this type of mini-batch, the Stream operator is actually the wrong operator to use. A user would typically use the Load operator to load the data into the staging table (because most staging tables are empty), and then issue the INS-SEL/MERGE-INTO SQL. Both of these operations can be performed by TPT as well.
As to file-to-table and table-to-file, yes TPT is able to perform that and there are several options for operators that can be used. For "file-to-table" you use the DataConnector operator for the file reader, and then you can pick from the Load, Update, Stream or Inserter operator to load the data into Teradata. For "table-to-file", you use the DataConnector operator as the file writer, and then you use the Export or Selector operator to extract the data out of Teradata.
The TPT User Guide has examples of these scenarios. And in the installation directories for TPT, there are sample scripts that you can reference.
There are plenty of sample scripts provided when you install TPT.
You can find them in the "samples" directory where TPT is installed.
And the TPT User Guide will describe these samples; the User Guide is structured to document how to use TPT in various scenarios.
As for which operator to use, it depends on the type of load job.
For initial loads of empty tables, you would use the Load operator.
For updating tables you can use the Update or Stream operator.
For jobs using the Update operator, Teradata will lock the target table for the duration of the job.
For jobs using the Stream operator, the target tables are not locked. But performance is quite a bit slower.
The Stream operator is best for small loads, loads where the user needs access to the table while the load is taking place, and for streaming data into Teradata.