文档

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

更新时间:

AnalyticDB MySQL湖仓版(3.0)支持通过XIHE SQL和Spark SQL访问OSS数据。本文以基金交易数据处理为例,介绍通过XIHE SQL和Spark SQL访问OSS数据的具体步骤。

前提条件

操作步骤

  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控制台,在左上角选择集群所在地域。在左侧导航栏,单击集群列表,在湖仓版(3.0)页签,单击目标集群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控制台,在左上角选择集群所在地域。在左侧导航栏,单击集群列表,在湖仓版(3.0)页签,单击目标集群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;

相关文档

通过外表导入至湖仓版

  • 本页导读 (1)
文档反馈