例子:读取宽度固定的数据

例子:读取宽度固定的数据

下面的例子展示了如何读取宽度固定的数据。

例 1 – 装载一个所有域都定义好的表

CREATE READABLE EXTERNAL TABLE students (
name varchar(20), address varchar(30), age int)
LOCATION ('file://<host>/file/path/')
FORMAT 'CUSTOM' (formatter=fixedwidth_in, 
         name=20, address=30, age=4);

例 2 – 装载一个打开了PRESERVED_BLANKS的表

CREATE READABLE EXTERNAL TABLE students (
name varchar(20), address varchar(30), age int)
LOCATION ('gpfdist://<host>:<portNum>/file/path/')
FORMAT 'CUSTOM' (formatter=fixedwidth_in, 
         name=20, address=30, age=4,
        preserve_blanks='on',null='NULL');

例 3 – 装载没有行定界符的数据

CREATE READABLE EXTERNAL TABLE students (
name varchar(20), address varchar(30), age int)
LOCATION ('file://<host>/file/path/')
FORMAT 'CUSTOM' (formatter=fixedwidth_in, 
         name='20', address='30', age='4', line_delim='?@')

例 4 – 用\r\n行定界符创建一个可写的外部表

CREATE WRITABLE EXTERNAL TABLE students_out (
name varchar(20), address varchar(30), age int)
LOCATION ('gpfdist://<host>:<portNum>/file/path/students_out.txt')     
FORMAT 'CUSTOM' (formatter=fixedwidth_out, 
        name=20, address=30, age=4, line_delim=E'\r\n');