TeradataBinarySerde

TeradataBinarySerde

TeradataBinarySerde는 Teradata의 바이너리 데이터를 Hive 테이블로 읽거나 쓸 수 있게 해주는 SerDe예요. TPT( Teradata Parallel Transporter)나 BTEQ( Basic Teradata Query )로 내보낸 gzip 압축 데이터 파일을 Hive에서 직접 사용할 수 있게 합니다.

출처: 문서

본문

사용 가능 버전 (Availability)

TeradataBinarySerDe는 Hive 2.4 이상에서 사용할 수 있어요. (가장 이른 버전은 CSVSerde)

개요 (Overview)

Teradata는 TPT 또는 BTEQ를 사용해 gzip으로 압축된 데이터 파일을 매우 빠른 속도로 내보내고 가져올 수 있어요. 하지만 이런 바이너리 파일은 Teradata의 독점 형식으로 인코딩되어, 사용자 정의 SerDe 없이는 Hive가 직접 소비할 수 없습니다. TeradataBinarySerde는 사용자가 Teradata 바이너리 데이터를 Hive 테이블로 읽거나 쓸 수 있게 해줘요.

• TPT/BTEQ로 내보내 Hive에 등록한 Teradata 바이너리 데이터 파일을 직접 소비
• Hive에서 Teradata 바이너리 데이터 파일을 생성해 TPT/BTEQ로 Teradata에 직접 로드

내보내는 방법 (How to export)

TeradataBinarySerde는 다음 제약과 함께 'Formatted' 또는 'Formatted4' 모드의 데이터 파일을 지원합니다.

• INDICDATA 형식을 사용 (DATA를 사용하지 말 것)
• 최대 소수 자릿수(decimal digits) = 38 (18을 사용하지 말 것)
• 날짜 형식 = integer (ANSI를 사용하지 말 것)

TPT FastExport 사용하기

TPT FastExport를 호출하는 bash 스크립트 예시입니다.

