最近，我在一个处理大量 CSV 数据的 Laravel 应用程序上工作。不同的业务合作伙伴每小时都向 S3 上传 CSV 文件的新版本。
我的应用程序处理这些文件以构建各种报表图表。它还为授权用户提供查看上传的 CSV 文件内容的界面。用户也可以从界面中筛选和搜索不同的记录。大多数时候，应用程序只需要访问最新的行。
我最初的方案是在每次请求时都将整个 CSV 文件从 s3 下载到内存中。然后逐行遍历以计算图表所需的数据。为了提高性能，我将计算的结果缓存了半小时。这种方法有几个缺点
- 当缓存过期时，我不得不重新从 s3 下载整个文件。有些文件超过 5MB 大小。下载这样大的文件会极大地降低性能。
- 在 S3 中，您在检索数据时需要付费。频繁地下载大文件会增加成本。
- 我的应用程序的用户想要看到最近的记录。大多数时候，他们查看的是旧缓存数据。
- 编写过滤 CSV 内容的代码并不像编写 SQL 查询那样愉快。
另一个方法是在每15分钟获取CSV的内容。然后将这些记录插入到SQL数据库中。我不喜欢这种方法，因为现在数据存在两个不同的地方。这种方法的扩展性也不好。随着CSV文件中记录的增加，我必须更新或插入更多的记录到数据库中。好处是，我可以使用Eloquent或数据库查询构建器来访问数据。
AWS S3 Select
虽然Amazon S3非常适合存储，但它还有一个称为S3 Select的功能。使用S3 Select，您可以使用简单的SQL查询来过滤存储对象的包含内容，并检索您需要的部分数据。
S3 Select目前适用于存储CSV、JSON和Apache Parquet格式数据的对象。它还可以处理GZIP或BZIP2压缩的CSV和JSON对象。
S3 Select查询的Laravel示例
为了演示目的，我将使用以下示例数据。您可以从这里下载完整的数据集。
Laravel文件系统不支持QueryObjectContent操作，因此我创建了以下宏，以便可以在许多地方重用相同的逻辑。
<?php
namespace App\Providers;
use Illuminate\Filesystem\AwsS3V3Adapter;
use Illuminate\Support\ServiceProvider;
class MacroServiceProvider extends ServiceProvider
{
public function register(): void
{
AwsS3V3Adapter::macro('query', function (string $key, string $expression, array $overrides = []) {
/** @var AwsS3V3Adapter $this */
$default = [
'Bucket' => $this->config['bucket'],
'Expression' => $expression,
'ExpressionType' => 'SQL',
'InputSerialization' => [
'CSV' => [
'FileHeaderInfo' => 'USE',
'FieldDelimiter' => ','
],
],
'Key' => $key,
'OutputSerialization' => [
'CSV' => [
'RecordDelimiter' => ','
],
],
'RequestProgress' => [
'Enabled' => false,
],
];
return $this->getClient()->selectObjectContent(array_merge($default, $overrides));
});
}
}
此宏使用底层S3Client执行QueryObjectContent命令。它接受对象的名称、一个查询和QueryObjectContent命令的可选参数，并返回Aws\Result。
选择和限制子句
S3 Select使用简单的SQL语句。以下代码片段检索了示例数据集中的前五条记录。
$records = collect();
// S3 Query to select first 5 records
$query = 'SELECT "date", "canadian_dollar_to_usd" FROM s3object LIMIT 5';
// Execute the query and fetch the results
$result = Storage::disk('s3')->query('exchange_rate_to_usd.csv', $query);
// Loop through payload events to collect result
foreach($result['Payload'] as $event) {
if (isset($event['Records'])) {
$records = $records->merge(
Str::of($event['Records']['Payload'])
->explode(PHP_EOL)
->filter()
->map(fn(string $item) => Str::of($item)->explode(','))
);
}
}
dd($records);
如果您以前的CSV文件没有标题，您也可以在查询中指定索引数字。您可能需要更新FileHeaderInfo参数的值。可能的值是USE、IGNORE或NONE。如果您的CSV不包含标题，请使用NONE，否则使用IGNORE。
$query = 'SELECT _1, _2 FROM s3object LIMIT 5';
与SQL语句类似，您也可以使用*检索所有列。
$query = "SELECT * FROM s3Object LIMIT 5"
WHERE子句
为了探索WHERE子句，让我们获取从2022年3月1日以来CAD兑USD汇率的列表。请注意，日期是S3 Select中的一个保留关键字。要使用这些保留关键字，您必须将它们括在双引号中。您可以在这里找到保留关键字的列表。
// ...
$query = 'SELECT "date", canadian_dollar_to_usd FROM s3object WHERE CAST("date" AS TIMESTAMP) >= CAST(\'2022-03-01\' AS TIMESTAMP)';
$result = Storage::disk('s3')->query('exchange_rate_to_usd.csv', $query);
// ...
使用函数
与SQL类似，S3 Select也允许我们使用函数。您可以在AWS文档中找到函数的完整列表。在以下代码中，我们将使用COUNT聚合函数获取文件中的记录总数。
$count = null;
$query = 'SELECT COUNT(*) FROM s3object';
$result = Storage::disk('s3')->query('exchange_rate_to_usd.csv', $query);
// Loop through payload events to get records
foreach($result['Payload'] as $event) {
if (isset($event['Records'])) {
$count = Str::of($event['Records']['Payload']);
}
}
dd($count);
S3 Select的成本
Amazon S3 Select非常便宜。S3 Select的成本取决于选择请求的数量、扫描的数据量和返回性的数据量。比如说，我在S3中存储了50GB的数据。我每月执行10万个选择请求，并返回20GB的数据。这将花费我不到2美元。以下是截至2022年3月的AWS定价计算器中的定价估计。
Tiered price for: 50 GB
50 GB x 0.0230000000 USD = 1.15 USD
Total tier cost = 1.1500 USD (S3 Standard storage cost)
100,000 GET requests in a month x 0.0000004 USD per request = 0.04 USD (S3 Standard GET requests cost)
20 GB x 0.0007 USD = 0.014 USD (S3 select returned cost)
30 GB x 0.002 USD = 0.06 USD (S3 select scanned cost)
1.15 USD + 0.04 USD + 0.014 USD + 0.06 USD = 1.26 USD (Total S3 Standard Storage, data requests, S3 select cost)
S3 Standard cost (monthly): 1.26 USD
结论
总的来说，使用S3 Select可以减少数据传输的时间和成本。虽然S3只支持简单的查询，但在某些场景下它非常有用。
driesvints, justsanjit, nadu, ngoquocdat, phcostabh 喜欢了这篇文章
Laravel 
Laravel News 
Laracasts 
Laravel Podcast