Azure Cosmos DB(SQL/Core API)へのdtui.exeを使ったJSONのインジェスト

#1. はじめに
<!-- 自己紹介や発端や概要を記載 -->
ATLシステムズの田中です～。
　本記事では、**Azure Cosmos DB**に**dtui.exe**というツールを使ってJSONのインジェストをする方法を紹介します。

　経緯として、**Azure Cosmos DB SQL/Core API**で、**GROUP BY**や、**ORDER BY**といったステートメントがどのように書けば動作するのかを検証するにしても、Azure Cosmos DBにコンテナーを**どのように作り**、**どのようにJSONデータを入れられるのか**、を知る必要があったため、本記事にまとめた次第です。

**※免責事項(必ず、ご一読ください)**
　本記事の情報により生じた、いかなる損害や損失についても、当社は一切の責任を負いかねます。
また、誤情報が入り込んだり、情報が古くなったりすることもありますので、必ずしも正確性を保証するものではありませんのでご了承ください。
#2. 概要図
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/f0444199-1516-0920-8712-ba4d32b8842c.png)

#3. 結果
　完成した**GROUP BY**と、**ORDER BY**を合わせた集計クエリは下記のようになりました。


```:集計クエリ.sql
SELECT * FROM (SELECT v.名前,count(v.名前) FROM Votes as v  GROUP BY v.名前) as view ORDER BY view.名前
```
※本記事のデータは**[疑似個人情報生成サービス](https://hogehoge.tk/personal/)**から生成したものです。
　　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/027abe1f-fc94-8109-40fd-bf9d149abbf0.png)
どうも、**副問い合わせ**の形式にしないと、ORDER BYがうまく解釈されないようで、下記のようになります。
　　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/79f0557b-7360-5c10-8329-2f34a40e13ad.png)

