OpenX JSON SerDe - Amazon Athena
オプションのプロパティ例: 広告データ例: ネスト JSON の逆シリアル化

OpenX JSON SerDe

Hive JSON SerDe と同様に、OpenX JSON を使用して JSON データを処理できます。また、データは改行で区切られ JSON 形式でエンコードされたテキストの 1 行の文字列としても表現されます。Hive JSON SerDe と同様に、OpenX JSON SerDe では、map または struct キー名の重複キーは許可されません。

注記

SerDe では、各 JSON ドキュメントが、レコード内のフィールドを区切る行終端文字なしの、1 行のテキストに収まっていることを想定しています。JSON テキストがプリティプリント形式の場合、テーブルを作成した後にクエリを実行しようとすると、以下のようなエラーメッセージが表示される場合があります。「HIVE_CURSOR_ERROR: Row is not a valid JSON Object」、または「HIVE_CURSOR_ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT」。詳細については、GitHub の OpenX SerDe のドキュメントで「JSON Data Files」(JSON データファイル) を参照してください。

オプションのプロパティ

Hive JSON SerDe とは異なり、OpenX JSON SerDe には、データの不整合に対処するのに役立つ次のオプションの SerDe プロパティもあります。

ignore.malformed.json

オプション。TRUE に設定すると、不正な形式の JSON 構文を無視できます。デフォルト: FALSE。

dots.in.keys

オプション。デフォルト: FALSE。TRUE に設定すると、SerDe はキー名のドットをアンダースコアに置き換えることができます。例えば、JSON データセットに "a.b" という名前のキーが含まれている場合は、このプロパティを使用して、Athena で列名が "a_b" になるように定義できます。デフォルトで (この SerDe がない場合)、Athena は列名にドットを許可しません。

case.insensitive

オプション。デフォルト: TRUE。TRUE に設定すると、SerDe はすべての大文字の列を小文字に変換します。

データで大文字と小文字を区別するキー名を使用するには、WITH SERDEPROPERTIES ("case.insensitive"= FALSE;) を使用します。次に、まだすべてが小文字になっていないすべてのキーに、次の構文を使用して列名からプロパティ名へのマッピングを指定します。

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.userid" = "userId")

URL と Url のように小文字である 2 つのキーがある場合は、次のようなエラーが発生する可能性があります。

HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key "url"

これを解決するには、次の例のように、case.insensitive プロパティを FALSE に設定し、キーを異なる名前にマッピングします。

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.url1" = "URL", "mapping.url2" = "Url")
マッピングを

オプション。列名を、列名と同一ではない JSON キーにマップします。mapping パラメータは、JSON データにキーワードのキーが含まれている場合に便利です。たとえば、timestamp という名前の JSON キーがある場合、次の構文を使用して、キーを ts という名前の列にマッピングします。

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.ts" = "timestamp")
コロンを含むネストされたフィールド名を Hive 互換の名前にマッピングする

struct の内部にコロンを含むフィールド名がある場合は、mapping プロパティを使用してそのフィールドを Hive 互換の名前にマップできます。例えば、列タイプの定義に my:struct:field:string を含めることで、WITH SERDEPROPERTIES で次のエントリー定義を my_struct_field:string にマップできます。

("mapping.my_struct_field" = "my:struct:field")

次の例は、対応する CREATE TABLE ステートメントを示しています。

CREATE EXTERNAL TABLE colon_nested_field (
item struct<my_struct_field:string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.my_struct_field" = "my:struct:field")

例: 広告データ

次の DDL ステートメントの例では、OpenX JSON SerDe を使用して、Hive JSON SerDe の例で使用したのと同じサンプルオンライン広告データに基づいてテーブルを作成します。LOCATION 句で、myregion を Athena が実行されるリージョンの識別子に置き換えます。

CREATE EXTERNAL TABLE impressions (
    requestbegintime string,
    adid string,
    impressionId string,
    referrer string,
    useragent string,
    usercookie string,
    ip string,
    number string,
    processid string,
    browsercokie string,
    requestendtime string,
    timers struct<
       modellookup:string, 
       requesttime:string>,
    threadid string, 
    hostname string,
    sessionid string
)   PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://myregion.elasticmapreduce/samples/hive-ads/tables/impressions';

例: ネスト JSON の逆シリアル化

JSON SerDe を使用して、より複雑な JSON エンコードされたデータを解析できます。このためには、ネストされた構造を表すために struct 要素と array 要素を使用する CREATE TABLE ステートメントを使用する必要があります。

以下の例は、ネストされた構造を持つ JSON データから Athena テーブルを作成します。JSON でエンコードされたデータを Athena で解析するには、各 JSON ドキュメントが改行で区切られた独自の行にあることを確認します。

この例で、JSON エンコード形式のデータ構造は次のとおりとします。

{
"DocId": "AWS",
"User": {
        "Id": 1234,
        "Username": "bob1234", 
        "Name": "Bob",
"ShippingAddress": {
"Address1": "123 Main St.",
"Address2": null,
"City": "Seattle",
"State": "WA"
   },
"Orders": [
   {
     "ItemId": 6789,
     "OrderDate": "11/11/2017" 
   },
   {
     "ItemId": 4352,
     "OrderDate": "12/12/2017"
   }
  ]
 }
}

次の CREATE TABLE ステートメントは、Openx-JsonSerDe を struct および array コレクションデータ型とともに使用して、オブジェクトのグループを構築します。各 JSON ドキュメントは個別の行にリストされ、新しい行で区切られます。エラーを回避するため、クエリ中のデータには、struct またはマップキー名の重複するキーは含まれません。

CREATE external TABLE complex_json (
   docid string,
   `user` struct<
               id:INT,
               username:string,
               name:string,
               shippingaddress:struct<
                                      address1:string,
                                      address2:string,
                                      city:string,
                                      state:string
                                      >,
               orders:array<
                            struct<
                                 itemid:INT,
                                  orderdate:string
                                  >
                              >
               >
   )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://mybucket/myjsondata/';