Skip to content

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

@bencaine1

Description

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

Metadata

Metadata

Assignees

Labels

api: bigqueryIssues related to the BigQuery API.type: feature request‘Nice-to-have’ improvement, new feature or different behavior or design.

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions