一 需求背景

大數據環(huán)境下，metastore一般都交個hive處理，隨著數據庫 表 越來越多，進行源數據管理的就會成為痛點，如何能夠查詢出所有的數據庫下的所有表

二 官方文檔

Spark 官方文檔Tables

-- List all tables from default database matching the pattern `sam*|suj`
SHOW TABLES FROM default LIKE 'sam*|suj';
  +-----------+------------+--------------+--+
  | database  | tableName  | isTemporary  |
  +-----------+------------+--------------+--+
  | default   | sam        | false        |
  | default   | sam1       | false        |
  | default   | suj        | false        |
  +-----------+------------+--------------+--+

官方給的sample中，只能一個庫一個庫查詢，如果有成百上千個庫呢？

三 解法1

3.1 DB合集

databases = [
    db.databaseName 
    for db in spark.sql('show databases').collect()
]

3.2 tables合集

tables = [
    for db_rows in [
        spark.sql(f'show tables in {db}').collect() for db in databases
    ] 
    for row in db_rows
]

3.3 result format

targetTables = [
    f"{row['database']}.{row['tableName']}" 
    for db_rows in [
        spark.sql(f'show tables in {db}').collect() for db in targetDBList
    ] 
    for row in db_rows
]

從 Python 3.6 開始，Python f 字符串可用。 該字符串具有f前綴，并使用{}評估變量

python fyingyong

四 解法2

%scala
import org.apache.spark.sql.types._
// Create schema for final result
val schema = List(
    StructField("database", StringType, true),
    StructField("tableName", StringType, true),
    StructField("isTemporary", BooleanType, true)
)
// Create an empty Dataframe in Scala using VAR so its mutable
var resultDF = spark.createDataFrame(spark.sparkContext.emptyRDD[Row], StructType(schema))
// Get list of all Databases and store it in a Data frame
val df = spark.sql("show databases")
// Loop through all the databases and get list of all tables using // show tables from database 
// using unionAll append the dataframe
df.collect.foreach {db =>
   val dbname = db.toString().replaceAll("[\\[\\]]","")
   val tbldf=spark.sql(s"show tables from ${dbname}")
   resultDF = resultDF.unionAll(tbldf)
}
// display is available in databricks. Traditional spark use show
display(resultDF)
// display.show()

解法2出處文章來源地址http://www.zghlxwxcb.cn/news/detail-623269.html

到了這里，關于spark sql 查看全部數據庫的表的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！