Laravel Bigquery Laravel Package

Getting Started

Minimal Setup

1. Install the package:

   composer require pelfox/laravel-bigquery
   

2. Configure config/database.php:

   'bigquery' => [
       'driver' => 'bigquery',
       'dataset' => env('BIGQUERY_DATASET'), // Required
       'keyFilePath' => env('BIGQUERY_KEY_PATH'), // Required (path to .json service account key)
       'database' => '', // Optional (default: empty)
       'prefix' => '', // Optional (default: empty)
   ],
   

3. First Query:

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

First Use Case: Analytics Query

Replace a PostgreSQL analytics query with BigQuery:

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

// After (BigQuery)
$users = DB::connection('bigquery')
    ->table('analytics.users')
    ->where('created_at', '>', \Pelfox\LaravelBigQuery\Escape::date(now()->subDays(30)))
    ->count();

Implementation Patterns

Query Builder Workflows

1. Dataset Switching:

   // Default dataset (from config)
   DB::connection('bigquery')->table('events')->get();
   
   // Explicit dataset
   DB::connection('bigquery')->table('analytics.events')->get();
   
   // Dynamic via Facade
   \Pelfox\LaravelBigQuery\Facades\BigQuery::dataset('marketing')->table('campaigns')->get();
   

2. Complex Queries:

   $query = DB::connection('bigquery')
       ->select('user_id', DB::raw('COUNT(*) as event_count'))
       ->from('events')
       ->join('users', 'events.user_id', '=', 'users.id')
       ->where('events.timestamp', '>', \Pelfox\LaravelBigQuery\Escape::timestamp(now()->subDays(7)))
       ->groupBy('user_id')
       ->orderBy('event_count', 'desc')
       ->limit(100);
   

3. Batch Operations:

   // Insert (use chunking for large datasets)
   DB::connection('bigquery')->table('logs')->insert([
       ['user_id' => 1, 'action' => 'login', 'timestamp' => now()],
       ['user_id' => 2, 'action' => 'purchase', 'timestamp' => now()],
   ]);
   
   // Update (requires WHERE clause)
   DB::connection('bigquery')->table('users')->where('last_active', '<', now()->subDays(90))->update(['status' => 'inactive']);
   

Eloquent Patterns

1. Model Configuration:

   class UserEvent extends Model {
       protected $connection = 'bigquery';
       protected $table = 'analytics.user_events';
       public $incrementing = false; // BigQuery uses arbitrary IDs
       public $timestamps = false;  // BigQuery lacks native timestamps
   
       protected $casts = [
           'user_id' => 'integer',
           'event_time' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsTimestamp::class,
           'metadata' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsJson::class,
       ];
   }
   

2. Repeated Fields:

   class Product extends Model {
       protected $casts = [
           'tags' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsString::class . ':1', // Repeated STRING
           'dimensions' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsFloat::class . ':1', // Repeated FLOAT
       ];
   }
   

3. Struct/Record Types:

   class Order extends Model {
       protected $casts = [
           'shipping_address' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsStruct::class . ':0,getAddressSchema',
       ];
   
       public function getAddressSchema(): array {
           return [
               'street' => \Pelfox\LaravelBigQuery\Types\StringType::class,
               'city' => \Pelfox\LaravelBigQuery\Types\StringType::class,
               'zip' => \Pelfox\LaravelBigQuery\Types\StringType::class,
           ];
       }
   }
   

Integration Tips

1. Hybrid Applications: Use Laravel’s connection() method to switch between databases:

   // Transactional data (PostgreSQL)
   $user = DB::connection('pgsql')->table('users')->find(1);
   
   // Analytics (BigQuery)
   $events = DB::connection('bigquery')->table('user_events')->where('user_id', $user->id)->get();
   

2. Caching Queries: Cache frequent BigQuery results (e.g., dashboards) to reduce costs:

   $revenue = Cache::remember('bigquery_revenue_daily', now()->addHours(1), function () {
       return DB::connection('bigquery')->table('sales')->sum('amount');
   });
   

3. Error Handling: Wrap BigQuery queries in try-catch to handle quota/execution errors:

   try {
       $results = DB::connection('bigquery')->select('SELECT * FROM large_table LIMIT 1000');
   } catch (\Google\Cloud\BigQuery\Exception\BigQueryException $e) {
       Log::error('BigQuery query failed: ' . $e->getMessage());
       // Fallback to cached data or notify admin
   }
   

4. Migrations: Use raw SQL for schema changes (BigQuery lacks Laravel migrations):

   DB::connection('bigquery')->statement('
       ALTER TABLE analytics.users
       ADD COLUMN IF NOT EXISTS last_login TIMESTAMP
   ');
   

Gotchas and Tips

Pitfalls

1. Connection Management:

   • Issue: BigQuery connections are not pooled by default; frequent queries may hit rate limits.
   • Fix: Enable permanent connection caching in config/database.php:

     'bigquery' => [
         'driver' => 'bigquery',
         'dataset' => '...',
         'keyFilePath' => '...',
         'cache_connection' => true, // Add this
     ],
     

2. Data Type Mismatches:

   • Issue: Eloquent’s default casts (e.g., integer) may fail for BigQuery types like INT64 or FLOAT64.
   • Fix: Use package-specific casts:

     protected $casts = [
         'price' => \Pelfox\LaravelBigQuery\Eloquent\Casts\AsFloat::class, // Not \Illuminate\Database\Eloquent\Casts\Attribute
     ];
     

3. Repeated Fields:

   • Issue: Repeated fields (e.g., ARRAY<STRING>) require special cast syntax (:1 suffix) and may return arrays unexpectedly.
   • Fix: Explicitly cast and handle arrays:

     $model->tags = collect($model->tags)->map(fn($tag) => (string)$tag); // Ensure strings
     

4. Timestamp Handling:

   • Issue: BigQuery’s TIMESTAMP type may not serialize correctly with Laravel’s Carbon.
   • Fix: Use the package’s escape methods:

     $query->where('created_at', '>', \Pelfox\LaravelBigQuery\Escape::timestamp(now()->subDays(1)));
     

5. JSON Data:

   • Issue: Inserting JSON requires escaping:

     DB::connection('bigquery')->table('logs')->insert([
         'metadata' => \Pelfox\LaravelBigQuery\Escape::json(['key' => 'value']),
     ]);
     

6. Query Performance:

   • Issue: Complex queries may time out or exceed slot limits.
   • Fix: Use LIMIT and avoid SELECT *:

     $query->select(['user_id', 'COUNT(*) as event_count'])
           ->groupBy('user_id')
           ->limit(1000);
     

7. Service Account Permissions:

   • Issue: Missing permissions (e.g., bigquery.tables.getData) cause silent failures.
   • Fix: Grant the service account:
     • BigQuery Data Viewer (for reads)
     • BigQuery Data Editor (for writes)
     • BigQuery Job User (for query execution)

8. Case Sensitivity:

   • Issue: BigQuery table/column names are case-sensitive in some contexts.
   • **