How to use fully formed SQL with spark structured streaming

Question

Documentation for Spark structured streaming says that - as of spark 2.3 all methods on the spark context available for static DataFrame/DataSet's are also available for use with structured streaming DataFrame/DataSet's as well. However I have yet to run across any examples of same.

Using fully formed SQL's is more flexible, expressive, and productive for me than the DSL. In addition for my use case those SQL's are already developed and well tested for static versions. There must be some rework - in particular to use joins in place of correlated subqueries. However there is still much value in retaining the overall full-bodied sql structure.

The format for which I am looking to use is like this hypothetical join:

 val tabaDf = spark.readStream(..)
 val tabbDf = spark.readStream(..)

 val joinSql = """select a.*, 
                  b.productName 
                  from taba
                  join tabb 
                  on a.productId = b.productId
                  where ..
                  group by ..
                  having ..
                  order by .."""
 val joinedStreamingDf = spark.sql(joinSql)

There are a couple of items that are not clear how to do:

• Are the tabaDf and tabbDf supposed to be defined via spark.readStream: this is my assumption

• How to declare taba and tabb . Trying to use

  tabaDf.createOrReplaceTempView("taba")
  tabbDf.createOrReplaceTempView("tabb")
  

  results in

  WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException

All of the examples I could find are using the DSL and/or the selectExpr() - like the following https://databricks.com/blog/2017/04/26/processing-data-in-apache-kafka-with-structured-streaming-in-apache-spark-2-2.html

 df.selectExpr("CAST(userId AS STRING) AS key", "to_json(struct(*)) AS value")

or using select :

sightingLoc
  .groupBy("zip_code", window("start_time", "1 hour"))
  .count()
  .select( 
    to_json(struct("zip_code", "window")).alias("key"),
    col("count").cast("string").alias("value")) 

Are those truly the only options - so that the documentation saying that all methods supported on the static dataframe/datasets are not really accurate? Otherwise: aAny pointers on how to correct the above issue(s) and use straight-up sql with streaming would be appreciated.

Answer 1

The streams need to be registered as temporary views using createOrReplaceTempView. AFAIK createOrReplaceView is not a part of the Spark API (perhaps you have something that provides an implicit conversions to a class with such method).

spark.readStream(..).createOrReplaceTempView("taba")
spark.readStream(..).createOrReplaceTempView("tabb")

Now the views can be accessed using pure SQL. For example, to print the output to console:

spark
  .sql(joinSql)
  .writeStream
  .format("console")
  .start()
  .awaitTermination()

Edit: After question edit, I don't see anything wrong with your code. Here is a minimal working example. Assuming a test file /tmp/foo/foo.csv

"a",1
"b",2

import org.apache.spark.sql.types._
val schema = StructType(Array(StructField("s", StringType), StructField("i", IntegerType)))
spark.readStream
  .schema(schema)
  .csv("/tmp/foo")
  .createOrReplaceTempView("df1")
spark.readStream
  .schema(schema)
  .csv("/tmp/foo")
  .createOrReplaceTempView("df2")

spark.sql("SELECT * FROM df1 JOIN df2 USING (s)")
  .writeStream
  .format("console")
  .start()
  .awaitTermination()

outputs

-------------------------------------------
Batch: 0
-------------------------------------------
+---+---+---+
|  s|  i|  i|
+---+---+---+
|  b|  2|  2|
|  a|  1|  1|
+---+---+---+