ClickHouse & Parquet

Overview

AR.IO gateway Release 33 introduces a new configuration option for using Parquet files and ClickHouse to improve performance and scalability of your AR.IO gateway for large datasets.

This guide will walk you through the process of setting up ClickHouse with your AR.IO gateway, and importing Parquet files to bootstrap your ClickHouse database.

What is Parquet?

Apache Parquet is a columnar storage file format designed for efficient data storage and retrieval. Unlike row-based storage formats like SQLite, Parquet organizes data by column rather than by row, which provides several advantages for analytical workloads:

• Efficient compression: Similar data is stored together, leading to better compression ratios
• Columnar access: You can read only the columns you need, reducing I/O operations
• Predicate pushdown: Filter operations can be pushed down to the storage layer, improving query performance

For more information about Parquet, see the Parquet documentation.

Current Integration with AR.IO Gateways

In the current AR.IO gateway implementation, Parquet and ClickHouse run alongside SQLite rather than replacing it. This parallel architecture allows each database to handle what it does best:

• SQLite continues to handle transaction writes and updates
• ClickHouse with Parquet files is optimized for fast query performance, especially with large datasets

The gateway continues to operate with SQLite just as it always has, maintaining all of its normal functionality. Periodically, the gateway will export batches of data from SQLite to Parquet files, which are then imported into ClickHouse. This batch-oriented approach is much more efficient than attempting to synchronize the databases in real-time, as it leverages Parquet's strength in handling large, immutable data sets.

Note that despite Parquet's efficient compression, gateways may not see significant disk space reduction in all cases. While bundled transaction data is exported to Parquet, L1 data remains in SQLite. Without substantial unbundling and indexing filters, minimal data gets exported to Parquet, limiting potential storage savings.

With ClickHouse integration enabled, GraphQL queries are primarily routed to ClickHouse, leveraging its superior performance for large datasets. This significantly improves response times while maintaining SQLite's reliability for transaction processing.

For more information about gateway architecture and data processing, see our Gateway Architecture documentation.

Parquet vs. SQLite in AR.IO Gateways

While SQLite is excellent for transactional workloads and small to medium datasets, it faces challenges with very large datasets:

Benefits for Gateway Operators

Implementing Parquet and ClickHouse alongside SQLite in your AR.IO gateway offers several key advantages:

• Dramatically improved query performance for GraphQL endpoints, especially for large result sets
• Reduced storage requirements through efficient columnar compression
• Better scalability for growing datasets
• Faster bootstrapping of new gateways through Parquet file imports
• Reduced load on SQLite by offloading query operations to ClickHouse

The primary focus of the Parquet/ClickHouse integration is the significant speed improvement for querying large datasets. Gateway operators managing significant volumes of data will notice substantial performance gains when using this configuration.

Storage Considerations

While Parquet files offer more efficient compression for the data they contain, it's important to understand the storage impact:

• Bundled transaction data is exported to Parquet and removed from SQLite, potentially saving space
• L1 data remains in SQLite regardless of Parquet configuration
• Space savings are highly dependent on your unbundling filters - without substantial unbundling configurations, minimal data gets exported to Parquet
• The more data you unbundle and export to Parquet, the greater the potential storage efficiency

For gateway operators, this means proper filter configuration is crucial to realize storage benefits. The primary advantage remains significantly improved query performance for large datasets, with potential space savings as a secondary benefit depending on your specific configuration.

The following sections will guide you through setting up ClickHouse with your AR.IO gateway, exporting data from SQLite to Parquet, and importing Parquet files to bootstrap your ClickHouse database.

Installing ClickHouse

ClickHouse is a powerful, open-source analytical database that excels at handling large datasets and complex queries. It is the tool used by the gateway to integrate with the Parquet format.

For more information about ClickHouse, see the ClickHouse documentation.

sudo apt-get install -y apt-transport-https ca-certificates curl gnupg
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

ARCH=$(dpkg --print-architecture)
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

sudo apt-get install -y clickhouse-client

Configure Gateway to use ClickHouse

