When running SQL queries through Hue for Impala or Hive, encountering errors is a common frustration. Each time a query fails, I would need to manually hit the 'run' button or press `Ctrl + Enter` repeatedly, hoping the query would eventually succeed.
This process ties me to the screen, forcing constant supervision. It's not only time-consuming but also mentally exhausting, as any moment away from the monitor could result in missed retries.
It would be far more efficient to automate this process, ensuring that queries handle errors gracefully and retry automatically—giving me peace of mind that the execution will succeed without my direct oversight.
Get Brian Ivan Cusuanto’s stories in your inbox
Join Medium for free to get updates from this writer.
In real-world environments, database connections can be affected by network instability, timeouts, or temporary server issues. With a retry mechanism, we can ensure the system remains resilient, reducing the chance of manual intervention during these issues.
So, here’s the python script that could make it happens:
def execute_query(cursor, query, retry_limit=500):
retry_count = 0
while retry_count < retry_limit:
try:
cursor.execute(query)
now = datetime.datetime.now(jakarta_tz).strftime("%Y-%m-%d %H:%M:%S")
print(f'[{now}] Successfully executed the query after {retry_count} retries.')
break
except OperationalError as e:
now = datetime.datetime.now(jakarta_tz).strftime("%Y-%m-%d %H:%M:%S")
retry_count += 1
print(f'[{now}] Encountered an OperationalError: {e}.\nRetry #{retry_count}. Retrying...')
if retry_count == retry_limit:
print(f'Failed to execute the query after {retry_limit} retries.')
os.exit_(1)
In real-world environments, database connections can be affected by network instability, timeouts, or temporary server issues. With a retry mechanism, we can ensure the system remains resilient, reducing the chance of manual intervention during these issues.
Feel free to submit pull requests to improve the script. Contributions are always welcome!
Visit my repo: GitHub — brdx88/execute-sql-with-auto-retries
