# 导入OBS桶数据到DWS集群及从DWS导出数据到OBS桶 本实践演示将样例数据上传OBS，并通过OBS外表访问OBS桶内的数据，根据需要，您也可以将OBS的数据导入DWS的目标表中，同时也可以将DWS的某张表数据导出到OBS桶。 - **导入OBS支持的数据格式** ：**TXT、CSV、ORC、PARQUET、CARBONDATA、JSON** **。** - **导出OBS支持的数据格式** ： - **ORC、CARBONDATA、PARQUET** ：此种格式需要单独创建[外部服务器（Foreign Server）]，本例描述的步骤属于此场景。 - **TXT、CSV** ：此种方式不需要单独建外部服务器，系统默认自带gsmpp_server服务器。导出此格式的方法可参见[导出CSV、TXT数据到OBS](https://support.huaweicloud.com/migration-dws/dws_15_0067.html)。 **对象存储服务** （Object Storage Service，简称OBS）是一种基于对象的存储服务，为客户提供海量、安全、可靠、低成本的数据存储能力。OBS可以存储任何类型的文件，适合普通用户、网站、企业和开发者。用户可以通过OBS Console或OBS Browser+在任何一台连接到Internet的计算机上访问和管理存储在OBS上的数据。详情请参见[OBS官网文档](https://support.huaweicloud.com/obs/index.html)。 本实践预计时长：1小时，基本流程如下： - [准备工作]，创建好DWS集群和OBS桶、获取AK、SK。 - [步骤一：准备OBS数据]，将预置的样例数据上传到OBS桶。 - [步骤二：创建OBS的外部服务器和外表]，为导入OBS桶数据做准备。 - [步骤三：访问和导入OBS桶数据到DWS集群]，通过OBS外表实现数据导入。 - [步骤四：将DWS某张表的数据导出到OBS桶]，并验证只写外表不支持更新、删除数据等约束。 #### 准备工作 - 已创建数据仓库服务DWS，参见[创建集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0221.html)。 - 已创建OBS桶，**注意与DWS在同一个区域下** ，例如桶名为obs-demo01（如果提示桶名已被占用，则命名为obs-demo02，以此类推），参见[OBS文档](https://support.huaweicloud.com/obs/index.html)。 - 已获取账户的AK和SK，用于访问OBS桶数据，参见[访问密钥](https://support.huaweicloud.com/usermanual-ca/ca_01_0003.html)。 #### 步骤一：准备OBS数据 1. 下载[数据样例文件](https://dws-lab01.obs.cn-north-4.myhuaweicloud.com/dli_data/order.parquet)。 2. [登录OBS控制台](https://console.huaweicloud.com/obs)，单击桶列表中已创建好的obs-demo01桶名称。 3. 左侧选择"对象"，单击"新建文件夹"，命名为obs-dws。 4. 进入到obs-dws的文件夹下，单击"上传对象"，将[1]下载的样例文件上传到obs-dws文件夹下。 5. 获取OBS的Endpoint。 1. 回到obs-dws桶名的主页面，左侧选择"概览"。 2. 在"域名信息"中记录Endpoint，例如obs.cn-north-4.myhuaweicloud.com。 #### 步骤二：创建OBS的外部服务器和外表 **外部服务器**（Foreign server）是数据仓库/数据库系统中用于定义和管理对外部数据源（其他数据库、文件系统等）的逻辑连接对象。它的主要作用是支持异构分布式数据的联邦访问，是实现数据整合、实时分析和数据虚拟化的重要工具。 创建OBS外部Server，通过在Server中指定OBS的Endpoint，AK、SK，才可以基于外部Server访问OBS桶数据。 1. 连接数据库后，执行以下SQL语句创建外部服务器。 其中ADDRESS填写[5]获取地址，ACCESS_KEY、 SECRET_ACCESS_KEY分别填写[准备工作]中获取账户的AK和SK。 ``` CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.aaaaa.bbbbb.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'OBS' ); ``` 2. 执行以下SQL，创建名为dws_data的Schema。 ``` CREATE SCHEMA dws_data; ``` 3. 切换到新创建的Schema下，创建外表。 **注意**，foldername '/obs-demo01/obs-dws/'请替换为数据文件实际存放的OBS路径，且确保OBS桶与DWS集群在同一个区域下，本例为obs-demo01桶名下的obs-dws文件夹。 SERVER obs_server为[1]创建的外部服务器实际的名称，本例为obs_server。 ``` CREATE FOREIGN TABLE dws_data.obs_pq_order ( order_id VARCHAR(14) PRIMARY KEY NOT ENFORCED, order_channel VARCHAR(32), order_time TIMESTAMP, cust_code VARCHAR(6), pay_amount DOUBLE PRECISION, real_pay DOUBLE PRECISION ) SERVER obs_server OPTIONS ( foldername '/obs-demo01/obs-dws/', format 'parquet', encoding 'utf8' ) READ ONLY DISTRIBUTE BY roundrobin; ``` #### 步骤三：访问和导入OBS桶数据到DWS集群 1. 可通过以上步骤创建好的外表直接访问OBS桶数据。 ``` SELECT * FROM dws_data.obs_pq_order; ``` 2. 也可以通过外表执行带条件的SELECT语句来访问。 ``` SELECT COUNT(*) FROM dws_data.obs_pq_order; SELECT order_id, order_channel, order_time, cust_code FROM dws_data.obs_pq_order; SELECT TO_CHAR(order_time, 'Month, YYYY') AS order_month, cust_code, COUNT(*) AS order_cnt FROM dws_data.obs_pq_order WHERE DATE_PART('Year', order_time) = 2023 GROUP BY TO_CHAR(order_time, 'Month, YYYY'), cust_code HAVING COUNT(*) >= 10; ``` 3. 创建一张本地表，以通过OBS外表将数据导入DWS集群。 ``` CREATE TABLE dws_data.dws_monthly_order ( order_month CHAR(8), cust_code VARCHAR(6), order_count INT, total_pay_amount DOUBLE PRECISION, total_real_pay DOUBLE PRECISION ); ``` 4. 您可以通过OBS外表数据统计出2023年的每月订单明细，然后将结果导入到DWS集群的本地表中。 ``` INSERT INTO dws_data.dws_monthly_order ( order_month, cust_code, order_count , total_pay_amount, total_real_pay ) SELECT TO_CHAR(order_time, 'MON-YYYY'), cust_code, COUNT(*) , SUM(pay_amount), SUM(real_pay) FROM dws_data.obs_pq_order WHERE DATE_PART('Year', order_time) = 2023 GROUP BY TO_CHAR(order_time, 'MON-YYYY'), cust_code; ``` 5. 最后查看表数据导入情况。 ``` SELECT * FROM dws_data.dws_monthly_order; ``` #### 步骤四：将DWS某张表的数据导出到OBS桶 1. 创建一张新的本地表。 ``` CREATE TABLE dws_data.dws_order ( order_id VARCHAR(14) PRIMARY KEY, order_channel VARCHAR(32), order_time TIMESTAMP, cust_code VARCHAR(6), pay_amount DOUBLE PRECISION, real_pay DOUBLE PRECISION ); ``` 2. 插入三条数据。 ``` INSERT INTO dws_data.dws_order VALUES ('20230627000001', 'webShop', TIMESTAMP '2023-06-27 10:00:00', 'CUST1', 1000, 1000) , ('20230627000002', 'webShop', TIMESTAMP '2023-06-27 11:00:00', 'CUST2', 5000, 5000) , ('20240309000003', 'webShop', TIMESTAMP '2024-03-09 13:00:00', 'CUST1', 2000, 2000); ``` 3. 创建一张外表，用于导出数据到OBS桶。 其中foldername '/obs-demo01/obs-dws/'请替换为实际需要导出的OBS桶路径。 ``` CREATE FOREIGN TABLE dws_data.obs_orc_order ( order_id VARCHAR(14) PRIMARY KEY NOT ENFORCED, order_channel VARCHAR(32), order_time TIMESTAMP, cust_code VARCHAR(6), pay_amount DOUBLE PRECISION, real_pay DOUBLE PRECISION ) SERVER obs_server OPTIONS ( foldername '/obs-demo01/obs-dws/', format 'ORC', encoding 'utf8' ) READ WRITE DISTRIBUTE BY roundrobin; ``` 4. 执行以下语句，将本地表数据写入OBS外表。 ``` INSERT INTO dws_data.obs_orc_order ( order_id, order_channel, order_time, cust_code, pay_amount, real_pay ) SELECT order_id, order_channel, order_time, cust_code, pay_amount, real_pay FROM dws_data.dws_order; ``` 5. 查询外表，数据写入成功。 ``` SELECT * FROM dws_data.obs_orc_order; ``` 6. 成功写入后，您可以[登录OBS控制台](https://console.huaweicloud.com/obs)，查看OBS桶对应路径下是否有数据文件存在。 7. 同时可以验证，是否允许同一张外表下额外插入数据。 ``` INSERT INTO dws_data.obs_orc_order ( order_id, order_channel, order_time, cust_code, pay_amount, real_pay ) SELECT order_id, order_channel, order_time, cust_code, pay_amount, real_pay FROM dws_data.dws_order; ``` 结果会报错："the file path specified in the foreign table is not empty"，但如果删除外表中指定的OBS文件路径下的数据文件后，可以重新插入数据。 8. 执行以下语句，验证是否允许更新和删除外表数据。 ``` UPDATE dws_data.obs_orc_order SET pay_amount = 3000, real_pay = 3000 WHERE order_id = '20240309000003'; DELETE FROM dws_data.obs_orc_order WHERE order_id = '20240309000003'; ``` 由查询结果可知，不支持更新和删除外表数据。