unable to show views

[Shantanu0701]

As I have created a view as seen below but when I am printing its showing error. So can't even say if the views are created successfully or not.

import pyspark
from pyspark.sql import SparkSession
import os

Initialize SparkConf with necessary configurations

conf = (
pyspark.SparkConf()
.setAppName('app_name') # Set the application name for Spark
# Specify the Iceberg package to be used
.set('spark.jars.packages', 'org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.5.1')
# Add Iceberg SQL Extensions to enable Iceberg-specific SQL operations
.set('spark.sql.extensions', 'org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions')
# Configure the Iceberg catalog
.set('spark.sql.catalog.localcat', 'org.apache.iceberg.spark.SparkCatalog') # Define the catalog implementation
.set('spark.sql.catalog.localcat.type', 'hadoop') # Set the catalog type to Hadoop
.set('spark.sql.catalog.localcat.warehouse', '/home/user/Downloads/first_task/warehouse') # Specify the warehouse directory

.set('spark.sql.catalog.localcat2', 'org.apache.iceberg.spark.SparkCatalog')  # Define the catalog implementation
.set('spark.sql.catalog.localcat2.type', 'hadoop')  # Set the catalog type to Hadoop
.set('spark.sql.catalog.localcat2.warehouse', '/home/user/Downloads/first_task/warehouse2')

)

Create a SparkSession with the previously defined configurations

spark = SparkSession.builder.config(conf=conf).getOrCreate()
print("Spark Running")

Create a table named 'first_table' in the 'localcat.localdb' catalog using Iceberg format

spark.sql("CREATE TABLE localcat.localdb.first_table (id bigint, first_name string, last_name string ) USING iceberg")

spark.sql("CREATE TABLE localcat2.localdb3.demo1 (id bigint, first_name string, last_name string) USING iceberg")

Insert data into the 'first_table'

spark.sql("INSERT INTO localcat2.localdb3.demo1 VALUES (1,'Alex', 'Merced'),(2,'Demon', 'Merced'),(3,'Lord', '')")
spark.sql("INSERT INTO localcat.localdb.first_table VALUES (1,'Alex', 'Merced'),(2,'Demon', 'Merced'),(3,'Lord', '')")

Query the table to display all records

spark.sql("SELECT * FROM localcat.localdb.first_table").show()
spark.sql("SELECT * FROM localcat.localdb.first_table.history").show()

spark.sql("CREATE VIEW filtered_view AS SELECT * FROM localcat.localdb.first_table WHERE id > 1")
spark.sql("SHOW VIEWS").show()

Getting below error:

spark.sql("SHOW VIEWS").show()
py4j.protocol.Py4JJavaError: An error occurred while calling o43.sql.
: java.lang.AssertionError: assertion failed
at scala.Predef$.assert(Predef.scala:208)
at org.apache.spark.sql.connector.catalog.LookupCatalog$CatalogAndNamespace$.unapply(LookupCatalog.scala:84)
at org.apache.spark.sql.catalyst.analysis.RewriteViewCommands$$anonfun$apply$1.applyOrElse(RewriteViewCommands.scala:74)
at org.apache.spark.sql.catalyst.analysis.RewriteViewCommands$$anonfun$apply$1.applyOrElse(RewriteViewCommands.scala:51)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.$anonfun$resolveOperatorsUpWithPruning$3(AnalysisHelper.scala:138)
at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(origin.scala:76)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.$anonfun$resolveOperatorsUpWithPruning$1(AnalysisHelper.scala:138)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:323)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsUpWithPruning(AnalysisHelper.scala:134)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsUpWithPruning$(AnalysisHelper.scala:130)
at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperatorsUpWithPruning(LogicalPlan.scala:32)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsUp(AnalysisHelper.scala:111)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.resolveOperatorsUp$(AnalysisHelper.scala:110)
at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperatorsUp(LogicalPlan.scala:32)
at org.apache.spark.sql.catalyst.analysis.RewriteViewCommands.apply(RewriteViewCommands.scala:51)
at org.apache.spark.sql.catalyst.parser.extensions.IcebergSparkSqlExtensionsParser.parsePlan(IcebergSparkSqlExtensionsParser.scala:126)
at org.apache.spark.sql.SparkSession.$anonfun$sql$2(SparkSession.scala:631)
at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:138)
at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:630)
at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:900)
at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:629)
at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:659)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:374)
at py4j.Gateway.invoke(Gateway.java:282)
at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
at py4j.commands.CallCommand.execute(CallCommand.java:79)
at py4j.ClientServerConnection.waitForCommands(ClientServerConnection.java:182)
at py4j.ClientServerConnection.run(ClientServerConnection.java:106)
at java.base/java.lang.Thread.run(Thread.java:1570)

[Shantanu0701]

Also let me know the proper syntax to create a view and show all views as per above catalog name, database name and table name.

I have below record stored in SELECT * FROM localcat.localdb.first_table:
+---+----------+---------+
| id|first_name|last_name|
+---+----------+---------+
| 1| Alex| Merced|
| 2| Demon| Merced|
| 3| Lord| |
+---+----------+---------+

[jbonofre]

You are using the Iceberg HadoopCatalog (set('spark.sql.catalog.localcat2.type', 'hadoop')). The HadoopCatalog doesn't support view right now (it implements BaseMetastoreCatalog not BaseMetastoreViewCatalog).
You can use JdbcCatalog or RestCatalog which support views.

[nastra]

The root cause of the issue was just fixed by #10442, but as @jbonofre mentioned, there's no view support available for HadoopCatalog. I'll go ahead and close this, since it should be fixed now