外部表的应用:
1.创建可读外部表例子:
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(10),
work_area varchar(10),
entry_date date,
work_position varchar(10),
salary_amount decimal(18,2),
phone_number varchar(11),
e_mail varchar(50)
)
LOCATION ('gpfdist://192.168.245.128:8099/data1/gpfdist/employee.txt')
FORMAT 'TEXT'(DELIMITER '|+|')
ENCODING 'UTF-8'
LOG ERRORS INTO ERROR_EMPLOYEE SEGMENT REJECT LIMIT 1000 rows
;^C
[gpadmin@mdw gpfdist]$ psql -d gpload_testdb -f employee.txt
psql:employee.txt:16: NOTICE: Error table "error_employee" does not exist. Auto generating an error table with the same name
^C
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE TEST.EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(32),
work_area varchar(32),
1.创建可读外部表例子:
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(10),
work_area varchar(10),
entry_date date,
work_position varchar(10),
salary_amount decimal(18,2),
phone_number varchar(11),
e_mail varchar(50)
)
LOCATION ('gpfdist://192.168.245.128:8099/data1/gpfdist/employee.txt')
FORMAT 'TEXT'(DELIMITER '|+|')
ENCODING 'UTF-8'
LOG ERRORS INTO ERROR_EMPLOYEE SEGMENT REJECT LIMIT 1000 rows
;^C
[gpadmin@mdw gpfdist]$ psql -d gpload_testdb -f employee.txt
psql:employee.txt:16: NOTICE: Error table "error_employee" does not exist. Auto generating an error table with the same name
psql:employee.txt:16: ERROR: delimiter must be a single ASCII character, or 'off'
这部分的错误是由于分割符的问题,应该是不支持‘|+|’这种分割方式,下面换成|竖线分割
[gpadmin@mdw gpfdist]$ cat>employee.txt^C
[gpadmin@mdw gpfdist]$ cat>employee.txt
CREATE READABLE EXTERNAL TABLE TEST.EX_EMPLOYEE
(
employee_id varchar(10),
employee_name varchar(100),
entry_area varchar(32),
work_area varchar(32),