Fast-export example:
As the definition says, FastExport is a command-driven utility that uses multiple sessions to quickly transfer large amounts of data from tables and views of the Teradata Database to a client-based application.
Example of Fast-export:
.logtable $LOADDB.LOGTABLE_ARRANGEMENT;
.run File $LOGON_FILE;
.begin export sessions 2;
.export outfile $VAR6 FORMAT TEXT MODE RECORD;
SELECT TRIM(CAST(HEAD1.HEADER AS CHAR(100))) FROM
(
SELECT
'¿'||
TRIM(A.ID_HEADER)
|| '|' ||TRIM(A.CLUSTER_NAME)
|| '|' || TRIM(A.BUS_DATE)
|| '|' ||TRIM(A.PRODUCT_NAME)
|| '|' ||TRIM(A.PROCESS_NAME)
|| '|' ||TRIM(A.FREQUENCY_NAME)
|| '|' ||TRIM(A.VERSION_NO)
|| '¿'
AS HEADER
FROM
(
SELECT
'01' AS ID_HEADER
,TRIM(CLUSTER_NAME) AS CLUSTER_NAME
, CAST(CAST(CAST('$BUS_DATE' AS DATE FORMAT 'YYYY-MM-DD') AS DATE FORMAT 'DDMMMYYYY') AS VARCHAR(12)) AS BUS_DATE
,TRIM(PRODUCT_NAME) AS PRODUCT_NAME
,TRIM(PROCESS_NAME) AS PROCESS_NAME
,TRIM(FREQUENCY_NAME) AS FREQUENCY_NAME
,MAX(VERSION_NO)+1 AS VERSION_NO
FROM
?LOADDB.VERSION_HEAD
WHERE CAST('$BUS_DATE' AS DATE FORMAT 'YYYY-MM-DD')= RUNDATE
AND PRODUCT_NAME='PT_007_LEA'
group by 1,2,3,4,5,6
) AS A
) AS HEAD1
;
.end export;
.logoff;
TIPS:
- Convert date fields to varchar(12) before export
- Generally Fast-export will append the first 2 bytes with header information, so to eliminate that we have appended '¿' symbol
Use a post-treatment script to remove the '¿' character
Include the following command in UNIX script for post-treatment:
cat $VAR6 | cut -f 2 -d ¿ >$VAR6.txt
Please let me know if you need more information.
Please let me know if you need more information.
Is there any commands in the FEXP mode which would allow the files to be appended instead of overwriting it?
ReplyDeleteThere are no EXPORT commands that can append a file. The same can be achieved by writing into separate files and then concatenating the files.
DeleteIn the unix environment, you can concat the files recursively using:
cat File$N.csv >> File1.csv
$N can be set in the script
Let us know the scenario you are trying to address.
This comment has been removed by the author.
ReplyDeleteI did an export following your example. However when I look at the output file, I see binary characters in it. What tool do you have to use to import such an exported file?
ReplyDeleteRegards,
Girish
Hi,
Deletecan you post your script ? and how are you opening the file. On windows, or in vi editor?
Appreciate you getting back to me on this post.
ReplyDeleteI've 256 columns in this table. Here is a sample of the script (shortened). I've tried open the output file using various editors like vi and EmEditor (can handle large files) and I can't see any data in the file. The table has 100 rows in it.
Export script.
.logtable TESTDB.gtst_export_log;
.LOGON /,;
.begin export
sessions 4;
.export outfile C:\temp\exp\fast_export.out mode record format text;
SELECT
CAST(COLUMN_1 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_2 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_3 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_4 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_5 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_6 AS VARCHAR(100)) || '|' ||
..
..
..
..
CAST(COLUMN_250 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_251 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_252 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_253 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_254 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_255 AS VARCHAR(100)) || '|' ||
CAST(COLUMN_256 AS VARCHAR(100)) as rec
FROM TESTDB.GTST_EXP_TEST
;
.END EXPORT;
.LOGOFF;
Plesae see if the link below is helpful. You may want to check for NULL values in any of your columns. If yo see null, Coalesce(Column_1,'0') should solve the problem.
Deletehttp://teradataexample.blogspot.in/2013/04/fastexport-returning-blank-lines-common.html
Please check if you have NULL values in any of your columns. Kindly use Coalesce(Column_1,0) or whatever seems appropriate and then rerun the fastexport job.
ReplyDeleteLet me know your results.