JSONファイルをMaxComputeの内部テーブルに格納する
JSONファイルをMaxComputeの内部テーブルに格納する
本記事では、JSONファイルをMaxComputeの内部テーブルに格納する方法について説明します。
前書き
MaxCompute (旧プロダクト名 ODPS) は、大規模データウェアハウジングのためのフルマネージドかつマルチテナント形式のデータ処理プラットフォームです。さまざまなデータインポートソリューションと分散計算モデルにより、大規模データの効率的な照会、運用コストの削減、データセキュリティを実現します。
少し前になりますが、MaxComputeについての資料をSlideShareへアップロードしていますので、こちらも参考になればと思います。
今回はAlibaba Cloud MaxComputeでJSONファイルを格納、SQL処理してみましょう。構成図で、こんな感じです。
今回はMovielensというオープンデータをJSONへ変換して使います。
注意として、MaxComputeはJSONデータを1つのString文字型として格納するため、JSONデータを少し加工する必要があります。
具体的には以下の通り、 開始の "[" と 終了の"]" を除外、{ ... }を1つのレコードとして格納するために、{ ... } , { ... } の間の"," を除去します。
※少し奇妙かもしれないですが、本家Apache Hiveも同じアプローチです。
{"movieId": "1", "title": "Toy Story (1995)", "genres": "Adventure|Animation|Children|Comedy|Fantasy"}{"movieId": "2", "title": "Jumanji (1995)", "genres": "Adventure|Children|Fantasy"}{"movieId": "3", "title": "Grumpier Old Men (1995)", "genres": "Comedy|Romance"}{"movieId": "4", "title": "Waiting to Exhale (1995)", "genres": "Comedy|Drama|Romance"}{"movieId": "5", "title": "Father of the Bride Part II (1995)", "genres": "Comedy"}{"movieId": "6", "title": "Heat (1995)", "genres": "Action|Crime|Thriller"}{"movieId": "7", "title": "Sabrina (1995)", "genres": "Comedy|Romance"}{"movieId": "8", "title": "Tom and Huck (1995)", "genres": "Adventure|Children"}{"movieId": "9", "title": "Sudden Death (1995)", "genres": "Action"}{"movieId": "10", "title": "GoldenEye (1995)", "genres": "Action|Adventure|Thriller"}{"movieId": "11", "title": "American President, The (1995)", "genres": "Comedy|Drama|Romance"}{"movieId": "12", "title": "Dracula: Dead and Loving It (1995)", "genres": "Comedy|Horror"}{"movieId": "13", "title": "Balto (1995)", "genres": "Adventure|Animation|Children"}{"movieId": "14", "title": "Nixon (1995)", "genres": "Drama"}{"movieId": "15", "title": "Cutthroat Island (1995)", "genres": "Action|Adventure|Romance"}{"movieId": "16", "title": "Casino (1995)", "genres": "Crime|Drama"}{"movieId": "17", "title": "Sense and Sensibility (1995)", "genres": "Drama|Romance"}{"movieId": "18", "title": "Four Rooms (1995)", "genres": "Comedy"}{"movieId": "19", "title": "Ace Ventura: When Nature Calls (1995)", "genres": "Comedy"}{"movieId": "20", "title": "Money Train (1995)", "genres": "Action|Comedy|Crime|Drama|Thriller"}{"movieId": "21", "title": "Get Shorty (1995)", "genres": "Comedy|Crime|Thriller"}{"movieId": "22", "title": "Copycat (1995)", "genres": "Crime|Drama|Horror|Mystery|Thriller"}{"movieId": "23", "title": "Assassins (1995)", "genres": "Action|Crime|Thriller"}{"movieId": "24", "title": "Powder (1995)", "genres": "Drama|Sci-Fi"}{"movieId": "25", "title": "Leaving Las Vegas (1995)", "genres": "Drama|Romance"}{"movieId": "26", "title": "Othello (1995)", "genres": "Drama"}{"movieId": "27", "title": "Now and Then (1995)", "genres": "Children|Drama"}{"movieId": "28", "title": "Persuasion (1995)", "genres": "Drama|Romance"}
共通作業(MaxCompute全体で共通事項)
RAM ユーザー作成&権限付与
もしMaxComputeを操作するユーザがRAMユーザーの場合は以下を実施してください。
RAMより対象のユーザーを選定します。ユーザーが無い場合は新規作成します。 このときにAccessKey IDとAccessKey Secretをメモとして残してください。AccessKey IDとAccessKey SecretはDataWorks DataIntegrationの処理に必要となります。
対象のユーザーには権限ロールとしてAliyunDataWorksFullAccessをアタッチします。 これはDataWorksを操作するためのFull権限です。
DataWorks側にてユーザーごとに読み取り専用や一部プロジェクト・テーブルなどのきめ細かい権限付与ができますが、ここでは割愛します。
Workspace作成
MaxComputeを操作するためにはワークスペースおよびプロジェクトが必要なので新たに作成します。DataWorksコンソールから 「Create Project」を選択し、起動します。
Modeは「Basic Mode(基本モード)」「と「Standard Mode(標準モード)」の2種類があります。ここは「Basic Mode(基本モード)」として選定します。
続けて、MaxCompute を選定します。料金は初めて操作するなら Pay-As-You-Go(使った分だけ課金) が良いと思います。
MaxComputeに関する必要な情報を設定し、Workspaceを作成します。
JSONファイルを MaxCompute Tableへ格納 (Tunnelを使用)
ここのチュートリアルは、JSONファイルをMaxComputeの内部テーブルへ格納する方法です。 前回の記事:CSVファイルをMaxComputeの内部テーブルへ、、ではGUIベースで追加していたので、今回はTunnelを使います。
https://www.alibabacloud.com/cloud-tech/doc-detail/27833.htm
STEP1:データ格納
Workspace、Project作成直後は何もない状態と思います。
なので、まずはDataWorks のHomepageへ移動し、データを格納する準備を進めます。
DataWorksのコンソール画面です。今回はTunnelを使うので、「DataStdio」をクリックします。
DataStdio画面です。最初は何もない状態です。そこからプラレールのように色々構築することが出来ます。
STEP2:JSONファイルを認識し、MaxCompute Tableに格納
メニューバーからworkflowを作成します。
workflow名を入力し、「Create」ボタンで作成します。ここは「csv2maxcompute」というタイトルで登録します。
workflow画面が出たら、メニューバーにて、緑色の「Table」から「Create Table」でテーブルを新規作成します。
「csv2maxcompute」というワークフロー画面はタブより閉じても問題ありません。
external_oss_table_json
という名前のテーブルを作成します。
Table名を入力して作成します。
DataStdio画面です。最初は何もない状態です。この状態からJSONデータを登録してみます。
JSONデータは1つのフィールドとして格納してから処理するので、「json_data」というフィールドを作成します。
Display/Nameを入力したら、Commitします。
STEP2:JSONファイルをMaxCompute TableにUpload
JSONファイルをMaxComputeに格納します。Tunnelを使います。
ファイルはTunnelが使えれば、ECSでもどこでも大丈夫です。今回、著者はローカルにJSONデータを保存しています。この状態から、tunnel コマンドでアップロードします。
tunnel upload C:/Users/1200358/Desktop/ml-latest-small-master/movieslite.json oss_target_table_json -fd ";";
STEP3:実行
DataWorks DataStdio画面に戻って、、TableにJSONデータが無事格納されたか確認しましょう。Ad-Hoc Query画面を開きます。
Ad-Hoc Query用のノードを作成します。
まずは、oss_target_table_jsonテーブルをそのまま表示してみます。
select * from oss_target_table_json;
このように、1つのフィールドにJSONファイルが格納されたことがわかります。
これに対し、Selectサブクエリおよびget_json_object
関数を使ってJSONデータを正しく表示します。
SELECT * FROM (SELECTget_json_object(json_data, '$.movieId') as movieId,get_json_object(json_data, '$.title') as title,get_json_object(json_data, '$.genres') as genresFROM oss_target_table_json)
JSONファイルを MaxCompute Tableへ格納 (OSSからSQLクエリを使用)
今度はOSSにあるJSONファイルをSQLクエリで格納してみましょう。 同じく、oss_target_table_jsonテーブルを使います。
先にTRUNCATE TABLE
コマンドで、oss_target_table_jsonテーブルのデータを空にします。
※注意として、MaxComputeは「DELETE」をサポートしません。なので、全てのレコードを削除したい場合は、DELETEの代わりにTRUNCATE TABLEを入れて全てのレコードを削除します。
TRUNCATE TABLE ratings;
https://www.alibabacloud.com/cloud-tech/doc-detail/73768.htm
STEP1: 事前準備
OSSにて、JSONファイルを以下のどおりに格納します。
OSSはbucket名bigdata-dwh
、ディレクトリ(Object Name Prefix)はjson/
、jsonフォルダに対象のjsonファイルを格納しています。
注意点として、MaxComputeでLOAD OVERWRITE TABLEによるSQLでのテーブル格納時は、対象のjsonファイルをディレクトリ単位で管理する必要があります。
RAMロール画面を開いて、AliyunODPSDefaultRole
を検索します。
RAMロールからAliyunODPSDefaultRole
のARNをコピーします。
STEP2: SQLクエリで格納 上記、準備が完了したら、DataWorks DataStdioのAd-Hock Query画面にて、以下SQLクエリを入力します。
LOAD OVERWRITE TABLE hands_on_workspace.oss_target_table_jsonFROMLOCATION 'oss://<Access key ID>:<AccessKey Secret>@oss-apnortheast-1.aliyuncs.com/bigdata-dwh/json/'ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'WITH WITH SERDEPROPERTIES ('odps.properties.rolearn' = 'acs:ram::5536281650763903:role/aliyunodpsdefaultrole')STORED AS TEXTFILE;
STEP3:完了
Tableが無事格納できたと思うので、確認します。
先ほどと同じように、Selectサブクエリおよびget_json_object
関数を使ってJSONデータを正しく表示します。
SELECT * FROM (SELECTget_json_object(json_data, '$.movieId') as movieId,get_json_object(json_data, '$.title') as title,get_json_object(json_data, '$.genres') as genresFROM oss_target_table_json)
これでJSONファイルが正しく表示されてることが確認できました。
最後に
本記事では、JSONファイルをMaxCompute Tableとして格納する方法を簡単に説明しました。