import data from file in to teradata table

Database
Enthusiast

import data from file in to teradata table

Hello,

 I am trying to import data from a file (that is created exporting from db2 table) and load it to in teradata file. The file has clob data. MLOAD does not work with clob data. 

I just have 2 columns, UNQ_ID VARCHAR(26) and MSG_CNT CLOB(25000).

Thanks for your help

4 REPLIES
Enthusiast

Re: import data from file in to teradata table

If your clob is only 25k, then change the definition to varchar(25000), then you could load using mload.  If you want to load data as a clob, then you can only use bteq with deferred or tpt streams/tpump with deferred.

What this would mean is that you would have to seperate your non-clob data from your clob data.  You would have one file that contains all the non-clob data with a pointer to the location of the clob file in the position of the clob data which should be the last element in the file/table.   

so for example my data file might look like this.

Data file layout:

1a|2a|3a|4a|/path/to/clob/file1

1b|2b|3b|4b|/path/to/clob/file2

Clob File:

file1

file2

This is how you would have to build your files to load the clob into Teradata using the deferred method.  It is ugly and a major pita to get source systems to build files that way, and if you have a lot of data and need to load it fast, it isn't going to work well.

Since your clob value is would easily fit into a varchar data type, you would be better off converting it in Teradata to a varchar field, then when you extract it, you can load it via mload like you would a regular varchar field.

Other options for loading clob data...

1.  break the clob value up into sections when bringing from the source such that you could load to a staging table using fastload, and include some sort of sequence value for each set of records that holds the portion of the clob value and then use a recursive sql to put it back together and insert it into the final table.  Still ugly and requires a bit more etl work getting the data out of the source, but much easier than dealing with all the additional files.

2. Break the clob up into multiple rows and just store them in a varchar field as seperate rows with a sequence value (look at how the dbqlsqltbl is setup).  Then you can just construct the clob on the fly if needed.

3.  Build a special java process that can load the clob data inline (I believe that it can be done using java this way, but it requires a bit of tweaking of the java program, and it won't be very fast since it would be inserting a record at a time...so much like bteq but with a single file to deal with).

If you don't have to use the clob data type, then don't...that is really the best advice.  If you have to store clob data that is larger than 64k (latin) or 32k (unicode) your looking at some really convoluted ways of getting the data loaded.

There hasn't been any real progress on getting LOB (clob/blob), JSON, XML, ST_GEOMETRY data loaded using the bulk load operations and even using the deferred via BTEQ/Tpump/TPT Streams is painful if you have a lot of data to load and need to load frequently.

Now, my comments above are all based my experience when working with clob/blob data and ST_Geometry (which is a clob).   Others may have figured out better ways to do this, or have different methods that might make it easier to get clob data loaded, and if so they can add their two cents.

Enthusiast

Re: import data from file in to teradata table

thank you so much for sharing your knowledge.

I am first doing an export from db2 table and its in the file. since its a clob data i have reference file. so my export is creating 2 files

counts.dat

counts.dat.001.lob

sample data in the counts.dat file. Its comma delimited file.

70, $P,counts.dat.001.lob.0.29423/

70, $Q6v,counts.dat.001.lob.29423.22791/

The table that i am loading in to teradata is 

CREATE SET TABLE ISO_WORK.RTF_ARCHV58 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      CURR_XSITN_ST INTEGER,

      RTF_UNQ_ID CHAR(26) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      MSG_CNTNT CLOB(2097088000) CHARACTER SET LATIN)

PRIMARY INDEX pi_ETL_RTF_ARCHV ( RTF_UNQ_ID );

I am using the following bteq script and running through generic unix script.  following is content in the bteq script.

.IMPORT VARTEXT ',' FILE=/etl/tst/scripts/ids/counts.dat;

.QUIET ON

.REPEAT *

USING

(   CURR_XSITN_ST VARCHAR(10),

    RTF_UNQ_ID VARCHAR(26),

    MSG_CNTNT CLOB(2097088000)

)

INSERT INTO ISO_WORK.etl_rtf_archv58

    ( CURR_XSITN_ST, RTF_UNQ_ID,MSG_CNTNT)

VALUES (:CURR_XSITN_ST,:RTF_UNQ_ID:MSG_CNTNT);

.QUIT

It completing with the following msg. But zero records got loaded. 

 *** Logon successfully completed.

 *** Teradata Database Release is 14.10.04.03

 *** Teradata Database Version is 14.10.04.03

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Null statement accepted.

 *** Total elapsed time was 1 second.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

 *** Warning: EOF on INPUT stream.

 *** BTEQ exiting due to EOF on stdin.

 *** Exiting BTEQ...

 *** RC (return code) = 0

thanks




Enthusiast

Re: import data from file in to teradata table

any one who can give an insight on whats wrong here..  

thank you

Teradata Employee

Re: import data from file in to teradata table

The USING clause should specify MSG_CNTNT CLOB AS DEFERRED BY NAME