ステップ 2: テーブルを作成する - Amazon Athena

ステップ 2: テーブルを作成する

データベースが作成されたので、Athena テーブルを作成できます。作成するテーブルは、ロケーション s3://athena-examples-myregion/cloudfront/plaintext/ にあるサンプルの Amazon CloudFront ログデータをベースにします。ここで、myregion は現在の AWS リージョン です。

サンプルログデータは、タブ区切り (TSV) 形式で、フィールドがタブ文字で区切られています。結果は以下の例のようになります。読みやすくするため、抜粋箇所のタブがスペースに変換され、最後のフィールドが短縮されています。

2014-07-05 20:00:09 DFW3 4260 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-1.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:09 DFW3 4252 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-2.jpeg 200 - Mozilla/5.0[...]
2014-07-05 20:00:10 AMS1 4261 10.0.0.15 GET eabcd12345678.cloudfront.net /test-image-3.jpeg 200 - Mozilla/5.0[...]

Athena がこのデータを読み取れるようにするには、以下にあるような単純な CREATE EXTERNAL TABLE ステートメントを作成できます。テーブルを作成するステートメントは、データにマッピングする列を定義し、データを区切る方法を指定して、サンプルデータが含まれる Amazon S3 の場所を指定します。Athena はフォルダ内のすべてのファイルをスキャンすることを想定しているため、LOCATION 句は特定のファイルではなく、Amazon S3 フォルダの場所を指定します。

この後すぐに説明する重要な制限があることから、この例はまだ使用しないでください。

CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  ClientInfo STRING
  ) 
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  LOCATION 's3://athena-examples-my-region/cloudfront/plaintext/';

この例は、cloudfront_logs という名前のテーブルを作成し、各フィールドの名前とデータ型を指定します。これらのフィールドがテーブルの列になります。date は予約語であるため、バッククォート (`) 文字でエスケープされます。ROW FORMAT DELIMITED は、Athena が実際のデータ解析作業の実行に LazySimpleSerDe という名前のデフォルトライブラリを使用することを意味します。この例は、フィールドがタブ区切り (FIELDS TERMINATED BY '\t') であり、ファイル内の各レコードが改行文字 (LINES TERMINATED BY '\n) で終わることも指定します。最後に、LOCATION 句が、読み取られる実際のデータが格納されている Amazon S3 のパスを指定します。

独自のタブ区切りまたはカンマ区切りのデータがある場合は、フィールドにネストされた情報が含まれていない限り、先程紹介した例のような CREATE TABLE ステートメントを使用できます。ただし、異なる区切り形式を使用するネストされた情報が含まれる ClientInfo のような列がある場合は、異なる方法を使用する必要があります。

ClientInfo フィールドからのデータの抽出

サンプルデータでは、以下が最後のフィールドである ClientInfo の完全な例になります。

Mozilla/5.0%20(Android;%20U;%20Windows%20NT%205.1;%20en-US;%20rv:1.9.0.9)%20Gecko/2009040821%20IE/3.0.9

ご覧のとおり、このフィールドは複数値フィールドです。先程紹介した CREATE TABLE ステートメント例はフィールドの区切り形式としてタブを指定していることから、ClientInfo フィールド内の個別のコンポーネントを個別の列に分割できません。そのため、新しい CREATE TABLE ステートメントが必要になります。

ClientInfo フィールド内の値から列を作成するには、正規表現 (regex) グループを含む regex を使用できます。指定する regex グループは、個別のテーブル列になります。CREATE TABLE ステートメントで正規表現を使用するには、次のような構文を使用します。この構文によって、Athena が Regex SerDe ライブラリと指定した正規表現を使用するように指示されます。

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES ("input.regex" = "regular_expression")

正規表現は、複合型の CSV データまたは TSV データからのテーブルの作成に便利ですが、記述や保守が困難な場合があります。幸いに、JSON、Parquet、および ORC といった形式に使用できるライブラリは他にもあります。詳細については、「データ用に SerDe を選択する」を参照してください。

これで、Athena クエリエディタでテーブルを作成する準備が整いました。CREATE TABLE ステートメントと正規表現が用意されています。

Athena でテーブルを作成するには

  1. ナビゲーションペインにある [Database] (データベース) で、mydatabase が選択されていることを確認します。

  2. クエリエディタ内で表示スペースを拡大するために、矢印アイコンを選択してナビゲーションペインを折りたたむことができます。

    矢印アイコンを選択して、ナビゲーションペインを折りたたみます。

  3. クエリエディタでプラス記号 (+) をクリックして、新しいクエリのタブを作成します。一度に最大 10 個のクエリタブを開くことができます。

    [+] アイコンを選択して、新しいクエリを作成します。

  4. 1 つ以上のクエリタブを閉じるには、プラス記号の横にある矢印をクリックします。すべてのタブを一度に閉じるには、矢印を選択してから、[Close all tabs] (すべてのタブを閉じる) をクリックします。

    矢印アイコンを選択して、1 つ以上のクエリタブを閉じます。

  5. クエリペインに、次の CREATE EXTERNAL TABLE クエリを入力します。この正規表現で、ログデータの ClientInfo フィールドから、オペレーティングシステム、ブラウザ、およびブラウザバージョンの情報を抜き出すことができます。

    注記

    次の例で使用されている regex は、athena-examples Amazon S3 ロケーションで公開されているサンプルの CloudFront ログデータを操作するように設計されており、例を示すために示しています。標準およびリアルタイムの CloudFront ログファイルの両方をクエリする最新の正規表現については、「Amazon CloudFront ログのクエリ」を参照してください。

    CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  os STRING,
  Browser STRING,
  BrowserVersion STRING
  ) 
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
  "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
  ) LOCATION 's3://athena-examples-myregion/cloudfront/plaintext/';

  6. LOCATION ステートメントの中の myregion を、現在使用している AWS リージョン (us-west-1 など) に置き換えます。

  7. [Run] (実行) を選択します。

    テーブル cloudfront_logs が作成され、mydatabase データベースの [Tables] (テーブル) リストに表示されます。