Iceberg with Hive

Apache Iceberg integrates with Hive to support SQL operations on Iceberg tables and interoperability between Hive and Spark.

Spark and Hive Interoperability

Iceberg tables created by Spark can be queried from Hive using Beeline. Similarly, Iceberg tables created by Hive using the Iceberg storage handler can be queried from Spark.

The following interoperability scenarios are supported:

Create or write an Iceberg table from Spark and query the table from Hive using Beeline.
Create or write an Iceberg table from Hive and query the table from Spark.

This interoperability is validated on ODP 3.3.6.5-1009 with Spark 3.5.5 and Hive 4.1.

Iceberg JAR Requirements for Hive

Iceberg integration is included with Hive 4.1. You do not need to add a separate Iceberg JAR to the Hive auxiliary library path.

Unlike Hudi, which requires the hudi-hadoop-mr-bundle JAR in the Hive auxiliary path, Iceberg does not require an additional JAR in /usr/odp/current/hive/lib/. The required Iceberg storage handler classes are included with Hive 4.1.

Spark and Hive Interoperability

Iceberg tables created by Spark are available in Hive through the standard Hive Iceberg integration. Similarly, Iceberg tables created by Hive can be queried from Spark.

The following interoperability scenarios are supported:

Create or write an Iceberg table from Spark and query the table from Hive using Beeline.
Create or write an Iceberg table from Hive using the Iceberg storage handler and query the table from Spark.

This interoperability is validated on ODP 3.3.6.5-1009 with Spark 3.5.5 and Hive 4.1.

Iceberg JAR Requirements for Hive

Iceberg integration is included with Hive 4.1. You do not need to add a separate Iceberg JAR to the Hive auxiliary library path.

Hive Feature Support

The following table lists Iceberg features supported with Hive 2/3 and Hive 4.

Feature Support	Hive 2 / 3	Hive 4
[SQL create table](SQL create table)	✔️	✔️
[SQL create table as select (CTAS)](SQL create table as select (CTAS))	✔️	✔️
[SQL create table like table (CTLT)](SQL create table like table (CTLT))	✔️	✔️
[SQL drop table](SQL drop table)	✔️	✔️
[SQL insert into](SQL insert into)	✔️	✔️
[SQL insert overwrite](SQL insert overwrite)	✔️	✔️
[SQL delete from](SQL delete from)		✔️
[SQL update](SQL update)		✔️
[SQL merge into](SQL merge into)		✔️
[Branches and tags](Branches and tags)		✔️

Configure a Custom Iceberg Catalog

To globally register different Iceberg catalogs, configure the required Hadoop properties.

For example, to register a HiveCatalog:

Configuration Key	Description
iceberg.catalog.<catalog_name>.type	iceberg.catalog.<catalog_name>.type
iceberg.catalog.<catalog_name>.catalog-impl	catalog implementation, must not be null if type is empty
iceberg.catalog.<catalog_name>.<key>	any config key and value pairs for the catalog

To register a HadoopCatalog:

SET iceberg.catalog.him_catalog.type=hive; SET iceberg.catalog.him_catalog.uri=thrift://adi2.acceldata.ce:9083; SET iceberg.catalog.him_catalog.clients=10; SET iceberg.catalog.him_catalog.warehouse=hdfs://adi1.acceldata.ce:8020/warehouse;

SET iceberg.catalog.hadoop.type=hadoop; SET iceberg.catalog.hadoop.warehouse=hdfs://adi1.acceldata.ce:8020/warehouse;

Hive Operations on Iceberg Tables

Hive supports standard SQL operations on Iceberg tables, including creating tables, inserting and overwriting data, deleting and updating records, and merging data.

To enable Hive support globally for an application, set iceberg.engine.hive.enabled=true in its Hadoop configuration. For example, setting this property in the hive-site.xml file loaded by Spark enables the Iceberg storage handler for tables created by Spark.

Feature Support

The following features are supported across Hive for Iceberg tables:

Info

