プリザンター×Metabase データ分析基盤の構築②＜ビュー作成処理編＞

概要

前回の①システム構成編では、Metabaseの直接データベース接続によるアクセス権などの問題を解決し、セキュリティを確保したデータ分析基盤の全体像を説明しました。今回は、データ分析基盤の重要な要素の一つであるPowerShellとタスクスケジューラによるビュー作成処理について詳しく解説します。

シリーズ構成

• ①システム構成編：全体構成とアーキテクチャの紹介
• ②ビュー作成処理編（本記事）：PowerShellとタスクスケジューラによるビュー作成処理
• ③データ同期処理編：データ同期処理の仕組みとバックグラウンドサーバスクリプト

プリザンターの「ClassA」「NumA」といった汎用カラムを、Metabaseで分析しやすい意味のある名前に変換する仕組みをご紹介します。

ビュー作成が必要な理由

第1回で説明したように、データ分析用環境を構築してアクセス権などの問題は解決しましたが、プリザンターの汎用的なテーブル構造（ClassA、NumAなど）がそのままではMetabaseで分析しづらいという課題が残っていました。

PowerShellスクリプトによる自動化

そこで、タスクスケジューラで1分ごとに実行されるPowerShellスクリプト（PleasanterViewCreatorMetabaseSyncSchema.ps1）により、Metabase用のビューを自動生成する仕組みを実装しました。

自動化の仕組み

1. タスクスケジューラ: 1分ごとに定期実行
2. PowerShellスクリプト: ビュー作成とMetabaseスキーマ同期を自動実行
3. エラーハンドリング: 重複した列名やSQL実行エラーを適切に処理

プリザンターのデータ構造

汎用テーブル構造の特徴

プリザンターは汎用的なデータベースアプリケーションとして設計されているため、以下の特徴があります。

• 汎用カラム構造: ClassA〜ClassZ、NumA〜NumZ、DateA〜DateZ などの汎用カラム名
• サイト情報: Sitesテーブルでサイト設定情報を管理
• JSON形式データ: テーブルの管理で設定した各種設定値の情報をJSON形式で保存

ビューの作成方針

PowerShellスクリプトは、プリザンターのサイト設定を読み取って、汎用カラム名を表示名（日本語の列名）に変換するビューを作成します。

1. サイト設定の読み取り

• SitesテーブルのSiteSettingsカラムからエディタの設定と表示名の情報を取得

2. 表示名（日本語の列名）の付与

• ClassA、NumA などの汎用カラム名をサイト設定で定義された表示名（日本語名）に変換

3. サイト単位でのビュー作成

• 各サイトごとに [サイトタイトル（サイトID）] 形式でビューを作成

PowerShellによる実装

スクリプトの処理内容

プリザンターの公式マニュアル「FAQ：日本語のラベルテキストを持つビューを作成したい」を参考に、PowerShellスクリプトを作成しました。

スクリプトは以下の流れでビューを作成します。

1. Sitesテーブルからサイト情報を取得: サイト種別が「Issues」「Results」のサイトを抽出
2. SiteSettingsのJSON解析: 各サイトの設定からエディタの設定（EditorColumns）と表示名（LabelText）の情報を取得
3. SQL文の生成と実行: 日本語列名を持つビューを [サイトタイトル（サイトID）] 形式で作成
4. Metabase APIの呼び出し: スキーマ同期を実行してMetabaseに反映

生成されるビューの例

-- 例：サイトのタイトルが「商談管理」、サイトIDが「1001」の場合
CREATE VIEW [商談管理（1001）]
AS
    SELECT [Title] as [タイトル], [ClassA] as [顧客名], [NumA] as [金額]
    FROM [Issues]
    WHERE [SiteId]=1001;

追加実装したエラーハンドリング

公式マニュアルのサンプルコードをベースに、実際の運用で発生したエラーに対応する処理を追加で実装しました。

• 重複列名エラー: 同じ日本語名が複数のカラムに設定されている場合のスキップ処理
• SQL実行エラー: ビュー作成失敗時のエラーメッセージ出力
• 空のカラムリスト: 有効なカラムがない場合のスキップ処理

実装したスクリプト

以下がPowerShellで実装したスクリプトです。保守性向上のためDB接続情報やAPIキーなどの設定値は上部にまとめ、エラーハンドリングのほか、Metabaseのスキーマを同期する処理を追加で実装しています。

PleasanterViewCreatorMetabaseSyncSchema.ps1

##################################################
# 設定値（運用保守時はここを編集）
##################################################
# SQL Serverデータベース接続設定
[string]$SqlServerName = "(local)"
[string]$DatabaseName = "Implem.Pleasanter"
[string]$SqlUserName = "sa"
[string]$SqlPassword = "****"

# Metabase API設定
[string]$MetabaseBaseUrl = "http://localhost:3000/api"
[string]$MetabaseApiKey = "****"
[int]$MetabaseDatabaseId = 2

