Spark SQL Views and Tables

#spark #sql #view Spark SQL Views and Tables
Get an introduction to Spark SQL views and tables.
https://github.com/vishnoiprem/scala-spark-educative/blob/master/src/main/scala/SparkSql/sparkSqlExmple.scala
We'll cover the following

Managed vs unmanaged tables
Views
Catalog
In the previous lesson, we created a temporary view in Spark. We can also create a table using Spark SQL. Spark uses Apache Hive to persist metadata like the schema, description, table name, database name, column names, partitions, or physical location for tables created by users. In case Hive isn’t configured, Spark uses Hive’s embedded deployment mode, which employs Apache Derby as the underlying database. When we start the spark-shell without Hive configuration, the spark-shell creates metastore_db and warehouse directories in the current directory. We’ll see these directories when we work the terminal at the end of this lesson.



Managed vs unmanaged tables
In Spark, we can create two types of tables:

Managed: With managed tables, Spark is responsible for managing both the data and the metadata related to the table. If the user deletes a managed table, then Spark deletes both the data and the metadata for the table.

Unmanaged: With unmanaged tables, Spark is only responsible for managing the metadata of the table while the user has the onus of managing the table’s data in an external data source. If the user deletes the table, only the metadata for the table is deleted and not the actual data for the table.

Let’s see how we can create both. If we don’t specify the database, Spark uses the database default. We’ll start with creating the database spark_course.


movies.write.saveAsTable("movieShortDetailUsingDataFrame")


Views
Views can be created on top of existing tables. Views are of two types:

Global Views: are visible across all SparkSessions on a given cluster. An application may need to access and combine data from multiple SparkSessions with different Hive metastore configurations.

Session-scoped Views: are visible only to a single SparkSession.

Views don’t hold the actual data and disappear once the Spark application terminates.


Catalog
Spark manages metadata for both managed and unmanaged tables. We can access the metadata information for not only tables but also databases and views using a high level abstraction known as the Catalog.

Some examples include:


spark.catalog.listDatabases()spark.catalog.listTables()spark.catalog.listColumns("movies")

data engineer interview question