AI 助理
备案控制台
文档
输入文档关键字查找
首页 对象存储 实践教程 数据湖 阿里云生态 通过XIHE SQL或者Spark SQL访问OSS数据

通过XIHE SQL或者Spark SQL访问OSS数据

更新时间：
产品详情
相关技术圈
我的收藏

AnalyticDB for MySQL企业版、基础版及湖仓版支持通过XIHE SQL和Spark SQL访问OSS数据。本文以基金交易数据处理为例，介绍通过XIHE SQL和Spark SQL访问OSS数据的具体步骤。

前提条件

  • 已开通OSS服务并创建存储空间和目录。具体操作，请参见开通OSS服务、创建存储空间和创建目录。

  • 集群的产品系列为企业版、基础版或湖仓版。

  • 已在AnalyticDB for MySQL集群中创建Job型资源组。具体操作，请参见新建资源组。

  • 已创建AnalyticDB for MySQL集群的数据库账号。

  • 已授权AnalyticDB for MySQL扮演AliyunADBSparkProcessingDataRole角色来访问其他云资源。具体操作，请参见账号授权。

操作步骤

  1. 准备数据。

    1. 下载模拟数据并解压。

    2. 将模拟数据中的交易信息表和用户数据表分别上传至OSS中的<bucketname>/adb/trade/和<bucketname>/adb/user/目录。具体操作，请参见上传文件。

      说明

      • 模拟数据中，交易信息表的表名以dc_trade_final_dd开头，用户数据表的表名为user_info。

      • 您需将<bucketname>替换为实际的Bucket名称。

  2. 访问数据。

    通过XIHE SQL访问OSS数据

    1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在企业版、基础版或湖仓版页签下，单击目标集群ID。

    2. 在左侧导航栏，单击作业开发>SQL开发。

    3. 在SQLConsole窗口，选择Job型资源组和XIHE引擎。

    4. 创建数据库。如果有已创建的数据库，可以忽略本步骤。示例如下：

      CREATE DATABASE adb_demo;

    5. 创建OSS外表。

      创建交易信息表

      CREATE EXTERNAL TABLE tradelist_csv (
    t_userid STRING COMMENT '用户ID',
    t_dealdate STRING COMMENT '申请时间', 
    t_businflag STRING COMMENT '业务代码', 
    t_cdate STRING COMMENT '确认日期', 
    t_date STRING COMMENT '申请日期',
    t_serialno STRING COMMENT'申请序号', 
    t_agencyno STRING COMMENT'销售商编号', 
    t_netno STRING  COMMENT'网点编号',
    t_fundacco STRING COMMENT'基金账号',
    t_tradeacco STRING COMMENT'交易账号',
    t_fundcode STRING  COMMENT'基金代码',
    t_sharetype STRING COMMENT'份额类别',
    t_confirmbalance DOUBLE  COMMENT'确认金额',
    t_tradefare DOUBLE COMMENT'交易费',
    t_backfare DOUBLE COMMENT'后收手续费',
    t_otherfare1 DOUBLE COMMENT'其他费用1',
    t_remark STRING COMMENT'备注'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/trade/';

      语法说明，请参见OSS外表。其中，LOCATION为交易信息表所在的OSS路径。

      创建用户信息表

      CREATE EXTERNAL TABLE userinfo (
    u_userid STRING COMMENT '用户ID',
    u_accountdate STRING COMMENT '开户时间', 
    u_gender STRING COMMENT '性别', 
    u_age INT COMMENT '年龄', 
    u_risk_tolerance INT COMMENT '风险承受能力，1-10，10为最高级',
    u_city STRING COMMENT'所在城市', 
    u_job STRING COMMENT'工作类别， A-K', 
    u_income DOUBLE  COMMENT'年收入（万）'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/user/';

      语法说明，请参见OSS外表。其中，LOCATION为用户信息表所在的OSS路径。

    6. 查询外表数据。

      您可以选择通过同步执行或异步执行查询数据，同步执行的结果会直接显示，异步执行会返回job_id，您可以通过job_id查询异步任务状态，判断任务是否执行成功。

      同步查询

      • 示例一：查询交易机构SXS_0010，在0603至0604的100条交易记录。

        SELECT * FROM tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
limit 100;

        返回结果：

        +-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| t_userid  |     t_dealdate      | t_businflag   |   t_cdate    |   t_date     |  t_serialno       |  t_agencyno  |  t_netno   |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00041972  | 2018-06-03 09:11:57 |  保本基金B    |  2018-06-03  |  2018-06-03  | 2018-06-03-000846 |  SXS_0010S   | STORE-0135 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00051912  | 2018-06-03 09:28:20 |  保本基金D    |  2018-06-03  |  2018-06-03  | 2018-06-03-001046 |  SXS_0010S   | STORE-0397 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00120370  | 2018-06-03 11:21:39 |  保本基金B    |  2018-06-03  |  2018-06-03  | 2018-06-03-002393 |  SXS_0010S   | STORE-0800 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
| 00165308  | 2018-06-03 12:35:35 |  保本基金D    |  2018-06-03  |  2018-06-03  | 2018-06-03-003281 |  SXS_0010S   | STORE-0340 |
+-----------+---------------------+---------------+--------------+--------------+-------------------+--------------+------------+
        说明

        本示例为部分返回结果，实际结果请以控制台为准。

      • 示例二：查询各城市、男性女性人群，购买的基金总额（多表Join查询）。

        SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM tradelist_csv , userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

        返回结果：

        +-----------+------------+----------------+
| u_city    | u_gender   | sum_balance    |
+-----------+------------+----------------+
| Beijing   |  male      | 2445539161     |
+-----------+------------+----------------+
| Guangzhou |  male      | 1271999857     |
+-----------+------------+----------------+
| Qingdao   |  male      | 1266748660     |
+-----------+------------+----------------+
| Wuhan     |  male      | 12641688475    |
+-----------+------------+----------------+
        说明

        本示例为部分返回结果，实际结果请以控制台为准。

      异步查询

      查询各城市、男性女性人群，购买的基金总额（多表Join查询）。

      SUBMIT JOB SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM tradelist_csv , userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

      返回结果：

      +---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2023091410365702101701713803151****** |
+---------------------------------------+

      使用SUBMIT JOB提交异步任务后，您可以通过job_id终止异步任务或查询异步任务状态，具体操作，请参见异步提交任务。

    通过Spark SQL访问OSS数据

    1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，在企业版、基础版或湖仓版页签下，单击目标集群ID。

    2. 在左侧导航栏，单击作业开发>SQL开发。

    3. 在SQLConsole窗口，选择Job型资源组和Spark引擎。

    4. 创建数据库。如果有已创建的数据库，可以忽略本步骤。示例如下：

      CREATE DATABASE adb_test;

    5. 创建OSS外表。

      创建交易信息表

      CREATE EXTERNAL TABLE adb_test.tradelist_csv (
    t_userid STRING COMMENT '用户ID',
    t_dealdate STRING COMMENT '申请时间', 
    t_businflag STRING COMMENT '业务代码', 
    t_cdate STRING COMMENT '确认日期', 
    t_date STRING COMMENT '申请日期',
    t_serialno STRING COMMENT'申请序号', 
    t_agencyno STRING COMMENT'销售商编号', 
    t_netno STRING  COMMENT'网点编号',
    t_fundacco STRING COMMENT'基金账号',
    t_tradeacco STRING COMMENT'交易账号',
    t_fundcode STRING  COMMENT'基金代码',
    t_sharetype STRING COMMENT'份额类别',
    t_confirmbalance DOUBLE  COMMENT'确认金额',
    t_tradefare DOUBLE COMMENT'交易费',
    t_backfare DOUBLE COMMENT'后收手续费',
    t_otherfare1 DOUBLE COMMENT'其他费用1',
    t_remark STRING COMMENT'备注'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/trade/';

      语法说明，请参见OSS外表。其中，LOCATION为交易信息表所在的OSS路径。

      创建用户信息表

      CREATE EXTERNAL TABLE adb_test.userinfo (
    u_userid STRING COMMENT '用户ID',
    u_accountdate STRING COMMENT '开户时间', 
    u_gender STRING COMMENT '性别', 
    u_age INT COMMENT '年龄', 
    u_risk_tolerance INT COMMENT '风险承受能力，1-10，10为最高级',
    u_city STRING COMMENT'所在城市', 
    u_job STRING COMMENT'工作类别， A-K', 
    u_income DOUBLE  COMMENT'年收入（万）'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
    STORED AS TEXTFIlE
    LOCATION 'oss://<bucketname>/adb/user/';

      语法说明，请参见OSS外表。其中，LOCATION为用户信息表所在的OSS路径。

    6. 查询数据。

      说明

      执行Spark SQL语句，只返回执行成功或者失败，不返回查询结果。您可以在Spark Jar开发页面应用列表页签中的日志查看查询结果。详情请参见查看Spark应用信息。

      • 示例一：查询交易机构SXS_0010，在0603至0604的100条交易记录。

        SELECT * FROM adb_test.tradelist_csv 
WHERE t_cdate >= '2018-06-03' and t_cdate <= '2018-06-04' and t_agencyno = 'SXS_0010' 
LIMIT 100;

      • 示例二：查询各城市、男性女性人群，购买的基金总额（多表Join查询）。

        SELECT u_city, u_gender, SUM(t_confirmbalance) AS sum_balance 
FROM adb_test.tradelist_csv , adb_test.userinfo  
WHERE u_userid = t_userid 
GROUP BY u_city, u_gender 
ORDER BY sum_balance DESC;

相关文档

通过外表导入至湖仓版

上一篇：阿里云生态下一篇：在EMR Hive或Spark中访问OSS-HDFS
文档推荐