##################################################
# 1. プリザンターのサイトのビューを作成する
##################################################
[object]$ConnectionString = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder
[object]$ConnectionString['Data Source'] = $SqlServerName
[object]$ConnectionString['Initial Catalog'] = $DatabaseName
[object]$ConnectionString['UID'] = $SqlUserName
[object]$ConnectionString['PWD'] = $SqlPassword

[object]$ColumnNames = @{
    "IssueId" = "ID"
    "ResultId" = "ID"
    "Ver" = "バージョン"
    "Title" = "タイトル"
    "Body" = "内容"
    "StartTime" = "開始"
    "CompletionTime" = "完了"
    "WorkValue" = "作業量"
    "ProgressRate" = "進捗率"
    "Status" = "状況"
    "Manager" = "管理者"
    "Owner" = "担当者"
    "Comments" = "コメント"
    "CreatedTime" = "作成日時"
    "UpdatedTime" = "更新日時"
    "Creator" = "作成者"
    "Updator" = "更新者"
}
[object]$ExcludeNames = @(
    "RemainingWorkValue"
)

function Get-Site()
{
    [string]$SQLQuery = "select * from [Sites] where [ReferenceType] in ('Issues', 'Results');"
    [object]$ResultsDataTable = New-Object System.Data.DataTable
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    [object]$ResultsDataTable.Load($SqlCommand.ExecuteReader())
    [object]$SqlConnection.Close()
    return $ResultsDataTable
}

function Mainte-View($Row)
{
    [object]$Ss = ConvertFrom-Json ([string]$row.SiteSettings)
    [object]$SqlColumns = New-Object System.Collections.Generic.List[string]
    [object]$EditorColumns = $ss.EditorColumns
    if ($EditorColumns.Count -eq 0)
    {
        $EditorColumns = New-Object System.Collections.ArrayList
        $EditorColumnHash = (ConvertFrom-JsonPSCustomObjectToHash $ss.EditorColumnHash)
        foreach ($Key in $EditorColumnHash.Keys)
        {
            foreach ($Value in $EditorColumnHash[$Key])
            {
                [int]$Ret = $EditorColumns.Add($Value)
            }
        }
    }
    foreach ($ColumnName in $EditorColumns)
    {
        if ( -not (
            ($ColumnName -match '_Section-[\d]') -or
            ($ColumnName -match '_Links-[\d]')))
        {
            [string]$Alias = ""
            foreach ($Column in $Ss.Columns)
            {
                if ($Column.ColumnName -eq $ColumnName)
                {
                    $Alias = $Column.LabelText
                }
            }
            if ($Alias -eq "")
            {
                $Alias = $ColumnNames[$ColumnName]
            }
            if ($Alias -eq "")
            {
                $Alias = $ColumnName
            }
            if ($ExcludeNames -notcontains $ColumnName)
            {
                # 項目の表示名に ] が含まれる場合のSQL構文エラー対策
                $EscapedAlias = $Alias -replace '\]', ']]'
                $SqlColumns.Add("[$ColumnName] as [$EscapedAlias]")
            }
        }
    }
    [string]$JoinedSqlColumns = $SqlColumns -join ","
    # カラム未定義サイトでのSQL構文エラー対策
    if ($JoinedSqlColumns -ne "")
    {
        Delete-View $Row
        Create-View $Row $JoinedSqlColumns
    }
    # 空のカラムリストの場合はスキップ
    else
    {
        "ビュー作成スキップ：" + $Row.Title + "（" + $([long]$row.SiteId) + "）- カラムが空"
    }
}

function Create-View($Row, $JoinedSqlColumns)
{
    "ビュー作成：" + $Row.Title + "（" + $([long]$row.SiteId) + "）"
    [string]$SQLQuery = @"
        create view [$([string]$Row.Title)（$([long]$row.SiteId)）]
        as
            select $JoinedSqlColumns
            from [$([string]$Row.ReferenceType)]
            where [SiteId]=$([long]$row.SiteId);
"@
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    
    # 列名重複エラーなどのSQL実行エラーをハンドリング
    try {
        [int]$Ret = $SqlCommand.ExecuteNonQuery()
    }
    catch {
        # 列名重複エラーの判定
        if ($_.Exception.Message -match "列名.*が複数指定されています|列名.*一意にしてください") {
            "ビュー作成スキップ：" + $Row.Title + "（" + $([long]$row.SiteId) + "）- 列名重複エラー（項目の表示名を重複しないように設定してください）"
        }
        # その他のSQLエラー
        else {
            "ビュー作成エラー：" + $Row.Title + "（" + $([long]$row.SiteId) + "）- " + $_.Exception.Message
        }
    }
    finally {
        [object]$SqlConnection.Close()
    }
}

