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()