阿里云存储服务 关注
手机版

OSS Select使用场景及技巧

  1. 云栖社区>
  2. 阿里云存储服务>
  3. 博客>
  4. 正文

OSS Select使用场景及技巧

tonyxx 2018-11-01 10:07:47 浏览13275 评论4

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

背景介绍

OSS SelectOSS近期推出的一项新功能,它可以对OSS上的类CSV文件(其他类型文件比如Json也会很快推出)在服务器端运行SQL查询,仅将查询结果返回给客户端。举例来说,假如你有一个10GBcsv日志文件,有一列数据是错误码,想把其中所有错误码等于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"

如果我们要找出所有客户端IP192.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_rangeend_range表示起始分片号,从0total_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针对的是单个文件,如果要扫描整个目录,那需要首先调用bucketList API返回该前缀下的所有文件,然后对这个列表分别调用Select API,可以单线程也可以多线程调用。


普通文本文件

如果我的文件只是普通文本文件而不是CSVOSS 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有任何问题,欢迎留言一起讨论。

【云栖快讯】你想见的Java技术专家都在这了,向大佬提问,有问题必答  详情请点击

网友评论

1F
gydtep

2018阿里云双11优惠1折起,云主机99.5元/年!阿里云官网活动网址:https://m.aliyun.com/act/team1111/#/share?params=N.MfqJKPj5ff.2a7uv47d 参团后,即可享受优惠。

这么难啊

已经是超级大团了咯。已经是超级大团了咯。已经是超级大团了咯。重要的事情说三次!!!
1核2G内存 1年99元
2核8G 5M带宽 才 2070.
1核2G内存 两年199元
赶紧来!https://m.aliyun.com/act/team1111/#/share?params=N.kyFOaDiZPo.cuglkcp0

评论
2F
这么难啊

已经是超级大团了咯。已经是超级大团了咯。已经是超级大团了咯。重要的事情说三次!!!
1核2G内存 1年99元
2核8G 5M带宽 才 2070.
1核2G内存 两年199元
赶紧来!https://m.aliyun.com/act/team1111/#/share?params=N.kyFOaDiZPo.cuglkcp0

3F
云大使分享

阿里云新用户专享:2核8G5M带宽云服务器,3年只需¥2070
参加阿里云官方1折拼团(最低折扣只此一家)立即购买:
https://m.aliyun.com/act/team1111/#/share?params=N.EN2hxhpNQG.kzp4uq6k