Laravel Bigquery Laravel Package

Getting Started

Minimal Setup

1. Install the Package:

   composer require pelfox/laravel-bigquery
   

2. Configure Database Connection: Add to config/database.php under connections:

   'bigquery' => [
       'driver' => 'bigquery',
       'dataset' => 'your_dataset_name', // Required
       'keyFilePath' => storage_path('app/google/credentials.json'), // Required
       'database' => '', // Optional (BigQuery doesn't use this)
       'prefix' => '',   // Optional (BigQuery ignores this)
   ],
   

   • Store the service account JSON key file in storage/app/google/ and restrict permissions via .env:

     BIGQUERY_KEY_PATH=storage/app/google/credentials.json
     

3. First Query:

   // Query Builder
   $results = DB::connection('bigquery')->table('your_table')->get();
   
   // Eloquent Model
   class UserAnalytics extends Model {
       protected $connection = 'bigquery';
       protected $table = 'your_dataset.your_table';
       public $incrementing = false;
       public $timestamps = false;
   }
   

4. Verify Connection: Use the facade to test connectivity:

   \Pelfox\LaravelBigQuery\Facades\BigQuery::dataset('your_dataset')->query('SELECT 1')->get();
   

First Use Case: Analytics Dashboard

Replace a PostgreSQL-heavy analytics query with BigQuery:

// Before (PostgreSQL)
$users = DB::table('users')->where('created_at', '>', now()->subDays(30))->get();

// After (BigQuery)
$userAnalytics = DB::connection('bigquery')
    ->table('analytics.users')
    ->selectRaw('COUNT(*) as total, DATE(created_at) as day')
    ->where('created_at', '>', now()->subDays(30))
    ->groupBy('day')
    ->get();

Implementation Patterns

Query Builder Workflows

1. Dataset Context Switching: Use the facade to dynamically target datasets:

   // Default dataset (from config)
   $results = DB::connection('bigquery')->table('events')->get();
   
   // Override dataset
   $results = \Pelfox\LaravelBigQuery\Facades\BigQuery::dataset('marketing')
       ->table('campaigns')
       ->where('date', '>', '2023-01-01')
       ->get();
   

2. Complex Joins Across Datasets: Leverage BigQuery’s cross-dataset joins:

   $query = DB::connection('bigquery')
       ->select('users.name', 'orders.total')
       ->from('users')
       ->join('orders', 'users.id', '=', 'orders.user_id')
       ->where('users.dataset', 'user_data')
       ->where('orders.dataset', 'transaction_data');
   

3. Parameterized Queries: Use Laravel’s query builder bindings:

   $userId = 123;
   $events = DB::connection('bigquery')
       ->table('user_events')
       ->where('user_id', $userId)
       ->where('event_date', '>', now()->subDays(7))
       ->get();
   

Eloquent Patterns

1. Model-Level Dataset Configuration:

   class UserEvent extends Model {
       protected $connection = 'bigquery';
       protected $table = 'analytics.user_events'; // dataset.table format
       protected $primaryKey = 'event_id';
       public $incrementing = false;
   }
   

2. Custom Casts for BigQuery Types:

   protected $casts = [
       'user_id' => AsInteger::class,
       'event_data' => AsJson::class,
       'metadata' => AsStruct::class . ':0,getSchemaForMetadata',
   ];
   
   public function getSchemaForMetadata(): array {
       return [
           'ip_address' => StringType::class,
           'user_agent' => StringType::class,
       ];
   }
   

3. Repeated Fields Handling: For REPEATED fields (e.g., arrays in BigQuery):

   protected $casts = [
       'tags' => AsString::class . ':1', // Array of strings
   ];
   

Batch Operations

1. Bulk Inserts: Use insert with arrays:

   DB::connection('bigquery')->table('logs')->insert([
       ['user_id' => 1, 'action' => 'login', 'created_at' => now()],
       ['user_id' => 2, 'action' => 'purchase', 'created_at' => now()],
   ]);
   

2. Upserts: BigQuery lacks ON CONFLICT; use MERGE via raw SQL:

   DB::connection('bigquery')->statement(`
       MERGE `project.dataset.target_table` T
       USING (
           SELECT 'user123' as user_id, 'new_value' as data
       ) S
       ON T.user_id = S.user_id
       WHEN MATCHED THEN UPDATE SET data = S.data
       WHEN NOT MATCHED THEN INSERT (user_id, data) VALUES (S.user_id, S.data)
   `);
   

Performance Optimization

1. Query Caching: Cache frequent queries (e.g., dashboards):

   $cacheKey = 'user_metrics_' . $userId;
   return Cache::remember($cacheKey, now()->addHours(1), function () use ($userId) {
       return DB::connection('bigquery')->table('user_metrics')
           ->where('user_id', $userId)
           ->get();
   });
   

2. Partitioned Tables: Explicitly target partitions in queries:

   $yesterday = now()->subDay();
   $results = DB::connection('bigquery')
       ->table('logs_$2023_06_01') // Partitioned table
       ->where('date', '=', $yesterday->format('Y-m-d'))
       ->get();
   

3. Materialized Views: Pre-compute expensive queries as views:

   DB::connection('bigquery')->statement(`
       CREATE MATERIALIZED VIEW `project.dataset.user_daily_metrics`
       AS SELECT user_id, DATE(created_at) as day, COUNT(*) as events
       FROM `project.dataset.user_events`
       GROUP BY user_id, day
   `);
   

Gotchas and Tips

Pitfalls

1. Schema Mismatches:

   • Issue: Eloquent casts may fail if BigQuery schema differs from expected types (e.g., STRING vs. BYTES).
   • Fix: Validate schemas via:

     $schema = DB::connection('bigquery')->selectOne("SELECT * FROM `project.dataset.table` LIMIT 0");
     

   • Tip: Use AsStruct::class for nested/repeated fields and define schemas explicitly.

2. Connection Timeouts:

   • Issue: BigQuery connections may time out during long-running queries.
   • Fix: Configure the connection timeout in config/database.php:

     'bigquery' => [
         'timeout' => 300, // 5 minutes
     ],
     

   • Tip: Use DB::connection('bigquery')->reconnect() if queries hang.

3. Repeated Field Quirks:

   • Issue: REPEATED fields (arrays) require :1 suffix in casts, but values must be arrays:

     // Wrong: Cast expects array but gets string
     $model->tags = 'tag1,tag2'; // Fails
     
     // Correct: Pass as array
     $model->tags = ['tag1', 'tag2'];
     

   • Tip: Use AsString::class . ':1' for string arrays, but ensure data is normalized.

4. Timestamp Handling:

   • Issue: BigQuery’s TIMESTAMP vs. Laravel’s Carbon may cause serialization errors.
   • Fix: Use AsTimestamp::class and ensure timestamps are in UTC:

     protected $casts = [
         'created_at' => AsTimestamp::class,
     ];
     

5. Query Plan Limitations:

   • Issue: The package doesn’t expose BigQuery’s execution plan, making optimization harder.
   • Tip: Log raw SQL and use BigQuery’s UI to analyze performance:

     $query = DB::connection('bigquery')->table('large_table')->toSql();
     logger($query);
     

6. Service Account Permissions:

   • Issue: Missing permissions (e.g., bigquery.tables.getData) cause silent failures.
   • Fix: Grant roles via Google Cloud Console:

     roles/bigquery.dataViewer
     roles/bigquery.jobs.user
     

   • Tip: Use a dedicated service account per environment (dev/staging/prod).

7. Large Result Sets:

   • Issue: Fetching millions of rows may