Getting Started

Minimal Steps

Install the Package

composer require google/cloud-bigquery

Add the package to your config/app.php providers (if not using Laravel’s auto-discovery):

'providers' => [
    // ...
    Google\Cloud\BigQuery\BigQueryClient::class,
],

Set Up Authentication Configure credentials via .env (recommended) or config/services.php:

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

Or use environment variables:

putenv('GOOGLE_APPLICATION_CREDENTIALS=/path/to/key.json');

First Query

use Google\Cloud\BigQuery\BigQueryClient;

$bigQuery = new BigQueryClient([
    'projectId' => env('GOOGLE_CLOUD_PROJECT'),
]);

$query = 'SELECT * FROM `project.dataset.table` LIMIT 10';
$results = $bigQuery->query($query);

foreach ($results as $row) {
    dd($row); // Inspect the first row
}

Key First Use Cases
- Ad-hoc Analytics: Run SQL queries directly from Laravel controllers or Artisan commands.
- Data Ingestion: Load CSV/JSON files into BigQuery tables (e.g., nightly syncs from SaaS APIs).
- Schema Management: Create/drop tables or datasets programmatically (e.g., during migrations).

Implementation Patterns

Core Workflows

1. Querying Data

Simple Queries:

$query = $bigQuery->query('SELECT name, email FROM `users` WHERE active = true');
foreach ($query as $row) {
    // Process row (e.g., $row['name'])
}

Parameterized Queries (Avoid SQL injection):

$query = $bigQuery->query(
    'SELECT * FROM `users` WHERE created_at > @date',
    ['date' => new \Google\Cloud\Core\Timestamp('2023-01-01')]
);

Streaming Large Results (Memory-efficient):

$query = $bigQuery->query('SELECT * FROM `large_table`');
while ($row = $query->rows()->next()) {
    // Process row-by-row
}

2. Data Ingestion

Load from Local Files (CSV/JSON):

$table = $bigQuery->dataset('analytics')->table('events');
$jobConfig = $table->load(
    fopen('events.csv', 'r'),
    'CSV',
    ['schema' => ['fields' => [...]]] // Optional schema
);
$job = $table->runJob($jobConfig);
$job->wait(); // Block until completion

Load from GCS (Google Cloud Storage):

$jobConfig = $table->load(
    'gs://bucket/events.json',
    'NEWLINE_DELIMITED_JSON'
);

Track Job Status:

if ($job->isComplete()) {
    $errors = $job->info()->getErrors();
    if (!empty($errors)) {
        throw new \RuntimeException($errors[0]->getMessage());
    }
}

3. Schema Management

Create a Table:

$table = $bigQuery->dataset('analytics')->table('events');
$table->create([
    'schema' => [
        'fields' => [
            ['name' => 'event_id', 'type' => 'STRING'],
            ['name' => 'timestamp', 'type' => 'TIMESTAMP'],
        ],
    ],
]);

Partitioned/Clustered Tables:

$table->create([
    'timePartitioning' => ['type' => 'DAY'],
    'clustering' => ['fields' => ['user_id']],
]);

4. Integration with Laravel

Service Container Binding:

// In AppServiceProvider@boot()
$this->app->singleton(BigQueryClient::class, function ($app) {
    return new BigQueryClient([
        'projectId' => env('GOOGLE_CLOUD_PROJECT'),
    ]);
});

Artisan Commands:

use Illuminate\Console\Command;
use Google\Cloud\BigQuery\BigQueryClient;

class SyncUsersCommand extends Command
{
    protected $bigQuery;

    public function __construct(BigQueryClient $bigQuery)
    {
        parent::__construct();
        $this->bigQuery = $bigQuery;
    }

    public function handle()
    {
        $query = $this->bigQuery->query('SELECT * FROM `users`');
        // Process results...
    }
}

Eloquent Models + BigQuery: Use a repository pattern to abstract queries:

class BigQueryUserRepository
{
    protected $bigQuery;

    public function __construct(BigQueryClient $bigQuery)
    {
        $this->bigQuery = $bigQuery;
    }

    public function findActiveUsers()
    {
        return $this->bigQuery->query('SELECT * FROM `users` WHERE active = true');
    }
}

5. Stateless Queries (Serverless)

For high-throughput, ephemeral queries (e.g., in serverless environments):

$queryConfig = $bigQuery->query(
    'SELECT * FROM `project.dataset.table`',
    ['useLegacySql' => false, 'priority' => 'BATCH']
);
$queryConfig->setQueryOptions(['priority' => 'BATCH']);
$results = $bigQuery->runQuery($queryConfig);

Advanced Patterns

1. Batch Operations

Parallel Job Execution:

$jobs = [];
foreach ($files as $file) {
    $jobConfig = $table->load($file);
    $jobs[] = $table->runJob($jobConfig);
}
// Wait for all jobs
array_walk($jobs, function ($job) { $job->wait(); });

2. Error Handling

Retry Transient Errors:

try {
    $results = $bigQuery->query('SELECT * FROM `table`');
} catch (\Google\ApiCore\ApiException $e) {
    if ($e->getStatusCode() === 429) { // Too Many Requests
        sleep(2);
        retry();
    }
    throw $e;
}

Custom Logging:

$bigQuery = new BigQueryClient([
    'logger' => new \Monolog\Logger('bigquery', [
        new \Monolog\Handler\StreamHandler(storage_path('logs/bigquery.log')),
    ]),
]);

3. Caching Query Results

Use Laravel’s cache to store frequent queries:

$cacheKey = 'bigquery:active_users';
$users = cache()->remember($cacheKey, now()->addHours(1), function () {
    return $bigQuery->query('SELECT * FROM `users` WHERE active = true');
});

4. Dynamic SQL Generation

Build queries from Laravel collections:

$columns = ['name', 'email'];
$where = ['active' => true];
$sql = "SELECT " . implode(', ', $columns) .
        " FROM `users` WHERE " .
        implode(' AND ', array_map(fn($k, $v) => "$k = '$v'", array_keys($where), $where));

5. Webhooks for Job Completion

Use Cloud Functions or Pub/Sub to trigger Laravel jobs when BigQuery jobs complete:

// Example: Listen for job completion via Pub/Sub
$job->addListener(function ($job) {
    if ($job->isComplete()) {
        event(new BigQueryJobCompleted($job));
    }
});

Gotchas and Tips

Pitfalls

Authentication Issues
- Symptom: Google\Auth\Exception\GoogleAuthException: Could not load credentials.
- Fix:
  - Ensure GOOGLE_APPLICATION_CREDENTIALS points to a valid service account JSON key.
  - Verify the service account has BigQuery Admin or Data Viewer roles.
  - For Compute Engine, use the default credentials:
```
$bigQuery = new BigQueryClient(['projectId' => env('GOOGLE_CLOUD_PROJECT')]);
```
Query Timeouts
- Symptom: Queries hang or return partial results.
- Fix:
  - Use stateless queries for long-running jobs:
```
$queryConfig = $bigQuery->query('SELECT * FROM `
```

Cloud Bigquery Laravel Package