この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納(にいの)です。
json形式のデータをテーブル化しようとしたら躓き、調べているうちにAmazon Athenaへの理解が深まったのでシェアします。
やりたいこと
Athenaに以下のようなjson形式のデータが入っています。
{"name": "Susan Smith","org": "engineering","projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}
データは以下のドキュメントページを元にして作成しています。
JSON からのデータの抽出 - Amazon Athena
このデータを以下のような形に構造化したテーブルを作成したいです。
name | org | p_name | completed |
---|---|---|---|
"Susan Smith" | "engineering" | "project1" | FALSE |
"Susan Smith" | "engineering" | "project2" | TRUE |
Unsupported Hive type: jsonが発生
以下のCREATE TABLE文を実行してみます。
CREATE TABLE "default"."json_test_table" WITH (
format = 'PARQUET',
external_location = 's3://<バケット名>'
) AS
SELECT a.name,
a.org,
json_extract(t.projects, '$.name') as p_name,
json_extract(t.projects, '$.completed') as completed
FROM (
select json_extract(blob, '$.name') as name,
json_extract(blob, '$.org') as org,
cast(json_extract(blob, '$.projects') as array(json)) as projects
from "default"."json_test_view"
) a
CROSS JOIN UNNEST(projects) as t(projects)
すると、NOT_SUPPORTED: Unsupported Hive type: json.
エラーが発生します。
なお、CREATE TABLEをSELECTにしてみるとエラーは発生せず、期待した結果が取得されます。
SELECT
a.name,
a.org,
json_extract(t.projects, '$.name') as p_name,
json_extract(t.projects, '$.completed') as completed
FROM (
SELECT
json_extract(blob, '$.name') as name,
json_extract(blob, '$.org') as org,
cast(json_extract(blob, '$.projects') as array(json)) as projects
FROM
"default"."json_test_view"
) a cross join
unnest(projects) as t(projects)
解決策
データ型がjsonになっている項目に対し、CASTで適切なデータ型にします。
CREATE TABLE "default"."json_test_table" WITH (
format = 'PARQUET',
external_location = 's3://<バケット名>'
) AS
select a.name,
a.org,
cast(json_extract(t.projects, '$.name') as varchar) as p_name,
cast(json_extract(t.projects, '$.completed') as boolean) as completed
from (
select cast(json_extract(blob, '$.name') as varchar) as name,
cast(json_extract(blob, '$.org') as varchar) as org,
cast(json_extract(blob, '$.projects') as array(json)) as projects
from "default"."json_test_view"
) a
cross join unnest(projects) as t(projects)
解説
Amazon Athenaでは、CREATE TABLEなどのDDL(データ定義言語)はHiveが使われ、それ以外のステートメントではインメモリクエリエンジンであるPrestoが使われています。
Q: Athena で Hive クエリを実行できますか? Amazon Athena では、Hive を DDL (データ定義言語) 用や、テーブルおよび/またはパーティションの作成、変更、削除にのみ使用します。
(中略)
Amazon S3 で SQL クエリを実行する場合、Athena では Presto が使用されます。Amazon S3 のデータをクエリする場合、ANSI 準拠 SQL SELECT ステートメントを実行できます。
そして、Hiveの場合サポートしているデータ型にjsonはありません。このため、SELECTは問題なく実行できてもCREATE TABLEはエラーになってしまいました。上述の通り、DDLでもサポートしているデータ型に変換することでテーブル作成できるようになります。
「どうしてAthenaのクエリエンジンはPrestoなのにDDL機能だけHiveなの?」と疑問に思われた方は以下のエントリも合わせてご参照ください。
最後に
DDL文実行でNOT_SUPPORTED: Unsupported Hive type: json.
が発生したのでjson型のカラムを適切なデータ型にCASTしてエラー解消したというお話でした。このようなデータ型関連のエラーが発生した際はtypeof関数を利用してどんなデータ型で結果が返ってきているのか確かめると原因が特定しやすくなるかと思います。