Skip to content

Instantly share code, notes, and snippets.

@shunsugai
Created February 16, 2021 11:46
Show Gist options
  • Save shunsugai/cb5b7c255ef5a36b7e214a7198a61c96 to your computer and use it in GitHub Desktop.
Save shunsugai/cb5b7c255ef5a36b7e214a7198a61c96 to your computer and use it in GitHub Desktop.
サーモンランの結果をAmazon Athenaで分析するためのCREATE TABLE文
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment