blob: 0a2d074fdc7a7b1df150fe16d456626b4858f4fc [file]
<!doctype html><html><head><meta charset=utf-8><meta http-equiv=x-ua-compatible content="IE=edge"><meta name=viewport content="width=device-width,initial-scale=1"><meta name=description content><meta name=author content><title>Writes</title><link href=../css/bootstrap.css rel=stylesheet><link href=../css/markdown.css rel=stylesheet><link href=../css/katex.min.css rel=stylesheet><link href=../css/iceberg-theme.css rel=stylesheet><link href=../font-awesome-4.7.0/css/font-awesome.min.css rel=stylesheet type=text/css><link href="//fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic,700italic" rel=stylesheet type=text/css><link href=../css/termynal.css rel=stylesheet></head><body><head><script>function addAnchor(e){e.insertAdjacentHTML("beforeend",`<a href="#${e.id}" class="anchortag" ariaLabel="Anchor"> 🔗 </a>`)}document.addEventListener("DOMContentLoaded",function(){var e=document.querySelectorAll("h1[id], h2[id], h3[id], h4[id]");e&&e.forEach(addAnchor)})</script></head><nav class="navbar navbar-default" role=navigation><topsection><div class=navbar-fixed-top><div><button type=button class=navbar-toggle data-toggle=collapse data-target=div.sidebar>
<span class=sr-only>Toggle navigation</span>
<span class=icon-bar></span>
<span class=icon-bar></span>
<span class=icon-bar></span></button>
<a class="page-scroll navbar-brand" href=https://iceberg.apache.org/><img class=top-navbar-logo src=https://iceberg.apache.org/docs/UpdateSlackLink//img/iceberg-logo-icon.png> Apache Iceberg</a></div><div><input type=search class=form-control id=search-input placeholder=Search... maxlength=64 data-hotkeys=s/></div><div class=versions-dropdown><span>1.2.1</span> <i class="fa fa-chevron-down"></i><div class=versions-dropdown-content><ul><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../latest>latest</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../1.2.1>1.2.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../1.2.0>1.2.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../1.1.0>1.1.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../1.0.0>1.0.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.14.1>0.14.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.14.0>0.14.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.13.2>0.13.2</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.13.1>0.13.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.13.0>0.13.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../0.12.1>0.12.1</a></li></ul></div></div></div><div class="navbar-menu-fixed-top navbar-pages-group"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../spark-quickstart>Quickstart</a></div class="topnav-page-selection"><div class=topnav-page-selection><a id=active href=https://iceberg.apache.org/docs/UpdateSlackLink/../../docs/latest>Docs</a></div><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../releases>Releases</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../roadmap>Roadmap</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../blogs>Blogs</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../talks>Talks</a></div class="topnav-page-selection"><div class=versions-dropdown><div class=topnav-page-selection><a href>Project</a> <i class="fa fa-chevron-down"></i></div class="topnav-page-selection"><div class=versions-dropdown-content><ul><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../community>Community</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../spec>Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../view-spec>View Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../puffin-spec>Puffin Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../multi-engine-support>Multi-Engine Support</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../how-to-release>How To Release</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../terms>Terms</a></li class="topnav-page-selection"></ul></div></div><div class=versions-dropdown><div class=topnav-page-selection><a href>ASF</a> <i class="fa fa-chevron-down"></i></div class="topnav-page-selection"><div class=versions-dropdown-content><ul><li class=topnav-page-selection><a target=_blank href=https://www.apache.org/foundation/sponsorship.html>Donate</a></li class="topnav-page-selection"><li class=topnav-page-selection><a target=_blank href=https://www.apache.org/events/current-event.html>Events</a></li class="topnav-page-selection"><li class=topnav-page-selection><a target=_blank href=https://www.apache.org/licenses/>License</a></li class="topnav-page-selection"><li class=topnav-page-selection><a target=_blank href=https://www.apache.org/security/>Security</a></li class="topnav-page-selection"><li class=topnav-page-selection><a target=_blank href=https://www.apache.org/foundation/thanks.html>Sponsors</a></li class="topnav-page-selection"></ul></div></div><div class=topnav-page-selection><a href=https://github.com/apache/iceberg target=_blank><img src=https://iceberg.apache.org/docs/UpdateSlackLink//img/GitHub-Mark.png target=_blank class=top-navbar-logo></a></div><div class=topnav-page-selection><a href=https://join.slack.com/t/apache-iceberg/shared_invite/zt-1oj35f7yc-wuTEhvkiqjGLje83B7rG8A target=_blank><img src=https://iceberg.apache.org/docs/UpdateSlackLink//img/Slack_Mark_Web.png target=_blank class=top-navbar-logo></a></div></div></topsection></nav><section><div id=search-results-container><ul id=search-results></ul></div></section><body dir=" ltr"><section><div class="grid-container leftnav-and-toc"><div class="sidebar markdown-body"><div id=full><ul><li><a href=../><span>Introduction</span></a></li><li><a class="chevron-toggle collapsed" data-toggle=collapse data-parent=full href=#Tables><span>Tables</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=Tables class=collapse><ul class=sub-menu><li><a href=../branching/>Branching and Tagging</a></li><li><a href=../configuration/>Configuration</a></li><li><a href=../evolution/>Evolution</a></li><li><a href=../maintenance/>Maintenance</a></li><li><a href=../partitioning/>Partitioning</a></li><li><a href=../performance/>Performance</a></li><li><a href=../reliability/>Reliability</a></li><li><a href=../schemas/>Schemas</a></li></ul></div><li><a class=chevron-toggle data-toggle=collapse data-parent=full href=#Spark><span>Spark</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=Spark class="collapse in"><ul class=sub-menu><li><a href=../spark-ddl/>DDL</a></li><li><a href=../getting-started/>Getting Started</a></li><li><a href=../spark-procedures/>Procedures</a></li><li><a href=../spark-queries/>Queries</a></li><li><a href=../spark-structured-streaming/>Structured Streaming</a></li><li><a id=active href=../spark-writes/>Writes</a></li></ul></div><li><a class="chevron-toggle collapsed" data-toggle=collapse data-parent=full href=#Flink><span>Flink</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=Flink class=collapse><ul class=sub-menu><li><a href=../flink/>Flink Getting Started</a></li><li><a href=../flink-connector/>Flink Connector</a></li><li><a href=../flink-ddl/>Flink DDL</a></li><li><a href=../flink-queries/>Flink Queries</a></li><li><a href=../flink-writes/>Flink Writes</a></li><li><a href=../flink-actions/>Flink Actions</a></li><li><a href=../flink-configuration/>Flink Configuration</a></li></ul></div><li><a href=../hive/><span>Hive</span></a></li><li><a target=_blank href=https://trino.io/docs/current/connector/iceberg.html><span>Trino</span></a></li><li><a target=_blank href=https://prestodb.io/docs/current/connector/iceberg.html><span>Presto</span></a></li><li><a target=_blank href=https://docs.dremio.com/data-formats/apache-iceberg/><span>Dremio</span></a></li><li><a target=_blank href=https://docs.starrocks.com/en-us/main/using_starrocks/External_table#apache-iceberg-external-table><span>StarRocks</span></a></li><li><a target=_blank href=https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg.html><span>Amazon Athena</span></a></li><li><a target=_blank href=https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-iceberg-use-cluster.html><span>Amazon EMR</span></a></li><li><a target=_blank href=https://impala.apache.org/docs/build/html/topics/impala_iceberg.html><span>Impala</span></a></li><li><a target=_blank href=https://doris.apache.org/docs/dev/lakehouse/multi-catalog/iceberg><span>Doris</span></a></li><li><a class="chevron-toggle collapsed" data-toggle=collapse data-parent=full href=#Integrations><span>Integrations</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=Integrations class=collapse><ul class=sub-menu><li><a href=../aws/>AWS</a></li><li><a href=../dell/>Dell</a></li><li><a href=../jdbc/>JDBC</a></li><li><a href=../nessie/>Nessie</a></li></ul></div><li><a class="chevron-toggle collapsed" data-toggle=collapse data-parent=full href=#API><span>API</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=API class=collapse><ul class=sub-menu><li><a href=../java-api-quickstart/>Java Quickstart</a></li><li><a href=../api/>Java API</a></li><li><a href=../custom-catalog/>Java Custom Catalog</a></li></ul></div><li><a class="chevron-toggle collapsed" data-toggle=collapse data-parent=full href=#Migration><span>Migration</span>
<i class="fa fa-chevron-right"></i>
<i class="fa fa-chevron-down"></i></a></li><div id=Migration class=collapse><ul class=sub-menu><li><a href=../table-migration/>Overview</a></li><li><a href=../hive-migration/>Hive Migration</a></li><li><a href=../delta-lake-migration/>Delta Lake Migration</a></li></ul></div><li><a href=https://iceberg.apache.org/docs/UpdateSlackLink/../../javadoc/latest><span>Javadoc</span></a></li><li><a target=_blank href=https://py.iceberg.apache.org/><span>PyIceberg</span></a></li></div></div><div id=content class=markdown-body><div class=margin-for-toc><h1 id=spark-writes>Spark Writes</h1><p>To use Iceberg in Spark, first configure <a href=../spark-configuration>Spark catalogs</a>.</p><p>Some plans are only available when using <a href=../spark-configuration#sql-extensions>Iceberg SQL extensions</a> in Spark 3.</p><p>Iceberg uses Apache Spark&rsquo;s DataSourceV2 API for data source and catalog implementations. Spark DSv2 is an evolving API with different levels of support in Spark versions:</p><table><thead><tr><th>Feature support</th><th>Spark 3</th><th>Notes</th></tr></thead><tbody><tr><td><a href=#insert-into>SQL insert into</a></td><td>✔️</td><td></td></tr><tr><td><a href=#merge-into>SQL merge into</a></td><td>✔️</td><td>⚠ Requires Iceberg Spark extensions</td></tr><tr><td><a href=#insert-overwrite>SQL insert overwrite</a></td><td>✔️</td><td></td></tr><tr><td><a href=#delete-from>SQL delete from</a></td><td>✔️</td><td>⚠ Row-level delete requires Spark extensions</td></tr><tr><td><a href=#update>SQL update</a></td><td>✔️</td><td>⚠ Requires Iceberg Spark extensions</td></tr><tr><td><a href=#appending-data>DataFrame append</a></td><td>✔️</td><td></td></tr><tr><td><a href=#overwriting-data>DataFrame overwrite</a></td><td>✔️</td><td></td></tr><tr><td><a href=#creating-tables>DataFrame CTAS and RTAS</a></td><td>✔️</td><td></td></tr></tbody></table><h2 id=writing-with-sql>Writing with SQL</h2><p>Spark 3 supports SQL <code>INSERT INTO</code>, <code>MERGE INTO</code>, and <code>INSERT OVERWRITE</code>, as well as the new <code>DataFrameWriterV2</code> API.</p><h3 id=insert-into><code>INSERT INTO</code></h3><p>To append new data to a table, use <code>INSERT INTO</code>.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.<span style=color:#66d9ef>table</span> <span style=color:#66d9ef>VALUES</span> (<span style=color:#ae81ff>1</span>, <span style=color:#e6db74>&#39;a&#39;</span>), (<span style=color:#ae81ff>2</span>, <span style=color:#e6db74>&#39;b&#39;</span>)
</span></span></code></pre></div><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.<span style=color:#66d9ef>table</span> <span style=color:#66d9ef>SELECT</span> ...
</span></span></code></pre></div><h3 id=merge-into><code>MERGE INTO</code></h3><p>Spark 3 added support for <code>MERGE INTO</code> queries that can express row-level updates.</p><p>Iceberg supports <code>MERGE INTO</code> by rewriting data files that contain rows that need to be updated in an <code>overwrite</code> commit.</p><p><strong><code>MERGE INTO</code> is recommended instead of <code>INSERT OVERWRITE</code></strong> because Iceberg can replace only the affected data files, and because the data overwritten by a dynamic overwrite may change if the table&rsquo;s partitioning changes.</p><h4 id=merge-into-syntax><code>MERGE INTO</code> syntax</h4><p><code>MERGE INTO</code> updates a table, called the <em>target</em> table, using a set of updates from another query, called the <em>source</em>. The update for a row in the target table is found using the <code>ON</code> clause that is like a join condition.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span>MERGE <span style=color:#66d9ef>INTO</span> prod.db.target t <span style=color:#75715e>-- a target table
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>USING</span> (<span style=color:#66d9ef>SELECT</span> ...) s <span style=color:#75715e>-- the source updates
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ON</span> t.id <span style=color:#f92672>=</span> s.id <span style=color:#75715e>-- condition to find updates for target rows
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>WHEN</span> ... <span style=color:#75715e>-- updates
</span></span></span></code></pre></div><p>Updates to rows in the target table are listed using <code>WHEN MATCHED ... THEN ...</code>. Multiple <code>MATCHED</code> clauses can be added with conditions that determine when each match should be applied. The first matching expression is used.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> MATCHED <span style=color:#66d9ef>AND</span> s.op <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;delete&#39;</span> <span style=color:#66d9ef>THEN</span> <span style=color:#66d9ef>DELETE</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> MATCHED <span style=color:#66d9ef>AND</span> t.<span style=color:#66d9ef>count</span> <span style=color:#66d9ef>IS</span> <span style=color:#66d9ef>NULL</span> <span style=color:#66d9ef>AND</span> s.op <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;increment&#39;</span> <span style=color:#66d9ef>THEN</span> <span style=color:#66d9ef>UPDATE</span> <span style=color:#66d9ef>SET</span> t.<span style=color:#66d9ef>count</span> <span style=color:#f92672>=</span> <span style=color:#ae81ff>0</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> MATCHED <span style=color:#66d9ef>AND</span> s.op <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;increment&#39;</span> <span style=color:#66d9ef>THEN</span> <span style=color:#66d9ef>UPDATE</span> <span style=color:#66d9ef>SET</span> t.<span style=color:#66d9ef>count</span> <span style=color:#f92672>=</span> t.<span style=color:#66d9ef>count</span> <span style=color:#f92672>+</span> <span style=color:#ae81ff>1</span>
</span></span></code></pre></div><p>Source rows (updates) that do not match can be inserted:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> <span style=color:#66d9ef>NOT</span> MATCHED <span style=color:#66d9ef>THEN</span> <span style=color:#66d9ef>INSERT</span> <span style=color:#f92672>*</span>
</span></span></code></pre></div><p>Inserts also support additional conditions:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> <span style=color:#66d9ef>NOT</span> MATCHED <span style=color:#66d9ef>AND</span> s.event_time <span style=color:#f92672>&gt;</span> still_valid_threshold <span style=color:#66d9ef>THEN</span> <span style=color:#66d9ef>INSERT</span> (id, <span style=color:#66d9ef>count</span>) <span style=color:#66d9ef>VALUES</span> (s.id, <span style=color:#ae81ff>1</span>)
</span></span></code></pre></div><p>Only one record in the source data can update any given row of the target table, or else an error will be thrown.</p><h3 id=insert-overwrite><code>INSERT OVERWRITE</code></h3><p><code>INSERT OVERWRITE</code> can replace data in the table with the result of a query. Overwrites are atomic operations for Iceberg tables.</p><p>The partitions that will be replaced by <code>INSERT OVERWRITE</code> depends on Spark&rsquo;s partition overwrite mode and the partitioning of a table. <code>MERGE INTO</code> can rewrite only affected data files and has more easily understood behavior, so it is recommended instead of <code>INSERT OVERWRITE</code>.</p><h4 id=overwrite-behavior>Overwrite behavior</h4><p>Spark&rsquo;s default overwrite mode is <strong>static</strong>, but <strong>dynamic overwrite mode is recommended when writing to Iceberg tables.</strong> Static overwrite mode determines which partitions to overwrite in a table by converting the <code>PARTITION</code> clause to a filter, but the <code>PARTITION</code> clause can only reference table columns.</p><p>Dynamic overwrite mode is configured by setting <code>spark.sql.sources.partitionOverwriteMode=dynamic</code>.</p><p>To demonstrate the behavior of dynamic and static overwrites, consider a <code>logs</code> table defined by the following DDL:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>TABLE</span> prod.my_app.logs (
</span></span><span style=display:flex><span> uuid string <span style=color:#66d9ef>NOT</span> <span style=color:#66d9ef>NULL</span>,
</span></span><span style=display:flex><span> <span style=color:#66d9ef>level</span> string <span style=color:#66d9ef>NOT</span> <span style=color:#66d9ef>NULL</span>,
</span></span><span style=display:flex><span> ts <span style=color:#66d9ef>timestamp</span> <span style=color:#66d9ef>NOT</span> <span style=color:#66d9ef>NULL</span>,
</span></span><span style=display:flex><span> message string)
</span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg
</span></span><span style=display:flex><span>PARTITIONED <span style=color:#66d9ef>BY</span> (<span style=color:#66d9ef>level</span>, hours(ts))
</span></span></code></pre></div><h4 id=dynamic-overwrite>Dynamic overwrite</h4><p>When Spark&rsquo;s overwrite mode is dynamic, partitions that have rows produced by the <code>SELECT</code> query will be replaced.</p><p>For example, this query removes duplicate log events from the example <code>logs</code> table.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> OVERWRITE prod.my_app.logs
</span></span><span style=display:flex><span><span style=color:#66d9ef>SELECT</span> uuid, <span style=color:#66d9ef>first</span>(<span style=color:#66d9ef>level</span>), <span style=color:#66d9ef>first</span>(ts), <span style=color:#66d9ef>first</span>(message)
</span></span><span style=display:flex><span><span style=color:#66d9ef>FROM</span> prod.my_app.logs
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> <span style=color:#66d9ef>cast</span>(ts <span style=color:#66d9ef>as</span> date) <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;2020-07-01&#39;</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>GROUP</span> <span style=color:#66d9ef>BY</span> uuid
</span></span></code></pre></div><p>In dynamic mode, this will replace any partition with rows in the <code>SELECT</code> result. Because the date of all rows is restricted to 1 July, only hours of that day will be replaced.</p><h4 id=static-overwrite>Static overwrite</h4><p>When Spark&rsquo;s overwrite mode is static, the <code>PARTITION</code> clause is converted to a filter that is used to delete from the table. If the <code>PARTITION</code> clause is omitted, all partitions will be replaced.</p><p>Because there is no <code>PARTITION</code> clause in the query above, it will drop all existing rows in the table when run in static mode, but will only write the logs from 1 July.</p><p>To overwrite just the partitions that were loaded, add a <code>PARTITION</code> clause that aligns with the <code>SELECT</code> query filter:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> OVERWRITE prod.my_app.logs
</span></span><span style=display:flex><span>PARTITION (<span style=color:#66d9ef>level</span> <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;INFO&#39;</span>)
</span></span><span style=display:flex><span><span style=color:#66d9ef>SELECT</span> uuid, <span style=color:#66d9ef>first</span>(<span style=color:#66d9ef>level</span>), <span style=color:#66d9ef>first</span>(ts), <span style=color:#66d9ef>first</span>(message)
</span></span><span style=display:flex><span><span style=color:#66d9ef>FROM</span> prod.my_app.logs
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> <span style=color:#66d9ef>level</span> <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;INFO&#39;</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>GROUP</span> <span style=color:#66d9ef>BY</span> uuid
</span></span></code></pre></div><p>Note that this mode cannot replace hourly partitions like the dynamic example query because the <code>PARTITION</code> clause can only reference table columns, not hidden partitions.</p><h3 id=delete-from><code>DELETE FROM</code></h3><p>Spark 3 added support for <code>DELETE FROM</code> queries to remove data from tables.</p><p>Delete queries accept a filter to match rows to delete.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>DELETE</span> <span style=color:#66d9ef>FROM</span> prod.db.<span style=color:#66d9ef>table</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> ts <span style=color:#f92672>&gt;=</span> <span style=color:#e6db74>&#39;2020-05-01 00:00:00&#39;</span> <span style=color:#66d9ef>and</span> ts <span style=color:#f92672>&lt;</span> <span style=color:#e6db74>&#39;2020-06-01 00:00:00&#39;</span>
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>DELETE</span> <span style=color:#66d9ef>FROM</span> prod.db.all_events
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> session_time <span style=color:#f92672>&lt;</span> (<span style=color:#66d9ef>SELECT</span> <span style=color:#66d9ef>min</span>(session_time) <span style=color:#66d9ef>FROM</span> prod.db.good_events)
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>DELETE</span> <span style=color:#66d9ef>FROM</span> prod.db.orders <span style=color:#66d9ef>AS</span> t1
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> <span style=color:#66d9ef>EXISTS</span> (<span style=color:#66d9ef>SELECT</span> oid <span style=color:#66d9ef>FROM</span> prod.db.returned_orders <span style=color:#66d9ef>WHERE</span> t1.oid <span style=color:#f92672>=</span> oid)
</span></span></code></pre></div><p>If the delete filter matches entire partitions of the table, Iceberg will perform a metadata-only delete. If the filter matches individual rows of a table, then Iceberg will rewrite only the affected data files.</p><h3 id=update><code>UPDATE</code></h3><p>Spark 3.1 added support for <code>UPDATE</code> queries that update matching rows in tables.</p><p>Update queries accept a filter to match rows to update.</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>UPDATE</span> prod.db.<span style=color:#66d9ef>table</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>SET</span> c1 <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;update_c1&#39;</span>, c2 <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;update_c2&#39;</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> ts <span style=color:#f92672>&gt;=</span> <span style=color:#e6db74>&#39;2020-05-01 00:00:00&#39;</span> <span style=color:#66d9ef>and</span> ts <span style=color:#f92672>&lt;</span> <span style=color:#e6db74>&#39;2020-06-01 00:00:00&#39;</span>
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>UPDATE</span> prod.db.all_events
</span></span><span style=display:flex><span><span style=color:#66d9ef>SET</span> session_time <span style=color:#f92672>=</span> <span style=color:#ae81ff>0</span>, ignored <span style=color:#f92672>=</span> <span style=color:#66d9ef>true</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> session_time <span style=color:#f92672>&lt;</span> (<span style=color:#66d9ef>SELECT</span> <span style=color:#66d9ef>min</span>(session_time) <span style=color:#66d9ef>FROM</span> prod.db.good_events)
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>UPDATE</span> prod.db.orders <span style=color:#66d9ef>AS</span> t1
</span></span><span style=display:flex><span><span style=color:#66d9ef>SET</span> order_status <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;returned&#39;</span>
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> <span style=color:#66d9ef>EXISTS</span> (<span style=color:#66d9ef>SELECT</span> oid <span style=color:#66d9ef>FROM</span> prod.db.returned_orders <span style=color:#66d9ef>WHERE</span> t1.oid <span style=color:#f92672>=</span> oid)
</span></span></code></pre></div><p>For more complex row-level updates based on incoming data, see the section on <code>MERGE INTO</code>.</p><h2 id=writing-to-branches>Writing to Branches</h2><p>Branch writes can be performed via SQL by providing a branch identifier, <code>branch_yourBranch</code> in the operation.
Branch writes can also be performed as part of a write-audit-publish (WAP) workflow by specifying the <code>spark.wap.branch</code> config.
Note WAP branch and branch identifier cannot both be specified.
Also, the branch must exist before performing the write.
The operation does <strong>not</strong> create the branch if it does not exist.
For more information on branches please refer to <a href=../../tables/branching>branches</a></p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#75715e>-- INSERT (1,&#39; a&#39;) (2, &#39;b&#39;) into the audit branch.
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.<span style=color:#66d9ef>table</span>.branch_audit <span style=color:#66d9ef>VALUES</span> (<span style=color:#ae81ff>1</span>, <span style=color:#e6db74>&#39;a&#39;</span>), (<span style=color:#ae81ff>2</span>, <span style=color:#e6db74>&#39;b&#39;</span>);
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#75715e>-- MERGE INTO audit branch
</span></span></span><span style=display:flex><span><span style=color:#75715e></span>MERGE <span style=color:#66d9ef>INTO</span> prod.db.<span style=color:#66d9ef>table</span>.branch_audit t
</span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> (<span style=color:#66d9ef>SELECT</span> ...) s
</span></span><span style=display:flex><span><span style=color:#66d9ef>ON</span> t.id <span style=color:#f92672>=</span> s.id
</span></span><span style=display:flex><span><span style=color:#66d9ef>WHEN</span> ...
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#75715e>-- UPDATE audit branch
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>UPDATE</span> prod.db.<span style=color:#66d9ef>table</span>.branch_audit <span style=color:#66d9ef>AS</span> t1
</span></span><span style=display:flex><span><span style=color:#66d9ef>SET</span> val <span style=color:#f92672>=</span> <span style=color:#e6db74>&#39;c&#39;</span>
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#75715e>-- DELETE FROM audit branch
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>DELETE</span> <span style=color:#66d9ef>FROM</span> prod.dbl.<span style=color:#66d9ef>table</span>.branch_audit <span style=color:#66d9ef>WHERE</span> id <span style=color:#f92672>=</span> <span style=color:#ae81ff>2</span>;
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#75715e>-- WAP Branch write
</span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>SET</span> spark.wap.branch <span style=color:#f92672>=</span> audit<span style=color:#f92672>-</span>branch
</span></span><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.<span style=color:#66d9ef>table</span> <span style=color:#66d9ef>VALUES</span> (<span style=color:#ae81ff>3</span>, <span style=color:#e6db74>&#39;c&#39;</span>);
</span></span></code></pre></div><h2 id=writing-with-dataframes>Writing with DataFrames</h2><p>Spark 3 introduced the new <code>DataFrameWriterV2</code> API for writing to tables using data frames. The v2 API is recommended for several reasons:</p><ul><li>CTAS, RTAS, and overwrite by filter are supported</li><li>All operations consistently write columns to a table by name</li><li>Hidden partition expressions are supported in <code>partitionedBy</code></li><li>Overwrite behavior is explicit, either dynamic or by a user-supplied filter</li><li>The behavior of each operation corresponds to SQL statements<ul><li><code>df.writeTo(t).create()</code> is equivalent to <code>CREATE TABLE AS SELECT</code></li><li><code>df.writeTo(t).replace()</code> is equivalent to <code>REPLACE TABLE AS SELECT</code></li><li><code>df.writeTo(t).append()</code> is equivalent to <code>INSERT INTO</code></li><li><code>df.writeTo(t).overwritePartitions()</code> is equivalent to dynamic <code>INSERT OVERWRITE</code></li></ul></li></ul><p>The v1 DataFrame <code>write</code> API is still supported, but is not recommended.</p><div class=danger>When writing with the v1 DataFrame API in Spark 3, use <code>saveAsTable</code> or <code>insertInto</code> to load tables with a catalog.
Using <code>format("iceberg")</code> loads an isolated table reference that will not automatically refresh tables used by queries.</div><h3 id=appending-data>Appending data</h3><p>To append a dataframe to an Iceberg table, use <code>append</code>:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span><span style=color:#66d9ef>val</span> data<span style=color:#66d9ef>:</span> <span style=color:#66d9ef>DataFrame</span> <span style=color:#f92672>=</span> <span style=color:#f92672>...</span>
</span></span><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>).</span>append<span style=color:#f92672>()</span>
</span></span></code></pre></div><h3 id=overwriting-data>Overwriting data</h3><p>To overwrite partitions dynamically, use <code>overwritePartitions()</code>:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span><span style=color:#66d9ef>val</span> data<span style=color:#66d9ef>:</span> <span style=color:#66d9ef>DataFrame</span> <span style=color:#f92672>=</span> <span style=color:#f92672>...</span>
</span></span><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>).</span>overwritePartitions<span style=color:#f92672>()</span>
</span></span></code></pre></div><p>To explicitly overwrite partitions, use <code>overwrite</code> to supply a filter:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>).</span>overwrite<span style=color:#f92672>(</span>$<span style=color:#e6db74>&#34;level&#34;</span> <span style=color:#f92672>===</span> <span style=color:#e6db74>&#34;INFO&#34;</span><span style=color:#f92672>)</span>
</span></span></code></pre></div><h3 id=creating-tables>Creating tables</h3><p>To run a CTAS or RTAS, use <code>create</code>, <code>replace</code>, or <code>createOrReplace</code> operations:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span><span style=color:#66d9ef>val</span> data<span style=color:#66d9ef>:</span> <span style=color:#66d9ef>DataFrame</span> <span style=color:#f92672>=</span> <span style=color:#f92672>...</span>
</span></span><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>).</span>create<span style=color:#f92672>()</span>
</span></span></code></pre></div><p>If you have replaced the default Spark catalog (<code>spark_catalog</code>) with Iceberg&rsquo;s <code>SparkSessionCatalog</code>, do:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span><span style=color:#66d9ef>val</span> data<span style=color:#66d9ef>:</span> <span style=color:#66d9ef>DataFrame</span> <span style=color:#f92672>=</span> <span style=color:#f92672>...</span>
</span></span><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;db.table&#34;</span><span style=color:#f92672>).</span>using<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;iceberg&#34;</span><span style=color:#f92672>).</span>create<span style=color:#f92672>()</span>
</span></span></code></pre></div><p>Create and replace operations support table configuration methods, like <code>partitionedBy</code> and <code>tableProperty</code>:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>tableProperty<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;write.format.default&#34;</span><span style=color:#f92672>,</span> <span style=color:#e6db74>&#34;orc&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>partitionedBy<span style=color:#f92672>(</span>$<span style=color:#e6db74>&#34;level&#34;</span><span style=color:#f92672>,</span> days<span style=color:#f92672>(</span>$<span style=color:#e6db74>&#34;ts&#34;</span><span style=color:#f92672>))</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>createOrReplace<span style=color:#f92672>()</span>
</span></span></code></pre></div><p>The Iceberg table location can also be specified by the <code>location</code> table property:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span>data<span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.table&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>tableProperty<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;location&#34;</span><span style=color:#f92672>,</span> <span style=color:#e6db74>&#34;/path/to/location&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>createOrReplace<span style=color:#f92672>()</span>
</span></span></code></pre></div><h2 id=writing-to-partitioned-tables>Writing to partitioned tables</h2><p>Iceberg requires the data to be sorted according to the partition spec per task (Spark partition) in prior to write
against partitioned table. This applies both Writing with SQL and Writing with DataFrames.</p><div class=info>Explicit sort is necessary because Spark doesn&rsquo;t allow Iceberg to request a sort before writing as of Spark 3.0.
<a href=https://issues.apache.org/jira/browse/SPARK-23889>SPARK-23889</a> is filed to enable Iceberg to require specific
distribution & sort order to Spark.</div><div class=info>Both global sort (<code>orderBy</code>/<code>sort</code>) and local sort (<code>sortWithinPartitions</code>) work for the requirement.</div><p>Let&rsquo;s go through writing the data against below sample table:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample (
</span></span><span style=display:flex><span> id bigint,
</span></span><span style=display:flex><span> <span style=color:#66d9ef>data</span> string,
</span></span><span style=display:flex><span> category string,
</span></span><span style=display:flex><span> ts <span style=color:#66d9ef>timestamp</span>)
</span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg
</span></span><span style=display:flex><span>PARTITIONED <span style=color:#66d9ef>BY</span> (days(ts), category)
</span></span></code></pre></div><p>To write data to the sample table, your data needs to be sorted by <code>days(ts), category</code>.</p><p>If you&rsquo;re inserting data with SQL statement, you can use <code>ORDER BY</code> to achieve it, like below:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.sample
</span></span><span style=display:flex><span><span style=color:#66d9ef>SELECT</span> id, <span style=color:#66d9ef>data</span>, category, ts <span style=color:#66d9ef>FROM</span> another_table
</span></span><span style=display:flex><span><span style=color:#66d9ef>ORDER</span> <span style=color:#66d9ef>BY</span> ts, category
</span></span></code></pre></div><p>If you&rsquo;re inserting data with DataFrame, you can use either <code>orderBy</code>/<code>sort</code> to trigger global sort, or <code>sortWithinPartitions</code>
to trigger local sort. Local sort for example:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span>data<span style=color:#f92672>.</span>sortWithinPartitions<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;ts&#34;</span><span style=color:#f92672>,</span> <span style=color:#e6db74>&#34;category&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.sample&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>append<span style=color:#f92672>()</span>
</span></span></code></pre></div><p>You can simply add the original column to the sort condition for the most partition transformations, except <code>bucket</code>.</p><p>For <code>bucket</code> partition transformation, you need to register the Iceberg transform function in Spark to specify it during sort.</p><p>Let&rsquo;s go through another sample table having bucket partition:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>CREATE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample (
</span></span><span style=display:flex><span> id bigint,
</span></span><span style=display:flex><span> <span style=color:#66d9ef>data</span> string,
</span></span><span style=display:flex><span> category string,
</span></span><span style=display:flex><span> ts <span style=color:#66d9ef>timestamp</span>)
</span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg
</span></span><span style=display:flex><span>PARTITIONED <span style=color:#66d9ef>BY</span> (bucket(<span style=color:#ae81ff>16</span>, id))
</span></span></code></pre></div><p>You need to register the function to deal with bucket, like below:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span><span style=color:#66d9ef>import</span> org.apache.iceberg.spark.IcebergSpark
</span></span><span style=display:flex><span><span style=color:#66d9ef>import</span> org.apache.spark.sql.types.DataTypes
</span></span><span style=display:flex><span>
</span></span><span style=display:flex><span><span style=color:#a6e22e>IcebergSpark</span><span style=color:#f92672>.</span>registerBucketUDF<span style=color:#f92672>(</span>spark<span style=color:#f92672>,</span> <span style=color:#e6db74>&#34;iceberg_bucket16&#34;</span><span style=color:#f92672>,</span> <span style=color:#a6e22e>DataTypes</span><span style=color:#f92672>.</span><span style=color:#a6e22e>LongType</span><span style=color:#f92672>,</span> <span style=color:#ae81ff>16</span><span style=color:#f92672>)</span>
</span></span></code></pre></div><div class=info>Explicit registration of the function is necessary because Spark doesn&rsquo;t allow Iceberg to provide functions.
<a href=https://issues.apache.org/jira/browse/SPARK-27658>SPARK-27658</a> is filed to enable Iceberg to provide functions
which can be used in query.</div><p>Here we just registered the bucket function as <code>iceberg_bucket16</code>, which can be used in sort clause.</p><p>If you&rsquo;re inserting data with SQL statement, you can use the function like below:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-sql data-lang=sql><span style=display:flex><span><span style=color:#66d9ef>INSERT</span> <span style=color:#66d9ef>INTO</span> prod.db.sample
</span></span><span style=display:flex><span><span style=color:#66d9ef>SELECT</span> id, <span style=color:#66d9ef>data</span>, category, ts <span style=color:#66d9ef>FROM</span> another_table
</span></span><span style=display:flex><span><span style=color:#66d9ef>ORDER</span> <span style=color:#66d9ef>BY</span> iceberg_bucket16(id)
</span></span></code></pre></div><p>If you&rsquo;re inserting data with DataFrame, you can use the function like below:</p><div class=highlight><pre tabindex=0 style=color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4><code class=language-scala data-lang=scala><span style=display:flex><span>data<span style=color:#f92672>.</span>sortWithinPartitions<span style=color:#f92672>(</span>expr<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;iceberg_bucket16(id)&#34;</span><span style=color:#f92672>))</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>writeTo<span style=color:#f92672>(</span><span style=color:#e6db74>&#34;prod.db.sample&#34;</span><span style=color:#f92672>)</span>
</span></span><span style=display:flex><span> <span style=color:#f92672>.</span>append<span style=color:#f92672>()</span>
</span></span></code></pre></div><h2 id=type-compatibility>Type compatibility</h2><p>Spark and Iceberg support different set of types. Iceberg does the type conversion automatically, but not for all combinations,
so you may want to understand the type conversion in Iceberg in prior to design the types of columns in your tables.</p><h3 id=spark-type-to-iceberg-type>Spark type to Iceberg type</h3><p>This type conversion table describes how Spark types are converted to the Iceberg types. The conversion applies on both creating Iceberg table and writing to Iceberg table via Spark.</p><table><thead><tr><th>Spark</th><th>Iceberg</th><th>Notes</th></tr></thead><tbody><tr><td>boolean</td><td>boolean</td><td></td></tr><tr><td>short</td><td>integer</td><td></td></tr><tr><td>byte</td><td>integer</td><td></td></tr><tr><td>integer</td><td>integer</td><td></td></tr><tr><td>long</td><td>long</td><td></td></tr><tr><td>float</td><td>float</td><td></td></tr><tr><td>double</td><td>double</td><td></td></tr><tr><td>date</td><td>date</td><td></td></tr><tr><td>timestamp</td><td>timestamp with timezone</td><td></td></tr><tr><td>char</td><td>string</td><td></td></tr><tr><td>varchar</td><td>string</td><td></td></tr><tr><td>string</td><td>string</td><td></td></tr><tr><td>binary</td><td>binary</td><td></td></tr><tr><td>decimal</td><td>decimal</td><td></td></tr><tr><td>struct</td><td>struct</td><td></td></tr><tr><td>array</td><td>list</td><td></td></tr><tr><td>map</td><td>map</td><td></td></tr></tbody></table><div class=info><p>The table is based on representing conversion during creating table. In fact, broader supports are applied on write. Here&rsquo;re some points on write:</p><ul><li>Iceberg numeric types (<code>integer</code>, <code>long</code>, <code>float</code>, <code>double</code>, <code>decimal</code>) support promotion during writes. e.g. You can write Spark types <code>short</code>, <code>byte</code>, <code>integer</code>, <code>long</code> to Iceberg type <code>long</code>.</li><li>You can write to Iceberg <code>fixed</code> type using Spark <code>binary</code> type. Note that assertion on the length will be performed.</li></ul></div><h3 id=iceberg-type-to-spark-type>Iceberg type to Spark type</h3><p>This type conversion table describes how Iceberg types are converted to the Spark types. The conversion applies on reading from Iceberg table via Spark.</p><table><thead><tr><th>Iceberg</th><th>Spark</th><th>Note</th></tr></thead><tbody><tr><td>boolean</td><td>boolean</td><td></td></tr><tr><td>integer</td><td>integer</td><td></td></tr><tr><td>long</td><td>long</td><td></td></tr><tr><td>float</td><td>float</td><td></td></tr><tr><td>double</td><td>double</td><td></td></tr><tr><td>date</td><td>date</td><td></td></tr><tr><td>time</td><td></td><td>Not supported</td></tr><tr><td>timestamp with timezone</td><td>timestamp</td><td></td></tr><tr><td>timestamp without timezone</td><td></td><td>Not supported</td></tr><tr><td>string</td><td>string</td><td></td></tr><tr><td>uuid</td><td>string</td><td></td></tr><tr><td>fixed</td><td>binary</td><td></td></tr><tr><td>binary</td><td>binary</td><td></td></tr><tr><td>decimal</td><td>decimal</td><td></td></tr><tr><td>struct</td><td>struct</td><td></td></tr><tr><td>list</td><td>array</td><td></td></tr><tr><td>map</td><td>map</td><td></td></tr></tbody></table></div><div id=toc class=markdown-body><div id=full><nav id=TableOfContents><ul><li><a href=#writing-with-sql>Writing with SQL</a><ul><li><a href=#insert-into><code>INSERT INTO</code></a></li><li><a href=#merge-into><code>MERGE INTO</code></a></li><li><a href=#insert-overwrite><code>INSERT OVERWRITE</code></a></li><li><a href=#delete-from><code>DELETE FROM</code></a></li><li><a href=#update><code>UPDATE</code></a></li></ul></li><li><a href=#writing-to-branches>Writing to Branches</a></li><li><a href=#writing-with-dataframes>Writing with DataFrames</a><ul><li><a href=#appending-data>Appending data</a></li><li><a href=#overwriting-data>Overwriting data</a></li><li><a href=#creating-tables>Creating tables</a></li></ul></li><li><a href=#writing-to-partitioned-tables>Writing to partitioned tables</a></li><li><a href=#type-compatibility>Type compatibility</a><ul><li><a href=#spark-type-to-iceberg-type>Spark type to Iceberg type</a></li><li><a href=#iceberg-type-to-spark-type>Iceberg type to Spark type</a></li></ul></li></ul></nav></div></div></div></div></section></body><script src=https://iceberg.apache.org/docs/UpdateSlackLink//js/jquery-1.11.0.js></script>
<script src=https://iceberg.apache.org/docs/UpdateSlackLink//js/jquery.easing.min.js></script>
<script type=text/javascript src=https://iceberg.apache.org/docs/UpdateSlackLink//js/search.js></script>
<script src=https://iceberg.apache.org/docs/UpdateSlackLink//js/bootstrap.min.js></script>
<script src=https://iceberg.apache.org/docs/UpdateSlackLink//js/iceberg-theme.js></script></html>