OSS Select使用场景及技巧

背景介绍

OSS Select是OSS近期推出的一项新功能，它可以对OSS上的类CSV文件（其他类型文件比如Json也会很快推出）在服务器端运行SQL查询，仅将查询结果返回给客户端。举例来说，假如你有一个10GB的csv日志文件，有一列数据是错误码，想把其中所有错误码等于500的日志找出来，用OSS Select仅仅返回错误码是500的日志，在从而降低用户的数据传输成本以及处理数据的成本，相比在客户端下载整个文件再处理在性能上也可以提高最多到6倍以上。

那这个功能可以用在哪些场景呢，打开它的正确姿势是什么呢？下面就来一一讲解。

应用场景

日志文件分析

阿里云上的应用程序每天都在产生大量的日志文件，OSS是保存这些日志最可靠最经济的方式之一。通常的做法是每隔一段时间（比如一个小时）产生一个日志文件，在大部分情况下这些日志文件是GZIP压缩的。如果需要对这些日志文件做非常复杂的交叉查询，那阿里云的日志服务显然是不二的选择，但如果只是偶尔需要对这些文件做一些查询，比如在本文开头中描述的情况下，那OSS Select是更加简单、经济的做法。

OSS  Select支持类CSV文件，用户可以在Select请求中指定特定的行、列分隔符以及转义字符。简单插一句，CSV文件中如果一列数据包含换行符，那就需要将这些数据用转义字符将该列数据包含起来，从而将这个换行符转义。

比如用户有一个如下格式的nginx access log文件：

$remote_addr - $remote_user [$time_local] "$request" $status $body_bytes_sent "$http_referer" "$http_user_agent"

如果我们要找出所有客户端IP是192.168.0.1或者user agent包含chrome字符的请求的status，那可以用下面的Python代码来实现：

sql = “select _6 from ossobject where _1 = ‘192.168.0.1’ or _9 like ‘%chrome%’"

input_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”}

result = self.bucket.select_object(key, sql, select_params = input_format)

content = result.read()

如果想仅仅统计status 不等于200的请求数呢？那就可以用下面这个SQL实现

sql = “select count(*) from ossobject where _6 = ‘200’"

小伙伴们肯定注意到这里用了’200’而不是200，其实这里两者都是可以的，但是用’200’速度会更快一些，因为CSV文件的内容都是字符串。

如果我的日志文件第一行是CSV列名怎么办？可以用CsvHeaderInfo这个参数，并且可以在SQL中愉快的用列名了。

input_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”, ‘CsvHeaderInfo’:’Use’}

sql = “select count(*) from ossobject where status=’200’”

如果我的日志文件有注释行怎么办呢？简单，用CommentCharacter这个参数指定注释行的开头字母，这样所有的注释行就会被跳过。

input_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”, ‘CsvHeaderInfo’:’Use’, ‘CommentCharacter’:’#’}

更加复杂的情况来了，如果我的日志文件有的行缺数据，默认的行为是缺的数据列会被当做null来处理。但我想完全忽略这些缺数据的行怎么办？容易，用SkipPartialDataRecord这个参数为true就好了:

input_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”, ‘CsvHeaderInfo’:’Use’, ‘CommentCharacter’:’#’, ‘SkipPartialdataRecord’:’true’}

还有，我希望返回的结果用不同的行列分隔符，比如用逗号表示列分隔符，用’\r\n‘表示行分隔符？

input_output_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”, ‘CsvHeaderInfo’:’Use’, ‘CommentCharacter’:’#’, ‘SkipPartialdataRecord’:’true’, ‘OutputRecordDelimiter':’\r\n’, ‘OutputFieldDelimeter’:’,’}

如果我的日志文件有100G，且不是压缩的，上面的方法处理速度有点慢怎么破？分片查询可以解决这个问题。首先，上传文件后马上可以用下面的代码给这个日志文件创建分片信息,并返回总的分片数:

