Iceberg Catalog feature status
The Iceberg Catalog feature is in alpha. Expect rapid changes, limited features, and possible breaking updates.
Supabase · Storage · all subjects
analytics/iceberg
48 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
S3 credentials components for analytics
S3 credentials created through Project Settings > Storage include: Access Key ID, Secret Access Key, and Region (e.g., us-east-1).
Testing Iceberg REST Catalog connection
You can verify your analytics bucket setup by making a direct request to the Iceberg REST Catalog using curl with your Service Key as a Bearer token. The endpoint is: GET https://<your-project-ref>.supabase.co/storage/v1/iceberg/v1/config?warehouse=<bucket-name>. Include the header 'Authorization: Bearer <your-service-key>'. A successful response returns the catalog configuration including warehouse location and settings.
Required credentials for analytics bucket authentication
Connecting to an analytics bucket requires two distinct services: S3 credentials (Access Key ID, Secret Access Key, and Region) obtained through Project Settings > Storage, and a Supabase service key retrieved from Project Settings > API. Additionally, you need your project reference, which is the subdomain in your project URL.
S3-Compatible Storage Endpoint for analytics data
The S3-Compatible Storage Endpoint handles actual data storage and retrieval. It is optimized for reading and writing large analytical datasets stored in Parquet format, separate from the metadata management layer.
Iceberg REST Catalog metadata operations
The Iceberg REST Catalog serves as the metadata management system for Iceberg tables. It enables Iceberg clients such as PyIceberg and Apache Spark to create and manage tables and namespaces, track schemas and handle schema evolution, manage partitions and table snapshots, and ensure transactional consistency and isolation. The REST Catalog only stores metadata describing data structure, schema, and partitioning strategy—not the actual data itself.
Analytics buckets use Apache Iceberg for efficient large-dataset management
Analytics buckets are built on Apache Iceberg, an open-table format specifically designed for efficient management of large analytical datasets.
Reading data from Iceberg tables
Read Iceberg tables using spark.sql() with SELECT statements, apply filters with WHERE clauses, and perform aggregations with GROUP BY and aggregate functions like COUNT() and COUNT(DISTINCT).
Spark DataFrame operations on Iceberg tables
Read Iceberg tables as DataFrames using spark.read.format("iceberg").load("namespace.table_name"). Apply transformations using PySpark functions like withColumn(), groupBy(), agg() for complex operations.
Joining Iceberg tables in Spark
Join multiple Iceberg tables using SQL JOIN syntax: SELECT columns FROM table1 e JOIN table2 u ON e.join_column = u.join_column. Tables must be created in the same namespace.
Spark performance best practices for analytics
For optimal Spark performance: partition large tables by date or region, select only needed columns to reduce I/O, apply WHERE clauses early to reduce data processed, cache frequently accessed tables using spark.catalog.cacheTable(), and use cluster mode for production workloads instead of local mode.
Exporting Spark results to Parquet and CSV
Export query results using spark.sql(query).write.mode("overwrite").parquet("/path/to/file.parquet") for Parquet format or .option("header", "true").csv("/path/to/file.csv") for CSV format with headers.
Apache Spark feature status
Apache Spark integration with Supabase analytics is in alpha. Expect rapid changes, limited features, and possible breaking updates.
Apache Spark with Supabase Iceberg integration
Apache Spark enables distributed analytical processing of large datasets stored in Supabase analytics buckets. Use it for complex transformations, aggregations, and machine learning workflows.
Spark Supabase configuration parameters
To configure Spark with Supabase analytics, you need: PROJECT_REF (your project reference), WAREHOUSE (analytics bucket name), SERVICE_KEY (service key from project), S3_ACCESS_KEY and S3_SECRET_KEY (from Project Settings > Storage), S3_REGION (e.g., us-east-1), S3_ENDPOINT (https://{PROJECT_REF}.supabase.co/storage/v1/s3), and CATALOG_URI (https://{PROJECT_REF}.supabase.co/storage/v1/iceberg).
Spark session Iceberg configuration example
Complete working example showing Spark session initialization with Iceberg: Use SparkSession.builder with master="local[*]", appName, and spark.jars.packages including 'org.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.6.1' and 'org.apache.iceberg:iceberg-aws-bundle:1.6.1'. Configure spark.sql.extensions as 'org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions'. Set spark.sql.catalog.supabase with type="rest", uri=CATALOG_URI, warehouse=WAREHOUSE, token=SERVICE_KEY. Configure S3 access via spark.sql.catalog.supabase.s3.endpoint, spark.sql.catalog.supabase.s3.path-style-access="true", spark.sql.catalog.supabase.s3.access-key-id, spark.sql.catalog.supabase.s3.secret-access-key, and spark.sql.catalog.supabase.s3.remote-signing-enabled="false". Set spark.sql.defaultCatalog="supabase".
Creating Iceberg tables in Spark
Create a namespace with CREATE NAMESPACE IF NOT EXISTS analytics. Create Iceberg tables with CREATE TABLE IF NOT EXISTS namespace.table_name with column definitions and USING iceberg clause.
Writing data to Iceberg tables
Insert data into Iceberg tables using INSERT INTO statement with VALUES or SELECT. Example: INSERT INTO analytics.events (event_id, user_id, event_name, event_timestamp, properties) VALUES (1, 101, 'login', TIMESTAMP '2024-01-15 10:30:00', '{"browser":"chrome"}').
Export DuckDB query results to Parquet and CSV
Export DuckDB query results using the COPY command. Export to Parquet with `COPY (SELECT ...) TO 'results.parquet'`. Export to CSV with `COPY (SELECT ...) TO 'summary.csv' (FORMAT CSV, HEADER true)`.
DuckDB Iceberg extension installation
Install DuckDB and the Iceberg extension using pip: `pip install duckdb duckdb-iceberg`.
DuckDB connection to Supabase analytics bucket
Connect to a Supabase analytics bucket by installing and loading the Iceberg extension, then creating a secret with S3 credentials (S3_ACCESS_KEY, S3_SECRET_KEY, S3_REGION), and attaching an Iceberg REST catalog. The S3 endpoint is constructed as `https://{PROJECT_REF}.supabase.co/storage/v1/s3` and the catalog URI is `https://{PROJECT_REF}.supabase.co/storage/v1/iceberg`. The ATTACH command requires TYPE ICEBERG_REST, WAREHOUSE name, and SERVICE_KEY (TOKEN) for authentication.
DuckDB Iceberg catalog setup example
Complete Python example for setting up DuckDB with Supabase Iceberg tables: ```python import duckdb import os # Configuration PROJECT_REF = "your-project-ref" WAREHOUSE = "your-analytics-bucket-name" SERVICE_KEY = "your-service-key" # S3 credentials S3_ACCESS_KEY = "your-access-key" S3_SECRET_KEY = "your-secret-key" S3_REGION = "us-east-1" # Construct endpoints S3_ENDPOINT = f"https://{PROJECT_REF}.supabase.co/storage/v1/s3" CATALOG_URI = f"https://{PROJECT_REF}.supabase.co/storage/v1/iceberg" # Initialize DuckDB connection conn = duckdb.connect(":memory:") # Install and load the Iceberg extension conn.install_extension("iceberg") conn.load_extension("iceberg") # Configure Iceberg catalog with Supabase credentials conn.execute(f""" CREATE SECRET ( TYPE S3, KEY_ID '{S3_ACCESS_KEY}', SECRET '{S3_SECRET_KEY}', REGION '{S3_REGION}', ENDPOINT '{S3_ENDPOINT}', URL_STYLE 'virtual' ); """) # Configure the REST catalog conn.execute(f""" ATTACH 'iceberg://{CATALOG_URI}' AS iceberg_catalog ( TYPE ICEBERG_REST, WAREHOUSE '{WAREHOUSE}', TOKEN '{SERVICE_KEY}' ); """) # Query your Iceberg tables result = conn.execute(""" SELECT * FROM iceberg_catalog.default.events LIMIT 10 """).fetchall() for row in result: print(row) # Complex aggregation example analytics = conn.execute(""" SELECT event_name, COUNT(*) as event_count, COUNT(DISTINCT user_id) as unique_users FROM iceberg_catalog.default.events GROUP BY event_name ORDER BY event_count DESC """).fetchdf() print(analytics) ```
DuckDB lazy evaluation for efficient data exploration
DuckDB uses lazy evaluation and only scans the data you need. When selecting specific columns and filtering by time range, only those columns within the filtered time range are read from the Iceberg table.
Convert DuckDB query results to Pandas DataFrame
Use the `.fetchdf()` method on DuckDB query results to convert them to Pandas DataFrames for further analysis and visualization: `df = conn.execute("SELECT * FROM iceberg_catalog.default.events").fetchdf()`.
DuckDB Iceberg best practices
Best practices for querying Iceberg tables with DuckDB include: reuse connections for multiple queries through connection pooling, filter by partition columns to improve query performance (partition pruning), select only the columns you need to reduce I/O, and use LIMIT during exploration to avoid processing large datasets.
Analytics bucket setup via Dashboard UI
To enable querying of analytics bucket data through the Dashboard: (1) Navigate to your Analytics Bucket page in the Supabase Dashboard. (2) Locate the namespace you want to query and click 'Query with Postgres'. (3) Enter the Postgres schema where you want to create the foreign tables. (4) Click 'Connect' to configure the wrapper.
Query analytics bucket Iceberg tables with SQL
Once the Iceberg Foreign Data Wrapper is installed, query analytics bucket data using standard SQL: `select * from schema_name.table_name limit 100;`
Query latest events from analytics bucket
Example query to get the latest events from an analytics bucket: `select event_id, event_name, event_timestamp from analytics.events order by event_timestamp desc limit 1000;`
Join analytics bucket events with transactional data
Example query joining analytics bucket events with user data: `SELECT e.event_id, e.event_name, u.user_email FROM analytics.events e JOIN public.users u ON e.user_id = u.id WHERE e.event_timestamp > NOW() - INTERVAL '7 days' LIMIT 100;`
Supabase Pipelines replication to analytics buckets deprecated
Replication into Analytics Buckets via Supabase Pipelines is no longer supported. Analytics buckets must be populated by your own ingestion pipeline.
Manual installation of Iceberg Foreign Data Wrapper
For advanced use cases, the Iceberg Foreign Data Wrapper can be manually installed and configured using SQL. Detailed instructions are available in the Iceberg Foreign Data Wrapper documentation.
Query analytics bucket data with Postgres SQL
Analytics bucket data can be queried directly from Postgres using standard SQL through the Iceberg Foreign Data Wrapper. The wrapper creates a bridge between your Postgres database and Iceberg tables stored in analytics buckets.
Read data from Iceberg table
Scan and read data from an Iceberg table using table.scan().to_pandas() to get all rows. For filtered queries, use table.scan(filter="column = value").to_pandas(). To select specific columns, use table.scan(selected_fields=["col1", "col2"]).to_pandas().
List Iceberg namespaces and tables
List all namespaces using catalog.list_namespaces(). List tables in a specific namespace using catalog.list_tables(namespace_name). Load table metadata using catalog.load_table((namespace, table_name)) to access schema and partition information.
PyIceberg error handling for table operations
PyIceberg raises exceptions when attempting to load non-existent tables. Check table existence by catching exceptions or use catalog.create_table_if_not_exists() to avoid errors.
PyIceberg performance best practices
For PyIceberg performance: batch writes by inserting data in batches rather than row-by-row; use partitioning strategies for large tables to improve query performance; leverage schema evolution which PyIceberg supports without rewriting data; use Parquet format for efficient columnar storage.
PyIceberg complete setup example
from pyiceberg.catalog import load_catalog import pyarrow as pa import datetime PROJECT_REF = "your-project-ref" WAREHOUSE = "your-analytics-bucket-name" SERVICE_KEY = "your-service-key" S3_ACCESS_KEY = "your-access-key" S3_SECRET_KEY = "your-secret-key" S3_REGION = "us-east-1" S3_ENDPOINT = f"https://{PROJECT_REF}.supabase.co/storage/v1/s3" CATALOG_URI = f"https://{PROJECT_REF}.supabase.co/storage/v1/iceberg" catalog = load_catalog( "supabase-analytics", type="rest", warehouse=WAREHOUSE, uri=CATALOG_URI, token=SERVICE_KEY, **{ "py-io-impl": "pyiceberg.io.pyarrow.PyArrowFileIO", "s3.endpoint": S3_ENDPOINT, "s3.access-key-id": S3_ACCESS_KEY, "s3.secret-access-key": S3_SECRET_KEY, "s3.region": S3_REGION, "s3.force-virtual-addressing": False, }, ) print("✓ Successfully connected to Iceberg catalog")
PyIceberg create table example
catalog.create_namespace_if_not_exists("analytics") schema = pa.schema([ pa.field("event_id", pa.int64()), pa.field("user_id", pa.int64()), pa.field("event_name", pa.string()), pa.field("event_timestamp", pa.timestamp("ms")), pa.field("properties", pa.string()), ]) table = catalog.create_table_if_not_exists( ("analytics", "events"), schema=schema ) print("✓ Created table: analytics.events")
PyIceberg write data example
import datetime current_time = datetime.datetime.now() data = pa.table({ "event_id": [1, 2, 3, 4, 5], "user_id": [101, 102, 101, 103, 102], "event_name": ["login", "view_product", "logout", "purchase", "login"], "event_timestamp": [current_time] * 5, "properties": [ '{"browser":"chrome"}', '{"product_id":"123"}', '{}', '{"amount":99.99}', '{"browser":"firefox"}' ], }) table.append(data) print("✓ Appended 5 rows to analytics.events")
PyIceberg read data examples
Scan entire table: scan_result = table.scan().to_pandas() Query with filters: filtered = table.scan(filter="event_name = 'login'").to_pandas() Select specific columns: selected = table.scan(selected_fields=["user_id", "event_name", "event_timestamp"]).to_pandas()
PyIceberg feature status
PyIceberg integration with Supabase is in alpha. Expect rapid changes, limited features, and possible breaking updates.
PyIceberg installation for Supabase
Install PyIceberg with Supabase support using: pip install "supabase[iceberg]"
PyIceberg catalog connection configuration
To connect PyIceberg to Supabase analytics, load the Iceberg REST Catalog using the catalog.load_catalog() method with type='rest', passing the warehouse name, catalog URI (https://{PROJECT_REF}.supabase.co/storage/v1/iceberg), service key as token, and S3 configuration including s3.endpoint, s3.access-key-id, s3.secret-access-key, s3.region, s3.force-virtual-addressing (set to False), and py-io-impl set to pyiceberg.io.pyarrow.PyArrowFileIO.
S3 endpoint URL for PyIceberg
The S3 endpoint for Supabase Iceberg is constructed as: https://{PROJECT_REF}.supabase.co/storage/v1/s3
Iceberg catalog URI for Supabase
The Iceberg REST Catalog URI for Supabase is: https://{PROJECT_REF}.supabase.co/storage/v1/iceberg
Create Iceberg namespace
Create a namespace in PyIceberg using catalog.create_namespace_if_not_exists(namespace_name) to organize tables logically.
Define and create Iceberg table schema
Define a PyArrow schema with pa.schema() containing pa.field() definitions for each column, then create a table using catalog.create_table_if_not_exists((namespace, table_name), schema=schema).
Write data to Iceberg table
Append data to an Iceberg table using table.append(data) where data is a PyArrow table. Data should be prepared as pa.table() with column names and values.