EXPORT TO ICEBERG

Exports data from Vertica tables into a format compatible with Apache Iceberg.

Exports data from Vertica tables into Apache Iceberg format, an open table format for large-scale analytics with ACID transaction support. Iceberg tables store data in Parquet format, a columnar storage format optimized for such workloads. This integration allows database users to use data lake architectures and interoperate with query engines such as Apache Spark, Trino, and Flink.

Benefits

  • Interoperability: Share data across multiple query engines.
  • Open format: Avoid vendor lock-in by using Iceberg’s open standard.
  • Flexible storage: Supports cloud storage (AWS S3, Azure Blob, GCS) and on-premises storage (HDFS, NFS, S3-compatible, local storage).
  • Scalability: Manage large datasets efficiently with Iceberg’s metadata layer.

Use cases

  • Moving cold data from Vertica to a lower-cost data lake architecture.
  • Sharing database data with Spark or Trino to accomodate for existing data pipelines.
  • Building a multi-engine query environment using Iceberg tables.

Prerequisites

  • Vertica 25.4 or later.
  • Permissions on the target storage location.

Usage

Exporting to Iceberg is currently performed using the EXPORT TO PARQUET command along with the icebergCatalog parameter. For more information about the syntax, see EXPORT TO PARQUET.

Examples

Filesystem catalog

Export data from a table named large_t  to an Iceberg table on the file system, using /mnt/shared_nfs/accounts as the directory for the Iceberg table.

=> EXPORT TO PARQUET(icebergCatalog='filesystem', directory='/mnt/shared_nfs/accounts')
  AS SELECT * FROM large_t;

AWS Glue catalog

Export data from a table named sales_orders  to an Iceberg table on AWS Glue, using s3://data-lake/iceberg/customer_orders as the directory for the Iceberg table.

=> EXPORT TO PARQUET(icebergCatalog='glue', glueDB='analytics', glueTable='customer_orders', directory='s3://data-lake/iceberg/customer_orders') AS SELECT * FROM sales_orders;

Hive Metastore catalog

Export data from a table named users_connection to an HDFS location webhdfs://hive-metastore-host:9870/iceberg/users managed under the Hive Metastore Iceberg catalog.

=> EXPORT TO PARQUET(icebergCatalog='hms', hmsDB='testdb', hmsTable='users', hmsUri='thrift://hive-metastore-host:9083', directory='webhdfs://hive-metastore-host:9870/iceberg/users', ifDirExists='overwrite') 
AS SELECT * FROM users_connection;

Export data from a table named users_connection to an S3 location s3://data-lake/iceberg/users managed under the Hive Metastore Iceberg catalog.

=> EXPORT TO PARQUET(icebergCatalog='hms', hmsDB='testdb', hmsTable='users', hmsUri='thrift://hive-metastore-host:9083', directory='s3://data-lake/iceberg/users', ifDirExists='overwrite') 
AS SELECT * FROM users_connection;

REST catalog

Using a Bearer token:

=> EXPORT TO PARQUET (
     icebergCatalog='rest',
     restUri='http://10.20.71.25:19120/iceberg/v1/main/namespaces/my_ice_db/tables/rest_export_table',
     restAuth '{"bearerToken": "eyJhbGciOiJSUzI1NiIsInR5cCIgOiAiSldUIiwia2lkIiA6ICIzWFRweHB"}',
     directory='s3a://demobucket/rest_export',
     ifDirExists='append'
) AS SELECT * FROM orders;

Using OAuth2 authentication:

=> EXPORT TO PARQUET (
     icebergCatalog='rest',
     restUri='http://10.20.71.25:19120/iceberg/v1/main/namespaces/my_ice_db/tables/rest_export_table',
     restAuth='{
       "oauthTokenUri":"https://auth.example.com/oauth/token",
       "oauthClientId":"iceberg-client",
       "oauthClientSecret":"client-secret",
       "oauthAllowedScopes":["catalog","tables"]
     }',
     directory='s3a://demobucket/rest_export',
     ifDirExists='append'
) AS
SELECT * FROM orders;

Output directory structure

After a successful run, the directory specified in the examples contains the following structure:

├── data 

│   ├── *.parquet 

└── metadata 

    ├── *.manifest.avro 

    ├── manifest-list.avro 

    └── v1.metadata.json

  • All Parquet data files will be written to the data/subdirectory.

  • All Iceberg metadata files will be written to the metadata/subdirectory.

Not supported

The following items are not supported in this release:

  • No partition support: The export process does not partition data for Iceberg tables. All data is written as a single, flat structure without partitioned directories.

  • No complex data types: Only primitive data types (such as integers, strings, and dates) are supported. Complex types like arrays, maps, and structs cannot be exported.

  • No append or modify operations: Export does not support appending data to an existing Iceberg table or modifying its contents. It can only create a new table or overwrite all existing data in the table.

  • REST catalogs (Nessie and Polaris): The ifDirExists='overwrite' option for exporting to an existing Iceberg table is currently not supported when using Nessie and Polaris REST catalogs. This is a current compatibility limitation rather than a design restriction and is expected to be addressed in a future release.