> ## Documentation Index
> Fetch the complete documentation index at: https://goldrush.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

<Steps>
  <Step title="Create a new pipeline">
    In the [GoldRush Platform](https://goldrush.dev/platform/), navigate to **Manage Pipelines** and click **Create Pipeline**. Name it `hl-analytics`.
  </Step>

  <Step title="Select your source">
    Choose **Hyperliquid** as the chain and **Fills** as the data type. This streams every order fill event from Hyperliquid.
  </Step>

  <Step title="Configure the ClickHouse destination">
    Select **ClickHouse** as the destination type and enter your connection details:

    ```yaml theme={null}
    destination:
      type: "clickhouse"
      url: "clickhouse://your-host:8123/analytics"
      user: "${CH_USER}"
      password: "${CH_PASSWORD}"
      batch_size: 10000
    ```
  </Step>

  <Step title="Add a SQL transform (optional)">
    Add a transform to filter for specific trading pairs and map side codes to human-readable labels:

    ```yaml theme={null}
    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')
    ```
  </Step>

  <Step title="Deploy">
    Review and deploy. Fill events begin flowing to ClickHouse within seconds.
  </Step>
</Steps>

## Verify Data

```sql theme={null}
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:**

```sql theme={null}
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:**

```sql theme={null}
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).