Amazon Athenaで各種Join処理の動作を確認してみた

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 dataSourceBucket = new aws_s3.Bucket(this, 'dataSourceBucket', {
      bucketName: `data-source-${this.account}`,
      removalPolicy: RemovalPolicy.DESTROY,
    });

    // マスターデータ格納バケット
    const masterDataBucket = new aws_s3.Bucket(this, 'masterDataBucket', {
      bucketName: `master-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',
    });

    // データソースカタログテーブル
    new glue.Table(this, 'dataSourceGlueTable', {
      tableName: 'data_source_glue_table',
      database: dataCatalog,
      bucket: dataSourceBucket,
      s3Prefix: 'data/',
      dataFormat: glue.DataFormat.JSON,
      columns: [
        {
          name: 'userId',
          type: glue.Schema.STRING,
        },
        {
          name: 'count',
          type: glue.Schema.INTEGER,
        },
      ],
    });

    // マスターデータカタログテーブル
    new glue.Table(this, 'masterDataGlueTable', {
      tableName: 'master_data_glue_table',
      database: dataCatalog,
      bucket: masterDataBucket,
      s3Prefix: 'data/',
      dataFormat: glue.DataFormat.JSON,
      columns: [
        {
          name: 'userId',
          type: glue.Schema.STRING,
        },
        {
          name: 'name',
          type: glue.Schema.STRING,
        },
      ],
    });

    // Athenaワークグループ
    new aws_athena.CfnWorkGroup(this, 'athenaWorkGroup', {
      name: 'athenaWorkGroup',
      workGroupConfiguration: {
        resultConfiguration: {
          outputLocation: `s3://${athenaQueryResultBucket.bucketName}/result-data`,
        },
      },
      recursiveDeleteOption: true,
    });
  }
}

$ cat dataSouce
{"userId":"u001","count":3}
{"userId":"u001","count":1}
{"userId":"u002","count":5}
{"userId":"u002","count":8}
{"userId":"u003","count":2}
{"userId":"u999","count":4}

$ cat masterData
{"userId":"u001","name":"John"}
{"userId":"u002","name":"Bob"}
{"userId":"u003","name":"Tom"}
{"userId":"u004","name":"Peter"}

$ aws s3 cp dataSouce s3://${DATA_SOURCE_BUCKET_NAME}/data/dataSouce
$ aws s3 cp masterData s3://${MASTER_DATA_BUCKET_NAME}/data/masterData

WITH 
  raw AS (
    SELECT * FROM data_catalog.data_source_glue_table
  ),
  master AS (
    SELECT * FROM data_catalog.master_data_glue_table
  )
SELECT
  raw.userId, raw.count, master.name
FROM raw
<Join Type> master
  ON raw.userId = master.userId