How to Load Data From API to BigQuery?

Many businesses use cloud-based applications like Salesforce, HubSpot, Mailchimp, and Zendesk for daily operations. We need to combine data from these sources to measure key metrics and drive growth.

These applications, run by third-party vendors, provide APIs for data extraction into data warehouses like Google BigQuery. In this blog, we’ll walk you through the process of moving data from an API to BigQuery, discuss potential challenges, and share workarounds. Let’s dive in!

Note: When you perform this integration, consider factors like data format, update frequency, and API rate limits to design a stable integration.

Method 1: Using Hevo (No-Code, Beginner-Friendly)

Hevo is a no-code data pipeline tool that helps you move data from any REST API to BigQuery without writing a single line of code. It’s perfect if you want something fast, simple, and automated.

Step 1: Configure REST API as a Source

To set up your API as a source in Hevo:

• Log in to Hevo and go to Pipelines.
  
• On the Pipelines List View, click CREATE.
  
• On the Select Source Type page, choose REST API.
  
• On the Configure Your REST API Source page, provide the following:
  
  • Pipeline Name: A unique name for your pipeline (max 255 characters).
    
  • API Endpoint URL: The endpoint from which you want to pull data.
    
  • Request Method: Typically GET, unless your API requires something else.
    
  • Authentication Type: Choose from options like API Key, OAuth, or No Auth.
    
  • Request Headers/Params: Add required headers (e.g., authorization keys).
    
  • Polling Frequency: Set how often you want Hevo to pull data.
    
• Click TEST & CONTINUE to validate the setup.

Step 2: Configure Google BigQuery as a Destination

• In the same pipeline flow, choose BigQuery as the destination.
  
• Enter the following:
  
  • Destination Name: A name for your BigQuery destination.
    
  • Project ID: Your Google Cloud project where BigQuery is hosted.
    
  • Dataset Name: The BigQuery dataset where you want to send the data.
    
  • Authentication: Upload a service account key (JSON file) for access.
    
• Click TEST CONNECTION to check credentials.
  
• Click SAVE & CONTINUE.

Step 3: Final Settings (Optional)

• Use the Schema Mapper to view and customize how fields map from source to destination.
  
• Add Transformations to clean, filter, or enrich data before loading it into BigQuery.
  
• You can also set up alerts, c, and data freshness checks for monitoring.

That’s all, Hevo takes care of the rest. It automatically fetches, formats, and loads your data into BigQuery reliably and securely.

Want more details on setting up a REST API source? Check out Hevo’s official documentation.

Method 2: Using Custom Code

If you want complete control over how data is fetched, cleaned, and stored, writing custom Python code is the way to go. It requires coding skills but gives you flexibility with scheduling, transformations, and handling non-standard APIs.

Step 1: Extract Data from the API

We’ll use the ExchangeRate API to get real-time currency data.

import requests
# Replace with your actual API keyurl = "https://v6.exchangerate-api.com/v6/YOUR-API-KEY/latest/USD"response = requests.get(url)
# Check if the request was successfulif response.status_code == 200:    data = response.json()else:    raise Exception(f"API request failed with status {response.status_code}: {response.text}")

This sends a GET request to the ExchangeRate API and parses the response only if the request was successful.

Step 2: Parse and Prepare the Data

We’ll flatten the JSON into individual records for BigQuery.

from datetime import datetimeimport json
records = []
# Extracting key fieldsbase_currency = data["base_code"]timestamp = data["time_last_update_utc"]
# Convert to ISO 8601 formattimestamp = datetime.strptime(timestamp, "%a, %d %b %Y %H:%M:%S %z").isoformat()
# Convert each exchange rate into a rowfor target_currency, rate in data["conversion_rates"].items():    records.append({        "base_currency": base_currency,        "target_currency": target_currency,        "rate": rate,        "updated_at": timestamp    })

What it does: Turns nested exchange rate data into flat records. Converts the timestamp into ISO 8601 format so BigQuery can recognize it.

Step 3: Save Data as NDJSON

BigQuery requires a newline-delimited JSON format for loading structured data.

with open("currency_data.json", "w") as f:    for record in records:        f.write(json.dumps(record) + "\n")

Step 4: Load Data into BigQuery

Install the BigQuery client if you haven’t:

pip install google-cloud-bigquery

Then, run the following Python script:

from google.cloud import bigquery
# Make sure your Google Cloud credentials are set# Replace with the path to your downloaded service account keyimport osos.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your/credentials.json"
# Initialize BigQuery clientclient = bigquery.Client()
# Define your tabletable_id = "your-project.your_dataset.currency_rates"  # Make sure the dataset exists
# Configure the load jobjob_config = bigquery.LoadJobConfig(    source_format=bigquery.SourceFormat.NEWLINE_DELIMITED_JSON,    autodetect=True,    write_disposition=bigquery.WriteDisposition.WRITE_APPEND  # Use WRITE_TRUNCATE to overwrite)
# Load data into BigQuerywith open("currency_data.json", "rb") as source_file:    load_job = client.load_table_from_file(source_file, table_id, job_config=job_config)
load_job.result()  # Waits for the job to completeprint(f"Loaded {load_job.output_rows} rows into {table_id}")

