Analysis Exception : Table or View not found for existing Delta table

[Rajan Patki]

I am new to Delalake. I was trying a simple example.

1. Create dataframe from a csv using regualr Spark API
2. Save it is as delta table
3. Read it again.

It works fine. I can see the files are created in the default spark-warehouse folder.

But Next time I just want to read the saved table. So I comment code for the first two septs and re-run the program I get

Analysis Exception:Table or view not found

val transHistory = spark. read .option("header", "true") .option("inferschema", true) .csv(InputPath + "trainHistory.csv");

 transHistory.write.format("delta").mode(SaveMode.Overwrite).saveAsTable("transactionshistory") 

  val transHistoryTable = spark.read.format("delta").table("transactionshistory") transHistoryTable.show(10)

Am I doing something wrong ? I am using delta lake 0.8.0, Spark 3.0, and scala 2.12.13

[Denny Lee]

I'm wondering if there is an issue associated with how the metastore is configured.  For sake of argument, could you try:

txHistory.write.format("delta").save("/filepath/txHistory")

val txHistoryTbl = spark.read.format("delta").load("/filepath/txHistory")

txHistoryTbl.show(10) 

[Rajan Patki]

Thanks, Denny for your quick response.

I have already tried that and it works fine. When I read with file path it works fine but fails when I try to load it as a table.

I tested few more things yesterday. With Catalog API I tried to list databases and tables. I see the default database and when I am creating the table  and read it (above code) I see the table  transactionshistory as managed permanent table, however when next time if only try to read it (comment first 2 statement) I see that the default database does not have the table. So this is the actual problem. But I don't know why does this happen? I have set config same as given in quick start.

val spark = SparkSession.builder()
.master("local")
.config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
.config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")
.appName("Delta lake example")
.getOrCreate()

[Jacek Laskowski]

Hi Rajan,

Care to share the whole application and how you run it? Is this a standalone app or spark-shell?

Please post the whole stack trace.

I see nothing obvious that I could leverage to help you.

Pozdrawiam,

Jacek Laskowski

----

https://about.me/JacekLaskowski

"The Internals Of" Online Books

Follow me on https://twitter.com/jaceklaskowski

--
You received this message because you are subscribed to the Google Groups "Delta Lake Users and Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to delta-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/812813d5-705d-4e9a-bcb9-c6654ee594d2n%40googlegroups.com.

[Rajan Patki]

Hi Jacek,

Following is the complete code

import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}

object TableReadWrite {
  val InputPath = "D:\\Work\\RND\\Spark\\examples\\RetailData\\"

  def main(args: Array[String]): Unit = {
      

val spark = SparkSession.builder()
      .master("local")
      .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
      .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")
      .appName("Delta lake example"

)
      .getOrCreate()

    

val transHistory = spark.
      read
      .option("header", "true")
      .option("inferschema", true)
      .csv(InputPath + "trainHistory.csv"

);

    //transHistory.write.format("delta").saveAsTable("transactionshistory")
    val transTable = spark.table("transactionshistory")
    transTable.show(10)
  }
}

Following is the stack trace

Exception in thread "main" org.apache.spark.sql.AnalysisException: Table or view not found: transactionshistory;;
'UnresolvedRelation [transactionshistory]

    at org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
    at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.$anonfun$checkAnalysis$1(CheckAnalysis.scala:106)
    at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.$anonfun$checkAnalysis$1$adapted(CheckAnalysis.scala:92)
    at org.apache.spark.sql.catalyst.trees.TreeNode.foreachUp(TreeNode.scala:177)
    at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.checkAnalysis(CheckAnalysis.scala:92)
    at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.checkAnalysis$(CheckAnalysis.scala:89)
    at org.apache.spark.sql.catalyst.analysis.Analyzer.checkAnalysis(Analyzer.scala:130)
    at org.apache.spark.sql.catalyst.analysis.Analyzer.$anonfun$executeAndCheck$1(Analyzer.scala:156)
    at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.markInAnalyzer(AnalysisHelper.scala:201)
    at org.apache.spark.sql.catalyst.analysis.Analyzer.executeAndCheck(Analyzer.scala:153)
    at org.apache.spark.sql.execution.QueryExecution.$anonfun$analyzed$1(QueryExecution.scala:68)
    at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111)
    at org.apache.spark.sql.execution.QueryExecution.$anonfun$executePhase$1(QueryExecution.scala:133)
    at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:763)
    at org.apache.spark.sql.execution.QueryExecution.executePhase(QueryExecution.scala:133)
    at org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:68)
    at org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:66)
    at org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:58)
    at org.apache.spark.sql.Dataset$.$anonfun$ofRows$1(Dataset.scala:91)
    at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:763)
    at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:89)
    at org.apache.spark.sql.SparkSession.table(SparkSession.scala:584)
    at org.apache.spark.sql.SparkSession.table(SparkSession.scala:580)
    at TableReadWrite$.main(TableReadWrite.scala:22)
    at TableReadWrite.main(TableReadWrite.scala)

