Databricks create view partitioning fields on path

Question

I think it's a noob question but I couldn't find a answer at all. Let's assume I already have my data organized like this /mnt/raw/mydata/YYYY/MM/DD:

Example: /mnt/raw/mydata/2020/10/20

where YYYY is the year, MM is month and DD day. I would like to create a view that can map fields to the folder name. I've only seen examples to create views with 'YEAR=2020'. Is that possible?

It's related to partition discovery described here https://spark.apache.org/docs/latest/sql-data-sources-parquet.html but my folders don't have the field name. I would like to know it I can spedicy that the fisrt level is the field YEAR, the second is the Month and the third is the day.

CREATE TEMPORARY VIEW parquetTable
USING org.apache.spark.sql.parquet
OPTIONS (
  path "examples/src/main/resources/people.parquet"
)

Sorry I was actually talinking about creating views and I'm with data that already exists, maybe I have to padronize my forders in my datalake to have te fiend name on path. — Alex

Arjoon Arjoon · Accepted Answer · 2020-10-21T15:50:07

Yes, you can have folder break down by the distinct values of a field. This can be achieved through partitioning your table/dataframe. It is recommended that you store your data in parquet format to optimize for space and that your folder structure (i.e.: chosen partitions) contains data that is approx. 1GB in file size. See the links below for more details:

https://spark.apache.org/docs/3.0.1/sql-data-sources-parquet.html#partition-discovery https://docs.databricks.com/delta/best-practices.html#choose-the-right-partition-column https://docs.databricks.com/spark/latest/spark-sql/language-manual/sql-ref-syntax-ddl-create-table-datasource.html

CREATE OR REPLACE TABLE MyTable USING parquet OPTIONS (path "/mnt/raw/mydata") PARTITIONED BY (year, month, day)

Databricks create view partitioning fields on path

1 Answers