How to load data from BambooHR to BigQuery

To begin, you need to access the BambooHR API. Log into your BambooHR account and navigate to the API section under the 'Account' tab. Note down your API key, which will be required for authenticating requests to BambooHR. Ensure you have the necessary permissions to access the data you need.

Clearly define the data you need to move from BambooHR to BigQuery. Determine which endpoints of the BambooHR API contain the necessary data. For example, if you need employee data, you might use the `/employees` endpoint. Review BambooHR API documentation to understand the structure and parameters of the data.

Write a script using a programming language like Python to make HTTP GET requests to the BambooHR API. Use the API key for authentication. For example, use the `requests` library in Python to fetch data:

```python
import requests

url = "https://api.bamboohr.com/api/gateway.php/YOUR_SUBDOMAIN/v1/employees"
headers = {
"Accept": "application/json",
"Authorization": "Basic YOUR_API_KEY"
}

response = requests.get(url, headers=headers)
data = response.json()
```

Replace `YOUR_SUBDOMAIN` and `YOUR_API_KEY` with your actual BambooHR subdomain and API key. Store the extracted data in a structured format like JSON or CSV.

Once you have the data, clean and format it according to BigQuery's requirements. Ensure the data types match BigQuery's supported types and handle any necessary transformations. Convert the JSON or CSV data into a schema that BigQuery can understand, such as converting dates into the `DATE` format.

Before importing data into BigQuery, you need to upload it to Google Cloud Storage. Create a new bucket in GCS via the Google Cloud Console. Use a tool like `gsutil` or the web interface to upload your formatted data file to the bucket.

Use the BigQuery web UI, the `bq` command-line tool, or BigQuery API to load data from GCS into a BigQuery table. Define the schema and specify the data format (e.g., JSON or CSV) during the import process. For example, using the `bq` command-line tool:

```bash
bq load --source_format=CSV --autodetect mydataset.mytable gs://mybucket/mydatafile.csv
```

Replace `mydataset.mytable` with your dataset and table name, and `gs://mybucket/mydatafile.csv` with your GCS file path.

After loading the data, verify its integrity by running queries in BigQuery to ensure all records have been imported correctly. Check for any discrepancies or missing data. This step is crucial to ensure the data migration was successful and reliable.

By following these steps, you can manually move data from BambooHR to BigQuery without relying on third-party connectors or integrations, ensuring full control over the data transfer process.