CLICKHOUSE_URL="http://clickhouse:8123"
CLICKHOUSE_PASSWORD=<your-password>

CLICKHOUSE_USER=<your-username>

ANS104_UNBUNDLE_FILTER='{ "and": [ { "not": { "or": [ { "tags": [ { "name": "Bundler-App-Name", "value": "Warp" } ] }, { "tags": [ { "name": "Bundler-App-Name", "value": "Redstone" } ] }, { "tags": [ { "name": "Bundler-App-Name", "value": "KYVE" } ] }, { "tags": [ { "name": "Bundler-App-Name", "value": "AO" } ] }, { "attributes": { "owner_address": "-OXcT1sVRSA5eGwt2k6Yuz8-3e3g9WJi5uSE99CWqsBs" } }, { "attributes": { "owner_address": "ZE0N-8P9gXkhtK-07PQu9d8me5tGDxa_i4Mee5RzVYg" } }, { "attributes": { "owner_address": "6DTqSgzXVErOuLhaP0fmAjqF4yzXkvth58asTxP3pNw" } } ] } }, { "tags": [ { "name": "App-Name", "valueStartsWith": "ArDrive" } ] } ] }'
ANS104_INDEX_FILTER='{ "tags": [ { "name": "App-Name", "value": "ArDrive-App" } ] }'

ADMIN_API_KEY=<example>

Downloading and Importing the Parquet File

curl -L https://arweave.net/JVmsuD2EmFkhitzWN71oi9woADE4WUfvrbBYgremCBM -o 2025-04-23-ardrive-ans104-parquet.tar.gz

tar -xzf 2025-04-23-ardrive-ans104-parquet.tar.gz

mkdir -p data/parquet

docker compose --profile clickhouse up clickhouse -d

sudo chmod -R 777 data/parquet

mv 2025-04-23-ardrive-ans104-parquet/* data/parquet

docker compose --profile clickhouse up clickhouse -d

./scripts/clickhouse-import

Verifying Successful Import

clickhouse client --password <your-password> -h localhost -q 'SELECT COUNT(DISTINCT id) FROM transactions'

curl -g -X POST \
  -H "Content-Type: application/json" \
  -d '{"query":"query { transactions(ids: [\"YSNwoYB01EFIzbs6HmkGUjjxHW3xuqh-rckYhi0av4A\"]) { edges { node { block { height } bundledIn { id } } } } }"}' \
  http://localhost:3000/graphql

# Expected output:
# {"data":{"transactions":{"edges":[{"node":{"block":{"height":1461918},"bundledIn":{"id":"ylhb0PqDtG5HwBg00_RYztUl0x2RuKvbNzT6YiNR2JA"}}}]}}}

Starting and Stopping the Gateway with ClickHouse

The gateway ClickHouse container is run as a "profile" in the main docker compose file. That means you must specify the profile when starting or stopping the gateway if you want to include the ClickHouse container in the commands.

docker compose --profile clickhouse up -d

docker compose --profile clickhouse down

docker compose --profile clickhouse up clickhouse -d

docker compose --profile clickhouse down clickhouse

Useful Docker Commands

Monitor and manage your gateway with ClickHouse using these commands:

# View all running services
docker ps

# Start gateway with ClickHouse profile
docker compose --profile clickhouse up -d

# Stop gateway with ClickHouse profile
docker compose --profile clickhouse down

# Pull latest images
docker compose --profile clickhouse pull

# Start only ClickHouse container
docker compose --profile clickhouse up clickhouse -d

# Stop only ClickHouse container
docker compose --profile clickhouse down clickhouse

# Follow gateway logs
docker compose logs core -f -n 10

# Follow ClickHouse logs
docker compose --profile clickhouse logs clickhouse -f -n 10

# Check ClickHouse container status
docker compose --profile clickhouse ps clickhouse

# Restart ClickHouse container
docker compose --profile clickhouse restart clickhouse

Next Steps

Now that you have ClickHouse set up for improved query performance, continue building your gateway infrastructure: