我正在创建一个 Databricks 应用程序，并且数据库架构变得非常重要。有没有办法为 Databricks 数据库生成架构图（类似于可以从 mysql 生成的架构图）？
有两种可能的变体：
- 使用 Spark SQL 与
show databases, show tables in <database>, describe table ...
- using
spark.catalog.listDatabases, spark.catalog.listTables, spark.catagog.listColumns.
当数据库/命名空间中有很多表时，第二个变体的性能不是很好，尽管以编程方式使用它稍微容易一些。但在这两种情况下，实现都只是 3 个嵌套循环，迭代数据库列表，然后迭代数据库内的表列表，然后迭代表内的列列表。该数据可用于使用您最喜欢的图表工具生成图表。
这是生成源代码的代码PlantUML https://plantuml.com/（完整代码是here https://github.com/alexott/spark-playground/blob/master/pyspark/generate_database_diagram.py):
# 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")
然后可以将其转换为图片：
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
