# BigQuery

Loads data from a BigQuery table using timestamp-based tracking and scheduled ingestion.

# BigQuery Input

The BigQuery Input allows you to read data from BigQuery tables or custom queries incrementally.

## Requirements

### Setting up a Service Account

1. Create a new service account:
```bash
# Set your project ID
export PROJECT_ID="your-project-id"

# Create the service account
gcloud iam service-accounts create bigquery-reader \
    --description="Service account for BigQuery data reading" \
    --display-name="BigQuery Reader"
```

2. Grant the required permissions:
```bash
# Grant BigQuery Data Viewer role (for reading data)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/bigquery.dataViewer"

# Grant BigQuery Job User role (for executing queries)
gcloud projects add-iam-policy-binding $PROJECT_ID \
    --member="serviceAccount:bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com" \
    --role="roles/bigquery.jobUser"
```

3. Create and save the service account key (credentials):
```bash
# Create and save the key file
gcloud iam service-accounts keys create bigquery-reader-credentials.json \
    --iam-account=bigquery-reader@$PROJECT_ID.iam.gserviceaccount.com
```

This creates a `bigquery-reader-credentials.json` file in your current directory. Use the contents of this file as the value for the `credentials_json` secret in your input configuration.

Note: Keep this credentials file secure and never commit it to version control.

## Configuration

The BigQuery Input is configured using the following settings:

### Settings

| Setting | Type | Required | Description |
|---------|------|----------|-------------|
| project | string | Yes | The Google Cloud Project ID where your BigQuery dataset is located |
| dataset | string | Yes | The name of the BigQuery dataset containing your table |
| table | string | No | The name of the table to read from (required if query is not provided) |
| timestamp_column | string | Yes | Column used for incremental loading |
| query | string | No | Optional custom query to use instead of table (required if table is not provided) |

### Secrets

| Setting | Type | Required | Description |
|---------|------|----------|-------------|
| credentials_json | text | Yes | The Google Cloud service account credentials in JSON format |

## Features

### Sync Mode
- **Incremental Sync**: Uses the timestamp_column provided to read new or updated records
- Automatically manages state between runs for incremental syncs

### Query Support
- Simple table queries with automatic schema detection
- Custom SQL queries for complex data transformations
- Wraps custom queries in CTEs for incremental loading

### Efficient Data Reading
- Uses BigQuery Query API for reliable data access
- Automatic connection and retry handling
- Supports both simple and complex queries

## Best Practices

1. **Query Optimization**
   - Keep queries simple for better performance
   - Test custom queries with small data sets first

## Example Configurations
### Incremental Sync with Table
```json
{
  "settings": {
    "project": "my-project-123",
    "dataset": "my_dataset",
    "table": "my_table",
    "timestamp_column": "updated_at"
  },
  "secrets": {
    "credentials_json": {
      "value": "{ your service account JSON here }"
    }
  }
}
```

## Troubleshooting

Common issues and solutions:

1. **Permission Denied**
   - Verify service account has both bigquery.dataViewer and bigquery.jobUser roles
   - Check project ID matches credentials
   - Ensure dataset and table exist

2. **Invalid Query**
   - Test custom queries in BigQuery console first
   - Verify column names and types
   - Check for proper SQL syntax

3. **Timestamp Column Issues**
   - Verify column exists in table/query results
   - Ensure column is TIMESTAMP type
   - Check column name matches exactly

4. **Performance Issues**
   - Review query efficiency
   - Check BigQuery usage quotas