https://community.databricks.com/t5/community-articles/set-up-ai-driven-optimizations-in-databricks-sql/m-p/43916#M76 <P><SPAN>With Predictive I/O for reads (GA) and updates (Public Preview), Databricks SQL can now analyze historical read and write patterns to intelligently build indexes and optimize DELETE, MERGE, and UPDATE operations.</SPAN></P> <H3><STRONG>What is Predictive I/O?</STRONG></H3> <P>Predictive I/O is a collection of Databricks optimizations that improve performance for data interactions. Predictive I/O capabilities are grouped into the following categories:</P> <UL> <LI>Accelerated reads reduce the time it takes to scan and read data.</LI> <LI>Accelerated updates reduce the amount of data that needs to be rewritten during updates, deletes, and merges.</LI> </UL> <P>Predictive I/O leverages deletion vectors to accelerate updates by reducing the frequency of full file rewrites during data modification on Delta tables. Predictive I/O optimizes <CODE>Delete, MERGE, and UPDATE operations.</CODE></P> <P>Rather than rewriting all records in a data file when any record is updated or deleted, predictive I/O uses deletion vectors to indicate records have been removed from the target data files. Supplemental data files are used to indicate updates.<BR /><BR /></P> <H3><STRONG>How to get started:</STRONG></H3> <P>1. Use serverless and pro types of SQL warehouses + Photon-accelerated clusters running Databricks Runtime 11.2 and above.</P> <P><SPAN>2. Enable support for <A href="https://docs.databricks.com/en/delta/deletion-vectors.html" target="_blank" rel="noopener">deletion vectors</A> on a Delta Lake table by setting a Delta Lake table property as shown following: </SPAN></P> <PRE><SPAN>ALTER TABLE &lt;table-name&gt; SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);</SPAN></PRE> <P>Deletion vectors are a storage optimization feature that can be enabled on Delta Lake tables. Click <A href="https://docs.databricks.com/en/delta/deletion-vectors.html" target="_self">here to learn more.</A></P> <H3><STRONG><BR />Things to consider:</STRONG></H3> <P><SPAN>When you enable deletion vectors, the table protocol version is upgraded. <STRONG>Table protocol version upgrades are not reversible.</STRONG> After upgrading, the table will not be readable by Delta Lake clients that do not support deletion vectors. See <A class="" href="https://docs.databricks.com/en/delta/feature-compatibility.html" target="_blank" rel="noopener"> <SPAN>How does Databricks manage Delta Lake feature compatibility?</SPAN> </A> </SPAN></P> <P><SPAN>Predictive I/O updates share all limitations with deletion vectors. In Databricks Runtime 12.1 and greater, the following limitations exist:</SPAN></P> <UL class=""> <LI> <P>Delta Sharing is not supported on tables with deletion vectors enabled.</P> </LI> <LI> <P>You cannot <A class="" href="https://docs.databricks.com/en/delta/generate-manifest.html" target="_blank" rel="noopener"> <SPAN>generate a manifest file for a table with deletion vectors present</SPAN> </A>. Run <CODE>REORG TABLE ... APPLY (PURGE)</CODE> and ensure no concurrent write operations are running in order to generate a manifest.</P> </LI> <LI> <P><SPAN>You cannot </SPAN><A style="font-family: inherit; background-color: #ffffff;" href="https://docs.delta.io/latest/presto-integration.html#step-3-update-manifests" target="_blank" rel="noopener">incrementally generate manifest files for a table with deletion vectors enabled.</A></P> </LI> </UL> <P><SPAN>&nbsp;</SPAN></P> <H3><STRONG>Resources:</STRONG></H3> <UL class=""> <LI><SPAN> <A href="https://www.youtube.com/watch?v=TB8xrVeAbUo" target="_self">What's new in Databricks SQL (video)</A> </SPAN></LI> <LI><A href="https://docs.databricks.com/en/optimizations/predictive-io.html#:~:text=Predictive%20I%2FO%20leverages%20deletion,%2C%20MERGE%20%2C%20and%20UPDATE%20operations." target="_self"> <SPAN>What is Predictive I/O? (doc)</SPAN> </A></LI> <LI><A href="https://youtu.be/h4z4vBoxQ6s?t=6560" target="_self">Reynold Xin's Keynote (video)</A></LI> <LI><SPAN> <A href="https://www.databricks.com/dataaisummit/session/databricks-sql-serverless-under-hood-how-we-use-ml-get-best-priceperformance/" target="_blank" rel="noopener">Databricks SQL Serverless Under the Hood: How We Use ML to Get the Best Price/Performance (video)</A> </SPAN></LI> </UL> Thu, 14 Sep 2023 08:05:19 GMT Sujitha 2023-09-14T08:05:19Z https://community.databricks.com/t5/community-articles/set-up-ai-driven-optimizations-in-databricks-sql/m-p/43916#M76 <P><SPAN>Learn how Databricks SQL can analyze historical read and write patterns to intelligently build indexes and optimize DELETE, MERGE, and UPDATE operations.&nbsp;</SPAN></P> <P>&nbsp;</P> Thu, 14 Sep 2023 08:05:19 GMT https://community.databricks.com/t5/community-articles/set-up-ai-driven-optimizations-in-databricks-sql/m-p/43916#M76 Sujitha 2023-09-14T08:05:19Z