云数据库 GaussDB-CREATE FOREIGN TABLE (导入导出):示例

时间：2023-11-01 16:22:51

云数据库 GaussDB

示例

  
       --建立外表，用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据，导入过程错误信息将记录到err_HR_staffS中。openGauss=# CREATE FOREIGN TABLE foreign_HR_staffS(  staff_ID       NUMBER(6) ,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID  VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4)) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x20',  null '') WITH err_HR_staffS;

  
       --建立外表，用来以TEXT格式导入GDS服务器192.168.0.90和192.168.0.91上的数据，导入过程错误信息将记录到err_HR_staffS中。本次数据导入允许出现的数据格式错误个数为2。CREATE FOREIGN TABLE foreign_HR_staffS_ft3(  staff_ID       NUMBER(6) ,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID  VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4)) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x20',  null '',reject_limit '2') WITH err_HR_staffS_ft3;

  
       --建立外表，用来以 CS V格式导入input_data目录下存放在各个节点名文件下的所有文件。openGauss=# CREATE FOREIGN TABLE foreign_HR_staffS_ft1(  staff_ID       NUMBER(6) ,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID  VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID     NUMBER(4)) SERVER gsmpp_server OPTIONS (location 'file:///input_data/*', format 'csv', mode 'private', delimiter ',') WITH err_HR_staffS_ft1;

  
       --建立外表，用来以CSV格式导出数据到output_data目录下。openGauss=# CREATE FOREIGN TABLE foreign_HR_staffS_ft2(  staff_ID       NUMBER(6) ,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID  VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4)) SERVER gsmpp_server OPTIONS (location 'file:///output_data/', format 'csv', delimiter '|', header 'on') WRITE ONLY;

  
       --删除外表。openGauss=# DROP FOREIGN TABLE foreign_HR_staffS;openGauss=# DROP FOREIGN TABLE foreign_HR_staffS_ft1;openGauss=# DROP FOREIGN TABLE foreign_HR_staffS_ft2;openGauss=# DROP FOREIGN TABLE foreign_HR_staffS_ft3;