Help us understand the problem. What is going on with this article?

SQLからtable(view)の依存関係図(PlantUML)を作るPythonコードを書いた

これは何?

こんな感じのtable(view)の依存関係図を....

UML.png

↓こんな感じのSQL群から、出力するためのPythonコードを書いたというお話です。
(図の作成にはPlantUMLを用いました)

CREATE TABLE
  `project.dataset.table5` AS
SELECT
  *
FROM
  `project.dataset.table1`;
CREATE TABLE IF NOT EXISTS
  `project.dataset.table6` AS
SELECT
  *
FROM
  `project.dataset.table2`
UNION ALL
SELECT
  *
FROM
  `project.dataset.table3`;
CREATE VIEW
  `project.dataset.table7` AS
SELECT
  *
FROM
  `project.dataset.table3`
INNER JOIN
  `project.dataset.table4`
USING
  (user_id);

PlantUMLとは

wikipediaによると

PlantUMLはオープンソースのUMLダイアグラム作成用のテキストベースの言語である

だそうです。
様々なモデル図を作成可能です。
学ぶのに立ったサイトを並べておきます。

依存関係図作成の仕方

以下をPython3で実行してください。

sql1 = '''CREATE TABLE
  `project.dataset.table5` AS
SELECT
  *
FROM
  `project.dataset.table1`;
'''

sql2 = '''CREATE TABLE IF NOT EXISTS
  `project.dataset.table6` AS
SELECT
  *
FROM
  `project.dataset.table2`
UNION ALL
SELECT
  *
FROM
  `project.dataset.table3`;
'''

sql3 = '''CREATE VIEW
  `project.dataset.table7` AS
SELECT
  *
FROM
  `project.dataset.table3`
INNER JOIN
  `project.dataset.table4`
USING
  (user_id);
'''

import re
prog_destination = re.compile(r'(?:CREATE TABLE|CREATE TABLE IF NOT EXISTS|CREATE VIEW|CREATE VIEW IF NOT EXISTS|INSERT INTO|INSERT)[\s \n]+`(.+?)`')
prog_origin = re.compile(r'(?:FROM|JOIN)[\s \n]+`(.+?)`')

platuml_tempate = '''@startuml
skinparam padding 10 /'paddingの調整'/
left to right direction /'diagramを左から右に伸ばして行くレイアウトにしたい場合'/
hide members /'classの属性を消す'/
hide circle /'classマークを消す'/
{}
@enduml
'''


def make_table_dependencies_for_platuml(sql:str):
    # 作成するテーブル(ビュー)を取得
    if len(prog_destination.findall(sql)) != 1:
        raise Exception('CREATE TABLE|INSERT INTO句が存在しません。')
    else:
        destination_table = prog_destination.findall(sql)[0]

    # 自己参照を除いた参照元テーブルを取得
    origin_tables = [table for table in prog_origin.findall(sql) if table != destination_table]
    if len(origin_tables) == 0:
        raise Exception('依存関係が存在しません')

    return [F'"{table}" <|-- "{destination_table}"' for table in origin_tables]

# sql毎にPlantUMLを出力したい場合
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql1))))
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql2))))
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql3))))

all_sql_dependencies = [make_table_dependencies_for_platuml(sql) for sql in [sql1, sql2, sql3]]
print(platuml_tempate.format('\n'.join(sum(all_sql_dependencies, []))))

すると、以下の結果が出力されるかと思います。
先程紹介したリンク先などで図をレンダリングしてみましょう。
(冒頭に出てきた関係図が描画されます。)

@startuml
skinparam padding 10 /'paddingの調整'/
left to right direction /'diagramを左から右に伸ばして行くレイアウトにしたい場合'/
hide members /'classの属性を消す'/
hide circle /'classマークを消す'/
"project.dataset.table1" <|-- "project.dataset.table5"
"project.dataset.table2" <|-- "project.dataset.table6"
"project.dataset.table3" <|-- "project.dataset.table6"
"project.dataset.table3" <|-- "project.dataset.table7"
"project.dataset.table4" <|-- "project.dataset.table7"
@enduml

なぜこのコードを書いたか

データ分析の仕事をさせて頂いているのですが、BigQueryでテーブルを加工してまた別のテーブルを作り、さらにそのテーブルを加工してテーブルを作ることがよくあります。特にアドホックな分析をしていると、テーブル間の関係を把握するのが難しくなってきます。テーブル関係の把握や、無駄な中間テーブルを作っていないかなどに役立てられるかと思います。

↓ 膨れ上がったテーブルの依存関係
uml_table.png

参考

その他

最後に

弊社、今年もアドベントカレンダーに挑戦中ですのでどうぞご覧ください。
https://qiita.com/advent-calendar/2019/sensy

nassy20
201807 ~ Python webエンジニア; 内容が間違っていたら、ご指摘ください; 主に野球のデータ分析を題材にPythonの勉強していく https://nassy20.hatenablog.com
colorful-board
「すべての人々に、人生が変わる出会いを」をビジョンとして、これまで出会えなかった情報と瞬時に出会うことで人々の生活をより豊かにする、新しい情報発見のためのプラットフォームを目指しています。
https://sensy.ai/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした