BigQuery へのメタデータのエクスポート

BigQuery コネクタの概要

BigQuery コネクタを使用すると、Document AI Warehouse に保存されているドキュメントのメタデータ（プロパティを含む）を BigQuery テーブルにエクスポートできます。BigQuery にデータを取り込むと、分析を実行し、レポートやダッシュボードを作成して、ビジネス上の意思決定に役立てることができます。

BigQuery コネクタを有効にするには、必要な権限が付与された BigQuery テーブルを設定し、API を介して非同期タスクを構成する必要があります。BigQuery コネクタは、Document AI Warehouse から BigQuery テーブルにデータをエクスポートします。

始める前に

Document AI ウェアハウスを設定し、ドキュメントを取り込みます。詳しくは、クイック スタートをご覧ください。

BigQuery テーブルをホストするプロジェクトが、Document AI Warehouse でドキュメントの保存に使用されるプロジェクトと同じであることを確認する必要があります。つまり、データは常に同じプロジェクト内の Document AI Warehouse から BigQuery テーブルにエクスポートする必要があります。

プロジェクトで Owner（roles/owner）ロールが付与されているか、resourcemanager.projects.getIamPolicy と resourcemanager.projects.setIamPolicy の両方が付与されている必要があります。

権限を付与する。

BigQuery Access を設定する

サービス アカウント doc-ai-warehouse-dw-bq-connector@system.gserviceaccount.com をバインドする

BigQuery Admin ロールに付与します。

gcloud projects add-iam-policy-binding <var>PROJECT_ID</var> --member serviceAccount:doc-ai-warehouse-dw-bq-connector@system.gserviceaccount.com --role=roles/bigquery.admin

BigQuery データセットとテーブルを設定する

Document AI Warehouse がデータをエクスポートするための BigQuery データセットとテーブルを設定します。BigQuery データセットがない場合は、データセットの作成に沿って作成します。

BigQuery データセットに BigQuery テーブルを作成します。BigQuery の手順に沿って、DDL サンプル ステートメントを使用してテーブルを作成します。

CREATE TABLE `PROJECT_ID.DATASET_NAME.TABLE_NAME`
(
  project_number INT64,
  location STRING,
  mod_type STRING,
  document_id STRING,
  document_json JSON,
  create_time TIMESTAMP,
  creator STRING,
  update_time TIMESTAMP,
  updater STRING,
  document_state STRING,
  export_time TIMESTAMP
)
PARTITION BY TIMESTAMP_TRUNC(export_time, HOUR)
OPTIONS(
  partition_expiration_days=150,
  description="table partitioned by export_time on hour with expiry"
);

DDL によって、新しい BigQuery テーブルが作成されます。テーブルは 1 時間ごとにパーティション分割され、パーティションは 150 日後に削除されます。

BigQuery コネクタを構成する

データ エクスポート構成を作成する

次の手順では、新しいデータ エクスポート ジョブを作成します。これにより、データをエクスポートする非同期ジョブが設定されます。新しいデータ エクスポート ジョブごとに空のテーブルから始めることをおすすめします。構成の詳細については、API リファレンスをご覧ください。

次の実行オプションがあります。これらは FREQUENCY を使用して構成できます。API リファレンスを参照してください。

  • ADHOC: ジョブは 1 回のみ実行されます。すべてのデータが BigQuery テーブルにエクスポートされます。
  • DAILY: ジョブは毎日実行されます。初回実行では、すべてのデータが BigQuery テーブルにエクスポートされます。初回のエクスポートが完了すると、前日のデータ変更（または最後に成功した同期からの差分）のみが BigQuery テーブルにエクスポートされます。
  • HOURLY: ジョブは 1 時間ごとに実行されます。初回実行では、すべてのデータが BigQuery テーブルにエクスポートされます。初回のエクスポートが完了すると、前時間のデータ変更（または最後の同期が成功した時点からの差分）のみが BigQuery テーブルにエクスポートされます。

リクエストのデータを使用する前に、次のように置き換えます。

  • PROJECT_NUMBER: Google Cloud プロジェクトの番号
  • LOCATION: Document AI ウェアハウスのロケーション（`us` など）
  • DATASET_LOCATION: データセットのロケーション
  • DATASET_NAME: データセット名
  • TABLE_NAME: テーブル名
  • FREQUENCY: ADHOC、DAILY、HOURLY のいずれか。

リクエストの本文（JSON）: 

