Hyperliquid Trades to ClickHouse

Stream Hyperliquid fill and trade data into ClickHouse for real-time trading analytics dashboards.

Use Case

You want to build a real-time trading analytics dashboard by streaming Hyperliquid fill events into ClickHouse. This gives you sub-second query performance over high-volume trading data for PnL tracking, volume analysis, and market monitoring.

Pipeline Configuration

1. Create a new pipeline

   In the GoldRush Platform, navigate to Manage Pipelines and click Create Pipeline. Name it hl-analytics.

2. Select your source

   Choose Hyperliquid as the chain and Fills as the data type. This streams every order fill event from Hyperliquid.

3. Configure the ClickHouse destination

   Select ClickHouse as the destination type and enter your connection details:

   destination:
     type: "clickhouse"
     url: "clickhouse://your-host:8123/analytics"
     user: "${CH_USER}"
     password: "${CH_PASSWORD}"
     batch_size: 10000
   
4. Add a SQL transform (optional)

   Add a transform to filter for specific trading pairs and map side codes to human-readable labels:

   transforms:
     hl_fills: >
       SELECT block_number, block_time, user_address, coin, px, sz, fee,
              CASE WHEN side = 'B' THEN 'buy' ELSE 'sell' END AS trade_side,
              closed_pnl, tid
       FROM hl_fills
       WHERE coin IN ('ETH', 'BTC', 'SOL')
   
5. Deploy

   Review and deploy. Fill events begin flowing to ClickHouse within seconds.

Verify Data

SELECT coin, trade_side, count(*) AS fills,
       sum(toFloat64OrZero(sz)) AS total_size
FROM hl_analytics.hl_fills
WHERE block_time >= now() - INTERVAL 1 HOUR
GROUP BY coin, trade_side
ORDER BY total_size DESC;

Sample Analytical Queries

Volume by coin over the last 24 hours:

SELECT coin,
       count(*) AS trade_count,
       sum(toFloat64OrZero(sz) * toFloat64OrZero(px)) AS volume_usd
FROM hl_analytics.hl_fills
WHERE block_time >= toString(now() - INTERVAL 24 HOUR)
GROUP BY coin
ORDER BY volume_usd DESC;

Top traders by realized PnL:

SELECT user_address,
       sum(toFloat64OrZero(closed_pnl)) AS total_pnl,
       count(*) AS fill_count
FROM hl_analytics.hl_fills
WHERE closed_pnl != '0' AND closed_pnl != ''
GROUP BY user_address
ORDER BY total_pnl DESC
LIMIT 20;

Production Tips

• Batch size: ClickHouse performs best with large inserts. The default of 10,000 rows per batch is a good starting point. Increase to 50,000+ for high-throughput streams.
• ORDER BY key: Choose columns that match your most common query patterns. (coin, block_number) works well for time-series analytics by trading pair.
• Trades vs Fills: Use the fills entity for per-user fill data (includes PnL, fees). Use the trades entity for aggregate market trades (includes buyer/seller details).