How to import data into teradata tables from delimited file using BTEQ import?

Question

I am trying to execute following bteq command on linux environment but couldn't load data properly into Teradata DB server. Can someone please advise me to resolve the below issue that I am facing while loading.

BTEQ Command used :

.SET width 64000;
.SET session transaction btet;
.logmech ldap
.logon XXXXXXX/XXXXXXXX,********;

DATABASE corecm;

.PACK 1000
.IMPORT VARTEXT '~' FILE=/v/global/user/application_event_bus_evt
.REPEAT *
USING(APPLICATION_EVENT_ID CHAR(24),BUS_EVT_ID CHAR(24),BUS_EVT_VID BIGINT,BUS_EVT_RESTATE_IN SMALLINT)

insert into corecm.application_event_bus_evt (APPLICATION_EVENT_ID
, BUS_EVT_ID
, BUS_EVT_VID
, BUS_EVT_RESTATE_IN
)
values
( COALESCE(:APPLICATION_EVENT_ID,1)
, COALESCE(:BUS_EVT_ID,1)
, COALESCE(:BUS_EVT_VID,1)
, COALESCE(:BUS_EVT_RESTATE_IN,1)
) ;
.LOGOFF;
.EXIT;

SAMPLE INPUT FILE DELIMITTER "~" [ /v/global/user/application_event_bus_evt ] :

Ckn3gMxLEeOgIQBQVgErYA==~g+GDDtlaY3n7BdUrYshDFA==~1~1
CL1kEcxLEeOgIQBQVgErYA==~qoKoiuGDbClpcGt/z6RKGw==~1~1
oYIVcMxKEeOgIQBQVgErYA==~mfmQiwl7yAteevzJfilMvA==~1~1
5N7ME5bM4xGhM7exj3ykUw==~yFM2FZbM4xGhM7exj3ykUw==~1~0
JLBH4JfM4xGDH9s5+Ds/8w==~doZ/7pfM4xGDH9s5+Ds/8w==~1~0
fGvpoMxKEeOgIQBQVgErYA==~mQUQIK2mY6WIPcszfp5BTQ==~1~1

Table Definition :

CREATE MULTISET TABLE CORECM.APPLICATION_EVENT_BUS_EVT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      APPLICATION_EVENT_ID CHAR(26) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      BUS_EVT_ID CHAR(26) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      BUS_EVT_VID BIGINT NOT NULL,
      BUS_EVT_RESTATE_IN SMALLINT)
UNIQUE PRIMARY INDEX ( APPLICATION_EVENT_ID ,BUS_EVT_ID ,BUS_EVT_VID )
INDEX APPLICATION_EVENT_BUS_EVT_IDX1 ( APPLICATION_EVENT_ID )
INDEX APPLICATION_EVENT_BUS_EVT_IDX2 ( BUS_EVT_ID ,BUS_EVT_VID );

Results set in DB server as,

    APPLICATION_EVENT_ID        BUS_EVT_ID                  BUS_EVT_VID             BUS_EVT_RESTATE_IN 

1    Ckn3gMxLEeOgIQBQVgErYA     == g+GDDtlaY3n7BdUrYshD     85,849,873,219,141,958  12,544
2    CL1kEcxLEeOgIQBQVgErYA     == qoKoiuGDbClpcGt/z6RK     85,849,873,219,155,783  12,544
3    oYIVcMxKEeOgIQBQVgErYA     == mfmQiwl7yAteevzJfilM     85,849,873,219,142,006  12,544
4    5N7ME5bM4xGhM7exj3ykUw     == JAf0GpbM4xGhM7exj3yk     85,849,873,219,155,797  12,288
5    JLBH4JfM4xGDH9s5+Ds/8w     == Du6T7pfM4xGDH9s5+Ds/     85,849,873,219,155,768  12,288
6    fGvpoMxKEeOgIQBQVgErYA     == mQUQIK2mY6WIPcszfp5B     85,849,873,219,146,068  12,544

If we look at the Data, we can see two issues as,

1. First two column data length is 24 CHARACTERS ( as per input file ), but the issue is that it been shifted two characters in next column.

2. Column BUS_EVT_VID and BUS_EVT_RESTATE_IN has wrong data 85,849,873,219,141,958 and 12,544 instead of 1 and 1 respectively (this may be because first two column data got shifted)

I tried following options to resolve the above issue but couldn't resolve the issue,

1. Modified the Table Definition, i.e. changed datatype to CHAR(28),CHAR(24),CHAR(26)
2. Modified the Table Definition column datatypes to VARCHAR(24), VARCHAR(26)
3. Modified BTEQ command, i.e. altered datatype in below line, USING(APPLICATION_EVENT_ID CHAR(24),BUS_EVT_ID CHAR(24),BUS_EVT_VID BIGINT,BUS_EVT_RESTATE_IN SMALLINT)

Thanks in advance.

Answer 1

When you define VARTEXT all input columns must be defined as VARCHAR, but you used CHAR and INT.

This should work, VARCHAR length based on the definition of your target table:

USING(
      APPLICATION_EVENT_ID VARCHAR(26),
      BUS_EVT_ID           VARCHAR(26),
      BUS_EVT_VID          VARCHAR(19),
      BUS_EVT_RESTATE_IN   VARCHAR(6)
     )