サーモンランの結果をAmazon Athenaで分析するためのCREATE TABLE文

create_table_for_salmon_run.sql

CREATE EXTERNAL TABLE IF NOT EXISTS {your_table_name_here} (
  job_score int,
  job_result struct<failure_wave: int,
                    failure_reason: string,
                    is_clear: boolean
                    >,
  wave_details array<struct<golden_ikura_pop_num: int,
                            ikura_num: int,
                            golden_ikura_num: int,
                            quota_num: int,
                            event_type: struct<name: string,
                                               key: string
                                               >,
                            water_level: struct<name: string,
                                                key: string
                                                >
                            >>,
  job_id int,
  grade struct<short_name: string,
               long_name: string,
               name: string,
               id: string
               >,
  grade_point int,
  grade_point_delta int,
  start_time timestamp,
  end_time timestamp,
  my_result struct<weapon_list: array<struct<id: string,
                                             weapon: struct<image: string,
                                                            name: string,
                                                            thumbnail: string,
                                                            id: string
                                                            >
                                             >>,
                   ikura_num: int,
                   name: string,
                   pid: string,
                   player_type: struct<species: string,
                                       style: string
                                       >,
                   golden_ikura_num: int,
                   dead_count: int,
                   help_count: int,
                   special: struct<image_a: string,
                                   id: string,
                                   image_b: string,
                                   name: string
                                   >,
                   special_counts: array<int>
                   >,
  play_time timestamp,
  job_rate int,
  danger_rate float,
  player_type struct<species: string,
                     style: string
                     >,
  schedule struct<stage: struct<name: string,
                                image: string
                                >,
                  start_time: timestamp,
                  end_time: timestamp
                  >
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1' ) LOCATION 's3://{your_bucket_here}' TBLPROPERTIES ('has_encrypted_data'='false');