Important Notes:

• The BigQuery dataset must exist beforehand. BigQuery won’t create it automatically.
  
• Choose the correct write_disposition:
  
  • WRITE_APPEND = add to table
    
  • WRITE_TRUNCATE = replace all rows
    
  • WRITE_EMPTY = only write if table is empty

Optional Enhancements

Error Handling: Wrap network calls and BigQuery loading in try/except blocks.

Data Validation: Add checks to make sure the API response is complete before loading.

Scheduling: Use a tool like cron, Airflow, or Cloud Functions to automate this job.

Overview of BigQuery

Google BigQuery is a cloud data warehouse service provider and a part of the Google Cloud Platform. It helps companies store and analyze their business data at a secure data warehouse. Google allows users to leverage other Google Cloud Platform features, such as engines, APIs, etc, on their data directly from the Google BigQuery data warehouse.

Google BigQuery can manage terabytes of data using SQL language. Also, it enables companies to analyze their data stored in Data Warehouse using SQL queries. Google BigQuery has a columnar storage structure that helps deliver faster query processing and file compression. Moreover, BigQuery ML allows users to train and run machine learning models in BigQuery using only SQL syntax.

Why Connect APIs to BigQuery?

Integrating APIs with BigQuery offers several advantages:

• Centralized Analytics: Consolidate data from multiple APIs for unified reporting.
• Real-Time Insights: Analyze streaming data for timely decision-making.
• Scalability: Handle large datasets without worrying about infrastructure.
• Automation: Minimize manual effort and ensure consistent data pipelines.

Use Cases of API and BigQuery Integration

• Social Media Analytics: Fetch data from Twitter or Instagram to analyze engagement trends.
• IoT Data Aggregation: Collect sensor data to monitor equipment performance in real-time.
• E-Commerce Tracking: Combine data from APIs like Shopify or Stripe for sales and inventory analysis.

• Advanced Analytics: BigQuery has powerful data processing capabilities that enable you to perform complex queries and data analysis on your API data. This way, you can extract insights that would not be possible within API alone.
• Data Consolidation: If you’re using multiple sources along with API, syncing them to BigQuery can help you centralize your data. This provides a holistic view of your operations, and you can set up a change data capture process to avoid discrepancies in your data.
• Historical Data Analysis: API has limits on historical data. However, syncing your data to BigQuery allows you to retain and analyze historical trends.
• Machine Learning: You can apply machine learning models to your data for predictive analytics, customer segmentation, and more by having API data in BigQuery

• To learn more about configuring REST API as your source, refer to Hevo Documentation.

Limitations of writing custom scripts and developing ETL 

1. The above code is written based on the current source and target destination schema. If either the incoming data or the schema on BigQuery changes, the ETL process will break.
2. If you need to clean your data from API, transform time zones, hide personally identifiable information, and so on, the current method does not support it. You will need to build another set of processes to accommodate that. This would also require you to invest extra effort and money.
3. You are at a serious risk of data loss if, at any point, your system breaks. This could be anything from the source/destination that is not reachable to script breaks and more. You must invest upfront in building systems and processes that capture all the fail points and consistently move your data to the destination.
4. Since Python is an interpreted language, it might cause performance issues when extracting from API and loading data into BigQuery API. 
5. For many APIs, we need to supply credentials to access API. Passing credentials as plain text in Python script is a very poor practice. You will need to take additional steps to ensure your pipeline is secure. 

Challenges in API-to-BigQuery Integration

While connecting APIs to BigQuery unlocks significant value, it comes with its own set of challenges:

• API Rate Limits: Excessive requests may lead to throttling or blocked access.
• Authentication Complexities: Handling OAuth tokens or API keys securely.
• Data Transformation: Converting API data formats to match BigQuery’s schema.
• Error Handling: Managing failed API calls or incomplete data uploads.
  

Best Practices for API Data Ingestion

1. Batch Requests: Avoid rate-limit issues by batching API calls.
2. Schema Validation: Ensure API data matches your BigQuery table schema to avoid errors.
3. Monitor Pipelines: Set up alerts for failed or delayed data loads.
4. Use BigQuery Streaming API: For real-time data, use the streaming API to ensure low-latency ingestion.
5. Partition Tables: Organize data by date or other criteria to reduce query costs.
6. Avoid Redundant API Calls: Cache responses to minimize unnecessary requests.

Additional Resources

Read more on how to Load Data into Bigquery

Conclusion

From this blog, you will understand the process you need to follow to load data from API to BigQuery. This blog also highlights various methods and their shortcomings. Connecting APIs to BigQuery can unlock immense value for your business by enabling real-time analytics and centralized data management.

While manual integration offers flexibility, automated solutions like Hevo simplify the process and save time. By following best practices and optimizing your pipelines, you can ensure a seamless and cost-effective data integration experience. Ready to simplify your API-to-BigQuery integration? Try Hevo for Free.

FAQs