SelectObject用于对目标文件执行SQL语句,返回执行结果。

背景介绍

目前Hadoop 3.0已经支持OSS在EMR上运行Spark、Hive、Presto等服务,以及阿里自研的MaxCompute、HybridDB以及新上线的Data Lake Analytics都支持从OSS直接处理数据。

OSS提供的GetObject接口决定了大数据平台只能把OSS数据全部下载到本地然后进行分析过滤,在很多查询场景下浪费了大量带宽和客户端资源。

SelectObject接口是对上述问题的解决方案。其核心思想是大数据平台将条件、Projection下推到OSS层,让OSS做基本的过滤,从而只返回有用的数据。客户端一方面可以减少网络带宽,另一方面也减少了数据的处理量,从而节省了CPU和内存用来做其他更多的事情。这使得基于OSS的数据仓库、数据分析成为一种更有吸引力的选择。

细节分析

以下内容是对SelectObject支持的文件类型、支持的SQL语法等的详细介绍。

  • SelectObject支持的文件类型
    说明 SelectObject和Normal类型文件配合性能更佳。Multipart以及Appendable类型的文件由于其内部结构差异导致性能较差。
    • RFC 4180标准的CSV(包括TSV等类CSV文件,文件的行列分隔符以及Quote字符都可自定义)。
    • JSON文件,且文件编码为UTF-8。JSON支持DOCUMENT和LINES两种文件。
      • DOCUMENT是指整个文件是单一的JSON对象。
      • LINES表示整个文件由一行行的JSON对象组成,每一行是一个JSON对象(但整个文件本身并不是一个合法的JSON对象),行与行之间以换行分隔符隔开。OSS Select可以支持常见的\n,\r\n等分隔符,且无需用户指定。
    • 标准存储类型和低频访问存储类型的文件。归档类型文件需要先执行解冻操作。
    • OSS完全托管加密、KMS托管主密钥加密的文件。
  • 支持的SQL语法
    • SQL 语句: Select From Where
    • 数据类型:string、int(64bit)、double(64bit), decimal(128) 、timestamp、bool
    • 操作: 逻辑条件(AND,OR,NOT), 算术表达式(+-*/%), 比较操作(>,=, <, >=, <=, !=),String 操作 (LIKE, || )
  • 分片查询

    和GetObject提供的基于Byte的分片下载类似,SelectObject也提供了分片查询的机制,包括以下两种分片方式:

    • 按行分片:常用的分片方式,然而对于稀疏数据来说,按行分片可能会导致分片时负载不均衡。
    • 按Split分片:Split是OSS用于分片的一个概念,一个Split包含多行数据,每个Split的数据大小大致相等。
    说明 按Spit分片比按行分片更加高效。
  • 数据类型

    OSS中的CSV数据默认都是String类型,用户可以使用CAST函数实现数据转换,比如下面的SQL查询将_1和_2转换为int后进行比较。

    Select * from OSSOBject where cast (_1 as int) > cast(_2 as int)

    同时,对于SelectObject支持在Where条件中进行隐式的转换,比如下面的语句中第一列和第二列将被转换成int:

    Select _1 from ossobject where _1 + _2 > 100

    对于JSON文件,如果在SQL中未指定cast函数,则其类型根据JSON数据的实际类型而定,标准JSON内建的数据类型包括null、bool、int64、double、string等类型。

常见的SQL用例

