DM7 外部表

DM7 外部表

需指定如下資訊：

1. 表名、表所屬的模式名；
2. 列定義；

3. 控制檔案路徑。

   文法格式

CREATE EXTERNAL TABLE < 表名定義> < 表結構定義>;

< 表名定義> ::=[< 模式名>.]< 表名>

< 表結構定義> ::= (< 列定義> {,< 列定義>})

< 列定義> ::= < 列名> < 資料類型>

FROM子句 = FROM子句1 | FROM子句2 | FROM子句3| FROM子句4

FROM子句1 ::= FROM '< 控制檔案路徑>'

FROM子句2 ::= FROM DATAFILE '< 資料檔案路徑>' [< 資料檔案參數清單>]

FROM子句3 ::= FROM < 控制檔案目錄> < 控制檔案選項>

FROM子句4 ::= FROM DATAFILE < 資料檔案目錄> < 資料檔案選項> [< 資料檔案參數清單>]

< 資料檔案參數清單> ::= PARMS(< 參數選項> {,< 參數選項>})

< 參數選項> ：：= [FIELDS DELIMITED BY < 表達式>] | [RECORDS DELIMITED BY < 表達式>]|[ ERRORS ]|[BADFILE '< 資料檔案路徑>']| |［LOG < 日志檔案路徑>］|［NULL_STR ］|［SKIP < 跳過行數>］|［CHARACTER_CODE < 檔案字元集>］

< 控制檔案目錄> ::= DEFAULT DIRECTORY < 控制檔案目錄名>

< 控制檔案選項> ::= LOCATION ('< 控制檔案名>')

< 資料檔案目錄> ::= DEFAULT DIRECTORY < 資料檔案目錄名>

< 資料檔案選項> ::= LOCATION ('< 資料檔案名>')

參數

1. < 模式名> 指明該表屬于哪個模式，預設為目前模式；
2. < 表名> 指明被建立的外部基表名；
3. < 列名> 指明基表中的列名；
4. < 資料類型> 指明列的資料類型，暫不支援多媒體類型；
5. < 控制檔案路徑> 指明使用的控制檔案的路徑的字元串；
6. < 資料檔案路徑> 指明使用的資料檔案路徑的字元串；

7. < 參數選項> FIELDS表示列分隔符；RECORDS表示行分隔符；ERRORS表示忽略外部表資料轉換中出現錯誤的行數，取值範圍為大于0的正整

   數，預設為0，表示不忽略錯誤。此處ERRORS 和控制檔案中的ERRORS寫一個就行，如果都寫，以控制檔案中的為主；LOG表示日志檔案路徑，

預設日志檔案名為fldr.log；NULL_STR指定資料檔案中NULL值的表示字元串，預設忽略此參數；SKIP指定跳過資料檔案起始的邏輯行數，默

認為0；CHARACTER_CODE指定資料檔案中資料的編碼格式，預設為GBK，可選項有GBK，UTF-8，SINGLE_BYTE和EUC-KR；

1. < 表達式> 字元串或十六進制串類型表達式，列分隔符隻允許字元串類型；

2. < 控制檔案目錄名> 指資料庫對象目錄的名稱。

   圖例

語句功能

供DBA或具有CREATE_TABLE權限的使用者定義外部基表。MPP環境下不支援建立外部表。

使用說明

1. < 表名>指定了所要建立的外部基表名。如果< 模式名>預設，則預設為目前模式。表名需要是合法的辨別符。且滿足SQL文法要求；
2. 外部表的表名最大長度為128個字元；
3. 所建外部基表至少要包含一個< 列名>指定的列，在一個外部基表中，各< 列名>不得相同。一張外部基表中至多可以包含2048列；
4. 外部基表不能存在大字段列；
5. 外部基表不能存在任何限制條件；
6. 外部基表不能為臨時表，不能建立分區；
7. 外部基表上不能建立任何索引；
8. 外部基表是隻讀的，不存在表鎖，不允許任何針對外部表的增删改資料操作，不允許truncate外部表操作；
9. 控制檔案路徑，以及資料檔案路徑建議采用絕對路徑；
10. 控制檔案的格式為：

[OPTIONS(

errors=

)]

LOAD [DATA]

INFILE [LIST] |

INTO TABLE tablename

FIELDS

其中：

OPTIONS選項：可選部分，目前OPTIONS中僅支援errors選項，用于指定忽略資料轉換出現錯誤的行數；此處errors 和< 參數選項>中的errors寫一個就行，如果都寫，以此處控制檔案中的為主。

：指明使用的資料檔案清單；

tablename：指明表名，建立外部表時，表名可以與此不同；

：指明同一行中各個列的分隔符；

1. 資料檔案中一行資料必須以回車結束；
2. 外部表支援查詢ROWID、USER和UID僞列，不支援查詢TRXID僞列。

舉例說明

例1 指定作業系統的一個文本檔案作為資料檔案，編寫控制檔案及建表語句。資料檔案（/home/dmdba/data.txt），資料如下：

a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15

控制檔案（/home/dmdba/ctrl.txt）如下：

load data

infile '/home/dmdba/data.txt'

into table ext

fields '|'

建表：

SQL> create external table ext (

2 l_char char(1),

3 l_character character(3),

4 l_varchar varchar(20),

5 l_numeric numeric(6,2),

6 l_decimal decimal(6,2),

7 l_dec dec(6,2),

8 l_money decimal(19,4),

9 l_bit bit,

10 l_bool bit,

11 l_boolean bit,

12 l_integer integer,

13 l_int int,

14 l_bigint bigint,

15 l_tinyint tinyint,

16 l_byte byte,

17 l_smallint smallint,

18 l_binary binary,

19 l_varbinary varbinary,

20 l_float float,

21 l_double double,

22 l_real real,

23 l_date date,

24 l_time time,

25 l_timestamp timestamp,

26 l_interval interval year

27 )from '/home/dmdba/ctrl.txt';

executed successfully

used time: 62.123(ms). Execute id is 312.

系統執行建表語句後，就在資料庫中建立了相應的外部基表。查詢ext表：

SQL> select * from ext;

LINEID L_CHAR L_CHARACTER L_VARCHAR L_NUMERIC L_DECIMAL L_DEC L_MONEY L_BIT L_BOOL L_BOOLEAN L_INTEGER L_INT L_BIGINT L_TINYINT L_BYTE L_SMALLINT L_BINARY L_VARBINARY L_FLOAT L_DOUBLE L_REAL L_DATE L_TIME L_TIMESTAMP L_INTERVAL

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

1 a abc varchar_data 12.34 12.34 12.34 12.3400 0 1 1 1234 1234 1234 100 11 1234 0x01 0x01 1.420000000000000E+01 1.210000000000000E+01 1.2100000E+01 1999-10-01 09:10:21 2002-12-12 00:00:00.000000 INTERVAL '15' YEAR(2)

used time: 7.248(ms). Execute id is 314.

例 2 指定作業系統的一個文本檔案作為資料檔案（/home/dmdba/data1.txt），資料如下：

10|9|7

4|3|2

建表:

SQL> create external table ext_2(c1 int,c2 int,c3 int) from datafile '/home/dmdba/data1.txt' parms(fields delimited by '|');

used time: 11.008(ms). Execute id is 331.

查詢結果：

SQL> select * from ext_2;

LINEID C1 C2 C3

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

1 10 9 7

2 4 3 2

used time: 8.222(ms). Execute id is 332.