Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to specify the external table of DM7". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to specify the external table of DM7.

DM7 external table

The following information is required:

1. Table name, schema name to which the table belongs

two。 Column definition

3. Controls the file path.

Grammatical format

CREATE EXTERNAL TABLE

< 表名定义>

< 表结构定义>

< 表名定义>

: = [

< 模式名>

.]

< 表名>

< 表结构定义>

:: (

< 列定义>

{

< 列定义>

})

< 列定义>

:: =

< 列名>

< 数据类型>

FROM clause = FROM clause 1 | FROM clause 2 | FROM clause 3 | FROM clause 4

FROM clause 1:: = FROM'

< 控制文件路径>

'

FROM clause 2:: = FROM DATAFILE'

< 数据文件路径>

'[

< 数据文件参数列表>

]

FROM clause 3:: = FROM

< 控制文件目录>

< 控制文件选项>

FROM clause 4:: = FROM DATAFILE

< 数据文件目录>

< 数据文件选项>

[

< 数据文件参数列表>

]

< 数据文件参数列表>

:: = PARMS (

< 参数选项>

{

< 参数选项>

})

< 参数选项>

: = [FIELDS DELIMITED BY

< 表达式>

] | [RECORDS DELIMITED BY

< 表达式>

] | [ERRORS] | [BADFILE'

< 数据文件路径>

'] | | [LOG

< 日志文件路径>

] | [NULL_STR] | [SKIP]

< 跳过行数>

] | [CHARACTER_CODE

< 文件字符集>

]

< 控制文件目录>

:: = DEFAULT DIRECTORY

< 控制文件目录名>

< 控制文件选项>

:: = LOCATION ('

< 控制文件名>

')

< 数据文件目录>

:: = DEFAULT DIRECTORY

< 数据文件目录名>

< 数据文件选项>

:: = LOCATION ('

< 数据文件名>

')

Parameters.

1.

< 模式名>

Indicates which mode the table belongs to, and defaults to the current mode

two。

< 表名>

Indicates the name of the external base table that was created

3.

< 列名>

Indicates the column name in the base table

4.

< 数据类型>

Indicates the data type of the column. Multimedia type is not supported.

5.

< 控制文件路径>

A string indicating the path to the control file used

6.

< 数据文件路径>

A string indicating the path to the data file used

7.

< 参数选项>

FIELDS indicates column delimiter; RECORDS represents row delimiter; ERRORS indicates ignoring the number of rows with errors in external table data conversion, and the value range is positive if the value is greater than 0.

Number. The default is 0, which means that the error is not ignored. Here, just write one of the ERRORS and the ERRORS in the control file. If both are written, it is mainly in the control file. LOG indicates the path of the log file.

The default log file name is fldr.log;NULL_STR, which specifies the representation string of null value in the data file, and this parameter is ignored by default; SKIP specifies the number of logical lines to skip the beginning of the data file, default

It is considered that the code specifies the encoding format of the data in the data file. The default is GBK, and the options are GBK,UTF-8,SINGLE_BYTE and EUC-KR.

8.

< 表达式>

String or hexadecimal string type expression, column delimiter only allows string type

9.

< 控制文件目录名>

Refers to the name of the database object directory.

Legend

Sentence function

External base tables are defined for DBA or users with CREATE_TABLE privileges. Creating external tables is not supported in the MPP environment.

instructions

1.

< 表名>

Specifies the external base table name to be established. If

< 模式名>

The default is the current mode. The table name needs to be a valid identifier. And meet the requirements of SQL syntax.

two。 The maximum length of the table name of an external table is 128 characters

3. The built external base table must contain at least one

< 列名>

The specified column, in an external base table, each

< 列名>

It can't be the same. An external base table can contain up to 2048 columns

4. Large field columns cannot exist in the external base table

5. There cannot be any constraints on the external base table

6. The external base table cannot be a temporary table and cannot establish a partition

7. No indexes can be created on the external base table

8. The external base table is read-only, there is no table lock, no data operations are allowed for external tables, and truncate external table operations are not allowed.

9. Absolute path is recommended for control file path and data file path

10. The format of the control file is:

[OPTIONS (errors=)] LOAD [DATA] INFILE [LIST] | INTO TABLE tablenameFIELDS

Where:

OPTIONS option: optional section. Currently, only the errors option is supported in OPTIONS, which is used to specify the number of rows that ignore data conversion errors; here errors and

< 参数选项>

Just write one of the errors in. If you write all of them, control the file here as the main one.

Indicates the list of data files used

Tablename: indicates the table name, which can be different when creating an external table

Indicates the delimiter of each column in the same line

11. One line of data in the data file must end with enter.

twelve。 External tables support querying ROWID, USER, and UID pseudo columns, but not TRXID pseudo columns.

Give examples to illustrate

Example 1 specifies a text file of the operating system as a data file, writes control files and table-building statements. Data file (/ home/dmdba/data.txt), the data is as follows:

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

The control file (/ home/dmdba/ctrl.txt) is as follows:

Load datainfile'/ home/dmdba/data.txt'into table extfields'|'

Build a table:

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 l_decimal decimal 2), 6 l_decimal decimal (6 l_decimal decimal 2), 7 l_dec dec (6 l_money decimal 2), 8 l_money decimal (19L4), 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 year27) from'/ home/dmdba/ctrl.txt' Executed successfullyused time: 62.123 (ms). Execute id is 312.

After the system executes the table-building statement, the corresponding external base table is established in the database. Query ext table:

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 an abc varchar_data 12.34 12.34 12.34 12.3400 01 1 1234 1234 1234 11 1234 0x01 0x01 1.420000000000000E+01 1.210000000000000E+01 1.2100000E+01 09:10:21 2002-12-12 00:00 00.000000 INTERVAL '15' YEAR (2) used time: 7.248 (ms). Execute id is 314.

Example 2 specifies a text file of the operating system as a data file (/ home/dmdba/data1.txt). The data is as follows:

10 | 9 | 74 | 3 | 2

Build a table:

SQL > create external table ext_2 (C1 int,c2 int,c3 int) from datafile'/ home/dmdba/data1.txt' parms (fields delimited by'|'); executed successfullyused time: 11.008 (ms). Execute id is 331.

Query results:

SQL > select * from ext_2;LINEID C1 C2 C3-1 109 72 43 2used time: 8.222 (ms). Execute id is 332. At this point, I believe that everyone on the "DM7 external table how to specify" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.