Examples - Read Fixed-Width Data

The following examples show how to read fixed-width data.

Example 1 – Loading a table with PRESERVED_BLANKS on

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

Example 2 – Loading data with no line delimiter

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

Example 3 – Create a writable external table with a \r\n line delimiter

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