Notice that the statement

//transHistory.write.format(“delta”).saveAsTable(“transactionshistory”)

If I uncomment this and remove transactionhistory folder from the default spark warehouse path then it works fine. 

Meaning that when I am creating the table it gets the table in the catalog but if I am reading the existing table then it does not get the table. I have confirmed this with catalog APIs.

This is a very basic example and I am sure I am doing some silly mistakes or there is a gap in my understanding but I am not able to detect that.

Thanks for help

Regards,
Rajan

​

[Jacek Laskowski]

Hi,

How do you run the app? spark-submit?

Pozdrawiam,

Jacek Laskowski

----

https://about.me/JacekLaskowski

"The Internals Of" Online Books

Follow me on https://twitter.com/jaceklaskowski

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/374680c9-317a-4494-90d2-a9d9ec1dd79an%40googlegroups.com.

[Adam Dec]

Hi, don't you just miss DB name?

Like: spark.table("DB_NAME.transactionshistory")

Otherwise it will look in Hive default db

/Adam

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/CAB_G8Ztk_vpVD58Lbo5KgqmN6%3DjO9T0NnYhLe6jHacd3TTtNpw%40mail.gmail.com.

[Mich Talebzadeh]

Not really that familiar with delta but sounds like basics like DBName is missing or incorrect jar file

You can check if table created OK with

val DB = "test"

val tableName = "randomData"

val fullyQualifiedTableName =  DB + "."+ tableName

var rows = 0

if (spark.sql(s"SHOW TABLES IN ${DB} like '${tableName}'").count() == 1) {

     println (s"\n Table ${fullyQualifiedTableName} created OK")

 } else

 {   println (s"\n Table ${fullyQualifiedTableName} does not exist, quitting")

      sys.exit(1)

}

HTH

  

   view my Linkedin profile

 

Disclaimer: Use it at your own risk. Any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on this email's technical content is explicitly disclaimed. The author will in no case be liable for any monetary damages arising from such loss, damage or destruction.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/CAOWmjHTh0J0YL99%2BBKudEhZN62ccH5RMpnd28VaSqKpe4kWBAg%40mail.gmail.com.

[rohit haseja]

Hey Rajan,

If you want to read from table you should just use spark.table('table_name'). there is no need to mention the format delta.

you can refer to this link it might help you with this. I don't see anything else in this statement that can impact this.

Thanks,

Rohit Haseja.

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/CAOWmjHTh0J0YL99%2BBKudEhZN62ccH5RMpnd28VaSqKpe4kWBAg%40mail.gmail.com.

[rohit haseja]

sorry for delay but heres the link for that article from databricks.

Table batch reads and writes | Databricks on AWS

Thanks,

Rohit Haseja.

[Rajan Patki]

I am running directly in Intellij.

[Rajan Patki]

Hi All,
Thanks for response

@ adamdec85 & hasejarohit : I am creating the table in the default database so I had skipped it even while creating and I see that the database is created in the default spark warehouse location i.e. \spark_warehouse. I even tried giving explicit database name i.e. default.transactionhistory but still got the same error while reading the existing table.

@jacek : I am running directly from intellij

@Rohit Haseja : Yes I am following the same link and I am not using delta while reading the table.

