为databricks生成数据库模式图

xnifntxz  于 2021-05-27  发布在  Spark
关注(0)|答案(1)|浏览(625)

我正在创建一个databricks应用程序,数据库模式变得非常重要。有没有一种方法可以为databricks数据库生成模式图(类似于可以从mysql生成的模式图)?

up9lanfz

up9lanfz1#

有两种可能的变体:
结合使用spark sql show databases , show tables in <database> , describe table ... 使用 spark.catalog.listDatabases , spark.catalog.listTables , spark.catagog.listColumns .
当您在数据库/命名空间中有很多表时,第二个变体的性能不是很好,尽管以编程方式使用它稍微容易一些。但在这两种情况下,实现都只是3个嵌套循环,依次遍历数据库列表、数据库中的表列表和表中的列列表。这些数据可以用来生成一个图表使用您最喜欢的图表工具。
以下是为plantuml生成源代码的代码:


# This script generates PlantUML diagram for tables visible to Spark.

# The diagram is stored in the db_schema.puml file, so just run

# 'java -jar plantuml.jar db_schema.puml' to get PNG file

from pyspark.sql import SparkSession
from pyspark.sql.utils import AnalysisException

# Variables

# list of databases/namespaces to analyze.  Could be empty, then all existing

# databases/namespaces will be processed

databases = ["a", "airbnb"] # put databases/namespace to handle

# change this if you want to include temporary tables as well

include_temp = False

# implementation

spark = SparkSession.builder.appName("Database Schema Generator").getOrCreate()

# if databases aren't specified, then fetch list from the Spark

if len(databases) == 0:
    databases = [db["namespace"] for db in spark.sql("show databases").collect()]

with open(f"db_schema.puml", "w") as f:
    f.write("\n".join(
        ["@startuml", "skinparam packageStyle rectangle", "hide circle",
         "hide empty methods", "", ""]))

    for database_name in databases[:3]:
        f.write(f'package "{database_name}" {{\n')
        tables = spark.sql(f"show tables in `{database_name}`")
        for tbl in tables.collect():
            table_name = tbl["tableName"]
            db = tbl["database"]
            if include_temp or not tbl["isTemporary"]:
                lines = []
                try:
                    lines.append(f'class {table_name} {{')
                    cols = spark.sql(f"describe table `{db}`.`{table_name}`")
                    for cl in cols.collect():
                        col_name = cl["col_name"]
                        data_type = cl["data_type"]
                        lines.append(f'{{field}} {col_name} : {data_type}')

                    lines.append('}\n')
                    f.write("\n".join(lines))
                except AnalysisException as ex:
                    print(f"Error when trying to describe {tbl.database}.{table_name}: {ex}")

        f.write("}\n\n")

    f.write("@enduml\n")

然后可以转化成画面:

相关问题