从DLI导入表数据到GaussDB(DWS)集群

准备工作

• 已注册华为账号并开通华为云，且在使用GaussDB(DWS) 前检查账号状态，账号不能处于欠费或冻结状态。
• 已创建虚拟私有云和子网，参见创建虚拟私有云和子网。
• 已获取华为账号的AK和SK，参见访问密钥。

步骤一：准备DLI源端数据

1. 创建DLI弹性资源池及队列。
   1. 登录华为云控制台，服务列表选择“大数据 > 数据湖探索DLI”，进入DLI管理控制台。
   2. 左侧导航栏选择“资源管理 > 弹性资源池”，进入弹性资源池管理页面。
   3. 单击右上角“购买弹性资源池”，填写如下参数，其他参数项如表中未说明，默认即可。

      表1 DLI弹性资源池

      参数项	参数值
      计费模式	按需计费
      区域	中国-香港
      名称	dli_dws
      规格	基础版
      网段	172.16.0.0/18。

   4. 单击“立即购买”，单击“提交”。

      等待资源池创建成功，继续执行下一步。

   5. 在弹性资源池页面，单击创建好的资源池所在行右侧的“添加队列”，填写如下参数，其他参数项如表中未说明，默认即可。

      表2 添加队列

      参数项	参数值
      名称	dli_dws
      类型	SQL队列

   6. 单击“下一步”，单击“确定”。队列创建成功。

2. 上传源数据到OBS桶。
   1. 已创建OBS桶，桶名自定义，例如dli-obs01（如果桶名已被占用，可设为dli-obs02，依次叠加），区域选择中国-香港。
   2. 下载数据样例文件。
   3. 在OBS桶中，新建文件夹dli_order，并将下载好的数据文件上传到dli_order目录下。

3. 回到DLI管理控制台，左侧导航单击“SQL编辑器”，队列选择“dli_dws”，数据库选择“deafult”，执行以下命令创建名为“dli_data”的数据库。

   1	CREATE DATABASE dli_data;

4. 创建表。
   

   以下LOCATION为数据文件实际存放的OBS目录，格式为obs://obs桶名/文件夹名称，本例为obs://dli-obs01/dli_order，如果桶名或文件夹名称有修改，请自行替换。

   1 2 3 4 5 6 7 8 9

   CREATE EXTERNAL TABLE dli_data.dli_order ( order_id VARCHAR(12), order_channel VARCHAR(32), order_time TIMESTAMP, cust_code VARCHAR(6), pay_amount DOUBLE, real_pay DOUBLE ) STORED AS parquet LOCATION 'obs://dli-obs01/dli_order';

5. 执行以下语句查询数据，结果显示查询成功。

   1	SELECT * FROM dli_data.dli_order;

步骤二：创建GaussDB(DWS)集群

1. 创建集群，同时为确保网络连通，本实践GaussDB(DWS)集群的区域，选择为“中国-香港”。

步骤三：获取GaussDB(DWS)外部服务器所需鉴权信息

1. 获取OBS桶的终端节点。
   1. 登录OBS管理控制台。
   2. 单击桶名称，左侧选择“概览”，并记录终端节点。

      

2. 访问终端节点获取DLI的终端节点。

   本例（中国-香港）为dli.ap-southeast-1.myhuaweicloud.com。

   本例（欧洲-都柏林）为dli.eu-west-101.myhuaweicloud.com。

3. 获取创建DLI所使用的账号的特定区域的项目ID。
   1. 鼠标悬浮在右上方的账户名，单击“我的凭证”。
   2. 左侧选择“API凭证”。
   3. 从列表中，找到DLI所属区域，本例为中国-香港，记录区域名所在的项目ID。

   

4. 获取账号的AK和SK，参见准备工作。

步骤四：通过外表导入DLI表数据

1. 使用系统管理员dbadmin用户登录GaussDB(DWS)数据库，默认登录gaussdb数据库即可。
2. 执行以下SQL创建外部Server。其中OBS终端节点从1获取，AK和SK从准备工作获取，DLI终端节点从2获取。
   

   如果DWS和DLI是同一个账户创建下，则AK和SK分别对应重复填写一次。

   1 2 3 4 5 6 7 8 9

   CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'OBS终端节点', ACCESS_KEY 'AK值', SECRET_ACCESS_KEY 'SK值', TYPE 'DLI', DLI_ADDRESS 'DLI终端节点', DLI_ACCESS_KEY 'AK值', DLI_SECRET_ACCESS_KEY 'SK值' );

   

3. 执行以下SQL创建目标schema。

   1	CREATE SCHEMA dws_data;

4. 执行以下SQL创建外表。其中项目ID替换为3获取的实际值。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   CREATE FOREIGN TABLE dws_data.dli_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 dli_server OPTIONS ( FORMAT 'parquet', ENCODING 'utf8', DLI_PROJECT_ID '项目ID', DLI_DATABASE_NAME 'dli_data', DLI_TABLE_NAME 'dli_order') DISTRIBUTE BY roundrobin;

   

5. 执行以下SQL，通过外表查询DLI的表数据。
   结果显示，成功访问DLI表数据。

   1	SELECT * FROM dws_data.dli_pq_order;

   

6. 执行以下SQL，创建一张新的本地表，用于导入DLI表数据。

   1 2 3 4 5 6

   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 );

7. 执行以下SQL，查询出2023年的月度订单明细，并将结果导入DWS表。

   1 2 3 4 5 6 7 8

   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.dli_pq_order WHERE DATE_PART('Year', order_time) = 2023 GROUP BY TO_CHAR(order_time, 'MON-YYYY'), cust_code;

8. 执行以下SQL查询表数据。

   结果显示，DLI表数据成功导入DWS数据库。

   1	SELECT * FROM dws_data.dws_monthly_order;