目的
Teradata DatabaseへSQLクエリーを送信するCUIプログラムであるBasic Teradata Query(BTEQ)の操作まとめ
コマンド
| 内容 | コマンド | 実行例・補足 |
|---|---|---|
| BTEQ起動 | bteq | 文字セットとI/OエンコードはASCII |
| BTEQ起動（+ログイン） | bteq .LOGON [tdpid]/[userid], [password], '[acctid]' | bteq .logon 127.0.0.1/sysdba,sysdba |
| ユーザを確認 | select user; | ビルトイン関数 |
| 直前のコマンドを再実行 | = | |
| 現在の設定を確認 | show controls [設定]; | 設定が指定されない場合はALL |
| 書式設定をデフォルトに戻す | .set defaults; | |
| 分割して表示 | .foldline; | |
| 列名を左に表示 | .sidetitles | |
| 表示幅を変更 | .width [文字数] | .width 200 |
| 列の表示文字数を指定 | カラム名 (format 'X(文字数)') | カラム名(format 'X(20)') |
実行例
BTEQ起動
TDExpress1620_Sles11:~ # bteq
Teradata BTEQ 16.20.00.08 (32-bit) for LINUX. PID: 3711
Copyright 1984-2018 Teradata. All rights reserved.
Enter your logon or BTEQ command:
BTEQ起動+ログイン
bteq .LOGON [tdpid]/[userid], [password], '[acctid]'
tdpid(Teradata Director Program ID)を省略した場合、デフォルトは「dbc」
hostsファイルの「dbccop1」で指定されているIPアドレスに接続
IPアドレスを直接指定しても可
TDExpress1620_Sles11:~ # bteq .logon 127.0.0.1/sysdba,sysdba;
Teradata BTEQ 16.20.00.08 (32-bit) for LINUX. PID: 4420
Copyright 1984-2018 Teradata. All rights reserved.
Enter your logon or BTEQ command:
.logon 127.0.0.1/sysdba,
*** Logon successfully completed.
*** Teradata Database Release is 16.20.32.23
*** Teradata Database Version is 16.20.32.23
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.
ユーザを確認
select user;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
User
---------------------------------------------------------------------------
SYSDBA
設定を確認
show controls;
Maximum Instruction Bytes = 1048500
Maximum Record Bytes = 1024000
Maximum Request Buffer Bytes = 7340032
Maximum Response Buffer Bytes = 16775168
Maximum Packed Records = 64000
Activity Count Bytes = 8
Maximum Sessions = 200
Maximum Repeat Factor = 2147483647
Maximum String Length = 254
Maximum Report Columns = 65535
Maximum WITH Clauses for a Report = 10
Maximum Report Title Lines = 10
Maximum Report Page Lines = 2147483647
Minimum Report Width = 20
Maximum Report Width = 1048575
Maximum Notify MSG Text Bytes = 254
Maximum Bytes Saved for SUPPRESS,
PAGEBREAK, SKIPLINE, UNDERLINE,
and SKIPDOUBLE Commands, and for
&n Substitutions = 256
Client Platform Byte Order = LITTLE ENDIAN
EXPORT RESET
IMPORT RESET
LOGMECH = default
LOGON 127.0.0.1/sysdba
MESSAGEOUT RESET
REPEAT = 1
RUN
[SET] AUTOKEYRETRIEVE = OFF
[SET] BRANCHMSG = VERBOSE
[SET] DECIMALDIGITS = 0 (SPB DEFAULT)
[SET] ECHOREQ = ON
[SET] ENCRYPTION = OFF
[SET] ERRORLEVEL = ON
[SET] ERRORONDBSWARNING = OFF
[SET] ERROROUT = STDERR
[SET] EXITONDELAY = OFF
[SET] FOLDLINE = ON 1-100
[SET] FOOTING = NULL
[SET] FORMAT = OFF
[SET] FORMCHAR = OFF
[SET] FULLYEAR = OFF
[SET] HEADING = NULL
[SET] INDICATORMODE (alias INDICDATA) = OFF
[SET] LARGEDATAMODE = OFF
[SET] LOGONPROMPT = ON
[SET] MAXERROR = OFF
[SET] NOTIFY = OFF
[SET] NULL = ?
[SET] OMIT = OFF ALL
[SET] PACK = 0
[SET] PAGEBREAK = OFF ALL
[SET] PAGELENGTH = 55
[SET] PREPAREMODE = OFF
[SET] QUIET = OFF
[SET] RECORDLENGTH = Import/Export: MAX64 Stdout: MAX1MB
[SET] RECORDMODE = OFF
[SET] REPEATSTOP = OFF
[SET] REPORTALIGN = COMPATIBLE
[SET] RETCANCEL = OFF
[SET] RETLIMIT = Rows: No Limit Columns: 100
[SET] RETRY = ON
[SET] RTITLE = NULL
[SET] SEPARATOR = two spaces
[SET] SESSION CHARSET = ASCII
import/export encoding = ASCII
stdin/stdout encoding = ASCII
[SET] SESSION RESPBUFLEN = 8192
[SET] SESSION SQLFLAG = NONE
[SET] SESSION TRANSACTION = BTET
[SET] SESSION TWORESPBUFS = ON
[SET] SESSIONS = 1
[SET] SIDETITLES = OFF for the normal report.
And, it is ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
[SET] SKIPDOUBLE = OFF ALL
[SET] SKIPLINE = OFF ALL
[SET] STATEMENTINDEPENDENCE = OFF
[SET] SUPPRESS = OFF ALL
[SET] TDP = 127.0.0.1
[SET] TIMEMSG = DEFAULT
[SET] TITLEDASHES = ON for the normal report.
And, it is ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
[SET] TMSMMSG = OFF
[SET] TRAILINGLINES = DEFAULT
[SET] TRANSLATECTRLSTOSPACES = ON
[SET] TRIMTRAILINGSPACES = ON
[SET] UNDERLINE = OFF ALL
[SET] WIDTH = 75
分割して表示
.foldline;
show controls foldline;
[SET] FOLDLINE = ON 1-100
select c1, c2, c3 from t1 where c1 < 20;
*** Query completed. 10 rows found. 3 columns returned.
*** Total elapsed time was 2 seconds.
c1
-----------
c2
--
c3
----
19
a
abcd
17
a
abcd
7
a
abcd
15
a
abcd
5
a
abcd
13
a
abcd
3
a
abcd
11
a
abcd
1
a
abcd
9
a
abcd
列名を左に表示
.sidetitles;
BTEQ -- Enter your SQL request or BTEQ command:
.show controls sidetitles;
.show controls sidetitles;
[SET] SIDETITLES = ON for the normal report.
And, it is ON for results of WITH clause number: 1 2 3 4 5 6 7 8 9 10.
select c1, c2, c3 from t1 where c1 < 20;
*** Query completed. 10 rows found. 3 columns returned.
*** Total elapsed time was 2 seconds.
c1 19
c2 a
c3 abcd
c1 17
c2 a
c3 abcd
c1 7
c2 a
c3 abcd
c1 15
c2 a
c3 abcd
c1 5
c2 a
c3 abcd
c1 13
c2 a
c3 abcd
c1 3
c2 a
c3 abcd
c1 11
c2 a
c3 abcd
c1 1
c2 a
c3 abcd
c1 9
c2 a
c3 abcd
列の表示文字数を指定
UserName PermSpace
-------------------------------------------------------------------------------------------------------------------------------- --------------------------
dbadmin 10,000,000
LockLogShredder 4,800,000
DBC 25,776,351,028
TDPUSER 0
tdwm 100,000,000
SystemFe 6,400,000,000
External_AP 160,000,000
Sys_Calendar 25,600,000
SysAdmin 160,000,000
TDMaps 4,800,000,000
Crashdumps 0
viewpoint 10,000,000
console 50,000
select UserName(format 'X(20)'), PermSpace from dbc.UsersV;
UserName PermSpace
-------------------- --------------------------
dbadmin 10,000,000
LockLogShredder 4,800,000
DBC 25,776,351,028
TDPUSER 0
tdwm 100,000,000
SystemFe 6,400,000,000
External_AP 160,000,000
Sys_Calendar 25,600,000
SysAdmin 160,000,000
TDMaps 4,800,000,000
Crashdumps 0
viewpoint 10,000,000
console 50,000
参考
BTEQの紹介 • Basic Teradata Query リファレンス • リーダー • Teradataドキュメント