Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago
Solved

Seeking partition strategy

I am in early phase of ingestion-building and delta table creation. I know that having partition even increases the performance. Therefore, I am seeking a partitioning strategy on the delta tables. ...
data engineering
Lakehouse
Notebook
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi smpa01 ,

     

    Thanks for the reply from frithjof_v .

     

    To verify partitions on a managed delta table, there are the following methods:

     

    1. Delta Lake stores partitioned data in a nested catalog structure. You can navigate to where the table is stored and examine the catalog structure to view the partitions.

     

    2. Check for the existence of partitions by using a SQL query.

     

    SELECT DISTINCT emp_id FROM people

     

     

    3. Use the Delta Lake API to check for partitions.

     

    from delta.tables import DeltaTable

delta_table = DeltaTable.forName(spark, "people")
delta_table.toDF().select("emp_id ").distinct().show()

     

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi smpa01 ,

 

Thanks for the reply from lbendlin .

 

Partitioning by columns with a low cardinality is often recommended.

 

High cardinality : more partitions result in better parallelism but at the cost of increased overhead of managing many small files and the performance degradation of too many small partitions.


Low cardinality : fewer partitions are easier to manage.

 

Here are some tests I've done on partitions that you can refer to. Here's my data:

 

Use the following code to add a new cell; save the DataFrame and partition the data by Year and Month:

 

orders_df.write.partitionBy("Year", "Month").mode("overwrite").parquet("Files/partitioned_data")
print ("Transformed data saved!")

 

 

Check the Files folder to see if the partition folder was created successfully.

 

Use the following code to add a new cell to load a new data frame from the orders.parquet file:

 

orders_2021_df = spark.read.format("parquet").load("Files/partitioned_data/Year=2021/Month=*")
display(orders_2021_df)

 

 

Note that the partitioned columns specified in the paths (Year and Month) are not included in the DataFrame.

 

When you append new data to a Delta table, Delta Lake automatically creates a new partition based on the specified partition column. If the partition already exists, the data is appended to the existing partition.

 

More information on partitioning can be found in the following documentation:

Adding and Deleting Partitions in Delta Lake tables | Delta Lake

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

  • smpa01's avatar
    smpa01
    Community Champion
    2 years ago

      thanks I have managed to create partitons on maged delta tables using both SQL and Delta table API. 

    But I see that you are using Dataframe API. Can you use partition by in this API when saveAsTable to Delta to create partitions within table (as SQL / Delta table API would do)

     

    df.write.partitionBy('emp_id').mode('append').format('delta').saveAsTable('people')

    Anonymous

     

    Also, do you know how can I verify the partitions on managed delta tables? I tried the following it did not work

     

    //create table with partition
    spark.sql(f"""
    CREATE TABLE IF NOT EXISTS {table_name} (
        {query_string}
    )
    USING DELTA
    PARTITIONED BY ({partition_definition})
    LOCATION '{table_location}'

//verification
spark.sql("SHOW PARTITIONS StagingLakehouse.tbl").show()

//the above shows following
AnalysisException: Table spark_catalog.StagingLakehouse.tbl does not support partition management.;

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi smpa01 ,

       

      Thanks for the reply from frithjof_v .

       

      To verify partitions on a managed delta table, there are the following methods:

       

      1. Delta Lake stores partitioned data in a nested catalog structure. You can navigate to where the table is stored and examine the catalog structure to view the partitions.

       

      2. Check for the existence of partitions by using a SQL query.

       

      SELECT DISTINCT emp_id FROM people

       

       

      3. Use the Delta Lake API to check for partitions.

       

      from delta.tables import DeltaTable

delta_table = DeltaTable.forName(spark, "people")
delta_table.toDF().select("emp_id ").distinct().show()

       

       

      If you have any other questions please feel free to contact me.

       

      Best Regards,
      Yang
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!