I tried to check if the database and table is present in the catalog with the following APIs

spark.catalog.listDatabases().show()
spark.catalog.listTables().show()

When the table is not there and I am creating a new one I see that the database name is default and it has a table named transactionshistory which is managed table and not temporary, However when I run it only to read the existing table then the database name I get as default but the table is not there.

So the question is why Spark does not load the table.

Regards,
Rajan

​

[Michael Zhang]

Hi Rajan,

   it is an expected behaviour. 

   All the database and table metadata is stored in hive metastore. 

   By default, delta io uses a built-in hivestore in JVM. If you create a table in one job run, and re-run the job referring to the table created in the last run, you will get the table not found error because it is a new build-in hivestore.

   this is what you are experiencing right now.

   you can configure delta io to use external hive store which is backed by a database (mysql, postgres etc)

  https://tibco-computedb.readthedocs.io/en/enterprise_docv1.2.0/Data/external_hive_support/

   However, I cannot find any documentation regarding configuring an external hive store for delta io.

   If you use delta in databricks runtime, an external store is used and tables should be able to be referred globally. 

   Please note: tables created in delta derive from the parquet files which include the schema. The source of truth is parquet files. 

   given that, alternatively, you can use save to write dataframe to parquet files in delta format as @denny Lee suggested

txHistory.write.format("delta").save("/filepath/txHistory")

and at the beginning of the job, create unmanaged tables and databases using the sql below (https://docs.delta.io/latest/delta-batch.html#control-data-location)

and refer those tables in spark sql.

CREATE TABLE events
USING DELTA
LOCATION '/delta/events'

Hope this helps

refer this

   https://forums.databricks.com/questions/17954/where-does-databricks-delta-stores-its-metadata.html#:~:text=Regular%20Databricks%20tables%20stores%20its,where%20the%20table%20is%20created.&text=The%20actual%20schema%20of%20delta,directory%20under%20the%20data%20directory.

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/8c2fb0b6-a36b-4349-87da-ddba2f4ad9adn%40googlegroups.com.

--

Thanks
Heng Zhang

[Rajan Patki]

Thanks for the reply.  

Yes, your suggestion works I can create an external table on existing data, now I can move forward with my understanding.

However, df.write.format("delta").saveAsTable(...) should create managed table and one should be able to read it with spark.table(...) both these examples are mentioned in delta documentation.  https://docs.delta.io/latest/delta-batch.html#create-a-table&language-scala

but this does not work. saveAsTable (...) do create delta table files (delta_log folder and other files) but I can not read it with spark.table() in another session.

Regards,

Rajan

[Michael Zhang]

the documentation you specified does not discuss hive store

when you use saveAsTable(...), tables metadata still goes to hive store. as. long as you use in-mem hivestore, then you won't be able to read it cross jvms

Heng

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/e58259c6-8a24-4ed5-a9bb-26e955e631f5n%40googlegroups.com.

--

Thanks
Heng Zhang

[Rajan Patki]

Yes. I understand. Thank you for the help.

Regards,

Rajan

[Rajan Patki]

enableHiveSupport does the trick. You can read the delta table created in one session can be accessed from another session.

Just wanted to mention here because, I could not find this in the documentation, so it can be helpful if someone else stumbles. 

[Michael Zhang]

Hi Rajan,

do you mean like this?

def getSparkSession(): SparkSession = { SparkSession.builder() .appName("Example Spark Submit") .enableHiveSupport() .config("immuta.spark.acl.assume.not.privileged", "true") .config("spark.hadoop.immuta.databricks.config.update.service.enabled", "false") .getOrCreate() }

To view this discussion on the web visit https://groups.google.com/d/msgid/delta-users/63b71bf7-d4ec-4a65-be35-1e8a94b2e8a2n%40googlegroups.com.

--

Thanks
Heng Zhang

[Rajan Patki]

Yes. I used delta configuration with enableHiveSupport

 val spark = SparkSession.builder()
        .master("local[*]")
        .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
        .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")
        .appName("Delta lake example")
        .enableHiveSupport()
        .getOrCreate()

​