#4. データの用意
・今回は、前回の**[大量テストデータの生成(SQL Server)](https://qiita.com/ATL_Tanaka/items/ae36fcc412dafe2829bf)**で取り込んだデータをFOR JSONクエリを用いて、1つのJSONにして、**dtui.exe**を使い、**Azure Cosmos DB**に取り込みます。シチュエーションは、手元に大量のJSON形式のレコードをもつファイルがある場合を想定しています。
　　※データ元はこちらの疑似個人情報生成のWebサービスから生成したものです。(**[疑似個人情報生成サービス](https://hogehoge.tk/personal/)**)

・SQLの実行結果を右クリックし「結果に名前を付けて保存(V)」をして、test.jsonなど任意の名前を付け、.json形式で保存します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/c783a9ce-4fc8-60de-c49d-d049e00b7819.png">
#5.Dtuiのインストール
・こちらからダウンロードできます。
　　　[Azure DocumentDB Data Migration Tool](https://www.microsoft.com/en-us/download/details.aspx?id=46436)
・zipが手に入るので、dt-1.7を使いたい任意の場所で展開してください。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/70fafdee-313b-8712-47df-20f00c17699b.png">
・dtui.exeを選択して起動します。**Next**を押します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/adb800f7-9af1-261a-d3a1-65265212cf93.png">
#6. データクレンジング
　データにはいろいろと**ごみ**が混ざっていることがありますので、dtui.exeでjsonファイルを取り込む前に、まずは**データクレンジング**をします。
FOR JSONで変換すると、下記をする必要があります。

・**余分な行頭の削除(必須)**
　jsonファイルとして正しい形式にするために、行頭1行を削除します。
　　<img width="700" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/b5cf3aca-627a-bd4c-e4bc-f89f6a7ec39b.png">
・**半角空白、改行記号の削除**
　列が文字列型(nvarchar(24)等)の場合、半角埋めでバイト数分(この場合24byte分)**きっちり埋めて出力してしまう**ため、空文字を除去する必要があります。そこで、高機能エディタの置換機能で、不要な文字を空文字に置換して削除します。
　例：サクラエディタの置換の場合(**正規表現オン**)
　　　<img width="700" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/3e465213-193e-733e-6948-14108d4d7cfe.png">
※あまりにファイルサイズが大きい場合、エディタで開けないことあるので、エディタのGrep置換機能や、linuxOSの置換クエリsedなどを使用することをお勧めします。

#7. データベースとコンテナーの作成
以下の内容で作成します。
　　<img width="700" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/51f36118-ac97-1b6c-e63b-6505f189607b.png">
データベース**"Tohyo"**名前をパーティションキーとする、空のコンテナー**"Votes"**が出来上がりました。
　　<img width="700" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/5c915800-6859-052f-5723-5ac51d9103fe.png">

#8. Dtui.exeからのインジェスト
**・jsonファイルの指定**
　先ほど開いたdtui.exeで、先ほどデータクレンジングしたtest.jsonを指定します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/55763f4b-96cb-7a62-432d-9eef65f3a1ad.png">
　・**Import from**
　　　入力元のリソースを指定します。
　　　今回は、**JSON file(s)**を指定します。
　　　※実はSQLを指定して、SQL Serverから移行することができますが、シチュエーションから外れるため、今回はSQLは指定しません。
　Add Filesからjsonファイルを選択したらNextを押します。

**・接続の設定**
　・**Export to**
　　　今回は、デフォルトのDocumentDB - Sequential record import (partitioned collection)を選択します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/458433dc-e8ac-aac4-8bad-ca05962aca80.png">
　・**Connection String**
　　　Azure Cosmos DBの接続文字列と、データベース名を設定します。
　　　Connection Stringの欄は、以下を読むと、
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/ff437fa6-137b-6f73-e551-c51d33a81460.png">
　　　とあるので、それを満たす形式の接続文字列を取得するため、一旦Azure Portalを開き、Azure Cosmos DBのリソースのキーを開きます。
　　<img width=800" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/c94a2843-85a9-c34c-2e33-f5d7511430c9.png">
　　プライマリー接続文字列が当たるので、コピーして欄に張り付けます。
　　次にデータエクスプローラーからコンテナーの名前を確認します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/c08a9810-594e-770c-396e-26e4539f16bb.png">
　　今回、Tohyoコンテナーに入れたいため、ConnectionStringには、プライマリ接続文字列+Database="Tohyo"として以下のようにセットします。

```:ConnectionString
AccountEndpoint=エンドポイント;AccountKey=アカウントキー;Database="Tohyo"
```
　　左の**Verirfy**ボタンを押して、以下のダイアログが出れば、Azure Cosmos DBとの接続が成功です！
　　　　　　　　　　<img width="400" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/59b2de36-abfd-892f-f13e-b357f81b4e8c.png">
　　→設定したら**OK**をクリック。
**・その他項目の設定**
　下記のように設定します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/384fcceb-587c-2c8a-4897-175c650cb9e2.png">
　・**Collection**…インジェスト先のコンテナー(Container)の名前を指定
　　　今回は、**"Votes"**を指定します。
　・**Partitiion Key**…コンテナー作成時に設定したパーティションキーを指定
　　　今回の入力データでは、場合は投票先の「名前」の列ごとに分割するのが望ましいので、**"名前"**を指定します。
　　処理の内部的な並列化をするのに適当な単位（例えばカテゴリ単位や市町村単位など）が望ましいので、そのようなプロパティをセットしてあげます。(詳しくは[Azure Cosmos DB でのパーティション分割と水平スケーリング](https://docs.microsoft.com/ja-jp/azure/cosmos-db/partitioning-overview)を参照)
　・**Collection Throughput**…インジェストに使うRU/回を指定（詳しくは[Azure Cosmos DB の要求ユニット](https://docs.microsoft.com/ja-jp/azure/cosmos-db/request-units)を参照)
　・**Id Field**…今回の入力データの主キーとなる**"ID"**を指定します。
　　→設定したら**Next**をクリック。

**・エラーログ出力先の設定**
　以下のように設定します。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/d0ec2b5e-9735-37a8-862e-55864f642009.png">
　・**Error Log File**…ログ出力先のファイルパスを指定
　・**Detailed Error Information**…エラーログファイルに出力する、情報のレベルを指定します。
　　　今回は、失敗した場合がわかればよいので、**"Critical"**を指定します。
　・**Progress Update Interval**…進捗状況の更新頻度を指定します。
　　　今回は、1秒おきに画面更新させるため、デフォルトの**"00:00:01"**を指定します。
　　→設定したら**Next**をクリック。

**・実行前確認**
　実行する内容が表示されます。**Import**を押すと、Azure Cosmos DBへのインジェストが開始されます。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/5729cece-8dc6-f36b-c860-6aec89de8488.png">

**・実行結果確認**
　**・dtui.exeから**
　　　以下のようにImport resultsに**Failed:**が**0**と表示されていれば、成功です。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/d717efad-0a50-e9c3-3bd6-72880d96a45e.png">

　**・Azure Portalから**
　　赤枠をクリックしていき、itemsの中のレコードを開いてみます。
　　<img width="500" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/c83942f0-8470-d931-6c21-81d63393c932.png">
　　無事データが入っていますね。(^^)b
　　データが入っていることを確認したら、Azure Portal上よりクエリを作成して実行してみます。
#9. クエリ動作確認
　**・クエリの実行窓を開く**
　　  **New SQL Query**より、クエリの実行窓を開きます。
　　<img width="150" alt="画像" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/f44ab9ca-1757-0416-f3ee-c1abd54be06d.png">
　**・クエリの作成**
　　下記のMicrosoftのリファレンスを参考にしながらクエリを組み立てます。
　　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/5803a6f6-c894-6e46-c9b9-cdff8f8b55fc.png)
　　赤枠の、**Execute Query**を実行します。(私はよくSSMSを使っているので、F5を押してしまいますが、ウェブブラウザでは**更新**となってしまうため、**くれぐれも**F5を押さないように！！
　　[#3 結果](#3-結果)のようになるはずです。
　
以上です！！おつかれさまでした！

#10. 参考資料
- [FOR JSON を使用してクエリ結果を JSON として書式設定する (SQL Server)](https://docs.microsoft.com/ja-jp/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2016)
- [Azure DocumentDB Data Migration Tool](https://www.microsoft.com/en-us/download/details.aspx?id=46436)
- [Azure Cosmos DB での GROUP BY 句](https://docs.microsoft.com/ja-jp/azure/cosmos-db/sql/sql-query-group-by)
- [Azure Cosmos DB での ORDER BY 句](https://docs.microsoft.com/ja-jp/azure/cosmos-db/sql/sql-query-order-by)

#11. 最後に一言
　無事クエリの検証をすることができました。 最後まで読んでいただき、ありがとうございました！
　今後もいろいろとAzure周りや、その他の面白そうな技術について投稿していきます。
　余談ですが、弊社は**facebook**もしております！
　　　　　[![f_logo_RGB-Blue_64.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1661584/81cb9432-5fc7-7dfd-0155-15a602114fa2.png)](https://www.facebook.com/atlsystems)