Importing with BTEQ

Tools

Importing with BTEQ

I'm having a problem using BTEQ to import some rows into a table.

I have two bteq scripts, very basic: one exports a table to a flat file, the other imports the data right back from the flat file.

My problem arises with trying to import a row which has a timestamps with a null value. Rows with a null timestamp cause a "6760 Invalid timestamp." error. Both scripts have the statement ".SET NULL AS '?'" at the top of them.

I see Multiload has a NULLIF command, so you can say "NULLIF col01= ''". That's exactly what i want to do, except in BTEQ. Is there a way to do this?

In addition, using FastLoad/FastExport is not an option; this must be done with BTEQ.

Thanks for any advice; this is very frustrating.
9 REPLIES
Enthusiast

Re: Importing with BTEQ

Try using either the "Coalesce" function or a "case when ... then ... else ... end" statement... If you still cannot solve your problem, it would be helpful to see the actual script.
Hope it helps.

Re: Importing with BTEQ

Well, since I wasn't having much luck I'm now trying merely to insert a null string with bteq. However, I'm doing something wrong, because even that's not working.

All I'm trying to do is use one bteq script to export the data to a flat file, and use another bteq script to import it back. And Multiload/FastExport aren't options, has to be bteq.

Here's what I have. I appreciate any advice on what I'm doing. I hope this is something easy that I'm just missing because I'm a beginner.

--------------------------------------------
My test data is only one row in this table:
CREATE TABLE mydb.bteqtest
(
Number Integer NOT NULL
,Name CHAR(25)

)
UNIQUE PRIMARY INDEX( Number );
Test data:
5 NULL
---------------------------------------------
For my export script:

.LOGON machine/login,pwd;

.EXPORT DATA FILE=results.txt

SELECT TRIM(COALESCE(Number, ''))
||'|'||TRIM(COALESCE(Name, ''))
FROM mydb.bteqtest;

.quit
-------------------------------------------
Note: This script 'appears' to work fine. This isn't to say I'm doing it right: I might be exporting incorrectly and not successfully accounting for nulls. But it doesn't give me any erros when it runs.

Displaying the contents of results.txt shows:

5|
-------------------------------------------
Import script:

.LOGON machine/login,pwd;
.SET NULL AS ''
.IMPORT VARTEXT '|' file = results.txt

.REPEAT *

USING Number (Integer)
,String (CHAR(25))

INSERT INTO mydb.bteqtest( Number
,Name)

VALUES (:Number
,:String);

.QUIT
------------------------------------
This erros out and displays:
+---------+---------+---------+---------+---------+---------+---------+----

USING Number (Integer)
,String (CHAR(25))

INSERT INTO mydb.bteqtest( Number
,Name)

VALUES (:Number
,:String);
*** Starting Row 0 at Fri May 5 19:34:55 2006

*** Failure 2673 The source parcel length does not match data that was def
ined.
Statement# 1, Info =1
*** Total elapsed time was 1 second.

*** Warning: Out of data.
*** Finished at Fri May 5 19:34:55 2006
*** Total number of statements: 1, Accepted : 0, Rejected : 1

Teradata Employee

Re: Importing with BTEQ

If you are using VARTEXT format for the input file, all the fields in the USING clause should be declared as VARCHAR, with the max allowable length. You may need to use explicit FORMAT, CAST, etc. to control how Teradata will convert the strings to the appropriate data types, if the default isn't suitable.

But as written, your character field would be loaded with an empty string and padded with spaces. You can translate empty string:
CASE WHEN :String = '' THEN NULL ELSE :String END
Or if an empty string/spaces is a valid value then use COALESCE or CASE on the EXPORT to set the string to some special "flag" value (like '?').
Enthusiast

Re: Importing with BTEQ

Hello,

I am able to import timestamp from the file successfully. However, I am not able to compare it with a timstamp field on a table. Can some please help?

Here is the script:-

USING IN_LAST_UPDATE_DATE (VARCHAR(19))

SEL :IN_LAST_UPDATE_DATE,CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0))
,CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0)) ;

*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.

IN_LAST_UPDATE_DATE IN_LAST_UPDATE_DATE IN_LAST_UPDATE_DATE
------------------- ------------------- -------------------
2011-07-14 18:52:00 2011-07-14 18:52:00 2011-07-14 18:52:00

SEL EDW_UPD_TS FROM FDS_ETL_TARGET.QLT_INVSTGTN_F GROUP BY 1;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 6 seconds.

EDW_UPD_TS
-------------------
2011-04-11 04:22:31
SELECT F.PARNT_ID PARNT_PR_ID
FROM FDS_ETL_TARGET.QLT_INVSTGTN_F F
--and F.EDW_UPD_TS >= CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0));
and F.EDW_UPD_TS >= :IN_LAST_UPDATE_DATE;
*** Failure 3857 Cannot use value (or macro parameter) to match 'IN_LAST_UPDATE_DATE'.
Senior Apprentice

Re: Importing with BTEQ

USING is only for the next *request*, looks like you try to use it in another request.

USING IN_LAST_UPDATE_DATE (VARCHAR(19))
SELECT F.PARNT_ID PARNT_PR_ID
FROM FDS_ETL_TARGET.QLT_INVSTGTN_F F
WHERE F.EDW_UPD_TS >= CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0));

Dieter
Enthusiast

Re: Importing with BTEQ

gotcha...thanks.. :)

Re: Importing with BTEQ

I tried the same example through which this thread was initiated and found below error always. It looks like it has something to do with data placed in text file with export on step 1.

Step 1:

Export data from customers table to customers_backup flat file.

============================================================

.logon localhost/dbc,dbc

database TD_DEV;

.quiet on

.os rm -rf /root/Desktop/Teradata/customers_backup

.export report file=/root/Desktop/Teradata/customers_backup

select * from customers;

.export reset

============================================================

Step 2: Delete all data from source customers table and try to load data in back from flat file exported in Step 1.

============================================================

DELETE from customers all;

* Below command is the actual import command which runs in 2 modes, data mode which is of fixed width and vartext mode which is variable. *

.import vartext ' ' file=/root/Desktop/Teradata/customers_backup,skip=2

* Below command will limit packet size send to Micro Teradata Director Program *

.pack 100

* Below command will just push errors and warnings to stdout *

* Below command will repeat next subsequent transaction until all records from file are completely processed *

* This is the reason why we call BTEQ as row processor instead of 64K block processor. *

.repeat *

using (cust_id varchar(20)

,cust_name varchar(255)

,dept_id varchar(255)

,dept_name varchar(255)

)

insert into customers

(

cust_id

,cust_name

,dept_id

,dept_name

)

values

(

cast(trim(:cust_id) as int)

,trim(:cust_name)

,cast(trim(:dept_id) as int)

,trim(:dept_name)

);

.if errorcode != 0 then .goto errors

.label errors

* Below script is designed to populate customers table in case there are errors observed while loading it back*

.os bteq < /root/Desktop/Teradata/bteq_script

.quiet off

.quit 0

.logoff

=============================================================

I get error as below every time i try to load data into customers table at step 2 as below.

=============================================================

insert into customers

(

 cust_id

 ,cust_name

 ,dept_id

 ,dept_name

 ,row_id

)

values

(

 cast(trim(:cust_id) as int)

 ,trim(:cust_name)

 ,cast(trim(:dept_id) as int)

 ,trim(:dept_name)

 ,coalesce(:row_id,null)

);

 *** Starting Row 2 at Sun Aug 16 19:35:20 2015

 *** Warning: Out of data.

 *** Failure 2673 The source parcel length does not match data that was defi

 ned.

                Statement# 1, Info =1

 *** Total elapsed time was 1 second.

=============================================================

Below is my exported file contents generated during step 1.

    cust_id  cust_name                 dept_id  dept_name

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

          3  mom                           111  electricity

          1  kk                            123  sales

          4  dad                           189  railways

          2  kris                          101  ceo




Please suggest me of where am i going wrong.

Re: Importing with BTEQ

I am gettting failure 3857 errro while importing value.

Can i use my file column in multiple palces in script??

line clolumn name is SRC_DT and i want to use it as

cast(:src_dt as date format 'yyyy-mm-dd').

i want to use this many palces theni am gattting cant use value (or micro parameter ) to match *****.

Teradata Employee

Re: Importing with BTEQ

Please start a new topic for new questions.

Does your SQL start with a USING clause to define the parameter names?