How to load data from Freshdesk to BigQuery

Begin by familiarizing yourself with the Freshdesk API. Freshdesk provides a RESTful API that allows you to access data like tickets, contacts, and more. Review the API documentation to understand the endpoints, authentication methods, and rate limits.

Freshdesk API uses basic authentication. You will need your Freshdesk domain, API key, and password (usually an 'X'). Prepare these credentials for making authenticated HTTP requests to access your Freshdesk data.

Use a tool like Python's requests library to make HTTP GET requests to the Freshdesk API. You can script this process to extract data like tickets or contacts by specifying the appropriate endpoint and handling pagination for large data sets.

```python
import requests
import json

api_key = 'your_api_key'
password = 'X'
domain = 'your_domain'

response = requests.get(f'https://{domain}.freshdesk.com/api/v2/tickets',
auth=(api_key, password))
tickets = response.json()
```

Once the data is extracted, you may need to transform it into a format suitable for BigQuery. This could involve cleaning the data, flattening nested JSON structures, or converting data types. Use pandas in Python to manipulate and prepare your data.

```python
import pandas as pd

df = pd.json_normalize(tickets)
```

Convert the transformed data into a CSV file, which is a format that BigQuery can easily ingest. Save the DataFrame as a CSV file locally or in a cloud storage service if needed.

```python
df.to_csv('freshdesk_data.csv', index=False)
```

Use the Google Cloud SDK or a Python library like `google-cloud-storage` to upload your CSV file to a Google Cloud Storage bucket. Ensure that your Google Cloud account has the necessary permissions to perform this operation.

```python
from google.cloud import storage

storage_client = storage.Client()
bucket_name = 'your_bucket_name'
bucket = storage_client.bucket(bucket_name)
blob = bucket.blob('freshdesk_data.csv')

blob.upload_from_filename('freshdesk_data.csv')
```

Finally, load the data from Google Cloud Storage into BigQuery. Use the BigQuery client library in Python to create a load job that specifies your dataset and table. Configure the load job to handle CSV files and specify the necessary schema.

```python
from google.cloud import bigquery

client = bigquery.Client()
dataset_id = 'your_dataset_id'
table_id = 'your_table_id'
uri = f'gs://{bucket_name}/freshdesk_data.csv'

job_config = bigquery.LoadJobConfig(
source_format=bigquery.SourceFormat.CSV,
skip_leading_rows=1,
autodetect=True,
)

load_job = client.load_table_from_uri(
uri, f'{dataset_id}.{table_id}', job_config=job_config)

load_job.result() # Wait for the job to complete.

print(f'Loaded {load_job.output_rows} rows into {dataset_id}:{table_id}.')
```

By following these steps, you can effectively transfer data from Freshdesk to BigQuery without relying on third-party connectors.