常见的SQL用例包括CSV及JSON两种。

  • 常见的SQL用例(CSV)
    应用场景 SQL语句
    返回前10行数据 select * from ossobject limit 10
    返回第1列和第3列的整数,并且第1列大于第3列 select _1, _3 from ossobject where cast(_1 as int) > cast(_3 as int)
    返回第1列以'陈'开头的记录的个数(注:此处like后的中文需要用UTF-8编码) select count(*) from ossobject where _1 like '陈%'
    返回所有第2列时间大于2018-08-09 11:30:25且第3列大于200的记录 select * from ossobject where _2 > cast('2018-08-09 11:30:25' as timestamp) and _3 > 200
    返回第2列浮点数的平均值,总和,最大值,最小值

    select AVG(cast(_2 as double)), SUM(cast(_2 as double)), MAX(cast(_2 as double)), MIN(cast(_2 as double))

    返回第1列和第3列连接的字符串中以'Tom'为开头以’Anderson‘结尾的所有记录 select * from ossobject where (_1 || _3) like 'Tom%Anderson'
    返回第1列能被3整除的所有记录 select * from ossobject where (_1 % 3) == 0
    返回第1列大小在1995到2012之间的所有记录 select * from ossobject where _1 between 1995 and 2012
    返回第5列值为N,M,G,L的所有记录 select * from ossobject where _5 in ('N', 'M', 'G', 'L')
    返回第2列乘以第3列比第5列大100以上的所有记录 select * from ossobject where _2 * _3 > _5 + 100
  • 常见的SQL用例(JSON)

    假设JSON文件如下:

    {
      "contacts":[
    {
      "firstName": "John",
      "lastName": "Smith",
      "isAlive": true,
      "age": 27,
      "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021-3100"
      },
      "phoneNumbers": [
        {
          "type": "home",
          "number": "212 555-1234"
        },
        {
          "type": "office",
          "number": "646 555-4567"
        },
        {
          "type": "mobile",
          "number": "123 456-7890"
        }
      ],
      "children": [],
      "spouse": null
    },…… #此处省略其他类似的节点
    ]}

    SQL用例如下:

    应用场景 SQL语句
    返回所有age是27的记录

    select * from ossobject.contacts[*] s where s.age = 27

    返回所有的家庭电话

    select s.number from ossobject.contacts[*].phoneNumbers[*] s where s.type = “home”

    返回所有单身的记录

    select * from ossobject s where s.spouse is null

    返回所有没有孩子的记录 select * from ossobject s where s.children[0] is null
    说明 目前没有专用的空数组的表示方法,用以上语句代替。

使用场景

SelectObject通常用于大文件分片查询、JSON文件查询、日志文件分析等场景。

  • 大文件分片查询

    如果确定该CSV文件列中不含有换行符,则基于Bytes的分片由于不需要创建Meta,其使用最为简便。如果列中包含换行符或者是JSON文件时,则使用以下步骤:

    1. 调用CreateSelectObjectMeta API获得该文件的总的Split数。理想情况下如果该文件需要用SelectObject,则该API最好在查询前进行异步调用,这样可以节省扫描时间。
    2. 根据客户端资源情况选择合适的并发度n,用总的Split数除以并发度n得到每个分片查询应该包含的Split个数。
    3. 在请求Body中用诸如split-range=1-20的形式进行分片查询。
    4. 合并结果。
  • 查询JSON文件时,在SQL的From 语句中尽可能的缩小From后的JSON Path 范围。

    如下是JSON文件示例:

    { contacts:[
            {“firstName”:”John”, “lastName”:”Smith”, “phoneNumbers”:[{“type”:”home”, “number”:”212-555-1234”}, {“type”:”office”, “number”:”646-555-4567”}, {“type”:”mobile”, “number”:”123 456-7890”}], ”address”:{“streetAddress”: “21 2nd Street”, “city”:”New York”, “state”:NY, “postalCode”:”10021-3100”}
             }
    ]}

    如果要查找所有postalCode为 10021开头的streetAddress,SQL可以写为 select s.address.streetAddress from ossobject.contacts[*] s where s.address.postalCode like ‘10021%’或者select s.streetAddress from ossobject.contacts[*].address s where s.postalCode like ‘10021%’

    由于select s.streetAddress from ossobject.contacts[*].address s where s.postalCode like ‘10021%’的JSON Path更加精确,因此性能更优。

  • 在JSON文件中处理高精度浮点数

    在JSON文件中需要进行高精度浮点数的数值计算时,建议设置ParseJsonNumberAsString选项为true, 同时将该值cast成Decimal。比如一个属性a值为123456789.123456789,用select s.a from ossobject s where cast(s.a as decimal) > 123456789.12345就可以保持原始数据的精度不丢失。

API和SDK