I try to update 2K rows in BQ
def update_bq_ads_status_failed(self, update_ads):
affected_rows = 0
for update_ads_chunk in split(update_ads, _UPDATE_CHUNK_SIZE):
ad_ids = [item["ad_id"] for item in update_ads_chunk]
removal_errors = [item["removal_error"] for item in update_ads_chunk]
update_removal_error = ""
for ad_id, removal_error in zip(ad_ids, removal_errors):
update_removal_error = update_removal_error + \
f''' WHEN ad_id = '{ad_id}' Then '{removal_error}' '''
affected_rows += self.update_bq_ads_status(f"""
UPDATE '{table_full_name}'
SET status = 'Failed Removing'
SET removal_error = CASE {update_removal_error} END
WHERE ad_id IN {str(ad_ids)}
""")
return affected_rows
I'm getting this error. I know it's too vague and not possible to debug like this.
timeout=300.0, headers={'X-Server-Timeout': '300.0', 'Accept-Encoding': 'gzip', 'Content-Type': 'application/json', 'X-Goog-API-Client': 'gl-python/3.8.10 grpc/1.39.0 gax/2.0.0 gapic/2.26.0 gccl/2.26.0', 'User-Agent': 'gl-python/3.8.10 grpc/1.39.0 gax/2.0.0 gapic/2.26.0 gccl/2.26.0'})), last exception: ('Connection aborted.', RemoteDisconnected('Remote end closed connection without response'))
I'm trying to eliminate errors. Is my BQ update syntactically correct?
What's the BQ update timeout?
Several issues with last UPDATE
statement:
SET
should be used once with comma separation for multiple columnsWHERE
condition which requires parenthesesConsider adjusted code:
update_removal_error = " ".join(
f"WHEN ad_id = '{ad_id}' THEN '{removal_error}'"
for ad_id, removal_error in zip(ad_ids, removal_errors)
)
affected_rows += self.update_bq_ads_status(f"""
UPDATE {table_full_name}
SET status = 'Failed Removing'
, removal_error = CASE {update_removal_error} END
WHERE ad_id IN {tuple(ad_ids)}
""")