Why does BigQuery API call take so long?

Viewed 446

I'm trying to query BigQuery use the BigQuery API with the Python client library.

However, for some reason, my query seems to "hang" for about 150 seconds when calling the BigQuery API, i.e., at the following line (see below for full code sample): results = client.query(query)

Note: it doesn't matter what the actual query is. Therefore, in my sample code below, I'm just putting SELECT 1 as a query.

Interestingly, there is only a delay for the first query - all subsequent queries are as fast as expected.

I've checked the query time in the Query History for BQ, and it confirms that all of the queries take less than a second. So it's definitely not the actual query that's taking so long, but something else.

I'm guessing that this may somehow related to the authentication, but I'm not sure why that would be or if I'm doing anything wrong - or how it can be sped up, most importantly.

Any hints are greatly appreciated.

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# IMPORTS
from google.cloud import bigquery
import os 
from datetime import datetime

print(f"{datetime.now().strftime('%H:%M:%S')}: Starting script...")

# INIT BQ
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "my_credentials.json"
client = bigquery.Client()

# QUERY 1
print(f"{datetime.now().strftime('%H:%M:%S')}: Starting query 1...")

query1 = """SELECT 1 AS mycol LIMIT 1"""

print(f"{datetime.now().strftime('%H:%M:%S')}: Making API request...")
results1 = client.query(query1) # <-- this is where the 150s delay happens

for row in results1:
    print(f"{datetime.now().strftime('%H:%M:%S')}: Results: {row['mycol']}")

# QUERY 2
print(f"{datetime.now().strftime('%H:%M:%S')}: Starting query 2...")

query2 = """SELECT 2 AS mycol LIMIT 1"""

print(f"{datetime.now().strftime('%H:%M:%S')}: Making API request...")
results2 = client.query(query2) # <-- this is super-quick

for row in results2:
    print(f"{datetime.now().strftime('%H:%M:%S')}: Results: {row['mycol']}")

#EOF

This is the output (running this on my local machine):

22:33:48: Starting script...
22:33:48: Starting query 1...
22:33:48: Making API request... # <-- this is taking ~150s
22:36:24: Results: 1
22:36:24: Starting query 2...
22:36:24: Making API request...
22:36:26: Results: 2
1 Answers

Ok, so after two days of trying to find a solution, the exact same script, which took 160 seconds yesterday, is now running in about 4 seconds. It would seem that there was something wrong on Google's side of things.

Related