Loading XML files using Fastload - Using URL to access front-end data and moving data to Teradata

1. A standard xml format

Let us assume that below data is stored in Test.xml:

 <Response>

                <ResponseID>R_3JHyAiOCTXKq</ResponseID>

                <ResponseSet>Default Response Set</ResponseSet>

                <Name>Anonymous</Name>

                <ExternalDataReference></ExternalDataReference>

                <EmailAddress></EmailAddress>

                <IPAddress>96.224.174.233</IPAddress>

                <Status>0</Status>

                <StartDate>2013-01-11 13:55:53</StartDate>

                <EndDate>2013-01-11 13:56:18</EndDate>

                <Finished>1</Finished>

                <Flag></Flag>

 </Response>

So, anything that comes between <Response> and </Response> is our data set.

Each field data is represented within its corresponding tags.

For example : <ResponseID>R_3JHyAiOCTXKq</ResponseID>

So, now to parse this xml, we will have to write the corresponding xslt file

Let us now write the corresponding xslt file called Test.xslt

2. The sample xslt file for the above xml data will be as follows:

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsl:stylesheet version="2.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="text"/>

<xsl:template match="/">

<xsl:for-each select="xml/Response">

<xsl:value-of select="ResponseID"/>|<xsl:value-of select="ResponseSet"/>|<xsl:value-of select="Name"/>|<xsl:value-of select="ExternalDataReference"/>|<xsl:value-of select="EmailAddress"/>|<xsl:value-of select="IPAddress"/>|<xsl:value-of select="Status"/>|<xsl:value-of select="StartDate"/>|<xsl:value-of select="EndDate"/>|<xsl:value-of select="Finished"/>|<xsl:value-of select="Flag"/>|

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

3. Extract the xml data into a flat file

xsltproc is the command line processor for xml

-o = redirect to output file. Otherwise, data will be redirected to standard output.

Place the Test.xml , Test.xslt in the same directory

Let us now run the below command:

xsltproc Test.xslt Test.xml -o Test.txt

The file Test.txt will contain "|" (pipe delimited) data parsed from the Test.xml file based on the Test.xslt layout file.

Testing row counts:

Data2=`wc -l Test.txt|  cut -d ' ' -f1`

Data1=`grep -i "</Response>" Test.xml | wc -l`

If $Data1 == $Data2, then all the rows were successfully parsed and record count in Test.txt and Test.xml match.

4. Load the Test.txt using Fastload into Teradata

Let us know if this information helped. You could donate for a noble cause.

Also, help us understand what topics would you like covered in the future blogs.

Features of Fastload with examples

Fast-load has the following features: 

Important observations:

1. We can load both SET and MULTISET tables using Fastload


2. Fastload supports Primary Index (both PI and UPI). Teradata Fastload does not support join indexes, foreign key references in target tables and tables with secondary index defined. It is necessary to drop any of the constraints listed before loading and recreate them afterwards.
3. If duplicate records are present in the source file, and we are loading a MULTISET table, the duplicates will be silently dropped, since fastload does not support duplicates.


For data :
mathew|19111987
karan|24121987
gaurav|24101986
gaurav|24101986
Data loaded in table is:

NAME	DOB
Mathew	19/11/1987
Gaurav	24/10/1986
Karan	24/12/1987

**** 07:28:21 END LOADING COMPLETE
Total Records Read = 4
Total Error Table 1 = 0 ---- Table has been dropped
Total Error Table 2 = 0 ---- Table has been dropped
Total Inserts Applied = 3
Total Duplicate Rows = 1
We get the above report at the end of loading.
4. If we define a delimiter, then FLD expects VARCHAR, VARBYTE only in the .DEFINE layout definition.


**** 07:28:17 Now set to read 'Variable-Length Text' records
**** 07:28:17 Delimiter character(s) is set to '|'
5. If dates are present in the input file, then load using


0015 INSERT INTO tl_62917_dly1_qrmdb.fld1
VALUES
(
:NAME,
:DOB(date, format 'ddmmyyyy')
);

Please note that the date format is for  the DATE format in the INPUT file.



6. If FLD fails due to data errors, then to find the records that failed, perform the following steps:

Total Records Read = 4

Total Error Table 1 = 3

Total Error Table 2 = 0 ---- Table has been dropped

Total Inserts Applied = 0

Total Duplicate Rows = 1

Write a bteq :

.logon server1/user1,mar@2012;

.export data file=fastload_error.txt

select trim(dataparcel) from TL_62917_DLY1_QRMDB.FLD1_ET;

.logoff

The DataParcel column in _ET table is LOV, so it can only be read if the export file is DATA

You can revert back with your queries, if any.

How to write a sample Fastload script?

How to write a sample Fastload script?

A fastload script will load data into a single table from one/more files.

Multifile loading will be covered in a future post.

A single file Fastload example is given below:

---------------------------

SESSIONS 5;

TENACITY 5;

SLEEP 5;
ERRLIMIT 50;

.logon server1/user1,passwd123;

DROP TABLE TL_62917_DLY1_QRMDB.FLD1;

CREATE MULTISET TABLE TL_62917_DLY1_QRMDB.FLD1

(

NAME VARCHAR(20)

,DOB DATE format 'yyyy-mm-dd'

)

PRIMARY INDEX(NAME)

;

DROP TABLE tl_62917_dly1_qrmdb.fld1_WT;

DROP TABLE tl_62917_dly1_qrmdb.fld1_ET;

DROP TABLE tl_62917_dly1_qrmdb.fld1_UV;

　

.SET RECORD VARTEXT "|";

DEFINE

NAME (VARCHAR(20)),

DOB (varchar(20))

　

　

FILE=/wload/wzed/app/subwload/ALL_DWH/sql/NON_INCOME/fld1.csv;

SHOW;

BEGIN LOADING tl_62917_dly1_qrmdb.fld1 ERRORFILES tl_62917_dly1_qrmdb.fld1_ET , tl_62917_dly1_qrmdb.fld1_UV

CHECKPOINT 1000;

INSERT INTO tl_62917_dly1_qrmdb.fld1

VALUES

(

:NAME,

:DOB(date, format 'ddmmyyyy')

);

　

END LOADING;

.LOGOFF;

.QUIT;

FASTLOAD:

---------------------------


How to improve the Fastload performance:

1. Check the total sessions assigned for the FASTLOAD. Higher the sessions, lesser the time.

2. If you are loading a very large file, increase the .CHECKPOINT.
    A checkpoint regularly makes an entry about the rows successfully loaded. So, its an overhead.

    Rule of thumb: Set a checkpoint at 10% of your total row-count in the file.

Share with us your problems, and we will help you solve them.
Donate if you are helped.
 For any training inquires please call us or fill the inquiry form on the right.