#title Basic Teradata Query(BTEQ) [[TableOfContents]] ==== 개요 ==== BTEQ Session Modes - interactive mode - batch mode BTEQ Communication - channel-attached : a high-speed I/O channel - network-attached : a local area network The BTEQ Command Set - Session control commands - File control commands - Sequence control commands - Format control commands 무조건 따라하기 {{{ Teradata BTEQ 12.00.00.01 for WIN32. Copyright 1984-2007, NCR Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command: .logon 127.0.0.1/dbc .logon 127.0.0.1/dbc Password: *** Logon successfully completed. *** Teradata Database Release is 12.00.00.10 *** Teradata Database Version is 12.00.00.10 *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command: .SET SEPARATOR ' | ' .SET SEPARATOR ' | ' BTEQ -- Enter your DBC/SQL request or BTEQ command: select top 5 * from financial.customer; select top 5 * from financial.customer; *** Query completed. 5 rows found. 7 columns returned. *** Total elapsed time was 1 second. cust_id income age years_with_bank nbr_children gender marital_ ----------- ----------- ------ --------------- ------------ ------ -------- 1362752 47668 54 3 0 F 1 1362813 15684 53 3 2 F 2 1363221 0 10 4 0 F 1 1362956 0 10 4 0 F 1 1362487 6605 71 1 0 M 2 BTEQ -- Enter your DBC/SQL request or BTEQ command: }}} {{{ Basic TEradata Query ==== 시작 ==== 시작옵션 * -c [Charset Encoding] * BTEQ시작 할 때의 SESSION CHARSET * 데이터 Import/Export 시에 적용됨 * clispb.dat 를 override * -e [I/O Encoding] * I/O encoding BTEQ * stdin, stdout, stderr에 사용됨 * UTF8 or UTF16의 초기에 사용 bteq charset and I/O encoding default to ASCII bteq -c ASCII charset and I/O encoding are ASCII bteq -c KANJISJIS_0S charset and I/O encoding are KanjiShift-JIS bteq -c UTF8 charset and I/O encoding are UTF8 bteq -c UTF16 charset and I/O encoding are UTF16 bteq -e UTF8 -c UTF16 charset is UTF16; I/O encoding is UTF8 bteq -e 62 -c 63 charset is UTF8; I/O encoding is UTF16 bteq -c UTF16 < script charset and I/O encoding are UTF16; BTEQ receives input through a UTF16 script BTEQ Session Modes - interactive mode - batch mode BTEQ Communication - channel-attached : a high-speed I/O channel - network-attached : a local area network The BTEQ Command Set - Session control commands - File control commands - Sequence control commands - Format control commands Teradata BTEQ 12.00.00.01 for WIN32. Copyright 1984-2007, NCR Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command: .logon 127.0.0.1/dbc .logon 127.0.0.1/dbc Password: ----------------------> dbc 입력 *** Logon successfully completed. *** Teradata Database Release is 12.00.00.10 *** Teradata Database Version is 12.00.00.10 *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command: .SET SEPARATOR ' | ' .SET SEPARATOR ' | ' BTEQ -- Enter your DBC/SQL request or BTEQ command: select top 5 * from financial.customer; select top 5 * from financial.customer; *** Query completed. 5 rows found. 7 columns returned. *** Total elapsed time was 1 second. cust_id income age years_with_bank nbr_children gender marital_ ----------- ----------- ------ --------------- ------------ ------ -------- 1362752 47668 54 3 0 F 1 1362813 15684 53 3 2 F 2 1363221 0 10 4 0 F 1 1362956 0 10 4 0 F 1 1362487 6605 71 1 0 M 2 BTEQ -- Enter your DBC/SQL request or BTEQ command: /* dbc/dbc */ select 1 || '1' --문자열 연산(결과는 숫자형변환) , 1 + '1' --숫자 연산(float) , 1 + 1; *** Query completed. One row found. 3 columns returned. *** Total elapsed time was 1 second. (1||'1') (1+'1') (1+1) --------------- ---------------------- ----------- 11 2.00000000000000E 000 2 BTEQ -- Enter your DBC/SQL request or BTEQ command: select substr(a.acct_start_date, 1,4) yyyy , count(case when a.acct_type like 'S%' then 1 end) s_count , count(case when a.acct_type like 'C%' then 1 end) c_count from financial.accts a cross join customer b cross join credit_acct c group by substr(a.acct_start_date, 1, 4) ; }}}