AthenaにてJSONデータを操作時に、ファイル数が多くなるとクエリが失敗する

事象

athenaにてJSONファイルを共にしたテーブルを作成したところ、データ数が増えると以下のエラーが発生する

HIVE_BAD_DATA: Error parsing field value '2019-03-12' for field 0: For input string: ".2200199E4.2200199E4"

JSONファイル

読み込んでいるJSONファイルの内容は以下

• 日毎のJSONファイルが生成される
• その日のorganization毎のカウントを集計している

cat 2019-01-14.json | jq
{
  "date": "2019-01-14",
  "counts": [
    {
      "name": "organization_A",
      "count": 81
    },
    {
      "name": "organization_B",
      "count": 76
    },
    {
      "name": "organization_C",
      "count": 48
    },
    {
      "name": "organization_D",
      "count": 48
    },
  ]
}

athena

S3のアップロードした日毎のJSONファイルに対して、以下のクエリでテーブルを作成

CREATE EXTERNAL TABLE daily_count (
date date,
  counts array<struct<name:string,count:int>>
 )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://daily_count-data/'

原因

AWSサポートへ問い合わせところ、org.openx.data.jsonserde.JsonSerDe利用時の不具合の可能性があるとの事

対応

dateカラムの型をstringに変更して、テーブルを作成

CREATE EXTERNAL TABLE daily_count (
-- date date,
 date string,
  counts array<struct<name:string,count:int>>
 )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://daily_count-data/'

月毎のデータを集計をしたい為、参照する時に TRY_CASTを利用して、data型に変更して集計

SELECT
  to_char(TRY_CAST(date AS date),'yyyy/mm') as month,
  count.name,
  sum(count.count) as total
FROM daily_count
  cross join UNNEST(counts) as t (count)
  -- group by count.name, to_char(date,'yyyy/mm') 
  group by count.name, to_char(TRY_CAST(date AS date),'yyyy/mm') 
  order by month, total desc

メモ

athena SQLクエリ

docs.aws.amazon.com

TRY_CAST関数

prestodb.github.io