Azure Form Recognizer のカスタムモデル の JSONファイルを、Azure Synapse Analytics - SQL Serverless Pool からクエリする for サクッと検証

この記事はかなり初歩的な内容です。私の備忘録の意味が強いですね😁

# 更新履歴
2021/12/16 - Form Recognizer での日本語タグ付けの対応。SQL Serverless Pool に照合順序を設定
2022/2/1   - Form Recognizer でのテーブル形式のタグ付けの T-SQL 対応
2022/4/22  - Form Recognizer 3.0 Studio での処理結果に変更。Key-Value の方を

# 背景
PDF などの帳票はあちこちに溜まっていますが、構造化データ化がされない事もあります。
それを Azure Logic App + Azure Form Recognizer で JSON 文字列のファイルにします。ファイルは Azure Data Lake Storage Gen2 に入れます。
それを構造化データとみなし、Azure Synapse Analytics - SQL Serverless Pool から、サクッとクエリする時のステップを残しておきます。

アーキテクチャ図:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/18af1d4d-2e2e-df52-af9a-0055197776a2.png)

Azure Form Recognizer:
https://docs.microsoft.com/ja-jp/azure/cognitive-services/form-recognizer/overview

Azure Logic Apps:
https://docs.microsoft.com/ja-jp/azure/logic-apps/logic-apps-overview

Azure Synapse Analytics:
https://docs.microsoft.com/ja-jp/azure/synapse-analytics/

Azure Synapse Analytics - SQL Serverless Pool:
https://docs.microsoft.com/ja-jp/azure/synapse-analytics/sql/on-demand-workspace-overview

Azure Data Lake Storage Gen2:
https://docs.microsoft.com/ja-jp/azure/storage/blobs/data-lake-storage-introduction

# 留意事項

本番環境を見越す際には、以下を注意してください。つまり、この記事ではそれを **扱っていません!**

- Medallion Architecture を使う
    - 生データをいきなりクエリしない
        - クエリ用のファイル集約は是非行ってくださいね
    - 回避したい問題
        - Cloud Storage の低速
            - 1つのファイルサイズが 4MB 以上になると、Azure のストレージはメッチャ高速になります。これは分析時には大きなメリット
            - High Throughput Blob Storage
                - https://azure.microsoft.com/en-us/blog/high-throughput-with-azure-blob-storage/#:~:text=claus%20joergensen%20principal%20program%20manager%2c%20azure%20storage%20i,the%20storage%20account%20limits%20for%20a%20single%20blob.
        - many small files 問題
            - Hadoop でおなじみ
            - ある程度のサイズのファイルにマージしましょうね。1 step 増えますが、クエリのパフォーマンスは最終的に早くなります。データ加工では、そのステップは前処理として惜しんではいけません。勿論、やりすぎには注意。Simple is ALWAYS best です。
- フォーム と Form Recognizer のカスタムモデルの紐づけ
    - 特定フォルダーには、1つのフォームしかこない前提です
- エラーハンドル
    - Azure Logic App では、一切エラー処理していません

他にもあると思いますが...😅

# Azure Form Recognizer でのカスタムフォームの作成

公式ドキュメントの請求書 (Invoice) のサンプルを使います。カスタムモデルなので、アノテーションが必須ですね。

https://azure.microsoft.com/en-us/blog/high-throughput-with-azure-blob-storage/#:~:text=claus%20joergensen%20principal%20program%20manager%2c%20azure%20storage%20i,the%20storage%20account%20limits%20for%20a%20single%20blob.

アノテーションは以下の感じ。

Form Recognizer サンプルツール:
https://fott-2-1.azurewebsites.net/

画面の右側に、ラベル付けの項目と値が表示されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/29d4fa8d-a36a-32fa-6eab-fbc0d6ca40f8.png)

この画面は Visual Object Tagging Tool (VoTT) なので、私には使い慣れたものですね。以下は VoTT です。こっちは Form Recognizer の処理では使いません。Pythonでコードを書いて行うような Computer Vision のアノテーションツールとしてご活用ください。
https://github.com/Microsoft/VoTT

以下が結果例ですね。テストデータでの推論結果のJSON文字列です。Debug で利用した部分以外は省略しています。
そして **Visual Studio Code** で **JSON 整形**するのが本当に便利😍

```json
{
    "version": "2.1.0",
    "readResults": [
{省略}
    ],
    "pageResults": [
{省略}
    ],
    "documentResults": [
        {
            "docType": "custom:invoice01",
            "modelId": "68aa71e0-ac0d-4668-a2fe-d25a52b6a00b",
            "pageRange": [
                1,
                1
            ],
            "fields": {
                "Sales tax": {
                    "type": "string",
                    "valueString": "3%",
                    "text": "3%",
                    "page": 1,
                    "boundingBox": [
                        7.27,
                        6.57,
                        7.465,
                        6.57,
                        7.465,
                        6.685,
                        7.27,
                        6.685
                    ],
                    "confidence": 0.995,
                    "elements": [
                        "#/readResults/0/lines/37/words/0"
                    ]
                },
                "Video Delivery": {
                    "type": "string",
                    "valueString": "X",
                    "text": "X",
                    "page": 1,
                    "boundingBox": [
                        3.98,
                        3.765,
                        4.165,
                        3.765,
                        4.165,
                        3.9,
                        3.98,
                        3.9
                    ],
                    "confidence": 0.802,
                    "elements": [
                        "#/readResults/0/lines/10/words/0"
                    ]
                },
                "total": {
                    "type": "string",
                    "valueString": "$10,686.25",
                    "text": "$10,686.25",
                    "page": 1,
                    "boundingBox": [
                        7.01,
                        6.835,
                        7.725,
                        6.835,
                        7.725,
                        6.965,
                        7.01,
                        6.965
                    ],
                    "confidence": 0.995,
                    "elements": [
                        "#/readResults/0/lines/39/words/0"
                    ]
                },
{省略}
            ]
        }
    ],
    "errors": []
}
```

# Azure Logic App でファイルがアップロードされたら、自動的に Azure Form Recognizer を呼び出す

特定フォルダーにファイルがアップロードされたら、自動的に Azure Form Recognizer が呼び出されるようにします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/9fad3347-0946-811f-351a-d6b8dcdf20d3.png)

幾つかのポイントを。

- box コネクターを使っています。Logic App がサポートしているものなら、何でも良いです。
    - https://docs.microsoft.com/ja-jp/connectors/box/
- ファイル作成時のイベントを拾った際には、大きなファイルの時  (例: Azure Video Analyzer for Media の様な動画を扱う)以外は、**[xxx ファイルコンテンツの取得]** を行ってください。認証処理を別途考慮になったりします。Azure Logic App は、 Azure App Serviceがベースです。つまり **Web Server です**。Web Serverで大きなデータは扱わないでください。
- Azure Form Recognizer の呼び出し時のモデルの識別子
    - Azure Form Recognizer のサンプルツールから、 [Model Compose] から参照します。Azure Portal から取得できないのですー😭
    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/68fc7246-9403-b883-ce03-373d90d1a110.png)
- Azure Data Lake Storage Gen2 への書き込みですが、Blob コネクターを使っています。Azure Logic App のAzure Data Lake コネクターは、**v1用なので使わない**でください。
    - https://docs.microsoft.com/ja-jp/connectors/azureblob/
- 出力データは、Form Recognizer の [analyzeResult]です。
    - https://docs.microsoft.com/ja-jp/connectors/formrecognizer/

これで、出力結果が Azure Data Lake Storage Gen2 へ保存されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/894b3a3c-384d-e437-3cc3-858e287caf23.png)

# Azure Synapse Analytics の SQL Serverless Pool を使ってクエリする

SQL Serverless Pool の良い点は、分析用途にマッチした価格とパフォーマンスです。SQL と Spark 環境がサクッと少量のデータでもメッチャ便利に使える点が良いです。

## Key-Value ペアの場合

まずは1:1の単純な Key-Value でのタグ付けをした場合を取り扱います。

Synapse Studio で、Data Lake Storage を開きます。

Azure Data Lake Storage Gen2 の当該フォルダーまで移動して、ファイルを選択。[新しいSQLスクリプト] から、[上位100件を選択]を参照します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/2e8a6527-15e5-3ccd-fa1b-e56e9e573392.png)

以下、debug 中の列も含めて、クエリの例を記載します。

```sql
SELECT
    result.filename(),
    jsonContent,
    JSON_VALUE(jsonContent, '$.version') as ver,
    JSON_VALUE(jsonContent, '$.analyzeResult.documents[0].fields."Sales tax".valueString') as sales tax,
    JSON_VALUE(jsonContent, '$.analyzeResult.documents[0].fields.total.valueString') as sales tax,
FROM
    OPENROWSET(
        BULK 'https://dahatakeformrecognition.dfs.core.windows.net/invoice-raw/Invoice_6.pdf.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]
```

実行結果:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/f421c5ed-110d-64c7-3751-daabe6e2a934.png)

出ましたね。

SQL は集合演算に強いわけです。
このフォルダーには、ファイルが複数件あります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/a6a0879c-8cc6-2383-ad7a-360a4aec3dcb.png)

このフォルダーを仮想テーブル化して、クエリをしてみましょう。
複数ファイルをまとめる方法は簡単で、ファイルパスにワイルドカードを使うだけです😊

ここでは[合計金額]の総額を出してみましょう。

```sql
SELECT
    sum(CAST(JSON_VALUE(jsonContent, '$.analyzeResult.documents[0].fields.total.valueString') as MONEY)) as total_money
FROM
    OPENROWSET(
        BULK 'https://dahatakeformrecognition.dfs.core.windows.net/invoice-raw/Invoice_*.pdf.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR ='0x0b',
        ROWTERMINATOR = '0x0b'
    )
    WITH (
        jsonContent varchar(MAX)
    ) AS [result]
```

結果:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/7d94592b-83c9-857c-1b4f-82f8333ea524.png)

簡単ですね!😊

SQL Serverless Pool の SQL エンジンは SQL Server のものです。ですので SQL Serverのドキュメントに詳細があります。Azure Synapse Analytics には一部しかないのでご注意ください😅

