はじめに
このエントリは、Looker Advent Calendar 2022における17日目の記事です。 本日は、基幹システムのような歴史的な蓄積がある既存のデータを後付けで可視化する際に問題となる項目のラベリングに関するノウハウを説明します。
蓄積のあるデータベースに対してLookerのようなBIツールを直接接続するだけではうまく分析できません。 それには、様々な理由があります。その中の一つはテーブル名やカラム名がシステム開発にとって都合の良い表現になっており、BIツールにとって都合の良い表現にはなっていないことです。
例えば、以下のように既存のデータベースから何も変更せずにLookerにテーブルをインポートすると以下のようになります。
テーブル名やカラム名にアルファベットとアンダースコアのみを使ってテーブルを定義することになっているシステムは多いんじゃないでしょうか。これは、現代的なウェブシステムの多くがエンドユーザーに対して直接データベースへのアクセスを許可するような構造にはなっていないからです。
BIツールで使い易いテーブルについて
BIツールを使ってデータベースをエンドユーザーが直接見に行くことを前提に、使い易いテーブルとはどんなものか考えてみましょう。
まずテーブル名やカラム名が問題領域に即した日本語表記になっていることは必須でしょう。そして、カラム名については、その意味や意図を簡易的に参照できるとより望ましいですね。
また、実装の都合で場合分けするために保存されている項目については、無味乾燥な数字やアルファベットではなく論理的な名称になっていることが望ましいでしょう。
例えば、私たちのシステムには、ms_biz_prtnrというテーブルがあり、 hojin_kbn というカラムが含まれています。そこには、01、02、03などの値が保存されます。正直言って、このままでは意味が分かりませんよね。
これは取引先マスタというテーブルにある法人区分というカラムの話をしています。そして三つの値は、それぞれ 株式会社、有限会社、財団法人を意味しています。
使い込まれた基幹システムには、恐らく数百から数千のテーブルがあり、その中に含まれるカラムは延べ数千にも及ぶでしょう。それをBIツールで分析できるようにするために、このような変換処理を手動で行っていると時間がいくらあっても足りません。 当然、基幹システム側は変化する会社の状況に応じて変更されていくわけですから、BIツールの都合でデータベースに対する変更を止められるはずもありません。
では、どうするのがいいでしょうか？
設計ドキュメントからBIツールのモデルファイルを自動生成する
あまりに多くのテーブルを手作業でBIツール用に再定義していては、データ分析による価値を生みだす前にコストがかかりすぎてしまいます。分析環境の構築に大きなコストをかけてしまうと、それだけ大きな期待がかかります。
小さく始めて、徐々に大きな成果に結びつけるならできるかぎりコストをかけずに環境を作るのが望ましいでしょう。また、様々なBIツールが市場にはありますので、最適なものを目指して乗り換えていけるようにしたいものです。
というわけで、既存の設計成果物であるデータベース設計書からBIツール用のモデルファイルを自動生成しましょう。ISIDの場合は、テーブル設計書というExcelで記述された大量のファイルがありましたので、これを使います。
このファイルフォーマットについては詳細を説明しませんが、何かを感じ取って貰えると嬉しいです。
モデルファイルのフォーマットはできるかぎりテキスト形式のもので、その形式に関して仕様が公開されているものを使うのが良いでしょう。この条件を満たすBIツールとしてLookerがあります。
LookerのLookMLはyamlによく似たテキスト形式のファイルなので、gitやGitHubを使って簡単に構成管理できます。
特に説明はしませんが、LookMLに対するイメージを持ってもらうために少しだけ例をお見せしましょう。
- ecommercestore.model.lkml
connection: order_database
include: "*.view.lkml"
explore: orders {
join: customers {
sql_on: ${orders.customer_id} = ${customers.id} ;;
}
}
- orders.view.lkml
view: orders {
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;;
}
dimension: customer_id {
sql: ${TABLE}.customer_id ;;
}
dimension: amount {
type: number
value_format: "0.00"
sql: ${TABLE}.amount ;;
}
dimension_group: created {
type: time
timeframes: [date, week]
sql: ${TABLE}.created_at ;;
}
measure: count {
type: count # creates sql COUNT(orders.id)
sql: ${id} ;;
}
}
見ての通りごく単純な構造のテキストファイルです。単純な文字列処理ができれば、このファイルを自動生成できることがお分かりいただけることでしょう。
LookMLのさらに詳細な説明は公式のドキュメントが分かり易いのでおすすめです。
LookMLのラベル機能
自動生成するにあたって、押さえておくべきLookMLの機能を紹介しましょう。
まずは、ラベリング機能です。
といっても、それほど難しいことはありません。viewやdimensionにはlabelというフィールドがあるので、そこに分かり易い名前を指定するだけです。
view: ms_biz_prtnr {
label: "取引先マスタ"
sql_table_name: `MS_BIZ_PRTNR`;;
dimension: biz_prtnr_cd {
label: "取引先コード"
type: string
sql: ${TABLE}.biz_prtnr_cd;;
}
ラベルを使うとこのようになります。分かり易くなりますね。
ラベルについてより詳細な仕様を知りたい方は公式ドキュメントをご覧ください。
caseによるラベリング
テーブル名やカラム名のラベリングが分かったら今度は値のラベリングです。
ここで使うのは、caseとwhenを組み合わせた条件分岐です。
view: ms_biz_prtnr {
label: "取引先マスタ"
sql_table_name: `MS_BIZ_PRTNR`;;
dimension: hojin_kbn {
label: "法人区分"
type: string
case: {
when: {
sql: ${TABLE}.hojin_kbn = "01";;
label: "株式会社"
}
when: {
sql: ${TABLE}.hojin_kbn = "02";;
label: "有限会社"
}
when: {
sql: ${TABLE}.hojin_kbn = "03";;
label: "財団法人"
}
}
}
それぞれの値に対して、どんなラベルを表示するのか設定していますね。これを元にLookerはSQLのcase句を生成します。
ラベルのありなしで、どんな風に表示が変わるのか見てみましょう。データを見る人が良く使うであろう検索フィルターの表示を抜き出してあります。
ラベルを設定しない場合には、データベースに保存されている具体的な値である01や02といった値がどんな意味を持つのか詳細に理解していないとデータをフィルターできません。
一方でラベルを設定してあれば、候補の中から選ぶだけなので使い易くなります。
caseによるラベリングについてより詳細な仕様を知りたい方は公式ドキュメントをご覧ください。
LookMLの継承機能
既存の設計ドキュメントからviewファイルを自動生成する際には、手書きするファイルと自動生成するファイルは分割するべきです。
そういう時に使える機能が継承（extends）です。継承を使うと複数のview定義を合成して一つのviewにできます。
まずは、継承元となるviewを/generated/views/__ms_biz_prtnr.view というパスに定義します。
内容としては、ラベルの宣言だけを並べたものです。この例ではviewの名前にすこし癖がありますが、自動生成を意図して奇妙な印象を受けるようにしてあります。
view: __ms_biz_prtnr {
label: "取引先マスタ" # 1.
dimension: biz_prtnr_cd {
label: "取引先コード" # 2.
}
}
継承する側のview定義を見てみましょう。以下のようなコードになります。
include: "/generated/views/__ms_biz_prtnr.view" # 1.
view: ms_biz_prtnr {
extends: [__ms_biz_prtnr] # 2.
sql_table_name: `MS_BIZ_PRTNR`;;
dimension: biz_prtnr_cd {
type: string
sql: ${TABLE}.biz_prtnr_cd;;
}
}
- 継承元となるviewのファイルパスを指定してincludeします。これによって、分かれたファイルが単一のファイルであるかのように扱われます。
- 継承元となるviewの名前を指定します。
これによって、 ms_biz_prtnrと__ms_biz_prtnrを一つのviewとして混ざり合った状態になりました。結果的に以下のような宣言のviewになります。
view: ms_biz_prtnr {
label: "取引先マスタ" # __ms_biz_prtnr の 1. から取りこまれる
sql_table_name: `MS_BIZ_PRTNR`;;
dimension: biz_prtnr_cd {
label: "取引先コード" # __ms_biz_prtnr の 2. から取りこまれる
type: string
sql: ${TABLE}.biz_prtnr_cd;;
}
継承についてより詳細な仕様を知りたい方は公式ドキュメントをご覧ください。
Viewファイル自動生成ツールの実装
今回はJavaを使ってviewを定義するファイルを自動生成します。
なお、Javaを使った開発環境の構築方法については以前に書いた
を参考にしてください。
ビルドスクリプト
依存ライブラリについて説明するために、ビルドスクリプトを紹介しましょう。
plugins {
application
}
repositories.mavenCentral()
dependencies {
implementation("org.apache.poi:poi:5.2.3") // 1.
implementation("info.picocli:picocli:4.7.0") // 2.
annotationProcessor("info.picocli:picocli-codegen:4.7.0") // 2.
}
application {
mainClass.set("jp.co.isid.example.looker.App") // 3.
}
- JavaでExcelファイルを読みだすのであれば、実績のあるApache POIがおすすめです。
- コマンドラインアプリケーションの引数をパーズするライブラリとしてはpicocliを使うとアノテーションを付けるだけでやりたいことが実現できるので非常におすすめです。
- main関数を定義したクラスのクラス名を設定しています。
main関数の実装
picocliを使ったアプリケーションのブートストラップ部分の実装を紹介しましょう。
package jp.co.isid.example.looker;
import picocli.CommandLine;
import java.nio.file.*;
import java.util.List;
import java.util.concurrent.Callable;
@CommandLine.Command(name = "modelgen", // 1.
description = "generate looker view models")
public class App implements Callable<Integer> {
@CommandLine.Option(names = {"-o", "--output"}, // 2.
description = "output dir of generated resources", // 2.
defaultValue = "build/generated/views/") // 2.
Path outputDir;
@CommandLine.Parameters(paramLabel = "INPUTS", defaultValue = "tableDesc") // 3.
List<Path> inputs; // 3.
@Override
public Integer call() throws Exception {
var tables = new TableLoader().load(this.inputs);
Files.createDirectories(this.outputDir);
tables.stream().forEach(model -> new ViewWriter().writeTo(this.outputDir, model));
return 0;
}
public static void main(String[] args) {
System.exit(new CommandLine(App.class).execute(args)); // 4.
}
}
CommandLine.Commandアノテーションは、picocli に対してこのクラスがコマンドラインアプリケーションのエントリポイントであることを知らせるためのアノテーションです。nameにアプリケーションの名前、descriptionに説明を設定します。CommandLine.Optionアノテーションは、コマンドライン引数のうちname属性で指定したオプションの内容をフィールドに格納するためのアノテーションです。文字列からの型変換はpicocliが自動的に行います。CommandLine.Parametersアノテーションは、コマンドライン引数のうちオプションとしては解釈されなかったものが全て格納されるフィールドを指定するためのアノテーションです。ここでも、型変換は自動的に行われます。
Excelのパーズ処理
Excelファイルのパーズ処理は、細かく追っていくと膨大になりますので、いくつかポイントになる部分だけ紹介します。
まずは、パーズした結果を格納するデータ構造の紹介です。Java16から使えるようになったrecordを使うと驚くほどシンプルに書けますね。正直言って、Javaでアクセサを明示的に書かずに済む日がくるとは思っていませんでした。
package jp.co.isid.example.looker;
import java.nio.file.Path;
import java.util.List;
public record TableModel(String logicalName, String physicalName, Path sourceFilepath, List<ColumnModel> columns) {
public record ColumnModel(int no, String logicalName, String physicalName, String dataType) {
}
}
どうあっても、小綺麗にはなりえないということを、確認して貰えるExcelをパーズするコードです。いくつか、ポイントになる部分だけ選んで説明します。
ここで読んでいるExcelファイルは、一度お見せしたものですが分かり易いよう、もう一度お見せします。
package jp.co.isid.example.looker;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.nio.file.*;
import java.util.*;
import java.util.function.BiFunction;
import java.util.stream.*;
public class TableLoader {
public List<TableModel> load(List<Path> roots) {
var matcher = FileSystems.getDefault()
.getPathMatcher("glob:**/*.xls"); // 1.
return roots.stream().flatMap(path -> {
try (var stream = Files.walk(path)) {
return stream.filter(matcher::matches)
.collect(Collectors.toList()).stream();
} catch (IOException e) {
throw new UncheckedIOException(e);
}
}).map(this::parse).collect(Collectors.toList());
}
public TableModel parse(Path target) {
try (var fs = new POIFSFileSystem(target.toFile())) {
var book = new HSSFWorkbook(fs);
var sheet = book.getSheet("テーブル設計書");
var names = parseHeaders(sheet);
var columns = parseColumns(sheet);
return new TableModel(names.get(0), names.get(1), target, columns); // 2.
} catch (IOException e) {
throw new UncheckedIOException(e);
}
}
List<String> parseHeaders(HSSFSheet sheet) {} // 3. 省略
List<TableModel.ColumnModel> parseColumns(HSSFSheet sheet) {
var evaluator = sheet.getWorkbook()
.getCreationHelper().createFormulaEvaluator(); // 4.
var format = new DataFormatter();
BiFunction<Row, Integer, String> read = (row, index) -> // 5.
format.formatCellValue(row.getCell(index), evaluator);
return StreamSupport.stream(sheet.spliterator(), false)
.filter(row -> { // 6.
var no = read.apply(row, 0);
var dd = read.apply(row, 1);
return no != null && dd != null
&& no.matches("\\d+") && dd.matches("\\d+");
})
.map(row -> new TableModel.ColumnModel(
Integer.parseInt(read.apply(row, 0)),
read.apply(row, 3),
read.apply(row, 4),
read.apply(row, 5)))
.sorted(Comparator.comparing(TableModel.ColumnModel::no)) // 7.
.collect(Collectors.toList());
}
}
- JavaでファイルシステムをトラバースしながらGLOB形式に一致したファイルを取りだす処理が、ここから始まる一連のコードです。Java1.7からある仕組みですが意外と使われていないAPIだと感じています。
- recordを使って宣言したTableModelのコンストラクタを呼び出しています。呼び出し側は今まで通りのクラスに宣言されたコンストラクタを呼びだす時と同じですね。
- 私たちのテーブル設計書では、この部分にオートシェイプを使うなど煩雑なコードになっているので、説明を省略させてください。
- Excelのセルに書かれている式を解決済みの状態で読みだすためには
FormulaEvaluatorを使います。 - Excelのセルに書かれている日付書式を使ってフォーマット済みのデータを読みだすためには、
DataFormatterと組み合わせて使います。現代のJavaでラムダ式を使うことでローカルスコープの関数をこのように定義できるので便利になりましたね。 - Excelのシートの行を上から読みながらカラム定義が書いてある部分まで読み飛ばす処理です。実に泥臭いコードですね。
- recordを使って宣言したColumnModelに自動生成されたアクセサをメソッド参照しています。recordではデータの読み出し用のメソッドに
getが付いていないのでコードがシンプルですね。
Viewファイルの書き出し処理
最後は、ファイルを書きだすコードです。ここでは特別な事を何もしていません。
package jp.co.isid.example.looker;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Path;
public class ViewWriter {
public void writeTo(Path dir, TableModel table) {
try (var printer = new PrintWriter(dir.resolve(toFilename(table)).toFile(), StandardCharsets.UTF_8)) {
printer.println();
printer.format("view: %s {%n", toViewName(table));
printer.format(" label: \"%s\"%n", table.logicalName());
table.columns().forEach(model -> printColumn(printer, model));
printer.println("}");
} catch (IOException e) {
throw new UncheckedIOException(e);
}
}
void printColumn(PrintWriter printer, TableModel.ColumnModel model) {
printer.println();
printer.format(" dimension: %s {%n", model.physicalName().toLowerCase());
printer.format(" label: \"%s\"%n", model.logicalName());
printer.println(" }");
}
String toViewName(TableModel model) {
return "__" + model.physicalName().toLowerCase();
}
String toFilename(TableModel model) {
return toViewName(model) + ".view.lkml";
}
}
標準APIを使って単にテキストを出力していますね。
まとめ
このエントリでは、BIツールで使い易いデータベースと一般的な基幹システムにおけるデータベースにはギャップがあることを指摘しました。
既存の設計ドキュメントからコードの自動生成をすることで、このギャップを埋められることを実際に動作するコードと併せて説明しました。
このエントリを読んだ皆様が、よりよいデータの前処理が出来れば幸いです。
私たちは同じチームで働いてくれる仲間を探しています。今回のエントリで紹介したような仕事に興味のある方、ご応募をお待ちしています。
執筆：@sato.taichi、レビュー：@handa.kenta （Shodoで執筆されました）
