Quick tip: Using SingleStore for Iceberg Catalog Storage
Tags: singlestoredb, apacheiceberg, catalog, jdbc
Published: 2024-07-04 07:30:33

## Abstract
SingleStore [recently announced](https://www.singlestore.com/blog/bidirectional-integration-for-apache-iceberg/) bi-directional support for Apache Iceberg. Iceberg uses catalogs that are an integral part of the Iceberg table format, designed to manage large-scale tabular data in a more efficient and reliable way. Catalogs store metadata and track the location of tables, enabling data discovery, access, and management. Iceberg supports multiple catalog backends, including Hive Metastore, AWS Glue, Hadoop, and through a database system using JDBC. This allows users to choose the most suitable backend for their specific data infrastructure. In this short article, we'll implement an Iceberg catalog using SingleStore and JDBC.

The notebook file used in this article is available on [GitHub](https://github.com/VeryFatBoy/iceberg-catalog).

## Introduction
The JDBC catalog in Apache Iceberg is a specialised catalog implementation that uses a relational database system to store metadata about Iceberg tables. This option uses the transactions and scalability of relational database systems to manage and query metadata efficiently. The JDBC catalog provides a good choice for environments where relational database systems are already in use or preferred. The JDBC connection needs to support atomic transactions.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps to create a free SingleStoreDB Cloud account. We'll use the following settings:

- **Workspace Group Name:** Iceberg Demo Group
- **Cloud Provider:** AWS
- **Region:** US East 1 (N. Virginia)
- **Workspace Name:** iceberg-demo
- **Size:** S-00

We'll make a note of the password and store it in the [secrets vault](https://docs.singlestore.com/cloud/developer-resources/secrets/) using the name `password`.

## Import the notebook
We'll download the notebook from [GitHub](https://github.com/VeryFatBoy/iceberg-catalog).

From the left navigation pane in the SingleStore cloud portal, we'll select **DEVELOP > Data Studio**.

In the top right of the web page, we'll select **New Notebook > Import From File**. We'll use the wizard to locate and import the notebook we downloaded from GitHub.

## Run the notebook
After checking that we are connected to our SingleStore workspace, we'll run the cells one by one.

We'll use Apache Spark to create a tiny Iceberg Lakehouse in the SingleStore portal for testing purposes.

> For production environments, please use a robust file system for your Lakehouse.

For the `SparkSession`, we'll need two packages (`SingleStore JDBC Client` and `Iceberg Spark Runtime`), as follows:

```python
from pyspark.sql import SparkSession

# List of Maven coordinates for all required packages
maven_packages = [
    "com.singlestore:singlestore-jdbc-client:1.2.3",
    "org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.5.2"
]

# Create Spark session with all required packages
spark = (SparkSession
             .builder
             .config("spark.jars.packages", ",".join(maven_packages))
             .appName("Spark Iceberg Catalog Test")
             .getOrCreate()
        )

spark.sparkContext.setLogLevel("ERROR")
```

In the Iceberg Lakehouse, we'll store the [Iris flower data set](https://en.wikipedia.org/wiki/Iris_flower_data_set). We'll first download the Iris CSV file into a Pandas Dataframe and then convert this to a Spark Dataframe.

We'll need to create a SingleStore database to use with Iceberg:

```sql
DROP DATABASE IF EXISTS iceberg;
CREATE DATABASE IF NOT EXISTS iceberg;
```

A quick and easy way to find the connection details for the database is to use the following:

```python
from sqlalchemy import *

db_connection = create_engine(connection_url)
url = db_connection.url
```

The `url` will contain the `host`, the `port`, and the `database` name. We can use all these details to configure Spark:

```python
spark.conf.set("spark.sql.catalog.s2_catalog", "org.apache.iceberg.spark.SparkCatalog")
spark.conf.set("spark.sql.catalog.s2_catalog.type", "jdbc")
spark.conf.set("spark.sql.catalog.s2_catalog.warehouse", "warehouse")

# SSL/TLS configuration
spark.conf.set("spark.sql.catalog.s2_catalog.jdbc.useSSL", "true")
spark.conf.set("spark.sql.catalog.s2_catalog.jdbc.trustServerCertificate", "true")

# JDBC connection URL
spark.conf.set("spark.sql.catalog.s2_catalog.uri", f"jdbc:singlestore://{url.host}:{url.port}/{url.database}")

# JDBC credentials
spark.conf.set("spark.sql.catalog.s2_catalog.jdbc.user", "admin")
spark.conf.set("spark.sql.catalog.s2_catalog.jdbc.password", password)
```

Finally, we can test our setup.

First, we'll store the data from the Spark Dataframe in the Lakehouse, partitioned by Species:

```python
(iris_df.write
    .format("iceberg")
    .partitionBy("species")
    .save("s2_catalog.db.iris")
)
```

Next, we'll check what's stored, as follows:

```python
spark.sql("""
    SELECT file_path, file_format, partition, record_count
    FROM s2_catalog.db.iris.files
""").show()
```

Example output:

```
+--------------------+-----------+-----------------+------------+
|           file_path|file_format|        partition|record_count|
+--------------------+-----------+-----------------+------------+
|warehouse/db/iris...|    PARQUET| {Iris-virginica}|          50|
|warehouse/db/iris...|    PARQUET|    {Iris-setosa}|          50|
|warehouse/db/iris...|    PARQUET|{Iris-versicolor}|          50|
+--------------------+-----------+-----------------+------------+
```

We can run queries on our tiny Lakehouse:

```python
spark.sql("""
    SELECT * FROM s2_catalog.db.iris LIMIT 5
""").show()
```

Example output:

```
+------------+-----------+------------+-----------+--------------+
|sepal_length|sepal_width|petal_length|petal_width|       species|
+------------+-----------+------------+-----------+--------------+
|         6.3|        3.3|         6.0|        2.5|Iris-virginica|
|         5.8|        2.7|         5.1|        1.9|Iris-virginica|
|         7.1|        3.0|         5.9|        2.1|Iris-virginica|
|         6.3|        2.9|         5.6|        1.8|Iris-virginica|
|         6.5|        3.0|         5.8|        2.2|Iris-virginica|
+------------+-----------+------------+-----------+--------------+
```

We'll now delete all `Iris-virginica` records:

```python
spark.sql("""
    DELETE FROM s2_catalog.db.iris
    WHERE species = 'Iris-virginica'
""")
```

and check the Lakehouse:

```python
spark.sql("""
    SELECT file_path, file_format, partition, record_count
    FROM s2_catalog.db.iris.files
""").show()
```

Example output:

```
+--------------------+-----------+-----------------+------------+
|           file_path|file_format|        partition|record_count|
+--------------------+-----------+-----------------+------------+
|warehouse/db/iris...|    PARQUET|    {Iris-setosa}|          50|
|warehouse/db/iris...|    PARQUET|{Iris-versicolor}|          50|
+--------------------+-----------+-----------------+------------+
```

We can also check the metadata stored in SingleStore:

```sql
SELECT * FROM iceberg_tables;
```

Example output:

```
+--------------+-----------------+------------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| catalog_name | table_namespace | table_name | metadata_location                                                                   | previous_metadata_location                                                          |
+--------------+-----------------+------------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
| s2_catalog   | db              | iris       | warehouse/db/iris/metadata/00001-6ea55045-6162-4462-9f8c-597ddbc5b846.metadata.json | warehouse/db/iris/metadata/00000-39743969-9e4b-4875-81ad-d8310656d28f.metadata.json |
+--------------+-----------------+------------+-------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------+
```

## Summary
In this short article, we've seen how to configure SingleStore to manage an Iceberg Lakehouse catalog. Using a simple example, we've run some queries on our Lakehouse and SingleStore has managed the metadata for us using JDBC.