Bigquery Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require ccmbenchmark/bigquery-bundle
   

   Register the bundle in config/bundles.php:

   return [
       // ...
       CCMBenchmark\BigQueryBundle\BigQueryBundle::class => ['all' => true],
   ];
   

2. Configure Google Cloud Credentials Add credentials to .env:

   GOOGLE_APPLICATION_CREDENTIALS=/path/to/service-account.json
   

3. First Use Case: Upload a Simple Entity Define an entity and metadata:

   // src/Entity/MyEntity.php
   class MyEntity implements RowInterface {
       use RowTrait;
       private $sessions;
       // Getters/setters...
   }
   
   // src/BigQuery/MyMetadata.php
   class MyMetadata implements MetadataInterface {
       public function getEntityClass(): string { return MyEntity::class; }
       public function getDatasetId(): string { return 'mydataset'; }
       public function getProjectId(): string { return 'myproject'; }
       public function getTableId(): string { return 'mytable'; }
       public function getSchema(): array {
           return [["mode" => "NULLABLE", "name" => "sessions", "type" => "INTEGER"]];
       }
   }
   

4. Register Metadata In a service or controller:

   use CCMBenchmark\BigQueryBundle\BigQuery\UnitOfWork;
   
   $uow = new UnitOfWork();
   $uow->registerMetadata(new MyMetadata());
   
   $entity = new MyEntity();
   $entity->setSessions(100);
   $uow->persist($entity);
   
   $uow->flush(); // Uploads to BigQuery
   

Implementation Patterns

Workflow: Batch Processing

1. Collect Data Use UnitOfWork to accumulate entities before flushing:

   $uow = $container->get('ccmbenchmark_bigquery.uow');
   foreach ($rawData as $item) {
       $entity = new MyEntity();
       $entity->setSessions($item['sessions']);
       $uow->persist($entity);
   }
   

2. Flush Strategically Batch flushes to optimize performance:

   $uow->flush(); // Uploads all persisted entities
   

3. Async Processing (Optional) Use Symfony’s Messenger component to defer uploads:

   $message = new UploadToBigQueryMessage($uow);
   $bus->dispatch($message);
   

Integration Tips

• Symfony Dependency Injection Bind UnitOfWork as a service:

  # config/services.yaml
  services:
      CCMBenchmark\BigQueryBundle\BigQuery\UnitOfWork: ~
  

• Dynamic Metadata Load metadata from YAML/JSON for flexibility:

  $metadata = new DynamicMetadata($config['bigquery']);
  $uow->registerMetadata($metadata);
  

• Error Handling Wrap flush() in a try-catch to handle quota/exception:

  try {
      $uow->flush();
  } catch (Google\ApiCore\ApiException $e) {
      $this->handleBigQueryError($e);
  }
  

Gotchas and Tips

Pitfalls

1. Schema Mismatches

   • BigQuery schema is immutable. Changing getSchema() after initial upload requires table recreation.
   • Fix: Use a migration tool (e.g., Doctrine Migrations) to alter schemas.

2. Memory Limits

   • Large batches may hit PHP memory limits. Use smaller chunks:

     $uow->flush(1000); // Batch size
     

3. Credential Issues

   • Ensure GOOGLE_APPLICATION_CREDENTIALS points to a valid service account with BigQuery Data Editor role.
   • Debug: Test credentials with:

     gcloud auth application-default login
     

4. Idempotency

   • flush() overwrites existing data. Use merge mode or deduplication logic if needed.

Debugging

• Enable Logging Configure Monolog to log BigQuery operations:

  # config/packages/monolog.yaml
  handlers:
      bigquery:
          type: stream
          path: "%kernel.logs_dir%/bigquery.log"
          level: debug
  

• Dry Runs Use UnitOfWork::dryRun() to preview payloads:

  $payload = $uow->dryRun();
  dump($payload); // Inspect before upload
  

Extension Points

1. Custom Writers Extend UnitOfWork to support non-JSON-serializable entities:

   class CustomUnitOfWork extends UnitOfWork {
       protected function serializeEntity(RowInterface $entity): array {
           return ['custom' => $entity->getCustomData()];
       }
   }
   

2. Pre/Post Flush Hooks Subscribe to events via Symfony’s event dispatcher:

   $dispatcher->addListener(
       BigQueryEvents::PRE_FLUSH,
       [$this, 'validateEntities']
   );
   

3. Partitioning/Clustering Configure in metadata for optimized queries:

   public function getOptions(): array {
       return [
           'timePartitioning' => ['type' => 'DAY'],
           'clustering' => ['fields' => ['sessions']],
       ];
   }