| <!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’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>'a'</span>), (<span style=color:#ae81ff>2</span>, <span style=color:#e6db74>'b'</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’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>'delete'</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>'increment'</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>'increment'</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>></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’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’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’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>'2020-07-01'</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’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>'INFO'</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>'INFO'</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>>=</span> <span style=color:#e6db74>'2020-05-01 00:00:00'</span> <span style=color:#66d9ef>and</span> ts <span style=color:#f92672><</span> <span style=color:#e6db74>'2020-06-01 00:00:00'</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><</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>'update_c1'</span>, c2 <span style=color:#f92672>=</span> <span style=color:#e6db74>'update_c2'</span> |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>WHERE</span> ts <span style=color:#f92672>>=</span> <span style=color:#e6db74>'2020-05-01 00:00:00'</span> <span style=color:#66d9ef>and</span> ts <span style=color:#f92672><</span> <span style=color:#e6db74>'2020-06-01 00:00:00'</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><</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>'returned'</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,' a') (2, 'b') 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>'a'</span>), (<span style=color:#ae81ff>2</span>, <span style=color:#e6db74>'b'</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>'c'</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>'c'</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>"prod.db.table"</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>"prod.db.table"</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>"prod.db.table"</span><span style=color:#f92672>).</span>overwrite<span style=color:#f92672>(</span>$<span style=color:#e6db74>"level"</span> <span style=color:#f92672>===</span> <span style=color:#e6db74>"INFO"</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>"prod.db.table"</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’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>"db.table"</span><span style=color:#f92672>).</span>using<span style=color:#f92672>(</span><span style=color:#e6db74>"iceberg"</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>"prod.db.table"</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>"write.format.default"</span><span style=color:#f92672>,</span> <span style=color:#e6db74>"orc"</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>"level"</span><span style=color:#f92672>,</span> days<span style=color:#f92672>(</span>$<span style=color:#e6db74>"ts"</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>"prod.db.table"</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>"location"</span><span style=color:#f92672>,</span> <span style=color:#e6db74>"/path/to/location"</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’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’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’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’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>"ts"</span><span style=color:#f92672>,</span> <span style=color:#e6db74>"category"</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>"prod.db.sample"</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’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>"iceberg_bucket16"</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’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’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’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>"iceberg_bucket16(id)"</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>"prod.db.sample"</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’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> |