こんにちは、CX事業本部 IoT事業部の若槻です。
Amazon Athenaで日付カラムを持つデータを期間指定で取得する方法を確認していたところ、同じ取得でも何通りか方法があり迷うことがありました。
そこで今回は、WHERE句で期間を指定して日付カラムでデータを絞り込む方法をまとめてみました。
やってみた
環境構築
AWS CDK v2（TypeScript）で次のようなCDKスタックを作成します。
lib/process-stack.ts
import { Construct } from 'constructs';
import {
aws_s3,
aws_athena,
RemovalPolicy,
Stack,
StackProps,
} from 'aws-cdk-lib';
import * as glue from '@aws-cdk/aws-glue-alpha';
export class ProcessStack extends Stack {
constructor(scope: Construct, id: string, props: StackProps) {
super(scope, id, props);
// ソースデータ格納バケット
const sourceDataBucket = new aws_s3.Bucket(this, 'sourceDataBucket', {
bucketName: `data-${this.account}`,
removalPolicy: RemovalPolicy.DESTROY,
});
// Athenaクエリ結果格納バケット
const athenaQueryResultBucket = new aws_s3.Bucket(
this,
'athenaQueryResultBucket',
{
bucketName: `athena-query-result-${this.account}`,
removalPolicy: RemovalPolicy.DESTROY,
},
);
// データカタログ
const dataCatalog = new glue.Database(this, 'dataCatalog', {
databaseName: 'data_catalog',
});
// データカタログテーブル
const sourceDataGlueTable = new glue.Table(this, 'sourceDataGlueTable', {
tableName: 'source_data_glue_table',
database: dataCatalog,
bucket: sourceDataBucket,
s3Prefix: 'data/',
partitionKeys: [
{
name: 'dt',
type: glue.Schema.STRING,
},
],
dataFormat: glue.DataFormat.JSON,
columns: [
{
name: 'userId',
type: glue.Schema.STRING,
},
{
name: 'count',
type: glue.Schema.INTEGER,
},
],
});
// データカタログテーブルへのPartition Projectionの設定
// eslint-disable-next-line @typescript-eslint/no-explicit-any
(sourceDataGlueTable.node.defaultChild as any).tableInput.parameters = {
'projection.enabled': true,
'projection.dt.type': 'date',
'projection.dt.range': '2022/06/28,NOW',
'projection.dt.format': 'yyyy/MM/dd',
'projection.dt.interval': 1,
'projection.dt.interval.unit': 'DAYS',
'storage.location.template':
`s3://${sourceDataBucket.bucketName}/data/` + '${dt}',
};
// Athenaワークグループ
new aws_athena.CfnWorkGroup(
this,
'athenaWorkGroup',
{
name: 'athenaWorkGroup',
workGroupConfiguration: {
resultConfiguration: {
outputLocation: `s3://${athenaQueryResultBucket.bucketName}/result-data`,
},
},
},
);
}
}yyyy/MM/ddというフォーマットのdate型のカラムを持ったデータをクエリ対象とします。
上記をCDK Deployしてスタックをデプロイします。
日付カラムでデータを絞り込む
絞り込みを行わない場合
SELECT *
FROM "data_catalog"."source_data_glue_table"すべての日付のデータを取得できます。
等号で絞り込む
1つの日付なので期間とは言えないですが、等号（=）を使用できます。
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt = '2022/06/29'
不等号で絞り込む
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt <= '2022/06/29'2022/06/29以前のデータが絞り込めました。
ちなみに不等号は2つ以上を1度には使えません。
#エラーになる
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE '2022/06/28' < dt <= '2022/06/29'
2つ以上使いたい場合はAND句やOR句を使う必要があります。
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE '2022/06/28' < dt AND dt <= '2022/06/29'
BETWEEN句で絞り込む
BETWEEN句で2つの日付の間のデータを絞り込むことができます。その際にdate_parse()やdateでdate型を指定することがポイントです。
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE date_parse(dt, '%Y/%m/%d') BETWEEN date '2022-06-28' AND date '2022-06-29'2022/06/28から2022/06/29までのデータが絞り込めました。
動的な日付や差分を指定する
CURRENT_TIMESTAMPを使用すると現在時刻を取得できます。またINTERVALで日時の差分を取得できます。
SELECT
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP - INTERVAL '7' DAY
上記を利用して今日から1週間前までのデータを動的に取得するクエリも作れます。
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE date_parse(dt, '%Y/%m/%d') BETWEEN CURRENT_TIMESTAMP - INTERVAL '7' DAY AND CURRENT_TIMESTAMP
タイムゾーンを指定する
CURRENT_TIMESTAMPで取得できる日付のタイムゾーンは既定で世界標準時（UTC）です。AT TIME ZONE '<Time Zone>'を使用してタイムゾーンを指定することも可能です。
SELECT
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo'9時間ずれた日時が取得できています。
FORMAT_DATETIMEでdate型の値を指定の形式の文字列にフォーマットできます。これによりタイムゾーンを設定した日付を不等号でも使用できます。
SELECT *
FROM "data_catalog"."source_data_glue_table"
WHERE dt <= FORMAT_DATETIME(
CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo',
'YYYY/MM/dd'
)
その他の方法
Amazon AthenaはPrestoエンジンを使用しているので、基本的にはPrestoのドキュメントが参考になります。他の方法を知りたい場合は読んでみましょう。
参考
以上
14
