BigQuery: Print offending SQL on error with QueryJob.result()

[bencaine1]

OS: Linux dc32b7e8763a 4.9.0-6-amd64 #1 SMP Debian 4.9.82-1+deb9u3 (2018-03-02) x86_64 x86_64 x86_64 GNU/Linux
Python version: Python 2.7.6
google-cloud-bigquery: 1.1.0

It would be really helpful if the error message to QueryJob.result() printed out the query, along with line and column numbers. Here's our current workaround, which unfortunately does not allow us to use the built-in Retry due to a nested try/except.

RETRYABLE_EXCEPTIONS = (InternalServerError, TooManyRequests, ServiceUnavailable, BadGateway)

...

    def _execute_query_job(self, query_job, query, max_wait_secs=None):
        # Need to do retries manually, since the built-in retry doesn't work within a try/except.
        start_time = datetime.datetime.utcnow()
        deadline = start_time + datetime.timedelta(seconds=max_wait_secs or self.max_wait_secs)
        i = 1
        while datetime.datetime.utcnow() < deadline:
            try:
                return query_job.result()
            except RETRYABLE_EXCEPTIONS:
                time.sleep(i**2)
                i += 1
            except GoogleCloudError as e:
                msg = str(e)
                # Put line numbers next to the SQL.
                lines = query.split('\n')
                longest = max(len(l) for l in lines)
                # Print out a 'ruler' above and below the SQL so we can judge columns.
                ruler = ' ' * 4 + '|'  # Left pad for the line numbers (4 digits plus ':')
                for _ in range(longest / 10):
                    ruler += ' ' * 4 + '.' + ' ' * 4 + '|'
                header = '-----Offending Sql Follows-----'
                padding = ' ' * ((longest - len(header)) / 2)
                msg += '\n\n{}{}\n\n{}\n{}\n{}'.format(padding, header, ruler, '\n'.join(
                    '{:4}:{}'.format(n + 1, line) for n, line in enumerate(lines)), ruler)
                raise RuntimeError(msg)
        raise RuntimeError('Deadline of {} seconds exceeded.'.format(max_wait_secs))

[tswast]

Import information for query errors includes the job ID, any error messages, and as suggested: the SQL text.

[plamut]

Makes sense, will add job ID as well.