function Delete-View($Row)
{
    [string]$SQLQuery = @"
        if exists (select 1 from sysobjects where id = object_id('$([string]$row.Title)（$([long]$row.SiteId)）'))
        drop view [$([string]$row.Title)（$([long]$row.SiteId)）];
"@
    [object]$SqlConnection = New-Object System.Data.SQLClient.SQLConnection($ConnectionString)
    [object]$SqlCommand = New-Object System.Data.SQLClient.SQLCommand($SQLQuery, $SqlConnection)
    [object]$SqlConnection.Open()
    [int]$Ret = $SqlCommand.ExecuteNonQuery()
    [object]$SqlConnection.Close()
}

Function ConvertFrom-JsonPSCustomObjectToHash($obj)
{
    $hash = @{}
    # EditorColumnHashがnullの場合のGet-Memberエラー対策
    if ($obj -ne $null)
    {
        $obj | Get-Member -MemberType Properties | SELECT -exp "Name" | % {
            $hash[$_] = ($obj | SELECT -exp $_)
        }
    }
    $hash
}

[object]$Table = Get-Site
foreach ($Row in $Table)
{
    Mainte-View $Row
}

##################################################
# 2. Metabaseのスキーマを同期する
##################################################
$headers = @{
    "x-api-key" = $MetabaseApiKey
}
$endpoint = "$MetabaseBaseUrl/database/$MetabaseDatabaseId/sync_schema"

try {
    $response = Invoke-RestMethod -Uri $endpoint -Method Post -Headers $headers
    Write-Host "Schema sync initiated successfully."
} catch {
    Write-Host "Failed to initiate schema sync: $_"
    if ($_.Exception.Response -ne $null) {
        $errorResponse = $_.Exception.Response.GetResponseStream()
        $reader = New-Object System.IO.StreamReader($errorResponse)
        $reader.BaseStream.Position = 0
        $responseBody = $reader.ReadToEnd()
        $reader.Close()
        Write-Host "Error details: $responseBody"
    }
}

スクリプト実行後、SSMSでビューが作成されていればOKです。Metabase側でもビューが参照できることを確認してください。

タスクスケジューラの設定

タスクスケジューラの設定についても画面キャプチャを貼っておきます。下記のxmlは設定後にエクスポート出力した内容です。

PleasanterViewCreatorMetabaseSyncSchema.xml

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>[作成日時]</Date>
    <Author>[サーバー名]\[ユーザーID]</Author>
    <URI>\PleasanterViewCreatorMetabaseSyncSchema</URI>
  </RegistrationInfo>
  <Triggers>
    <TimeTrigger>
      <Repetition>
        <Interval>PT1M</Interval>
        <StopAtDurationEnd>false</StopAtDurationEnd>
      </Repetition>
      <StartBoundary>[開始日時]</StartBoundary>
      <Enabled>true</Enabled>
    </TimeTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>[ユーザーSID]</UserId>
      <LogonType>Password</LogonType>
      <RunLevel>LeastPrivilege</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>PT0S</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe</Command>
      <Arguments>-ExecutionPolicy Bypass -Command ".\PleasanterViewCreatorMetabaseSyncSchema.ps1"</Arguments>
      <WorkingDirectory>C:\web\pleasanter\Tools</WorkingDirectory>
    </Exec>
  </Actions>
</Task>

Metabaseでの表示比較

ビュー作成前 vs ビュー作成後

PowerShellスクリプトによるビュー作成処理の効果を、Metabaseでの実際の表示で比較してみましょう。

ビュー作成前：Metabaseでプリザンターの汎用テーブルを表示した画面

問題点：

• ClassA、NumA、DateAなどの汎用カラム名で内容が理解しにくい
• サイトが混在して表示され、どのテーブルのデータか分からない

ビュー作成後：Metabaseで分析用ビューを表示した画面

改善点：

• 汎用カラム名がサイト設定で定義された日本語名に変換されている
• サイトごとに整理されたビューで、対象データが明確になっている

ビューを作成したことで、プリザンターの画面で普段見ているテーブルと同じイメージでMetabaseから見られるようになりました！

プリザンターと同じ感覚でデータを見ることができて、いろんな種類のグラフが使えるようになりました🎉

Metabaseでの分析効率の向上

自動ビュー作成により、Metabaseでの分析作業を効率化することができました。

分析作業の効率化

• 汎用テーブル：テーブルの管理画面から表示名を確認する必要
• 分析用ビュー：日本語列名により直感的に分析可能

クエリ作成の容易さ

• 汎用テーブル：複雑なクエリを作成する技術的スキルが必要
• 分析用ビュー：GUIでの簡単な操作だけで可視化することが可能

おわりに

PowerShellとタスクスケジューラを活用した自動ビュー作成により、プリザンターの汎用テーブルをMetabaseで扱いやすくする仕組みをご紹介しました。③データ同期処理編では、バックグラウンドサーバスクリプトとデータ同期管理テーブルによる本番環境からデータ分析用環境へのデータ同期処理について詳しく解説します。次回もお楽しみに！

参考リンク

• プリザンター公式サイト
• プリザンター公式マニュアル - FAQ：プリザンターに設定した日本語の表示名でデータベースのビューを作成したい
• Metabase公式サイト