result = self.bucket.create_select_object_meta(key， {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’}

total_splits = result.splits

然后用Python多线程运行下面的代码，每个线程指定不同的分片范围—----start_range和end_range表示起始分片号，从0到total_splits - 1：

def select_by_range(start_range, end_range)：

       input_output_format = {‘RecordDelimiter’:’\n’, ‘FieldDelimiter’:’\t’, ‘QuoteCharacter’:’”’, ‘CompressionType’:”GZIP”, ‘CsvHeaderInfo’:’Use’, ‘CommentCharacter’:’#’, ‘SkipPartialdataRecord’:’true’, ‘OutputRecordDelimiter':’\r\n’, ‘OutputFieldDelimeter’:’,’, ‘SplitRange’:[start_range, end_range]}

       result = self.bucket.select_object(key, sql, select_params = input_output_format)

       return result

值得注意的是如果该文件的分片信息不存在，创建分片信息本身需要扫描整个文件。一旦创建完成就会被保存下来，下一次调用创建分片信息的函数时会直接返回结果。因此建议的策略是如果该文件需要分片查询，那么文件上传后就马上创建分片信息，从而避免在使用的时候需要扫描整个文件的开销。

最后，如果需要分析一个OSS前缀下的所有文件，有什么好办法吗？目前OSS Select API针对的是单个文件，如果要扫描整个目录，那需要首先调用bucket的List API返回该前缀下的所有文件，然后对这个列表分别调用Select API，可以单线程也可以多线程调用。

普通文本文件

如果我的文件只是普通文本文件而不是CSV，OSS Select对我而言有什么用呢？其实即使是普通文本文件（比如无固定格式的日志），你仍然可以用OSS Select实现服务器端grep的效果。比如，我可以用’\n’同时作为行列分隔符，这意味着可以用_1表示一整行信息。如果要搜索文本中所有含’对象存储’的行，只需要用select * from ossobject where _1 like ‘%对象存储%’。对于大文件而言，这比直接下载然后用grep要更简单高效。

和大数据平台结合

当大数据平台处理的数据源是OSS对象时，OSS Select还可以和大数据平台配合使用。目前阿里云的数据湖分析(Data Lake Analytics)服务支持OSS Select。只需要在Query语句前加一个Hint /*+oss-select-enabled=true*/即可。值得注意的是，如果用户输入的SQL语句可以有效过滤OSS数据的话，使用OSS Select后返回给Data Lake Analytics服务的数据可以有效减少，从而可以减少用户查询的时间并降低用户的总体成本。

另外，OSS Select还可以和Spark配合使用，我们将会有专门的文章详细讲解。

敏感数据脱敏

假如你有一些文件里包含一些敏感信息，比如身份证号码，但同时你需要给你的用户共享该文件中的其他信息。这个怎么做到呢？最简单的做法是你可以在你的服务里包一层OSS Select API，该API里调用Select接口只把不敏感的数据返回给你的用户。尤其是如果你的服务需要在欧洲上线的话，欧盟的GDPR对数据共享有着严格的限制。OSS Select可以简化包含敏感信息的数据的脱敏。

使用技巧

下面分享一些OSS Select使用过程中的小技巧，这些小技巧可以一方面提高用户查询的性能，另一方面在某些情况下降低使用成本。

1. 显式地选择所需要的列，而不是select *。这可以有效减少数据返回量，提高查询响应时间.
2. 在Where语句中优先使用字符串比较。当某些情况下既可以用字符串比较，也可以用Cast函数转换成其他类型后再比较。用字符串比较可以省去类型转换，从而使得查询更加高效。
3. 在Where语句中，把运行速度更快的表达式放在左边。由于Where条件是从左到右执行，左边的运算结果可以短路(short cut)掉右边的表达式。因此将表达式按照执行代价从左到右排列是一个最优的做法。关于不同表达式执行的代价，下面是从小到大的通常排序：字符串简单比较(=或者><等)，整数的比较和四则运算，浮点数的比较和四则运算，日期的比较，Decima的比较和四则运算，字符串的LIKE，字符串连接(concat)。
4. 合理使用IN语句。当需要对同一列进行多个数值的比较时，如果需要比较的数值在3个以内，那用or可能是一个更加简便的方法，但如果多余三个，那用IN更加简单高效。请注意OSS Select目前支持在IN中包含最多1024项。
5. 合理使用分片查询。当文件较小时，直接查询更加经济高效，因为创建分片信息本身需要扫描整个文件一遍，而且这个过程也是收费的。当文件很大且需要反复查询时，使用分片查询可以极大的提高速度（可以提高数十倍，取决于客户端并发数）。
6. 如何使用OutputRawData参数。OSS Select默认是用一个专门的格式包装返回的数据，该格式可以汇报进度查询执行进度，并且保活连接。但是如果被查询文件本身不是很大，或者使用的SQL语句过滤条件不强时，直接使用OutputRawData可以省去Frame本身的流量并提高客户端获取数据的速度。

总结

OSS Select功能旨在降低访问OSS时的数据传输量，简化客户端处理数据，并提高访问OSS的端到端性能以及降低客户的总体成本。他不仅可以用来直接查询类CSV文件（压缩或者未压缩）或者实现Server端的grep文本文件，同时和大数据平台如Data Lake Analyitcs或者Spark配合时，可以使整个查询过程更加高效并降低成本。

关于OSS Select有任何问题，欢迎留言一起讨论。