Spark AnalysisException global table or view not found

Question

I get the following error:

18/03/14 15:31:11 ERROR ApplicationMaster: User class threw exception: org.apache.spark.sql.AnalysisException: Table or view not found: products; line 1 pos 42

This is my code:

val spark = SparkSession
                .builder()
                .appName("Test")
                .getOrCreate()

val products = spark.read.parquet(productsPath)
products.createGlobalTempView("products")

val q1 = spark.sql("SELECT PERCENTILE(product_price, 0.25) FROM products").map(_.getAs[Double](0)).collect.apply(0)

What am I doing wrong? Is it possible to do the same thing in Spark without using sql?

Shouldn't it be `products.registerTempTable("products")`? – ernest_k Mar 14 '18 at 15:30 — ernest_k, Mar 14 '18 at 15:30
@ErnestKiwele: `registerTempTable` is deprecated. – Markus Mar 14 '18 at 15:41 — Markus, Mar 14 '18 at 15:41

Ramesh Maharjan · Accepted Answer · 2018-03-14T15:43:21.393

TEMPORARY VIEW

Just use createOrReplaceTempView as

products.createOrReplaceTempView("products")

val q1 = spark.sql("SELECT PERCENTILE(product_price, 0.25) FROM products").map(_.getAs[Double](0)).collect.apply(0)

GLOBAL TEMPORARY VIEW

If you use global temp view then you should do

products.createGlobalTempView("products")

val q1 = spark.sql("SELECT PERCENTILE(product_price, 0.25) FROM global_temp.products").map(_.getAs[Double](0)).collect.apply(0)

score 1 · Answer 2 · edited Mar 15 '18 at 05:38

1

All the global temporary views are created in Spark preserved temporary global_temp database.

Below should work-

val q1 = spark.sql("""SELECT PERCENTILE(product_price, 0.25) 
    FROM global_temp.products""").map(_.getAs[Double](0)).collect.apply(0)

Spark has 2 different types of views, Tempview and globalTempView, see post here for more details.

edited Mar 15 '18 at 05:38

philantrovert

9,904
3
37
61

answered Mar 14 '18 at 15:33

Rahul Sharma

5,614
10
57
91

score 1 · Answer 3 · answered Mar 14 '18 at 15:55

1

If you want to use sql API you can try

import org.apache.spark.sql.expressions.Window

val wdw =  Window.partitionBy($"Field1", $"Field2").orderBy($"Field".asc)

products.withColumn("percentile",functions.ntile(100).over(wdw))

answered Mar 14 '18 at 15:55

Angel F O

76
6

Spark AnalysisException global table or view not found

3 Answers3