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

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

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

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

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

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

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

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

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

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

Parent topic: 使用一种自定义格式