Using CTE to form loop in databricks

Question

I have to run this loop 20 times OR more based on conditions/data (last CTE = H20). Trying to find a way to recursively run the loop based on newly formed CTE in DATABRICKS SQL.

With H as (
    SELECT ..... ;
    )
, H1 as (
    SELECT ....  FROM H .. ;
        )
, H2 as (
    SELECT ....  FROM H1 .. ;
        )
, H_ALL as (
    SELECT * from H1
    UNION ALL
    SELECT * from H2
    UNION All
    SELECT * FROM H3
    TILL H20
    )
SELECT * FROM H_ALL

Answer 1

Try below code.

case class Catalog(database: String, tableName: String)
val database = ""

spark
  .sql(s"SHOW TABLES FROM ${database}")
  .select($"database", $"tableName")
  .filter($"tableName" rlike "h[1-9]") // condition to select all tables where each table startswith h1,h2 .. etc ..
  .as[Catalog]
  .collect
  .par
  .map { catalog =>
    spark.sql(s"SELECT * FROM ${catalog.database}.${catalog.tableName}")
  }
  .reduce(_.unionAll(_))
  .show(false)