開始 - Amazon Athena
前提条件ステップ 1: データベースを作成するステップ 2: テーブルを作成するステップ 3: データをクエリするクエリを保存するキーボードショートカットと先行入力候補他のデータソースへの接続

開始

このチュートリアルは、Amazon Athena を使用したデータのクエリについて詳しく説明するもので、Amazon Simple Storage Service に保存されたサンプルデータに基づいたテーブルを作成し、テーブルをクエリして、クエリ結果を確認します。

チュートリアルではライブリソースを使用するため、実行したクエリに対する料金が発生します。このチュートリアルが使用する場所にあるサンプルデータに対する料金は発生しませんが、独自のデータファイルを Amazon S3 にアップロードする場合は、料金が適用されます。

前提条件

  • まだサインアップしていない場合は、AWS アカウントにサインアップします。

  • Athena で使用しているものと同じ AWS リージョン (米国西部 (オレゴン) など) とアカウントを使用して、Athena からのクエリ結果を保存するバケットを Amazon S3 に作成するためのステップを実行します。このバケットをクエリ出力の場所として設定します。

ステップ 1: データベースを作成する

まず、Athena でデータベースを作成する必要があります。

Athena データベースを作成するには

  1. https://console.aws.amazon.com/athena/ で Athena コンソールを開きます。

  2. 現在の AWS リージョン で Athena コンソールを初めて使用する場合は、[Explore the query editor] (クエリエディタを試す) をクリックしてクエリエディタを開きます。初めてではない場合は、クエリエディタで Athena が開きます。

  3. [Edit Settings] (設定を編集) をクリックし、Amazon S3 内のクエリ結果の保存場所をセットアップします。

    [設定の編集] を選択します。

  4. [Manage settings] (設定の管理) で、以下のいずれかを実行します。

    • [Location of query result] (クエリ結果の場所) ボックスで、クエリ結果のために Amazon S3 に作成したバケットへのパスを入力します。パスの先頭に s3:// を付けます。

    • [Browse S3] (S3 をブラウズ) をクリックし、現在のリージョンで作成した Amazon S3 バケットを選択した上で、[Choose] (選択) をクリックします。

    Athena からのクエリ結果を受け取る Amazon S3 の場所を指定します。

  5. [Save] を選択します。

  6. [Editor] (エディタ) をクリックして、クエリエディタに切り替えます。

    [Editor] (エディタ) を選択します。

  7. ナビゲーションペインの右側から、Athena クエリエディタを使用してクエリとステートメントの入力と実行が行えます。

    Athena コンソールのクエリエディタ。

  8. mydatabase という名前のデータベースを作成するには、次の CREATE DATABASE ステートメントを入力します。

    CREATE DATABASE mydatabase

  9. [Run] (実行) をクリックするか、Ctrl+ENTER キーを押します。

  10. 左側の [Database] (データベース) リストから、現在のデータベースとして mydatabase を選択します。

    作成したデータベースを選択します。

ステップ 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 といった形式に使用できるライブラリは他にもあります。詳細については、「サポートされる SerDes とデータ形式」を参照してください。

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

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

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

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

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

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

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

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

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

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

    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] (テーブル) リストに表示されます。

ステップ 3: データをクエリする

Amazon S3 内のデータに基づいて Athena で cloudfront_logs テーブルを作成したところで、次は Athena でこのテーブルに SQL クエリを実行して、結果を確認できます。Athena での SQL の使用に関する詳細については、「Athena の SQL リファレンス」を参照してください。

クエリを実行する

  1. プラス (+) 記号をクリックして新しいクエリタブを開き、クエリペインに次の SQL ステートメントを入力します。

    SELECT os, COUNT(*) count 
FROM cloudfront_logs 
WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05' 
GROUP BY os

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

    結果は以下のようになります。

    Athena コンソールでのクエリ結果の表示。

  3. クエリ結果を .csv ファイルに保存するには、[Download results] (結果をダウンロード) をクリックします。

    クエリ結果の CSV 形式でのダウンロード。

  4. 以前のクエリを表示または実行するには、[Recent queries] (最近のクエリ) タブを開きます。

    [Recent queries] (最近のクエリ) をクリックして以前のクエリを表示します。

  5. 以前のクエリの結果を、[Recent queries] (最近のクエリ) タブからダウンロードするには、クエリを選択した上で、[Download results] (結果をダウンロード) をクリックします。クエリは 45 日間保持されます。

    Athena コンソールでの最近のクエリの表示とダウンロード。

  6. 1 つ以上の最新の SQL クエリ文字列を CSV ファイルにダウンロードするには、[Download CSV] (CSV をダウンロード) を選択します。

    最近のクエリ文字列を CSV ファイルにダウンロードします。

    詳細については、「クエリ結果、最近のクエリ、および出力ファイルの使用」を参照してください。

クエリを保存する

クエリエディタで作成または編集したクエリは、名前を付けて保存できます。Athena はこれらのクエリを [Saved queries] (保存されたクエリ) タブ。[Saved queries] (保存されたクエリ) タブを使用して、保存したクエリの呼び出し、実行、名前変更、または削除をおこなうことができます。詳細については、「保存されたクエリの使用」を参照してください。

キーボードショートカットと先行入力候補

Athena クエリエディタには、クエリの実行、クエリの書式設定、行操作、検索と置換などの操作のための、多数のキーボードショートカットが用意されています。詳細とショートカットの完全なリストについては、AWS Big Data Blog の「Improve productivity by using keyboard shortcuts in Amazon Athena query editor」を参照してください。

Athena クエリエディタでは、クエリの作成を高速化するための先行入力コード候補がサポートされています。SQL クエリをより正確に、より効率的に記述できるように、次の機能が提供されます。

  • 入力すると、キーワード、ローカル変数、スニペット、およびカタログ項目の候補がリアルタイムで表示されます。

  • データベース名またはテーブル名の後にドットを入力すると、エディタはテーブルまたは列のリストを表示して選択できるため便利です。

  • スニペットの候補にカーソルを合わせると、概要にスニペットの構文と使用法の概要が表示されます。

  • コードの読みやすさを向上させるため、キーワードとその強調表示ルールも Trino と Hive の最新構文に合わせて更新されました。

この機能は、デフォルトでご利用になれます。この機能を有効または無効にするには、クエリエディタウィンドウの右下にある [コードエディタの環境設定] (歯車アイコン) を使用します。

他のデータソースへの接続

このチュートリアルでは、Amazon S3 にある CSV 形式のデータソースを使用しました。AWS Glue での Athena の使用については、「AWS Glue を使用した Amazon S3 内のデータソースへの接続」を参照してください。ODBC ドライバー、JDBC ドライバー、外部の Hive メタストア、および Athena データソースコネクタを使用して、Athena をさまざまなデータソースに接続することもできます。詳細については、「データソースへの接続」を参照してください。