Query Does Not Skip Header Row On External Table - Knowledge Base

Databricks Help Center

Main Navigation

• Help Center
• Documentation
• Knowledge Base
• Community
• Training
• Feedback

• Home
• All articles
• SQL with Apache Spark
• Query does not skip header row on external table

Problem

You are attempting to query an external Hive table, but it keeps failing to skip the header row, even though TBLPROPERTIES ('skip.header.line.count'='1') is set in the HiveContext.

You can reproduce the issue by creating a table with this sample code.

%sql CREATE EXTERNAL TABLE school_test_score (   `school` varchar(254),   `student_id` varchar(254),   `gender` varchar(254),   `pretest` varchar(254),   `posttest` varchar(254)) ROW FORMAT DELIMITED   FIELDS TERMINATED BY ','   LINES TERMINATED BY '\n' STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION   'dbfs:/FileStore/table_header/' TBLPROPERTIES (    'skip.header.line.count'='1' )

If you try to select the first five rows from the table, the first row is the header row.

%sql SELECT * FROM school_test_score LIMIT 5

Cause

If you query directly from Hive, the header row is correctly skipped. Apache Spark does not recognize the skip.header.line.count property in HiveContext, so it does not skip the header row.

Spark is behaving as designed.

Solution

You need to use Spark options to create the table with a header option.

%sql CREATE TABLE student_test_score (school String, student_id String, gender String, pretest String, posttest String) USING CSV OPTIONS (path "dbfs:/FileStore/table_header/",         delimiter ",",         header "true")         ;

Select the first five rows from the table and the header row is not included.

%sql SELECT * FROM school_test_score LIMIT 5

Additional Informations

Related Articles

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

  Problem When you try to query a table or view, you get this error: AnalysisExcept...

• Generate unique increasing numeric values

  This article shows you how to use Apache Spark functions to generate unique incre...

• Broadcast join exceeds threshold, returns out of memory error

  Problem You are attempting to join two large tables, projecting selected columns ...

• Duplicate columns in the metadata error

  Problem Your Apache Spark job is processing a Delta table when the job fails with...

Related Articles

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

  Problem When you try to query a table or view, you get this error: AnalysisExcept...

• Generate unique increasing numeric values

  This article shows you how to use Apache Spark functions to generate unique incre...

• Broadcast join exceeds threshold, returns out of memory error

  Problem You are attempting to join two large tables, projecting selected columns ...

• Duplicate columns in the metadata error

  Problem Your Apache Spark job is processing a Delta table when the job fails with...

Contact Us

If you still have questions or prefer to get help directly from an agent, please submit a request. We’ll get back to you as soon as possible.

Your email address Subject Description

Please enter the details of your request. A member of our support staff will respond as soon as possible.