Cannot Query external Hive table from Spark

Question

I have a Hive external table as TEXTFILE FORMAT

hive> SHOW CRAETE TABLE customers;
CREATE EXTERNAL TABLE `customers`(
    `id` int, 
    `name` string, 
    `age` int, 
    `address` string, 
    `salary` double)
 COMMENT 'Customer Details'
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
 WITH SERDEPROPERTIES ('field.delim'=',','line.delim'='\n','serialization.format'=',')
 STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
           OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
 LOCATION 'hdfs://localhost:9000/user/hive/warehouse/shopping.db/customers'
 TBLPROPERTIES ('skip.header.line.count'='1','transient_lastDdlTime'='1605818870')

and a spark session as follows :

val warehouseLocation:String = "hdfs://localhost:9000/user/hive/warehouse/"
val spark = SparkSession.builder()
  .appName("Spark Extract")
  .enableHiveSupport()
  .master("local[*]")
  .getOrCreate()

another line that extracts the tables from hive using :

val df: DataFrame = spark.sql("SELECT * FROM database.table")

extracting the table with spark.sql("QUERY") gives the following exception:

java.lang.RuntimeException: hdfs://localhost:9000/user/hive/warehouse/shopping.db/customers/customers.txt is not a Parquet file. expected magic number at tail [80, 65, 82, 49] but found [46, 48, 48, 10]
at org.apache.parquet.hadoop.ParquetFileReader.readFooter(ParquetFileReader.java:476)
at org.apache.parquet.hadoop.ParquetFileReader.readFooter(ParquetFileReader.java:445)
at org.apache.parquet.hadoop.ParquetFileReader.readFooter(ParquetFileReader.java:401)
at org.apache.spark.sql.execution.datasources.parquet.SpecificParquetRecordReaderBase.initialize(SpecificParquetRecordReaderBase.java:106)
at org.apache.spark.sql.execution.datasources.parquet.VectorizedParquetRecordReader.initialize(VectorizedParquetRecordReader.java:133)
at org.apache.spark.sql.execution.datasources.parquet.ParquetFileFormat$$anonfun$buildReaderWithPartitionValues$1.apply(ParquetFileFormat.scala:404)
at org.apache.spark.sql.execution.datasources.parquet.ParquetFileFormat$$anonfun$buildReaderWithPartitionValues$1.apply(ParquetFileFormat.scala:345)
at org.apache.spark.sql.execution.datasources.FileScanRDD$$anon$1.org$apache$spark$sql$execution$datasources$FileScanRDD$$anon$$readCurrentFile(FileScanRDD.scala:128)
at org.apache.spark.sql.execution.datasources.FileScanRDD$$anon$1.nextIterator(FileScanRDD.scala:182)
at org.apache.spark.sql.execution.datasources.FileScanRDD$$anon$1.hasNext(FileScanRDD.scala:109)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage1.scan_nextBatch_0$(Unknown Source)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage1.processNext(Unknown Source)
at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
at org.apache.spark.sql.execution.WholeStageCodegenExec$$anonfun$10$$anon$1.hasNext(WholeStageCodegenExec.scala:614)
at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:253)
at org.apache.spark.sql.execution.SparkPlan$$anonfun$2.apply(SparkPlan.scala:247)
at org.apache.spark.rdd.RDD$$anonfun$mapPartitionsInternal$1$$anonfun$apply$25.apply(RDD.scala:836)
at org.apache.spark.rdd.RDD$$anonfun$mapPartitionsInternal$1$$anonfun$apply$25.apply(RDD.scala:836)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:49)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:49)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:324)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:288)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
at org.apache.spark.scheduler.Task.run(Task.scala:109)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

I tried various methods even:

hive> alter table customers SET FILEFORMAT PARQUET;

Please assist

@OneCricketeer i've edit the question to include customers DDL — Gontse Kekane

OneCricketeer OneCricketeer · Accepted Answer · 2020-11-19T21:24:41

I tried various methods even:

 hive> alter table customers SET FILEFORMAT PARQUET;

You need to remove the customers.txt file from the table if it is not parquet or you need to redefine the table to have a text fileformat

Cannot Query external Hive table from Spark

1 Answers