Write to Apache Iceberg™ tables by using an external query engine through Snowflake Horizon Catalog¶

Prerequisites¶

Before you can write to Apache Iceberg™ tables by using an external query engine, you must perform the following tasks in Snowflake:

1. If you don't have any Iceberg tables, create them by using Snowflake.

2. Configure access control.

3. Obtain an access token for authentication.

4. Verify the permissions for your access token.

5. Connect an external query engine to Iceberg tables through Horizon Catalog.

For instructions on completing the prerequisites, see Snowflake Horizon Catalogを介した外部エンジンによる|iceberg-tm|テーブルへのアクセス, which is a topic about querying Iceberg tables by using an external query engine through Snowflake Horizon Catalog. However, it includes the same prerequisite steps for writing to Iceberg tables by using an external query engine through Snowflake Horizon Catalog.

Write to Iceberg tables¶

This section provides the following code examples for using Apache Spark™ to write to Iceberg tables:

• INSERT INTO <table>

• ALTER TABLE ... ADD COLUMN

• UPDATE TABLE ... WHERE

• DELETE TABLE ... WHERE

• TRUNCATE TABLE

spark.sql("INSERT INTO MY_TABLE VALUES (600)").show()

spark.sql("ALTER TABLE MY_TABLE ADD COLUMN COLUMN2 INT").show()

spark.sql("UPDATE MY_TABLE SET COLUMN2 = 10 WHERE COLUMN1 = 100").show()

spark.sql("DELETE FROM MY_TABLE WHERE COLUMN2 = 10").show()

spark.sql("TRUNCATE TABLE MY_TABLE").show()

Usage notes¶

Consider the following usage notes for writing to Iceberg tables by using an external query engine through Snowflake Horizon Catalog:

• Iceberg tables in Snowflake

  • Only Snowflake-managed Iceberg tables are supported.

  • You can't write to dynamic tables in Snowflake.

  • You can't write to an Iceberg table that was converted from externally managed to Snowflake managed.

  • The external engine writes are supported only on Iceberg version 2; writing to Iceberg version 1 or Iceberg version 3 tables isn't supported.

  • You can't upgrade an Iceberg table from version 2 to version 3.

  • You can't write to shared Iceberg tables.

  • You can't write to tables by using row-level deletes; only copy-on-write mode is supported.

  • You can't write to an Iceberg table with change tracking enabled on it or with a stream created on it.

Unsupported maintenance operations

• You can't roll back a table to a previous snapshot.

• The snapshot expiration table maintenance operation isn't supported

Privileges

• To run Data Manipulation Language (DML) operations by using Apache Spark, a role used to execute the operation must have the following privileges:

  • SELECT, UPDATE, TRUNCATE, INSERT, and DELETE privileges on the table

  • MONITOR privilege for the parent schema where the table is nested under

  • USAGE privilege on the parent database or schema where the table is nested under

• For all other operations on the table, a role used to execute the operation must have the OWNERSHIP privilege on the table in addition to the privileges on the schema and database. For example, you must have these privileges to run the ALTER ICEBERG TABLE … ADD COLUMN or ALTER ICEBERG TABLE … DROP COLUMN operation.

Data definition language (DDL) operations

• The following DDL operations aren't supported:

  • CREATE TABLE

  • DROP NAMESPACE

  • DROP TABLE

  • RENAME TABLE

• You can't update a table's properties. For example, the following statement isn't supported:

  CopyExpand

  ALTER TABLE iceberg_prod.db.my_tbl
    SET TBLPROPERTIES ('write.target-file-size-bytes'='134217728');
  

  Show lessSee more

  Scroll to top

• You can't create branches or tags.

• You can't add columns with lowercase names to a table; you must add columns with all uppercase letters to a table.