SQL Server の JSON データを構造化する:
https://docs.microsoft.com/ja-jp/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

以下、はまりそうなポイントを。

- valueが値 (スカラー) の場合は JSON_VALUEを。配列の場合は JSON_QUERYを
- JSONのプロパティ名に空白がある際には "" (ダブルクォーテーション) で囲む
- JSONの値が配列になっている際には、JSON_VALUE や JSON_QUERY のパス指定で、配列指定 ( [0].[1] など) をする
- もし、Form Recognizer で**日本語のタグ付け**をした場合には、日本語でのJSONクエリがうまくいかない事が殆どです。照合順序 (COLLATE) の問題です。手前味噌ですが、こちらを参考にして、SQL Serverless Pool に新規Databaseを作成して、そのDatabaseに照合順序を設定してください。

https://qiita.com/dahatake/items/3c0486faa901b387106e


## テーブル形式の場合

帳票ですから、毎回件数がわからない場合もありえます。その場合に Form Recognizer は**テーブル形式**で n件の取得が出来ます。

テーブル形式でタグ付けをした場合ですが。`tables` の中に結果が出力されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/72bc0710-71f7-3325-e39f-83acf25a24b7.png)

構造化データとして扱うには、ちょっと困った点があって。

- テーブル毎に `table` が作成される
    - 帳票なので、テーブルが幾つあるかは決め打ちでもいいかもしれません。
 
- ヘッダーも値として含まれる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/10930ef3-db74-6a39-10c4-56926353445e.png)

- 行という概念がなく、全てが Cell の content として出力されている

この図の**値**としてほしいのは **Manager Training** なんです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/58008120-ece7-cd68-3ce7-3773fe2eeb0b.png)

ちょっと複雑なんですが、以下の SQL で「テーブル」として取得できます。

```sql
select
     tmp.rowid
    ,max(case row_inner_value when 0 then tmp.value else null end) as Training_Date
    ,max(case row_inner_value when 1 then tmp.value else null end) as Description
    ,max(case row_inner_value when 2 then tmp.value else null end) as Price
    ,max(case row_inner_value when 3 then tmp.value else null end) as Discount
    ,max(case row_inner_value when 4 then tmp.value else null end) as Line_total
from 
(
    SELECT
        c.[key] / 5 as rowid,
        c.[key] % 5 as row_inner_value,
        cells.[value] as value
    FROM
        OPENROWSET(
            BULK 'https://dahatakedatalake.dfs.core.windows.net/form/Invoice_6.pdf.json',
            FORMAT = 'CSV',
            FIELDQUOTE = '0x0b',
            FIELDTERMINATOR ='0x0b',
            ROWTERMINATOR = '0x0b'
        )
        WITH (
            jsonContent varchar(MAX)
        ) AS [result]
        CROSS APPLY OPENJSON(JSON_QUERY(jsonContent, '$.tables[0].cells')) as c
        CROSS APPLY OPENJSON(c.[value], '$') AS cells
    where
        cells.[key] = 'content'
) as tmp
WHERE
    tmp.rowid > 0
group by
    tmp.rowid

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/0248ed0d-0bc3-4323-3866-88e1b82c9284.png)

これは説明が必要かと思います。

- テーブルでは 5 列を定義しました。固定です。
- まずは内側のSelect文を見てください。5で割り算をして、かつ、剰余も取得しています。以下の様に行列を作成しています。

    - ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/85be420b-a9ec-3fba-ea17-1ed9ba81b5f7.png)
    

| 列名 | 意味 |
| -- | --- |
| rowid | テーブルでの行番号 |
| row_inner_value | テーブルでの列の位置 | 
| value | 抽出したい値です |

- ヘッダー行は不要なので
    -   `tmp.rowid > 0`
    - としています
- 元の1行を集約するために `Group By` を使っています
- 外側の Select 文では、case を使って、0-4 までの剰余を元に、テーブルでの列の位置とマッピングをしています。
- 帳票内にテーブルを複数定義した場合は、内側の　Select 文の中の JSON_QUERY の `$.tables[0]` の番号を変更してください。

SQL文作成にあたって、この2つのURLを参考にしました。ありがたし...

https://www.mssqltips.com/sqlservertip/4138/advanced-json-techniques-in-sql-server-part-3/
https://dev.classmethod.jp/articles/sql-data-horizontal-vertical/

# まとめ

これで、PDFや画像などの大量の帳票ファイルがあっても、一気に構造化データをするきっかけが出来ると思います。
この処理の良い点は、コードをほぼ書いていません。SQL くらい😊

Production にするにあたっての残作業は勿論ありますが、コア部分は上記で全てと言えます。
しかも Full Serverless 😊

- Azure Logic App
    - (モジュールとして呼ばれる) Form Recognizer
- Azure Synapse Analytics : SQL Serverless Pool

既存の紙や、ファイルサーバーのファイルは先人が蓄積した**資産**です。それを**データ化**する事は大事です。検索ができ、構造化データからは何がいつどのように起こっていたのかなど 5W1H が理解できます。

楽しんでください!