To enable Hive support globally for an application, set iceberg.engine.hive.enabled=true in its Hadoop configuration. For example, setting this in the hive-site.xml loaded by Spark will enable the storage handler for all tables created by Spark.

Create Table

Use the Iceberg storage handler to create an Iceberg table.

Non-partitioned tables

The Hive CREATE EXTERNAL TABLE command creates an Iceberg table when you specify the storage handler as follows:

0: jdbc:hive2://adi2.acceldata.ce:2181,adi1.a> CREATE EXTERNAL TABLE x (i int) STORED BY ICEBERG; No rows affected (0.46 seconds)

You can specify the default file format (Avro, Parquet, ORC) at the time of the table creation. The default is Parquet:

0: jdbc:hive2://adi2.acceldata.ce:2181,adi1.a> CREATE TABLE j (i int) STORED BY ICEBERG STORED AS AVRO; No rows affected (13.58 seconds)

Partitioned tables

You can create Iceberg partitioned tables using a command familiar to those who create non-Iceberg tables:

0: jdbc:hive2://adi2.acceldata.ce:2181,adi1.a> CREATE TABLE q (i int) PARTITIONED BY (j int) STORED BY ICEBERG; No rows affected (9.119 seconds)

Info

The resulting table does not create partitions in HMS, but instead, converts partition data into Iceberg identity partitions.

Use the DESCRIBE command to get information about the Iceberg identity partitions:

Create Table as Select

The CREATE TABLE AS SELECT operation resembles the native Hive operation with a single important difference. The Iceberg table and the corresponding Hive table are created at the beginning of the query execution. The data is inserted / committed when the query finishes. So for a transient period the table already exists but contains no data.

0: jdbc:hive2://adi2.acceldata.ce:2181,adi1.a> CREATE TABLE target PARTITIONED BY SPEC (year(year_field), identity_field) STORED BY ICEBERG AS . . . . . . . . . . . . . . . . . . . . . . .> SELECT * FROM source; No rows affected (26.508 seconds)

Create Table like Table

Hive with Iceberg storage handler attempts to create a new table named target that has the same schema and properties as an existing table named source.

Drop Table

Tables can be dropped using the DROP TABLE command:

DROP TABLE [IF EXISTS] table_name [PURGE];

Insert Into

Hive supports the standard single-table INSERT INTO operation:

INSERT INTO table_a VALUES ('a', 1); INSERT INTO table_a SELECT...;

The Multi-table insert is also supported, but it will not be atomic. Commits occur one table at a time. Partial changes will be visible during the commit process and failures can leave partial changes committed. Changes within a single table will remain atomic.

Insert-into operations on branches also work similar to the table level select operations. However, the branch must be provided as follows.

-- Branches should be specified as <database_name>.<table_name>.branch_<branch_name> INSERT INTO default.test.branch_branch1 VALUES ('a', 1); INSERT INTO default.test.branch_branch1 SELECT...;

Here is an example of inserting into multiple tables at once in Hive SQL:

FROM customers INSERT INTO target1 SELECT customer_id, first_name INSERT INTO target2 SELECT last_name, customer_id;

Insert Into Partition

Hive supports partition-level INSERT INTO operation:

INSERT INTO table_a PARTITION (customer_id = 1, first_name = 'sourabh') VALUES (1,2); INSERT INTO table_a PARTITION (customer_id = 1, first_name = 'sourabh') SELECT...;

The partition specification supports only identity-partition columns. Transform columns in partition specification are not supported.

Insert Overwrite

INSERT OVERWRITE can replace data in the table with the result of a query. Overwrites are atomic operations for Iceberg tables. For non partitioned tables the content of the table is always removed. For partitioned tables the partitions that have rows produced by the SELECT query will be replaced.

INSERT OVERWRITE TABLE target SELECT * FROM source;

Insert Overwrite Partition

Hive supports partition-level INSERT OVERWRITE operation:

INSERT OVERWRITE TABLE target PARTITION (customer_id = 1, first_name = 'sourabh') SELECT * FROM source;

