Spark (Databricks) unmanaged table from SQL not processing headers

user_8577305 0
Viewed 398

Trying to create an unmanaged table in Spark (Databricks) from a CSV file using the SQL API. But first row is not being used as headers.

Image 2, shows that the first row is correct when using the Dataframe API to create an unmanaged table. The Dataframe was loaded from the same csv file.

However, Image 1, shows that when creating an unmanaged table from a CSV file data source in SQL, does not process the first row as headers. Am I leaving out some "headers" option? And if so, how would that be coded?

enter image description here

enter image description here

Dataframe API

databricks apache-spark sql
1 Answers

You just need to provide OPTIONS as it's specified in the documentation.

In the that options block you can list key/value pairs that matches to the options specific to the Spark CSV reader, for example, options ('header' = 'true', 'sep' = ',') will force Spark to ignore header line, and set separator to comma. You can also add the 'inferSchema' = true into options, in this case you can just omit the columns declaration - Spark will infer it for you (it's ok for small datasets, but not for the big ones):

create table test.test using csv 
options ('header' = 'true', 'sep' = ',', 'inferSchema' = true) 
location '/databricks-datasets/Rdatasets/data-001/csv/COUNT/affairs.csv'
Alex_Ott_186270
Related