How to load data from xkcd to BigQuery

Begin by exploring xkcd's data structure. xkcd comics are accessible via a JSON API at `https://xkcd.com/info.0.json` for the latest comic or `https://xkcd.com/[comic_number]/info.0.json` for specific comics. Understand the JSON response structure, which typically includes fields like "month," "num," "link," "year," "news," "safe_title," "transcript," "alt," "img," and "title."

Log into your Google Cloud Platform account and create a new project (if you don't have one already). Enable the BigQuery API for this project by navigating to the APIs & Services dashboard, clicking on "Enable APIs and Services," and searching for "BigQuery API."

Ensure you have Python installed on your local machine along with the Google Cloud SDK, which provides the `gcloud` command-line tool. Additionally, install the `google-cloud-bigquery` library using pip:

```bash
pip install google-cloud-bigquery
```

Write a Python script that fetches data from the xkcd API. You can use the `requests` library to make HTTP requests. Loop through a range of comic numbers to fetch multiple comics, or start from the latest and work backward:

```python
import requests

def fetch_xkcd_data(comic_number):
url = f"https://xkcd.com/{comic_number}/info.0.json"
response = requests.get(url)
if response.status_code == 200:
return response.json()
else:
return None

# Example to fetch data for comic number 1
comic_data = fetch_xkcd_data(1)
```

Convert the fetched JSON data into a format suitable for BigQuery. Flatten the JSON structure if necessary and create a list of dictionaries where each dictionary corresponds to one row of data:

```python
def transform_data(comic_data):
# Example transformation
return {
"comic_number": comic_data.get("num"),
"title": comic_data.get("title"),
"img_url": comic_data.get("img"),
"alt_text": comic_data.get("alt"),
"year": int(comic_data.get("year")),
"month": int(comic_data.get("month"))
}

transformed_data = transform_data(comic_data)
```

Use the BigQuery Python client library to load data into BigQuery. First, create a dataset and a table if they don’t exist. Then, insert the transformed data into the table:

```python
from google.cloud import bigquery

client = bigquery.Client()

dataset_id = 'your_dataset'
table_id = 'your_table'

dataset_ref = client.dataset(dataset_id)
table_ref = dataset_ref.table(table_id)

# Create dataset and table if not exists
client.create_dataset(dataset_ref, exists_ok=True)

schema = [
bigquery.SchemaField("comic_number", "INTEGER"),
bigquery.SchemaField("title", "STRING"),
bigquery.SchemaField("img_url", "STRING"),
bigquery.SchemaField("alt_text", "STRING"),
bigquery.SchemaField("year", "INTEGER"),
bigquery.SchemaField("month", "INTEGER")
]

table = bigquery.Table(table_ref, schema=schema)
client.create_table(table, exists_ok=True)

# Insert data
errors = client.insert_rows_json(table, [transformed_data])
if errors:
print(f"Encountered errors while inserting rows: {errors}")
```

Finally, verify that the data was successfully loaded into BigQuery. Use the BigQuery console or the `bq` command-line tool to query your table and ensure the data appears as expected:

```bash
bq query --nouse_legacy_sql 'SELECT * FROM `your_project.your_dataset.your_table` LIMIT 10'
```

By following these steps, you should be able to extract data from xkcd, transform it, and load it into BigQuery without using any third-party connectors or integrations.