{
  "projectNumber": PROJECT_NUMBER,
  "location": "DATASET_LOCATION",
  "dataset": "DATASET_NAME",
  "table": "TABLE_NAME",
  "frequency": "FREQUENCY",
  "state": "ACTIVE"
}

リクエストを送信するには、次のいずれかのオプションを展開します。

次のような JSON レスポンスが返されます。

ジョブの実行

ジョブが正常に作成されると、構成に基づいてジョブが実行されます。ジョブの実行には時間がかかるため、ジョブは非同期で実行されます。エクスポートするデータの量によっては、初回実行の完了に時間がかかることがあります。毎日のジョブの場合、結果が BigQuery テーブルに表示されるまで 24 時間かかります。

データ エクスポート構成を削除する

次のコマンドは、作成したジョブを削除（アーカイブ）します。

リクエストのデータを使用する前に、次のように置き換えます。

  • PROJECT_NUMBER: Google Cloud プロジェクトの番号
  • LOCATION: Document AI ウェアハウスのロケーション（`us` など）
  • JOB_ID: ジョブ ID（作成時のレスポンスに含まれます）

リクエストの本文（JSON）: 

{}

リクエストを送信するには、次のいずれかのオプションを展開します。

次のような JSON レスポンスが返されます。

その後、エクスポート ジョブは削除（アーカイブ）され、Document AI Warehouse は実行されなくなります。

BigQuery に取り込まれたデータを探索する

分析のニーズに合わせてドキュメントのメタデータとプロパティを BigQuery の個別のテーブル フィールドに抽出するには、次のサンプル DDL クエリを使用します。抽出されたフィールドは、Looker Studio や BI ダッシュボード ツールでデータ内の関係を可視化するためにも使用できます。

document_json からキーフィールドを抽出する

このクエリは、ドキュメント メタデータ（document_json フィールドに保存）のキーフィールドなど、データ エクスポートから関連するフィールドを選択します。

DROP VIEW IF EXISTS
 `DATASET_NAME.VIEW_NAME_1`;
CREATE VIEW
 `DATASET_NAME.VIEW_NAME_1` AS
SELECT
 project_number,
 document_id,
 mod_type,
 create_time,
 update_time,
 location,
 creator,
 updater,
 document_state,
 SPLIT(JSON_EXTRACT_SCALAR(document_json,'$.documentSchemaName' ), '/')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(JSON_EXTRACT_SCALAR(document_json,'$.documentSchemaName' ), '/')) - 1)] AS document_schema_name,
 JSON_EXTRACT_SCALAR(document_json,'$.name') AS document_name,
 JSON_EXTRACT_SCALAR(document_json,'$.rawDocumentFileType')
AS raw_document_file_type,
 JSON_EXTRACT(document_json,'$.properties') AS properties
FROM
 `DATASET_NAME.SYSTEM_METADATA_AND_DOC_PROPERTIES_TABLE_EXPORT_NAME`;

document_json からプロパティのネストを解除する

このクエリは、ドキュメント メタデータ（document_json）からプロパティのネストを解除して、キーと値のペア（プロパティ名、値）を作成します。これらの Key-Value ペアは、次のクエリで個々のテーブル フィールドに変換され、プロパティ レベルのデータ探索とダッシュボードの可視化が可能になります。

DROP VIEW IF EXISTS
 `DATASET_NAME.VIEW_NAME_2`;
CREATE VIEW
 `DATASET_NAME.VIEW_NAME_2` AS
SELECT

    *   EXCEPT(key_value_pair,
   properties,raw_document_file_type)
FROM (
SELECT
 *,
 REPLACE(JSON_VALUE(key_value_pair,'$.name'),'/','-') property_name,
 -- Note: values are either text OR float values
 CASE
   WHEN JSON_VALUE(key_value_pair,'$.textValues.values[0]') IS NULL THEN JSON_VALUE(key_value_pair,'$.floatValues.values[0]')
 ELSE
 JSON_VALUE(key_value_pair,'$.textValues.values[0]')
END
 AS value,
 CASE
   WHEN raw_document_file_type IS NULL THEN "RAW_DOCUMENT_FILE_TYPE_UNSPECIFIED"
 ELSE
 raw_document_file_type
END
 AS document_file_type
FROM
 `DATASET_NAME.VIEW_NAME_1`,
 UNNEST(JSON_EXTRACT_ARRAY(properties)) AS key_value_pair);

document_json からプロパティをピボットして BigQuery にテーブル フィールドを作成する

