Ai Click House Store Laravel Package

Getting Started

Minimal Setup

1. Install the Package

   composer require symfony/ai-click-house-store
   

2. Configure ClickHouse Connection Add to your Laravel/Symfony config (e.g., config/ai.php):

   'stores' => [
       'clickhouse' => [
           'dsn' => 'http://clickhouse:8123', // or 'tcp://clickhouse:9000'
           'database' => 'your_db',
           'table' => 'vectors',
           'embedding_column' => 'embedding',
           'id_column' => 'id',
           'metadata_column' => 'metadata',
           'distance_strategy' => 'cosine', // or 'l2'
       ],
   ],
   

3. Define ClickHouse Table Ensure your table exists with an ANN index:

   CREATE TABLE vectors (
       id UInt64,
       embedding Array(Float32),
       metadata String,
       INDEX ann_index embedding TYPE ann(1024) GRANULARITY=3
   ) ENGINE = MergeTree();
   

4. First Usage

   use Symfony\AI\Store\StoreInterface;
   use Symfony\Component\DependencyInjection\ContainerInterface;
   
   $container = new ContainerInterface();
   $store = $container->get('ai.store.clickhouse');
   
   // Insert a vector
   $store->add(
       'doc1',
       [0.1, 0.2, ..., 0.768], // Your embedding
       ['source' => 'document', 'category' => 'tech']
   );
   
   // Query nearest neighbors
   $results = $store->nearest(
       [0.15, 0.25, ..., 0.769],
       3,
       ['category' => 'tech'] // Optional filter
   );
   

Implementation Patterns

Core Workflows

1. Vector Ingestion Pipeline

• Batch Inserts: Use ClickHouse’s bulk INSERT for efficiency:

  $store->addBatch([
      ['id' => 'doc1', 'embedding' => $embedding1, 'metadata' => $meta1],
      ['id' => 'doc2', 'embedding' => $embedding2, 'metadata' => $meta2],
  ]);
  

• Async Processing: Offload to a queue (e.g., Laravel Queues) for large datasets.

2. Hybrid Search Queries

Combine vector similarity with SQL filters:

$results = $store->nearest(
    $queryEmbedding,
    5,
    ['category' => 'tech', 'published_at' => ['>=', '2023-01-01']]
);

Equivalent ClickHouse Query:

SELECT * FROM vectors
WHERE category = 'tech'
  AND published_at >= '2023-01-01'
ORDER BY vector_distance(embedding, [0.1, 0.2, ...]) ASC
LIMIT 5;

3. Dynamic Filtering

Use Symfony’s Filter component for complex conditions:

use Symfony\Component\AI\Filter\Filter;

$filter = new Filter();
$filter->where('metadata.category', '=', 'tech')
       ->where('metadata.rating', '>', 4);

$results = $store->nearest($embedding, 3, $filter);

4. Vector Deletion

Remove specific vectors by ID:

$store->remove('doc1'); // Deletes by ID
$store->removeBatch(['doc1', 'doc2']); // Bulk delete

Integration Tips

Laravel-Specific Patterns

1. Service Provider Binding Bind the store in AppServiceProvider:

   public function register()
   {
       $this->app->bind('ai.store.clickhouse', function ($app) {
           return new \Symfony\AI\ClickHouseStore\ClickHouseStore(
               $app['ai.clickhouse.connection'],
               $app['ai.clickhouse.options']
           );
       });
   }
   

2. Queue-Based Processing For large-scale ingestion, use Laravel Queues:

   class VectorIngestJob implements ShouldQueue
   {
       public function handle()
       {
           $store = app('ai.store.clickhouse');
           $store->addBatch($this->vectors);
       }
   }
   

3. Caching Layer Cache frequent queries (e.g., top-5 recommendations):

   $cacheKey = 'recommendations:user123';
   $results = cache()->remember($cacheKey, now()->addHours(1), function () use ($store) {
       return $store->nearest($userEmbedding, 5);
   });
   

Performance Optimization

• ANN Index Tuning: Adjust GRANULARITY and GRAPH_SIZE based on dataset size:

  -- For 1M vectors, start with:
  INDEX ann_index embedding TYPE ann(1024) GRANULARITY=3 GRAPH_SIZE=100
  

• Query Optimization: Use LIMIT early and avoid SELECT *:

  $store->nearest($embedding, 10, [], ['embedding', 'metadata.category']);
  

• Connection Pooling: Reuse ClickHouse connections (Symfony’s Connection pool handles this by default).

Gotchas and Tips

Pitfalls

1. Schema Mismatch Errors

   • Issue: InvalidArgumentException if embedding_column doesn’t match ClickHouse’s Array(Float32).
   • Fix: Verify schema with:

     SELECT * FROM vectors LIMIT 1;
     

   • Debug Tip: Enable Symfony’s debug mode to see raw ClickHouse queries.

2. ANN Index Not Found

   • Issue: Unknown identifier 'ann_index' if the index isn’t created.
   • Fix: Recreate the table with the index:

     ALTER TABLE vectors DROP INDEX ann_index;
     ALTER TABLE vectors ADD INDEX ann_index embedding TYPE ann(1024);
     

3. Dimensionality Mismatch

   • Issue: Dimension mismatch when querying with embeddings of different lengths.
   • Fix: Validate embedding dimensions before insertion:

     if (count($embedding) !== 768) {
         throw new \InvalidArgumentException('Embedding must be 768-dimensional.');
     }
     

4. Filter Syntax Errors

   • Issue: SyntaxError in SQL filters (e.g., incorrect operators).
   • Fix: Use Symfony’s Filter component for complex queries:

     $filter = new Filter();
     $filter->where('metadata.rating', '>', 4)->andWhere('metadata.tags', 'LIKE', '%tech%');
     

5. Connection Timeouts

   • Issue: Slow responses due to network latency or ClickHouse overload.
   • Fix:
     • Increase PHP timeout: ini_set('default_socket_timeout', 30);
     • Use a local ClickHouse instance for development.

Debugging Tips

1. Enable Query Logging Configure ClickHouse’s logger to log slow queries:

   # In clickhouse-server.config.xml
   <logger>
       <level>trace</level>
       <log>query.log</log>
       <include>
           <query>.*</query>
       </include>
   </logger>
   

2. Symfony Debugging

   • Enable Symfony’s profiler to inspect store queries:

     if ($debug) {
         $store->setDebug(true);
     }
     

   • Check for deprecation warnings in newer Symfony AI versions.

3. ClickHouse Metrics Monitor query performance with:

   SELECT * FROM system.asynchronous_metrics
   WHERE query_id = [last_query_id];
   

Extension Points

1. Custom Distance Strategies Extend the DistanceStrategy interface to support custom metrics (e.g., Manhattan distance):

   class ManhattanDistance implements DistanceStrategyInterface
   {
       public function calculate(array $vector1, array $vector2): float
       {
           return array_sum(abs(array_map('floatval', array_diff($vector1, $vector2))));
       }
   }
   

   Register it in the store’s configuration.

2. Batch Processing Hooks Override addBatch() to preprocess embeddings (e.g., normalization):

   $store->addBatch($vectors, function ($vector) {
       return $this->normalizeEmbedding($vector['embedding']);
   });
   

3. Custom Filter Functions Add ClickHouse-specific filters (e.g., arrayHasAny):

   $filter->where('metadata.tags', 'arrayHasAny', ['tech', 'ai']);
   

   Requires extending the Filter component or using raw SQL.

4