| <!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>DDL</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/fd-add-github-release//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.4.0</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/fd-add-github-release/../latest>latest</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.4.0>1.4.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.3.1>1.3.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.3.0>1.3.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.2.1>1.2.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.2.0>1.2.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.1.0>1.1.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../1.0.0>1.0.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.14.1>0.14.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.14.0>0.14.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.13.2>0.13.2</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.13.1>0.13.1</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.13.0>0.13.0</a></li><li class=versions-dropdown-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../0.12.1>0.12.1</a></li></ul></div></div></div><div class="navbar-menu-fixed-top navbar-pages-group"><div class=versions-dropdown><div class=topnav-page-selection><a href>Quickstart</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/fd-add-github-release/../../hive-quickstart>Hive</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../spark-quickstart>Spark</a></li class="topnav-page-selection"></ul></div></div><div class=topnav-page-selection><a id=active href=https://iceberg.apache.org/docs/fd-add-github-release/../../docs/latest>Docs</a></div><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../releases>Releases</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../roadmap>Roadmap</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../blogs>Blogs</a></div class="topnav-page-selection"><div class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../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/fd-add-github-release/../../community>Community</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../spec>Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../view-spec>View Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../puffin-spec>Puffin Spec</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../multi-engine-support>Multi-Engine Support</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../how-to-release>How To Release</a></li class="topnav-page-selection"><li class=topnav-page-selection><a href=https://iceberg.apache.org/docs/fd-add-github-release/../../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/fd-add-github-release//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-1znkcg5zm-7_FE~pcox347XwZE3GNfPg target=_blank><img src=https://iceberg.apache.org/docs/fd-add-github-release//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=../getting-started/>Getting Started</a></li><li><a id=active href=../spark-ddl/>DDL</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 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://clickhouse.com/docs/en/engines/table-engines/integrations/iceberg><span>ClickHouse</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.io/en-us/latest/data_source/catalog/iceberg_catalog><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/fd-add-github-release/../../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-ddl>Spark DDL</h1><p>To use Iceberg in Spark, first configure <a href=../spark-configuration>Spark catalogs</a>. Iceberg uses Apache Spark’s DataSourceV2 API for data source and catalog implementations.</p><h2 id=create-table><code>CREATE TABLE</code></h2><p>Spark 3 can create tables in any Iceberg catalog with the clause <code>USING iceberg</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>CREATE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample ( |
| </span></span><span style=display:flex><span> id bigint <span style=color:#66d9ef>COMMENT</span> <span style=color:#e6db74>'unique id'</span>, |
| </span></span><span style=display:flex><span> <span style=color:#66d9ef>data</span> string) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg |
| </span></span></code></pre></div><p>Iceberg will convert the column type in Spark to corresponding Iceberg type. Please check the section of <a href=../spark-writes#spark-type-to-iceberg-type>type compatibility on creating table</a> for details.</p><p>Table create commands, including CTAS and RTAS, support the full range of Spark create clauses, including:</p><ul><li><code>PARTITIONED BY (partition-expressions)</code> to configure partitioning</li><li><code>LOCATION '(fully-qualified-uri)'</code> to set the table location</li><li><code>COMMENT 'table documentation'</code> to set a table description</li><li><code>TBLPROPERTIES ('key'='value', ...)</code> to set <a href=../configuration>table configuration</a></li></ul><p>Create commands may also set the default format with the <code>USING</code> clause. This is only supported for <code>SparkCatalog</code> because Spark handles the <code>USING</code> clause differently for the built-in catalog.</p><h3 id=partitioned-by><code>PARTITIONED BY</code></h3><p>To create a partitioned table, use <code>PARTITIONED BY</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>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><span style=color:#66d9ef>USING</span> iceberg |
| </span></span><span style=display:flex><span>PARTITIONED <span style=color:#66d9ef>BY</span> (category) |
| </span></span></code></pre></div><p>The <code>PARTITIONED BY</code> clause supports transform expressions to create <a href=../partitioning>hidden partitions</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:#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), days(ts), category) |
| </span></span></code></pre></div><p>Supported transformations are:</p><ul><li><code>years(ts)</code>: partition by year</li><li><code>months(ts)</code>: partition by month</li><li><code>days(ts)</code> or <code>date(ts)</code>: equivalent to dateint partitioning</li><li><code>hours(ts)</code> or <code>date_hour(ts)</code>: equivalent to dateint and hour partitioning</li><li><code>bucket(N, col)</code>: partition by hashed value mod N buckets</li><li><code>truncate(L, col)</code>: partition by value truncated to L<ul><li>Strings are truncated to the given length</li><li>Integers and longs truncate to bins: <code>truncate(10, i)</code> produces partitions 0, 10, 20, 30, …</li></ul></li></ul><h2 id=create-table--as-select><code>CREATE TABLE ... AS SELECT</code></h2><p>Iceberg supports CTAS as an atomic operation when using a <a href=../spark-configuration#catalog-configuration><code>SparkCatalog</code></a>. CTAS is supported, but is not atomic when using <a href=../spark-configuration#replacing-the-session-catalog><code>SparkSessionCatalog</code></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:#66d9ef>CREATE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>SELECT</span> ... |
| </span></span></code></pre></div><p>The newly created table won’t inherit the partition spec and table properties from the source table in SELECT, you can use PARTITIONED BY and TBLPROPERTIES in CTAS to declare partition spec and table properties for the new 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><span style=color:#66d9ef>USING</span> iceberg |
| </span></span><span style=display:flex><span>PARTITIONED <span style=color:#66d9ef>BY</span> (part) |
| </span></span><span style=display:flex><span>TBLPROPERTIES (<span style=color:#e6db74>'key'</span><span style=color:#f92672>=</span><span style=color:#e6db74>'value'</span>) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>SELECT</span> ... |
| </span></span></code></pre></div><h2 id=replace-table--as-select><code>REPLACE TABLE ... AS SELECT</code></h2><p>Iceberg supports RTAS as an atomic operation when using a <a href=../spark-configuration#catalog-configuration><code>SparkCatalog</code></a>. RTAS is supported, but is not atomic when using <a href=../spark-configuration#replacing-the-session-catalog><code>SparkSessionCatalog</code></a>.</p><p>Atomic table replacement creates a new snapshot with the results of the <code>SELECT</code> query, but keeps table history.</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>REPLACE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>SELECT</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>REPLACE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </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> (part) |
| </span></span><span style=display:flex><span>TBLPROPERTIES (<span style=color:#e6db74>'key'</span><span style=color:#f92672>=</span><span style=color:#e6db74>'value'</span>) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>SELECT</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>CREATE</span> <span style=color:#66d9ef>OR</span> <span style=color:#66d9ef>REPLACE</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>USING</span> iceberg |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>SELECT</span> ... |
| </span></span></code></pre></div><p>The schema and partition spec will be replaced if changed. To avoid modifying the table’s schema and partitioning, use <code>INSERT OVERWRITE</code> instead of <code>REPLACE TABLE</code>. |
| The new table properties in the <code>REPLACE TABLE</code> command will be merged with any existing table properties. The existing table properties will be updated if changed else they are preserved.</p><h2 id=drop-table><code>DROP TABLE</code></h2><p>The drop table behavior changed in 0.14.</p><p>Prior to 0.14, running <code>DROP TABLE</code> would remove the table from the catalog and delete the table contents as well.</p><p>From 0.14 onwards, <code>DROP TABLE</code> would only remove the table from the catalog. |
| In order to delete the table contents <code>DROP TABLE PURGE</code> should be used.</p><h3 id=drop-table-1><code>DROP TABLE</code></h3><p>To drop the table from the catalog, run:</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>DROP</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span></code></pre></div><h3 id=drop-table-purge><code>DROP TABLE PURGE</code></h3><p>To drop the table from the catalog and delete the table’s contents, run:</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>DROP</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample PURGE |
| </span></span></code></pre></div><h2 id=alter-table><code>ALTER TABLE</code></h2><p>Iceberg has full <code>ALTER TABLE</code> support in Spark 3, including:</p><ul><li>Renaming a table</li><li>Setting or removing table properties</li><li>Adding, deleting, and renaming columns</li><li>Adding, deleting, and renaming nested fields</li><li>Reordering top-level columns and nested struct fields</li><li>Widening the type of <code>int</code>, <code>float</code>, and <code>decimal</code> fields</li><li>Making required columns optional</li></ul><p>In addition, <a href=../spark-configuration#sql-extensions>SQL extensions</a> can be used to add support for partition evolution and setting a table’s write order</p><h3 id=alter-table--rename-to><code>ALTER TABLE ... RENAME TO</code></h3><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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>RENAME</span> <span style=color:#66d9ef>TO</span> prod.db.new_name |
| </span></span></code></pre></div><h3 id=alter-table--set-tblproperties><code>ALTER TABLE ... SET TBLPROPERTIES</code></h3><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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>SET</span> TBLPROPERTIES ( |
| </span></span><span style=display:flex><span> <span style=color:#e6db74>'read.split.target-size'</span><span style=color:#f92672>=</span><span style=color:#e6db74>'268435456'</span> |
| </span></span><span style=display:flex><span>) |
| </span></span></code></pre></div><p>Iceberg uses table properties to control table behavior. For a list of available properties, see <a href=../configuration>Table configuration</a>.</p><p><code>UNSET</code> is used to remove properties:</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample UNSET TBLPROPERTIES (<span style=color:#e6db74>'read.split.target-size'</span>) |
| </span></span></code></pre></div><p><code>SET TBLPROPERTIES</code> can also be used to set the table comment (description):</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>SET</span> TBLPROPERTIES ( |
| </span></span><span style=display:flex><span> <span style=color:#e6db74>'comment'</span> <span style=color:#f92672>=</span> <span style=color:#e6db74>'A table comment.'</span> |
| </span></span><span style=display:flex><span>) |
| </span></span></code></pre></div><h3 id=alter-table--add-column><code>ALTER TABLE ... ADD COLUMN</code></h3><p>To add a column to Iceberg, use the <code>ADD COLUMNS</code> clause with <code>ALTER TABLE</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> COLUMNS ( |
| </span></span><span style=display:flex><span> new_column string <span style=color:#66d9ef>comment</span> <span style=color:#e6db74>'new_column docs'</span> |
| </span></span><span style=display:flex><span> ) |
| </span></span></code></pre></div><p>Multiple columns can be added at the same time, separated by commas.</p><p>Nested columns should be identified using the full column name:</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>-- create a struct column |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> point struct<span style=color:#f92672><</span>x: double, y: double<span style=color:#f92672>></span>; |
| </span></span><span style=display:flex><span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- add a field to the struct |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> point.z double |
| </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:#75715e>-- create a nested array column of struct |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> points array<span style=color:#f92672><</span>struct<span style=color:#f92672><</span>x: double, y: double<span style=color:#f92672>>></span>; |
| </span></span><span style=display:flex><span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- add a field to the struct within an array. Using keyword 'element' to access the array's element column. |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> points.element.z double |
| </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:#75715e>-- create a map column of struct key and struct value |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> points <span style=color:#66d9ef>map</span><span style=color:#f92672><</span>struct<span style=color:#f92672><</span>x: int<span style=color:#f92672>></span>, struct<span style=color:#f92672><</span>a: int<span style=color:#f92672>>></span>; |
| </span></span><span style=display:flex><span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- add a field to the value struct in a map. Using keyword 'value' to access the map's value column. |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> points.value.b int |
| </span></span></code></pre></div><p>Note: Altering a map ‘key’ column by adding columns is not allowed. Only map values can be updated.</p><p>Add columns in any position by adding <code>FIRST</code> or <code>AFTER</code> clauses:</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> new_column bigint <span style=color:#66d9ef>AFTER</span> other_column |
| </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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ADD</span> <span style=color:#66d9ef>COLUMN</span> nested.new_column bigint <span style=color:#66d9ef>FIRST</span> |
| </span></span></code></pre></div><h3 id=alter-table--rename-column><code>ALTER TABLE ... RENAME COLUMN</code></h3><p>Iceberg allows any field to be renamed. To rename a field, use <code>RENAME COLUMN</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>RENAME</span> <span style=color:#66d9ef>COLUMN</span> <span style=color:#66d9ef>data</span> <span style=color:#66d9ef>TO</span> payload |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>RENAME</span> <span style=color:#66d9ef>COLUMN</span> <span style=color:#66d9ef>location</span>.lat <span style=color:#66d9ef>TO</span> latitude |
| </span></span></code></pre></div><p>Note that nested rename commands only rename the leaf field. The above command renames <code>location.lat</code> to <code>location.latitude</code></p><h3 id=alter-table--alter-column><code>ALTER TABLE ... ALTER COLUMN</code></h3><p>Alter column is used to widen types, make a field optional, set comments, and reorder fields.</p><p>Iceberg allows updating column types if the update is safe. Safe updates are:</p><ul><li><code>int</code> to <code>bigint</code></li><li><code>float</code> to <code>double</code></li><li><code>decimal(P,S)</code> to <code>decimal(P2,S)</code> when P2 > P (scale cannot change)</li></ul><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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> measurement <span style=color:#66d9ef>TYPE</span> double |
| </span></span></code></pre></div><p>To add or remove columns from a struct, use <code>ADD COLUMN</code> or <code>DROP COLUMN</code> with a nested column name.</p><p>Column comments can also be updated using <code>ALTER COLUMN</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> measurement <span style=color:#66d9ef>TYPE</span> double <span style=color:#66d9ef>COMMENT</span> <span style=color:#e6db74>'unit is bytes per second'</span> |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> measurement <span style=color:#66d9ef>COMMENT</span> <span style=color:#e6db74>'unit is kilobytes per second'</span> |
| </span></span></code></pre></div><p>Iceberg allows reordering top-level columns or columns in a struct using <code>FIRST</code> and <code>AFTER</code> clauses:</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> col <span style=color:#66d9ef>FIRST</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> nested.col <span style=color:#66d9ef>AFTER</span> other_col |
| </span></span></code></pre></div><p>Nullability can be changed using <code>SET NOT NULL</code> and <code>DROP NOT NULL</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>COLUMN</span> id <span style=color:#66d9ef>DROP</span> <span style=color:#66d9ef>NOT</span> <span style=color:#66d9ef>NULL</span> |
| </span></span></code></pre></div><div class=info><code>ALTER COLUMN</code> is not used to update <code>struct</code> types. Use <code>ADD COLUMN</code> and <code>DROP COLUMN</code> to add or remove struct fields.</div><h3 id=alter-table--drop-column><code>ALTER TABLE ... DROP COLUMN</code></h3><p>To drop columns, use <code>ALTER TABLE ... DROP COLUMN</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> <span style=color:#66d9ef>COLUMN</span> id |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> <span style=color:#66d9ef>COLUMN</span> point.z |
| </span></span></code></pre></div><h2 id=alter-table-sql-extensions><code>ALTER TABLE</code> SQL extensions</h2><p>These commands are available in Spark 3 when using Iceberg <a href=../spark-configuration#sql-extensions>SQL extensions</a>.</p><h3 id=alter-table--add-partition-field><code>ALTER TABLE ... ADD PARTITION FIELD</code></h3><p>Iceberg supports adding new partition fields to a spec using <code>ADD PARTITION FIELD</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ADD</span> PARTITION FIELD <span style=color:#66d9ef>catalog</span> <span style=color:#75715e>-- identity transform |
| </span></span></span></code></pre></div><p><a href=#partitioned-by>Partition transforms</a> are also supported:</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ADD</span> PARTITION FIELD bucket(<span style=color:#ae81ff>16</span>, id) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ADD</span> PARTITION FIELD <span style=color:#66d9ef>truncate</span>(<span style=color:#ae81ff>4</span>, <span style=color:#66d9ef>data</span>) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ADD</span> PARTITION FIELD years(ts) |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- use optional AS keyword to specify a custom name for the partition field |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>ADD</span> PARTITION FIELD bucket(<span style=color:#ae81ff>16</span>, id) <span style=color:#66d9ef>AS</span> shard |
| </span></span></code></pre></div><p>Adding a partition field is a metadata operation and does not change any of the existing table data. New data will be written with the new partitioning, but existing data will remain in the old partition layout. Old data files will have null values for the new partition fields in metadata tables.</p><p>Dynamic partition overwrite behavior will change when the table’s partitioning changes because dynamic overwrite replaces partitions implicitly. To overwrite explicitly, use the new <code>DataFrameWriterV2</code> API.</p><div class=note>To migrate from daily to hourly partitioning with transforms, it is not necessary to drop the daily partition field. Keeping the field ensures existing metadata table queries continue to work.</div><div class=danger><strong>Dynamic partition overwrite behavior will change</strong> when partitioning changes |
| For example, if you partition by days and move to partitioning by hours, overwrites will overwrite hourly partitions but not days anymore.</div><h3 id=alter-table--drop-partition-field><code>ALTER TABLE ... DROP PARTITION FIELD</code></h3><p>Partition fields can be removed using <code>DROP PARTITION FIELD</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> PARTITION FIELD <span style=color:#66d9ef>catalog</span> |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> PARTITION FIELD bucket(<span style=color:#ae81ff>16</span>, id) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> PARTITION FIELD <span style=color:#66d9ef>truncate</span>(<span style=color:#ae81ff>4</span>, <span style=color:#66d9ef>data</span>) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> PARTITION FIELD years(ts) |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> PARTITION FIELD shard |
| </span></span></code></pre></div><p>Note that although the partition is removed, the column will still exist in the table schema.</p><p>Dropping a partition field is a metadata operation and does not change any of the existing table data. New data will be written with the new partitioning, but existing data will remain in the old partition layout.</p><div class=danger><strong>Dynamic partition overwrite behavior will change</strong> when partitioning changes |
| For example, if you partition by days and move to partitioning by hours, overwrites will overwrite hourly partitions but not days anymore.</div><div class=danger>Be careful when dropping a partition field because it will change the schema of metadata tables, like <code>files</code>, and may cause metadata queries to fail or produce different results.</div><h3 id=alter-table--replace-partition-field><code>ALTER TABLE ... REPLACE PARTITION FIELD</code></h3><p>A partition field can be replaced by a new partition field in a single metadata update by using <code>REPLACE PARTITION FIELD</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>REPLACE</span> PARTITION FIELD ts_day <span style=color:#66d9ef>WITH</span> days(ts) |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- use optional AS keyword to specify a custom name for the new partition field |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>REPLACE</span> PARTITION FIELD ts_day <span style=color:#66d9ef>WITH</span> days(ts) <span style=color:#66d9ef>AS</span> day_of_ts |
| </span></span></code></pre></div><h3 id=alter-table--write-ordered-by><code>ALTER TABLE ... WRITE ORDERED BY</code></h3><p>Iceberg tables can be configured with a sort order that is used to automatically sort data that is written to the table in some engines. For example, <code>MERGE INTO</code> in Spark will use the table ordering.</p><p>To set the write order for a table, use <code>WRITE ORDERED BY</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> ORDERED <span style=color:#66d9ef>BY</span> category, id |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- use optional ASC/DEC keyword to specify sort order of each field (default ASC) |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> ORDERED <span style=color:#66d9ef>BY</span> category <span style=color:#66d9ef>ASC</span>, id <span style=color:#66d9ef>DESC</span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- use optional NULLS FIRST/NULLS LAST keyword to specify null order of each field (default FIRST) |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> ORDERED <span style=color:#66d9ef>BY</span> category <span style=color:#66d9ef>ASC</span> NULLS <span style=color:#66d9ef>LAST</span>, id <span style=color:#66d9ef>DESC</span> NULLS <span style=color:#66d9ef>FIRST</span> |
| </span></span></code></pre></div><div class=info>Table write order does not guarantee data order for queries. It only affects how data is written to the table.</div><p><code>WRITE ORDERED BY</code> sets a global ordering where rows are ordered across tasks, like using <code>ORDER BY</code> in an <code>INSERT</code> command:</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>To order within each task, not across tasks, use <code>LOCALLY ORDERED BY</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> LOCALLY ORDERED <span style=color:#66d9ef>BY</span> category, id |
| </span></span></code></pre></div><h3 id=alter-table--write-distributed-by-partition><code>ALTER TABLE ... WRITE DISTRIBUTED BY PARTITION</code></h3><p><code>WRITE DISTRIBUTED BY PARTITION</code> will request that each partition is handled by one writer, the default implementation is hash distribution.</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> DISTRIBUTED <span style=color:#66d9ef>BY</span> PARTITION |
| </span></span></code></pre></div><p><code>DISTRIBUTED BY PARTITION</code> and <code>LOCALLY ORDERED BY</code> may be used together, to distribute by partition and locally order rows within each task.</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>WRITE</span> DISTRIBUTED <span style=color:#66d9ef>BY</span> PARTITION LOCALLY ORDERED <span style=color:#66d9ef>BY</span> category, id |
| </span></span></code></pre></div><h3 id=alter-table--set-identifier-fields><code>ALTER TABLE ... SET IDENTIFIER FIELDS</code></h3><p>Iceberg supports setting identifier fields to a spec using <code>SET IDENTIFIER FIELDS</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>SET</span> IDENTIFIER FIELDS id |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- single column |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>SET</span> IDENTIFIER FIELDS id, <span style=color:#66d9ef>data</span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- multiple columns |
| </span></span></span></code></pre></div><p>identifier fields must be <code>NOT NULL</code>, The later <code>ALTER</code> statement will overwrite the previous setting.</p><h3 id=alter-table--drop-identifier-fields><code>ALTER TABLE ... DROP IDENTIFIER FIELDS</code></h3><p>Identifier fields can be removed using <code>DROP IDENTIFIER FIELDS</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> IDENTIFIER FIELDS id |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- single column |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> IDENTIFIER FIELDS id, <span style=color:#66d9ef>data</span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- multiple columns |
| </span></span></span></code></pre></div><p>Note that although the identifier is removed, the column will still exist in the table schema.</p><h3 id=branching-and-tagging-ddl>Branching and Tagging DDL</h3><h4 id=alter-table--create-branch><code>ALTER TABLE ... CREATE BRANCH</code></h4><p>Branches can be created via the <code>CREATE BRANCH</code> statement, which includes |
| the snapshot to create the branch at and an optional retention clause.</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>-- CREATE audit-branch at snapshot 1234 with default retention. |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>CREATE</span> BRANCH audit<span style=color:#f92672>-</span>branch |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>OF</span> <span style=color:#66d9ef>VERSION</span> <span style=color:#ae81ff>1234</span> |
| </span></span><span style=display:flex><span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- CREATE audit-branch at snapshot 1234, retain audit-branch for 31 days, and retain the latest 31 days. The latest 3 snapshot snapshots, and 2 days worth of snapshots |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>CREATE</span> BRANCH audit<span style=color:#f92672>-</span>branch |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>OF</span> <span style=color:#66d9ef>VERSION</span> <span style=color:#ae81ff>1234</span> RETAIN <span style=color:#ae81ff>30</span> DAYS |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>WITH</span> RETENTION <span style=color:#ae81ff>3</span> SNAPSHOTS <span style=color:#ae81ff>2</span> DAYS |
| </span></span></code></pre></div><h4 id=alter-table--create-tag><code>ALTER TABLE ... CREATE TAG</code></h4><p>Tags can be created via the <code>CREATE TAG</code> statement, which includes |
| the snapshot to create the branch at and an optional retention clause.</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>-- CREATE historical-tag at snapshot 1234 with default retention. |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>CREATE</span> TAG historical<span style=color:#f92672>-</span>tag <span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>OF</span> <span style=color:#66d9ef>VERSION</span> <span style=color:#ae81ff>1234</span> |
| </span></span><span style=display:flex><span> |
| </span></span><span style=display:flex><span><span style=color:#75715e>-- CREATE historical-tag at snapshot 1234 and retain it for 1 year. |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>CREATE</span> TAG historical<span style=color:#f92672>-</span>tag |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>OF</span> <span style=color:#66d9ef>VERSION</span> <span style=color:#ae81ff>1234</span> RETAIN <span style=color:#ae81ff>365</span> DAYS |
| </span></span></code></pre></div><h3 id=alter-table--replace-branch><code>ALTER TABLE ... REPLACE BRANCH</code></h3><p>The snapshot which a branch references can be updated via |
| the <code>REPLACE BRANCH</code> sql. Retention can also be updated in this statement.</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>-- REPLACE audit-branch to reference snapshot 4567 and update the retention to 60 days |
| </span></span></span><span style=display:flex><span><span style=color:#75715e></span><span style=color:#66d9ef>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>REPLACE</span> BRANCH audit<span style=color:#f92672>-</span>branch |
| </span></span><span style=display:flex><span><span style=color:#66d9ef>AS</span> <span style=color:#66d9ef>OF</span> <span style=color:#66d9ef>VERSION</span> <span style=color:#ae81ff>4567</span> RETAIN <span style=color:#ae81ff>60</span> DAYS |
| </span></span></code></pre></div><h4 id=alter-table--drop-branch><code>ALTER TABLE ... DROP BRANCH</code></h4><p>Branches can be removed via the <code>DROP BRANCH</code> sql</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> BRANCH audit<span style=color:#f92672>-</span>branch |
| </span></span></code></pre></div><h4 id=alter-table--drop-tag><code>ALTER TABLE ... DROP TAG</code></h4><p>Tags can be removed via the <code>DROP TAG</code> sql</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>ALTER</span> <span style=color:#66d9ef>TABLE</span> prod.db.sample <span style=color:#66d9ef>DROP</span> TAG historical<span style=color:#f92672>-</span>tag |
| </span></span></code></pre></div></div><div id=toc class=markdown-body><div id=full><nav id=TableOfContents><ul><li><a href=#create-table><code>CREATE TABLE</code></a><ul><li><a href=#partitioned-by><code>PARTITIONED BY</code></a></li></ul></li><li><a href=#create-table--as-select><code>CREATE TABLE ... AS SELECT</code></a></li><li><a href=#replace-table--as-select><code>REPLACE TABLE ... AS SELECT</code></a></li><li><a href=#drop-table><code>DROP TABLE</code></a><ul><li><a href=#drop-table-1><code>DROP TABLE</code></a></li><li><a href=#drop-table-purge><code>DROP TABLE PURGE</code></a></li></ul></li><li><a href=#alter-table><code>ALTER TABLE</code></a><ul><li><a href=#alter-table--rename-to><code>ALTER TABLE ... RENAME TO</code></a></li><li><a href=#alter-table--set-tblproperties><code>ALTER TABLE ... SET TBLPROPERTIES</code></a></li><li><a href=#alter-table--add-column><code>ALTER TABLE ... ADD COLUMN</code></a></li><li><a href=#alter-table--rename-column><code>ALTER TABLE ... RENAME COLUMN</code></a></li><li><a href=#alter-table--alter-column><code>ALTER TABLE ... ALTER COLUMN</code></a></li><li><a href=#alter-table--drop-column><code>ALTER TABLE ... DROP COLUMN</code></a></li></ul></li><li><a href=#alter-table-sql-extensions><code>ALTER TABLE</code> SQL extensions</a><ul><li><a href=#alter-table--add-partition-field><code>ALTER TABLE ... ADD PARTITION FIELD</code></a></li><li><a href=#alter-table--drop-partition-field><code>ALTER TABLE ... DROP PARTITION FIELD</code></a></li><li><a href=#alter-table--replace-partition-field><code>ALTER TABLE ... REPLACE PARTITION FIELD</code></a></li><li><a href=#alter-table--write-ordered-by><code>ALTER TABLE ... WRITE ORDERED BY</code></a></li><li><a href=#alter-table--write-distributed-by-partition><code>ALTER TABLE ... WRITE DISTRIBUTED BY PARTITION</code></a></li><li><a href=#alter-table--set-identifier-fields><code>ALTER TABLE ... SET IDENTIFIER FIELDS</code></a></li><li><a href=#alter-table--drop-identifier-fields><code>ALTER TABLE ... DROP IDENTIFIER FIELDS</code></a></li><li><a href=#branching-and-tagging-ddl>Branching and Tagging DDL</a></li><li><a href=#alter-table--replace-branch><code>ALTER TABLE ... REPLACE BRANCH</code></a></li></ul></li></ul></nav></div></div></div></div></section></body><script src=https://iceberg.apache.org/docs/fd-add-github-release//js/jquery-1.11.0.js></script> |
| <script src=https://iceberg.apache.org/docs/fd-add-github-release//js/jquery.easing.min.js></script> |
| <script type=text/javascript src=https://iceberg.apache.org/docs/fd-add-github-release//js/search.js></script> |
| <script src=https://iceberg.apache.org/docs/fd-add-github-release//js/bootstrap.min.js></script> |
| <script src=https://iceberg.apache.org/docs/fd-add-github-release//js/iceberg-theme.js></script></html> |