次の手順では、プロパティと関連する値をピボットして、すべてのドキュメント プロパティを個々のテーブル フィールドとして変換したテーブルを作成します。このテーブルの結果を活用して、Looker Studio や他の BI 可視化ツールの後続のクエリでさらに分析情報を導き出すことができます。

DECLARE
 property_field STRING;
-- Extracting distinct property_names from the previous view and storing it in property_field, declared above

EXECUTE IMMEDIATE
 """SELECT string_agg(CONCAT("'",property_name,"'")) from (select distinct property_name from DATASET.VIEW_NAME_2)""" INTO property_field;

DROP TABLE IF EXISTS `DATASET_NAME.ANALYTICS_TABLE_NAME`;
-- Creating pivot table with the aid of extracted distinct property_names
-- Casting numerical values to float/int
-- Pivot on property_name and value (ie. create a new column for each of the property_name, substitute the value)

EXECUTE IMMEDIATE
 FORMAT ("""
CREATE TABLE `DATASET_NAME.ANALYTICS_TABLE_NAME` AS
SELECT * FROM `DATASET_NAME.VIEW_NAME_2`
PIVOT(min(value) FOR property_name IN (%s))""", property_field);

データ クリーニングと変換の手順（ビジネスケース固有）

BigQuery に取り込まれたデータによっては、詳細な分析を行うために、追加のデータ クリーニングと変換の手順が必要になることがあります。このような手順はケース（データセット）ごとに異なり、適宜実行する必要があります。

データ クリーニングの手順の例としては、次のようなものがあります（これらに限定されません）。

  • 日付形式の統一。
  • プロパティ値を統合します。
  • たとえば、データ型を文字列、浮動小数点数、整数にキャストします。

Looker Studio でデータを可視化する

BigQuery でデータの抽出、クレンジング、変換が完了したら、最終的なデータセットを Looker Studio にエクスポートして、分析を可視化できます。

Looker ダッシュボード

概要で説明したサンプル ダッシュボードは、データセットから作成できるビジュアリゼーションの例を示しています。このシナリオでは、Document AI Warehouse からのエクスポート サンプルデータは W2 と請求書（2 つのスキーマ）で構成されています。

一般公開されている Looker ダッシュボード

サンプルビュー: Document AI ウェアハウスの分析の概要

次のダッシュボードでは、Document AI Warehouse インスタンスに取り込まれたさまざまなドキュメントの概要を確認できます。

Looker ダッシュボード 1

ドキュメント レベルの詳細（以下を含む）を表示できます。

  • ドキュメントの合計数。
  • ドキュメント スキーマの合計数。
  • ドキュメント スキーマ別のレコード数。
  • ドキュメント ファイル形式（PDF、テキスト、指定なしなど）。

また、ドキュメント メタデータ（document_json）から抽出したプロパティを使用して、BigQuery に取り込まれた請求書と W-2 のキーブレークダウンを構築することもできます。

サンプルビュー: ビジネス固有の分析情報ダッシュボード（請求書）

次のダッシュボードでは、単一のドキュメント スキーマ（請求書）の詳細を確認し、Document AI Warehouse に取り込まれたすべての請求書に関する分析情報を取得できます。

Looker ダッシュボード 2

請求書でスキーマ固有の詳細を確認できます。たとえば、次の情報が表示されます。

  • 請求額で上位のサプライヤー。
  • 地域別のサプライヤー。
  • 請求日とそれに対応するお支払い期限。
  • 請求書の金額とレコード数の月ごとの推移。

データソースをダッシュボードに接続する

これらのダッシュボード サンプルをデータセットの可視化の出発点として使用するには、BigQuery からデータソースを接続します。

サンプル ダッシュボードを BigQuery データソースに接続する前に、 Google Cloud環境に関連付けられているアカウントにログインしていることを確認してください。

looker dashboard three

ハイライト表示されたボタンを選択すると、プルダウン オプションが表示されます。

looker ダッシュボード 4

[コピーを作成] を選択します。

looker dashboard five

[新しいデータソース] サブセクションで、[データソースを作成] を選択します。

Looker ダッシュボード 6

[BigQuery] を選択します。

looker ダッシュボード 7

データセットが保存されているプロジェクトを選択し、プロンプトに沿ってデータセットとテーブルを選択します。[接続] をクリックします。

Looker ダッシュボード 8

[レポートに追加] をクリックします。

Looker ダッシュボード 9

[レポートをコピー] をクリックします。

Looker ダッシュボード 10

ダッシュボードのウィジェットを編集して更新する場合は、抽出されたプロパティを含むダッシュボードのコピーがあるため、編集できます。