Friday 17 August 2012

Fast-export Utility in Teradata with example: How to effecitvely send data from Teradata to Client-applications


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.


    Wednesday 15 August 2012

    Connect using SFTP and transfer files


    How to connect to SFTP?


    Sftp is Simple File Transfer protocol which comes very handy in all of BI projects. The following steps are followed to Copy and Paste files.

    Copy files to a Directory:

    $ sftp
    lscpxx3@35.101.11.50

    Connecting to 35.101.11.50...
    sftp> mput ALL_DWH_v1.1.tar /wload/lscp/home/lscpxx3
    Uploading ALL_DWH_v1.1.tar to /wload/lscp/home/lscpxx3/ALL_DWH_v1.1.tar
    ALL_DWH_v1.1.tar 100% 2130KB 152.1KB/s 00:14
    sftp> exit

    Paste files to the other server:

    Now Connect to the other server:
    /wload/wzad/home/albatuat>sftp lscpxx3@35.101.11.50
    Connecting to 35.101.11.50...
    sftp> mget /wload/lscp/home/lscpxx3/ALL_DWH_v1.1.tar /wload/wzad/app/subwload/ALL_DWH
    Fetching /wload/lscp/home/lscpxx3/ALL_DWH_v1.1.tar to /wload/wzad/app/subwload/ALL_DWH/ALL_DWH_v1.1.tar
    /wload/lscp/home/lscpxx3/ALL_DWH_v1.1.tar 100% 2130KB 142.0KB/s 00:15
    sftp> exit

    This will help you to copy files to and fro across DEV, Test and Production UNIX boxes.