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!

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!