The partition specification supports only identity-partition columns. Transform columns in partition specification are not supported.

Delete From

Hive supports DELETE FROM queries to remove data from tables.

Delete queries accept a filter to match rows to delete.

DELETE FROM target WHERE id > 1 AND id < 10; DELETE FROM target WHERE id IN (SELECT id FROM source); DELETE FROM target WHERE id IN (SELECT min(customer_id) FROM source);

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.

Update

Hive supports UPDATE queries which accept a filter to match rows to update.

UPDATE target SET first_name = 'him' WHERE id > 1 AND id < 10; UPDATE target SET first_name = 'him' WHERE id IN (SELECT id FROM source); UPDATE target SET first_name = 'him' WHERE id IN (SELECT min(customer_id) FROM source);

For more complex row-level updates based on incoming data, see the section on MERGE INTO.

Merge Into

Hive support for MERGE INTO queries that can express row-level updates.

MERGE INTO updates a table, called the target table, using a set of updates from another query, called the source. The update for a row in the target table is found using the ON clause that is like a join condition.

MERGE INTO target AS t -- a target table USING source s -- the source updates ON t.id = s.id -- condition to find updates for target rows WHEN ... -- updates

Updates to rows in the target table are listed using WHEN MATCHED ... THEN .... Multiple MATCHED clauses can be added with conditions that determine when each match should be applied. The first matching expression is used.

WHEN MATCHED AND s.op = 'delete' THEN DELETE WHEN MATCHED AND t.count IS NULL AND s.op = 'increment' THEN UPDATE SET t.count = 0 WHEN MATCHED AND s.op = 'increment' THEN UPDATE SET t.count = t.count + 1

Source rows (updates) that do not match can be inserted:

WHEN NOT MATCHED THEN INSERT VALUES (s.a, s.b, s.c)

Only one record in the source data can update any given row of the target table, or else an error will be thrown.

This worked successfully, returning the rows as expected.

2. Updating the Table

UPDATE iceberg_table SET name = 'Eve' WHERE id = 1;

The update operation was successful and affected the rows.

3. Deleting from the Table

DELETE FROM iceberg_table WHERE id = 2;

The delete operation was executed, but it seems no rows were affected.

4. Describing the Table Formatted

DESCRIBE FORMATTED iceberg_table;

This command successfully described the table and its metadata.

SHOW TABLE EXTENDED LIKE 'iceberg_table';

5. Showing Extended Table Information

SHOW TABLE EXTENDED LIKE 'iceberg_table';

6. Showing Functions

SHOW FUNCTIONS LIKE 'iceberg%';

Time Travel Queries

Since DESCRIBE HISTORY and the snapshots and current_snapshot tables aren’t available, here are alternative steps to check time travel functionality if supported by your Hive/Iceberg setup:

Create Snapshot: Insert data and create a new snapshot.

INSERT INTO iceberg_table VALUES (3, 'Bob', '2024-08-08 16:00:00');

Query Specific Snapshot: If you have snapshots, you can specify timestamps or snapshot IDs in your queries to check historical data.

SELECT * FROM iceberg_table FOR SYSTEM_TIME AS OF '2024-08-08 15:00:00';

Check Table Versions: If available, you can check table versions directly.

SHOW TABLES;

For more details, see Hive - Apache Iceberg.

Last updated on Aug 31, 2026

Was this page helpful?

Iceberg with Hive

Spark and Hive Interoperability

Iceberg JAR Requirements for Hive

Spark and Hive Interoperability

Iceberg JAR Requirements for Hive

Hive Feature Support

Configure a Custom Iceberg Catalog

Hive Operations on Iceberg Tables

Feature Support

Create Table

Create Table as Select

Create Table like Table

Drop Table

Insert Into

Insert Overwrite Partition

Delete From

Update

Merge Into

Hive Table Operations using Iceberg

Creating an Iceberg Table

Inserting Data into the Iceberg Table

Describing the Iceberg Table

Summary of Commands

Time Travel Queries