数据仓库服务 GAUSSDB(DWS)-CREATE FOREIGN TABLE (OBS导入导出):示例

时间：2024-05-22 15:13:27

示例

创建外表OBS_ft，用来以txt格式导入OBS上指定的对象数据到row_tbl表中：

认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。

   
        DROP FOREIGN TABLE IF EXISTS OBS_ft;
NOTICE:  foreign table "obs_ft" does not exist, skipping
DROP FOREIGN TABLE

CREATE FOREIGN TABLE OBS_ft( a int, b int)SERVER gsmpp_server OPTIONS (location 'obs://gaussdbcheck/obs_ddl/test_case_data/txt_obs_informatonal_test001',format 'text',encoding 'utf8',chunksize '32', encrypt 'on',ACCESS_KEY 'access_key_value_to_be_replaced',SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced',delimiter E'\x08') read only;
CREATE FOREIGN TABLE

DROP TABLE row_tbl;
DROP TABLE

CREATE TABLE row_tbl( a int, b int);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

INSERT INTO row_tbl select * from OBS_ft;
INSERT 0 3