DATABRICKS SQL - can't read data from partitioned parquet file

Question

I'm trying to read parquet files structured as:

filename/year=2020/month=12/day=1

files are under the following Mounted AzureStorage as following logic: /mnt/silver/root_folder/folder_A/parquet/year=2020/month=01/day=1

I'm trying to create a table, using this sintax:

CREATE  TABLE tablename
(
  FIELD1 string,
  ...
  ,FIELDn Date
  ,Year INT
  ,Month INT
  ,Day INT
)
USING org.apache.spark.sql.parquet 
LOCATION '/mnt/silver/root_folder/folder_A/parquet/'
OPTIONS(  'compression'='snappy')
PARTITIONED BY (Year, Month, Day)

But all options I tried for LOCATION gets no Results. I already tried: /mnt/silver/folder/folder/parquet/* and also many variations of it.

Any suggestion please?

Alex Ott Alex Ott · Accepted Answer · 2021-02-24T17:38:09

You need to execute MSCK REPAIR TABLE <table_name> or ALTER TABLE <table_name> RECOVER PARTITIONS - any of them forces to re-discover data in partitions.

From documentation:

When creating a table using PARTITIONED BY clause, partitions are generated and registered in the Hive metastore. However, if the partitioned table is created from existing data, partitions are not registered automatically in the Hive metastore

P.S. when you use Delta, that's done automatically, so that's one of the good reasons for using it :-)

DATABRICKS SQL - can't read data from partitioned parquet file

1 Answers