Error in SQL statement: AnalysisException: Table or view not found:

Question

I've just started with Hive. I'm working on Databricks community. I write in python but wanted to write something in SQL but there is an error I cannot understand. I cannot see anything wrong in my code. Please help me.

spark.sql("create table happiness_perm as select * from happiness_tmp"); 


%sql 
select Country, count(*) from happiness_perm group by Country

I tried use my data freame df_happiness instead happiness_perm and still I receive this:

Error in SQL statement: AnalysisException: Table or view not found: happiness_perm; line 1 pos 30; 'Aggregate ['Country], ['Country, unresolvedalias(count(1), None)] +- 'UnresolvedRelation [happiness_perm], [], false

I would really appreciate your help!

Answer 1

Try this:

df = spark.sql("select * from happiness_tmp")

df.createOrReplaceTempView("happiness_perm")

First you get your data into a dataframe, then you write the contents of the dataframe to a table in the catalog.

You can then query the table.