topic Re: Is there a way to create a non-temporary Spark View with PySpark? in Data Engineering

Is there a way to create a non-temporary Spark View with PySpark?

MartinB — Thu, 14 Oct 2021 14:18:49 GMT

Hi,

When creating a Spark view using SparkSQL ("CREATE VIEW AS SELCT ...") per default, this view is non-temporary - the view definition will survive the Spark session as well as the Spark cluster.

In PySpark I can use DataFrame.createOrReplaceTempView or DataFrame.createOrReplaceGlobalTempView to create a temporary view for a DataFrame.

Is there a way to create a non-temporary Spark View with PySpark for a DataFrame programatically?

spark.sql('CREATE VIEW AS SELCT ...') doesn't count 😉

I did not find a DataFrame method to do so...

Re: Is there a way to create a non-temporary Spark View with PySpark?

Hubert-Dudek — Thu, 14 Oct 2021 15:07:04 GMT

Just create Table instead

Re: Is there a way to create a non-temporary Spark View with PySpark?

MartinB — Thu, 14 Oct 2021 16:45:36 GMT

Creating a table would imply data persistance, wouldn't it?

I don't want that.

Re: Is there a way to create a non-temporary Spark View with PySpark?

jose_gonzalez — Thu, 14 Oct 2021 17:18:03 GMT

hi @Martin B. ,

There are 2 types of views. TEMPORARY views are session-scoped and is dropped when session ends because it skips persisting the definition in the underlying metastore, if any. GLOBAL TEMPORARY views are tied to a system preserved temporary database global_temp. If you would like to know more about it, please refer to the docs

If none of these two options work for you, then the other option will be to create a physical table like @Hubert Dudek mentioned.

Thanks

Re: Is there a way to create a non-temporary Spark View with PySpark?

MartinB — Sat, 16 Oct 2021 08:37:34 GMT

Hi @Jose Gonzalez ,

I would argue, there are 3 types of views:

TEMPORARY VIEWS
- CREATE TEMPORARY VIEW sam AS SELECT * FROM ...
GLOBAL TEMPORARY VIEWS
- CREATE GLOBAL TEMPORARY VIEW sam AS SELECT * FROM ...
NON-TEMPORARY VIEWS
- CREATE VIEW sam AS SELECT * FROM ...

Please see the example here: https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-aux-show-views.html#examples

I want to create a non-temporary view (isTemporary=false) - not with SparkSQL but with PySpark.

Re: Is there a way to create a non-temporary Spark View with PySpark?

MartinB — Wed, 10 Nov 2021 13:20:02 GMT

@Jose Gonzalez or @Piper Wilson any ideas?

Re: Is there a way to create a non-temporary Spark View with PySpark?

Hubert-Dudek — Wed, 10 Nov 2021 13:29:29 GMT

why not to create manage table?

dataframe.write.mode(SaveMode.Overwrite).saveAsTable("<example-table>")
 
# later when we need data
resultDf = spark.read.table("<example-table>")

Re: Is there a way to create a non-temporary Spark View with PySpark?

MartinB — Wed, 10 Nov 2021 16:24:45 GMT

@Hubert Dudek, creating a managed table means persisting the data frame (writing the content of the dataframe to storage) .

Imagine you have a Spark table (in delta lake format) containing your raw data. Every 5 Minutes there is new data appended to that raw table.

I want to refine the raw data: filter it, select just specific columns and convert some columns to a better data type (e.g string to date). With VIEWSs you can apply this transformations virtually. Every time you access the view, the data are transformed at access time - but you always get the current data from the underlying table.

When creating a managed table you basically create a copy of the content of the raw table with the transformations applied. Persisting data comes at performance and storage costs.

Moreover, every time, I want to access my "clean" version of the data I have to specify the transformation logic again. VIEWs allow me to just access my transformed raw data without the need of manual refresh or persistence.

Re: Is there a way to create a non-temporary Spark View with PySpark?

Hubert-Dudek — Wed, 10 Nov 2021 19:17:45 GMT

ok now I got it finally 🙂 so whole question is just to create CREATE VIEW AS SELECT via PySpark API.

we can see that in dataframe.py that all views are temp:

https://github.com/apache/spark/blob/7d9a4fab7957086a12ba3e9e2856e20566531e3a/python/pyspark/sql/dataframe.py

this PySpark API is routing to Dataset.scala:

https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/Dataset.scala

in Dataset.scala we can see condition which should be rebuild-ed. It is not allowing PersistedView

val viewType = if (global) GlobalTempView else LocalTempView

it should be something like in SparkSqlParser.scala

      val viewType = if (ctx.TEMPORARY == null) {
        PersistedView
      } else if (ctx.GLOBAL != null) {
        GlobalTempView
      } else {
        LocalTempView
      }

so:

private def createTempViewCommand in Dataset.scala need additional viewType param and rather should be renamed (already name is wrong as Global was added)

2) than functions like createGlobalPersistantCommand etc. ould be added in Dataset.scala

3) than de fcreateGlobalPersistantView etc. could be added to dataframe.py

after it is done in Spark X (? :- ) it will be possible.

Maybe someone want to contribute and create commits 🙂