# Load job postings data into BigQuery

> Load job posts into a BigQuery table with an explicit schema, ready for SQL analysis and Looker Studio dashboards.

Page: https://jobdataapi.com/integrations/bigquery/
Category: Databases and warehouses
You need: Python 3.9+, pip install requests google-cloud-bigquery, application default credentials with BigQuery access and a jobdata API key (https://jobdataapi.com/accounts/pricing/).

## Steps

1. Create a dataset (e.g. jobs) in your Google Cloud project.
2. Set your API key and table ID below; the load job creates the table if needed.
3. Each run appends the latest job posts; deduplicate by id in a view (see notes).
4. Schedule it with Cloud Scheduler + Cloud Run, Airflow or any cron.

## Code

```python
import requests

API_KEY = "YOUR_API_KEY"


def fetch_jobs(**params):
    """Yield every job for the given filters, following pagination."""
    url = "https://jobdataapi.com/api/jobs/"
    params = {"page_size": 1000, **params}
    headers = {"Authorization": f"Api-Key {API_KEY}"}
    while url:
        resp = requests.get(url, headers=headers, params=params, timeout=60)
        resp.raise_for_status()
        data = resp.json()
        yield from data["results"]
        url, params = data["next"], None  # "next" already carries the filters


from google.cloud import bigquery

client = bigquery.Client()
table_id = "your-project.jobs.job_posts"

schema = [
    bigquery.SchemaField("id", "INT64", mode="REQUIRED"),
    bigquery.SchemaField("title", "STRING"),
    bigquery.SchemaField("company", "STRING"),
    bigquery.SchemaField("location", "STRING"),
    bigquery.SchemaField("published", "TIMESTAMP"),
    bigquery.SchemaField("has_remote", "BOOL"),
    bigquery.SchemaField("salary_min", "NUMERIC"),
    bigquery.SchemaField("salary_max", "NUMERIC"),
    bigquery.SchemaField("salary_currency", "STRING"),
    bigquery.SchemaField("application_url", "STRING"),
]

rows = [{
    "id": job["id"],
    "title": job["title"],
    "company": job["company"]["name"],
    "location": job["location"],
    "published": job["published"],
    "has_remote": job["has_remote"],
    "salary_min": job["salary_min"],
    "salary_max": job["salary_max"],
    "salary_currency": job["salary_currency"],
    "application_url": job["application_url"],
} for job in fetch_jobs(max_age=1)]

load = client.load_table_from_json(rows, table_id, job_config=bigquery.LoadJobConfig(
    schema=schema, write_disposition="WRITE_APPEND"))
load.result()
print(f"Loaded {len(rows)} job posts into {table_id}")
```

## Notes

- Run it daily with max_age=1 to pick up new job posts; on access lite, drop max_age - the feed covers the past 90 days and upserting by id keeps it free of duplicates.
- Keep the latest version of each job with SELECT * FROM `your-project.jobs.job_posts` QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY published DESC) = 1.
- For full-history backfills, the weekly Parquet downloads (access ultra) load into BigQuery directly with bq load --source_format=PARQUET.

## Related docs

- [Jobs API Ingestion Guide: Reliable Historical Backfills and Incremental Syncs](https://jobdataapi.com/c/jobs-api-ingestion-guide-reliable-historical-backfills-and-incremental-syncs/)
- [Date, ID, and Age Slicing Parameters Documentation](https://jobdataapi.com/c/date-id-and-age-slicing-parameters-documentation/)
- [Jobs Expired API Endpoint Documentation](https://jobdataapi.com/c/jobs-expired-api-endpoint-documentation/)
- [CSV and Parquet File Downloads Documentation](https://jobdataapi.com/c/csv-and-parquet-file-downloads-documentation/)