Might sound silly, but are you using a Hive context? What errors do the Hive query results return?
spark = SparkSession.builder.enableHiveSupport().getOrCreate() The second part of your questions, you are creating a temp table and then subsequently creating another table from that temp view. Doesn’t seem like you are reading the table from the spark or hive warehouse. This works fine for me; albeit I was using spark thrift to communicate with my directory of choice. from pyspark import SparkContext from pyspark.sql import SparkSession, Row, types from pyspark.sql.types import * from pyspark.sql import functions as f from decimal import * from datetime import datetime # instantiate our sparkSession and context spark = SparkSession.builder.enableHiveSupport().getOrCreate() sc = spark.sparkContext # Generating customer orc table files # load raw data as an RDD customer_data = sc.textFile("/data/tpch/customer.tbl") # map the data into an RDD split with pipe delimitations customer_split = customer_data.map(lambda l: l.split("|")) # map the split data with a row method; this is where we specificy column names and types # default type is string- UTF8 # there are issues with converting string to date and these issues have been addressed # in those tables with dates: See notes below customer_row = customer_split.map( lambda r: Row( custkey=long(r), name=r, address=r, nationkey=long(r), phone=r, acctbal=Decimal(r), mktsegment=r, comment=r )) # we can have Spark infer the schema, or apply a strict schema and identify whether or not we want null values # in this case we don't want null values for keys; and we want explicit data types to support the TPCH tables/ data model customer_schema = types.StructType([ types.StructField('custkey',types.LongType(),False) ,types.StructField('name',types.StringType()) ,types.StructField('address',types.StringType()) ,types.StructField('nationkey',types.LongType(),False) ,types.StructField('phone',types.StringType()) ,types.StructField('acctbal',types.DecimalType()) ,types.StructField('mktsegment',types.StringType()) ,types.StructField('comment',types.StringType())]) # we create a dataframe object by referencing our sparkSession class and the createDataFrame method # this method takes two arguments by default (row, schema) customer_df = spark.createDataFrame(customer_row,customer_schema) # we can now write a file of type orc by referencing our dataframe object we created customer_df.write.orc("/data/tpch/customer.orc") # read that same file we created but create a seperate dataframe object customer_df_orc = spark.read.orc("/data/tpch/customer.orc") # reference the newly created dataframe object and create a tempView for QA purposes customer_df_orc.createOrReplaceTempView("customer") # reference the sparkSession class and SQL method in order to issue SQL statements to the materialized view spark.sql("SELECT * FROM customer LIMIT 10").show() From: "☼ R Nair (रविशंकर नायर)" <ravishankar.n...@gmail.com> Date: Friday, February 9, 2018 at 7:03 AM To: "user @spark/'user @spark'/spark users/user@spark" <firstname.lastname@example.org> Subject: Re: Spark Dataframe and HIVE An update: (Sorry I missed) When I do passion_df.createOrReplaceTempView("sampleview") spark.sql("create table sample table as select * from sample view") Now, I can see table and can query as well. So why this do work from Spark and other method discussed below is not? Thanks On Fri, Feb 9, 2018 at 9:49 AM, ☼ R Nair (रविशंकर नायर) <ravishankar.n...@gmail.com<mailto:ravishankar.n...@gmail.com>> wrote: All, It has been three days continuously I am on this issue. Not getting any clue. Environment: Spark 2.2.x, all configurations are correct. hive-site.xml is in spark's conf. 1) Step 1: I created a data frame DF1 reading a csv file. 2) Did manipulations on DF1. Resulting frame is passion_df. 3) passion_df.write.format("orc").saveAsTable("sampledb.passion") 4) The metastore shows the hive table., when I do "show tables" in HIVE, I can see table name 5) I can't select in HIVE, though I can select from SPARK as spark.sql("select * from sampledb.passion") Whats going on here? Please help. Why I am not seeing data from HIVE prompt? The "describe formatted " command on the table in HIVE shows he data is is in default warehouse location ( /user/hive/warehouse) since I set it. I am not getting any definite answer anywhere. Many suggestions and answers given in Stackoverflow et al.Nothing really works. So asking experts here for some light on this, thanks Best, Ravion -- [mage removed by sender.]