Slow data loading from API response to GCS and Bigquery

Viewed 696

We have requirement to load data from series of API call and load the response in GCS and Bigquery. While I am gettign quick response from API call but writing file to GCS seems to be very slow. I am using below code. My approach is API response (sequential based on list of id values in API parameter) -> json load to gcs -> bq load. It is taking long time in this approach.Is there any approach or code tunning can be done to load data in gcs in fastest way ?

import requests
import json
from datetime import date
from google.cloud import storage
from google.cloud import bigquery

today = date.today()

timestr = str(today.strftime('%Y')) + "-" + str(today.strftime('%m')) + "-" + str(today.strftime('%d'))
bucket_name='bucket_name'
project_id='project_name'
dataset_id='ds_name'
table_id='table_name'




def get_id():
    client = bigquery.Client()
    id_list = []
    query = """
        select id from table 
        """

    query_job = client.query(query)
    data = query_job.result()
    # rows = list(data)
    for row in data:
        # print(format(row.value))
        store_list.append(format(row.id))
    return id_list

def upload_to_bucket(blob_name, output, bucket_name):
    storage_client = storage.Client(project_id)
    bucket = storage_client.get_bucket(bucket_name)
    blob = bucket.blob(blob_name)
    blob.upload_from_string(data=output, content_type='application/json')
    return blob.public_url

def build_full_gcs_path(blob_name, bucket_name):
    return 'gs://' + bucket_name + '/' + blob_name

   
def get_api_response():
    rows = get_id()
    for id in rows:
        blob_name =  timestr + id+ '.json'
        try:
            response = requests.get("REST API url" + id )
            if "NOT_FOUND" in response.json():
                print('No data found')
            else:
                api_response = response.json()
                dt = {"currentDate": timestr}
                api_response.update(dt)
                ot=json.dumps(api_response)
                print(json.dumps(api_response))

                g = upload_to_bucket(blob_name, json.dumps(api_response), bucket_name)
                print(g)
                loc = build_full_gcs_path(blob_name, bucket_name)
                print(loc)
        except Exception as e:
            print(e)
get_api_response()
1 Answers

In accordance with the main issue you have in your question, I found the Bigquery storage read API overview documentation, which provides faster access to BigQuery-managed Storage than Bulk data export.

You can also save all the records to a local file and then upload the entire file to GCS. You can break the files into many parts and use this command to speed up the process: gsutil -m cp -j (which is a command-line tool for accessing Cloud Storage).

-m: is used to perform a multi-threaded/multi-processing copy simultaneously.

cp: is used to copy files.

-j: Any file upload is compressed using gzip transport encoding. This saves network bandwidth while leaving the data in Cloud Storage uncompressed.

Now, if you are using gsutil for a large, single-file transfer, for example, you will need to change the default settings to get the best performance.

gcloud storage takes large files and breaks them down into pieces, so that transfers can best take advantage of the available bandwidth.

You can also review the Faster Cloud Storage transfers using the gcloud command-line documentation

For more reference, you can also look into the Query Optimization techniques provided by BigQuery.

Related