query_table=foo.teradata_binary_table
data_date=20180108
select_statement="SELECT * FROM $query_table WHERE transaction_date BETWEEN DATE '2018-01-01' AND DATE '2018-01-08' AND is_deleted=0"
select_statement=${select_statement//\'/\'\'}  # Do not put double quote here
output_path=/data/foo/bar/${data_date}
num_chunks=4

tbuild -C -f $td_export_template_file -v ${tpt_job_var_file} \
  -u "ExportSelectStmt='${select_statement}',FormatType=Formatted,DataFileCount=${num_chunks},
  FileWriterDirectoryPath='${output_path},FileWriterFileName='${query_table}.${data_date}.teradata',
  SourceTableName='${query_table}'"

td_export_template_file은 적절한 Format, MaxDecimalDigits, DateForm이 들어간 아래와 같은 형태입니다.

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_TO_BINARY_FORMAT
DESCRIPTION 'Export to the INDICDATA file'
(
  /* https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/Load_and_Unload_Utilities/B035-2436%E2%80%90086K/2436ch03.05.3.html */
  APPLY TO OPERATOR ($FILE_WRITER()[@DataFileCount] ATTR
    (
    IndicatorMode = 'Y',
    OpenMode      = 'Write',
    Format        = @FormatType,
    DirectoryPath = @FileWriterDirectoryPath,
    FileName      = @FileWriterFileName,
    IOBufferSize  = 2048000
    )
  )
  SELECT * FROM OPERATOR ($EXPORT()[1] ATTR
    (
    SelectStmt        = @ExportSelectStmt,
    MaxDecimalDigits  = 38,
    DateForm          = 'INTEGERDATE',  /* ANSIDATE is hard to load in BTEQ */
    SpoolMode         = 'NoSpool',
    TdpId             = @SourceTdpId,
    UserName          = @SourceUserName,
    UserPassword      = @SourceUserPassword,
    QueryBandSessInfo = 'Action=TPT_EXPORT;SourceTable=@SourceTableName;Format=@FormatType;'
    )
  );
);

로그인 자격 증명은 명령줄이 아니라 tpt_job_var_file에 제공됩니다.

 SourceUserName=<td_use>
,SourceUserPassword=<td_pass>
,SourceTdpId=<td_pid>

BTEQ 사용하기

BTEQ 스크립트는 적절한 INDICDATA, Format, MaxDecimalDigits, DateForm이 들어간 아래와 같은 형태예요. 기본적으로 recordlength=max64(Formatted)가 적용되므로, 'Formatted4' 모드가 필요하면 MAX1MB를 명시적으로 지정해야 합니다.

SET SESSION DATEFORM=INTEGERDATE;
.SET SESSION CHARSET UTF8
.decimaldigits 38

.export indicdata recordlength=max1mb file=td_data_with_1mb_rowsize.dat
select * from foo.teradata_binary_table order by test_int;
.export reset

가져오는 방법 (How to import)

BTEQ 사용하기

유니코드를 사용할 때 CHAR(n) 컬럼은 USING() 절에서 CHAR(n x 3)으로 지정해야 해요. 예를 들어 test_char가 DDL에서 CHAR(1) CHARACTER SET UNICODE로 정의됐다면, BTEQ로 로드할 때 최대 3바이트를 차지하므로 USING()에 CHAR(3)으로 나타납니다. n x 3 규칙을 적용하지 않으면 BTEQ가 "Failure 2673 The source parcel length does not match data that was defined" 같은 오류를 만날 수 있어요.

RECORDLENGTH=MAX1MB 데이터 파일을 로드하는 샘플 BTEQ 스크립트:

SET SESSION DATEFORM=INTEGERDATE;
.SET SESSION CHARSET UTF8
.decimaldigits 38

.IMPORT INDICDATA RECORDLENGTH=MAX1MB FILE=td_data_with_1mb_rowsize.teradata
.REPEAT *
USING(
      test_tinyint BYTEINT,
      test_smallint SMALLINT,
      test_int INTEGER,
      test_bigint BIGINT,
      test_double FLOAT,
      test_decimal DECIMAL(15,2),
      test_date DATE,
      test_timestamp TIMESTAMP(6),
      test_char CHAR(3),  -- CHAR(1) will occupy 3 bytes
      test_varchar VARCHAR(40),
      test_binary VARBYTE(500)
)
INSERT INTO foo.stg_teradata_binary_table
(
 test_tinyint, test_smallint, test_int, test_bigint, test_double, test_decimal,
 test_date, test_timestamp, test_char, test_varchar, test_binary
)
values (
:test_tinyint,
:test_smallint,
:test_int,
:test_bigint,
:test_double,
:test_decimal,
:test_date,
:test_timestamp,
:test_char,
:test_varchar,
:test_binary
);

.IMPORT RESET

TPT FastLoad 사용하기

tbuild는 여러 gzip 파일을 병렬로 로드할 수 있어서, TPT는 대용량 데이터 파일을 벌크 로드하는 가장 좋은 선택이에요. TPT FastLoad를 호출하는 bash 스크립트 예시입니다.

staging_database=foo
staging_table=stg_table_name_up_to_30_chars
table_name_less_than_26chars=stg_table_name_up_to_30_c
file_dir=/data/foo/bar
job_id=<my_job_execution_id>

tbuild -C -f $td_import_template_file -v ${tpt_job_var_file} \
  -u "TargetWorkingDatabase='${staging_database}',TargetTable='${staging_table}',
     SourceDirectoryPath='${file_dir}',SourceFileName='*.teradata.gz',
     FileInstances=8,LoadInstances=1,
     Substr26TargetTable='${table_name_less_than_26chars}',
     TargetQueryBandSessInfo='TptLoad=${staging_table};JobId=${job_id};'"

td_import_template_file은 다음과 같은 형태입니다.

USING CHARACTER SET @Characterset
DEFINE JOB LOAD_JOB
DESCRIPTION 'Loading Data From File To Teradata Table'
(
set LogTable=@TargetWorkingDatabase||'.'||@Substr26TargetTable||'_LT';
set ErrorTable1=@TargetWorkingDatabase||'.'||@Substr26TargetTable||'_ET';
set ErrorTable2=@TargetWorkingDatabase||'.'||@Substr26TargetTable||'_UT';
set WorkTable=@TargetWorkingDatabase||'.'||@Substr26TargetTable||'_WT';
set ErrorTable=@TargetWorkingDatabase||'.'||@Substr26TargetTable||'_ET';

set LoadPrivateLogName=@TargetTable||'_load.log'
set UpdatePrivateLogName=@TargetTable||'_update.log'
set StreamPrivateLogName=@TargetTable||'_stream.log'
set InserterPrivateLogName=@TargetTable||'_inserter.log'
set FileReaderPrivateLogName=@TargetTable||'_filereader.log'

STEP PRE_PROCESSING_DROP_ERROR_TABLES
(
APPLY
('release mload '||@TargetTable||';'),
('drop table '||@LogTable||';'),
('drop table '||@ErrorTable||';'),
('drop table '||@ErrorTable1||';'),
('drop table '||@ErrorTable2||';'),
('drop table '||@WorkTable||';')
TO OPERATOR ($DDL);
);

STEP LOADING
(
    APPLY $INSERT TO OPERATOR ($LOAD() [@LoadInstances])
    SELECT * FROM OPERATOR ($FILE_READER() [@FileInstances]);
);
);

tpt_job_var_file에 SourceFormat, DateForm, MaxDecimalDigits 같은 올바른 값을 설정하세요. 예:

 Characterset='UTF8'
,DateForm='integerDate'
,MaxDecimalDigits=38

,TargetErrorLimit=100
,TargetErrorList=['3807','2580', '3706']
,TargetBufferSize=1024
,TargetDataEncryption='off'
,SourceOpenMode='Read'
,SourceFormat='Formatted'
,SourceIndicatorMode='Y'
,SourceMultipleReaders='Y'

,LoadBufferSize=1024
,UpdateBufferSize=1024
,LoadInstances=1

,TargetTdpId=<td_pid>
,TargetUserName=<td_user>
,TargetUserPassword=<td_pass>

사용법 (Usage) — 테이블 생성

특정 Teradata 속성으로 테이블 생성:

CREATE TABLE `teradata_binary_table_1mb`(
  `test_tinyint` tinyint,
  `test_smallint` smallint,
  `test_int` int,
  `test_bigint` bigint,
  `test_double` double,
  `test_decimal` decimal(15,2),
  `test_date` date,
  `test_timestamp` timestamp,
  `test_char` char(1),
  `test_varchar` varchar(40),
  `test_binary` binary
 )
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.teradata.TeradataBinarySerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.TeradataBinaryFileOutputFormat'
TBLPROPERTIES (
  'teradata.timestamp.precision'='6',
  'teradata.char.charset'='UNICODE',
  'teradata.row.length'='1MB'
);

기본 Teradata 속성:

'teradata.timestamp.precision'='6',
'teradata.char.charset'='UNICODE',
'teradata.row.length'='64KB'

Table Properties

Teradata → Hive 타입 변환

Serde 제약 (Serde Restriction)

TeradataBinarySerde에는 몇 가지 제약이 있어요.

• 위에 나열된 단순 데이터 타입만 지원하며, INTERVAL, TIME, NUMBER, CLOB, BLOB 같은 Teradata의 다른 타입은 아직 지원하지 않습니다.
• ARRAY, MAP 같은 복합 데이터 타입은 지원하지 않아요.

더 알아보기 (Learn more)

Teradata의 독점 바이너리 파일을 Hive에서 직접 다룰 수 있게 해주는 SerDe예요. 내보내기(Formatted/Formatted4)와 가져오기 양쪽을 지원하며, 사용 전에 teradata.row.length·teradata.char.charset·teradata.timestamp.precision 속성을 제대로 설정해야 합니다.