Igor Vishnevskiy Igor Vishnevskiy - 2 months ago 16
Apache Configuration Question

Receiving intermittent error when querying BigQuery database from Django App hosted on Apache2 Server

Not sure if I'm allowed, but for the sake of those who wants to see my issue first hand, I am adding url of my development environment (www.blesque.tv). Issue is easily reproducible by reloading that page multiple times.

I switched the home page of my site to query data from my BigQuery database. When I am reloading the page, 2 times out of 3 approximately, I receive error as on the attached image below. Not sure what is happening. When content loads, it loads very quickly (the main reason I am switching to BQ), but then next time reloading the page, error is thrown. I tried to look through Google's API reference thinking maybe DB connection needs to be closed explicitly after each time I query it, but could not find anything about it. Need help from StackOverflow community. Thank you.

This is the Google's API I am using:
https://cloud.google.com/bigquery/docs/reference/v2/


Error message:

Error at /

[('rsa routines', 'RSA_setup_blinding', 'BN lib'), ('rsa routines', 'RSA_EAY_PRIVATE_ENCRYPT', 'internal error')]

Request Method: GET
Request URL: http://www.blesque.tv/
Django Version: 1.9.8
Exception Type: Error
Exception Value:

[('rsa routines', 'RSA_setup_blinding', 'BN lib'), ('rsa routines', 'RSA_EAY_PRIVATE_ENCRYPT', 'internal error')]

Exception Location: /home/businessai/lib/python2.7/OpenSSL/_util.py in exception_from_error_queue, line 48
Python Executable: /usr/local/bin/python
Python Version: 2.7.5

Code of my BigQuery controller

class BQController():

def __init__(self):

configFile = "pass_to_config_file_containing_p12_key_client_email_and_project_id"

#Authorizing connection
with open(configFile, "r") as conConf:
filedata = conConf.read()
jsonData = self.getJsonContent(filedata)
KEY_FILE = jsonData["signin_credentials"]["key_file"]
self.PROJECT_ID = jsonData["signin_credentials"]["project_id"]
CLIENT_EMAIL = jsonData["signin_credentials"]["client_email"]

scopes = ['https://www.googleapis.com/auth/bigquery']

credentials = ServiceAccountCredentials.from_p12_keyfile(
CLIENT_EMAIL, KEY_FILE, scopes=scopes)

http_auth = credentials.authorize(Http())
self.bigquery = build('bigquery', 'v2', http=http_auth)

#Method that I run to select data.
def runQuery(self, queryStr):
try:
query_request = self.bigquery.jobs()
query_data = {
'query': (
queryStr)
}

query_response = query_request.query(
projectId=self.PROJECT_ID,
body=query_data).execute()

queryset = query_response['rows']

except HttpError as err:
queryset = []
print('Error: {}'.format(err.content))
raise err

return queryset


Screenshot of the error:

enter image description here

My second implementation based on the suggestion from Thang. Still produces the same errors. It is interesting because when python script is executed on it's own on back end, it works fine all the time, but as soon as I plug it into the Django, I start seeing these issues.

from gcloud import bigquery

class GcloudController():
def __init__(self):
self.client = bigquery.Client.from_service_account_json('/keys/client_secrets.json','project_name')


def runSelectQuery(self, query, maxResults):
query_results = self.client.run_sync_query(query)

# Use standard SQL syntax for queries.
# See: https://cloud.google.com/bigquery/sql-reference/
query_results.use_legacy_sql = False

query_results.run()

# Drain the query results by requesting a page at a time.
page_token = None

while True:
rows, total_rows, page_token = query_results.fetch_data(
max_results=maxResults,
page_token=page_token)

return rows

if not page_token:
break

Answer

After trying many different methods to make a workaround for this issue, I ended up developing a hack that works for now I guess. But I really think there is a bug in Google API's Authentication. I mean if you want to test it out, visit www.blesque.tv. You can see that now it works good. Slower than if I was hitting Google's API directly, but works. I really didn't change anything in my BigQuery Controller's Authentication. I simply started executing it through a "subprocess" and then read the results of a query from "stdout". But that's not the fix, it is a hack. Google needs to fix the bug that is blocking me and I bet many others from being able to use Bigquery API the correct way. Code to my fix is below. Feel free to comment. I mean maybe someone still knows better way to fix this issue and I hope in the future Google will fix their API Authentication bugs. There is some conflict when authenticating from within the application running on Django platform when it tries to create a new token or acquire existing one. Bug basically that Google needs to fix.

My BigQuery Controller:

__author__ = 'igor_vishnevskiy'


#Gogle's API Refference page:
#https://cloud.google.com/bigquery/docs/reference/v2/jobs#configuration.query
#https://cloud.google.com/bigquery/docs/reference/v2/tables


from oauth2client.service_account import ServiceAccountCredentials
from httplib2 import Http
from apiclient.discovery import build
import os
from googleapiclient.errors import HttpError
import random
import json
import sys


class BQController():

    def __init__(self):
        self.authenticate_one()
        query = sys.argv[1]
        print self.runQuery(query)


    def authenticate_one(self):

        configFile = "pass_to_config_file_containing_p12_key_client_email_and_project_id"

        #Authorizing connection
        with open(configFile, "r") as conConf:
            filedata = conConf.read()
            jsonData = self.getJsonContent(filedata)
            KEY_FILE = jsonData["signin_credentials"]["key_file"]
            self.PROJECT_ID = jsonData["signin_credentials"]["project_id"]
            CLIENT_EMAIL = jsonData["signin_credentials"]["client_email"]

        scopes = ['https://www.googleapis.com/auth/bigquery']

        self.credentials = ServiceAccountCredentials.from_p12_keyfile(
            CLIENT_EMAIL, KEY_FILE, scopes=scopes)

        self.http_auth = self.credentials.authorize(Http())#ca_certs=os.environ['REQUESTS_CA_BUNDLE']))
        self.credentials.refresh(self.http_auth)
        self.bigquery = build('bigquery', 'v2',  http=self.http_auth)

    def runQuery(self, queryStr):
        try:
            query_request = self.bigquery.jobs()
            query_data = {
                'query': (
                    queryStr)
            }

            try:
                query_response = query_request.query(
                    projectId=self.PROJECT_ID,
                    body=query_data).execute()
                queryset = query_response['rows']
                return queryset
            except:
                self.credentials.refresh(self.http_auth)
                self.runQuery(queryStr)

        except HttpError as err:
            queryset = []
            print('Error: {}'.format(err.content))
            raise err
            return queryset


    def getJsonContent(self, jsonStr):
        jsonData = json.loads(jsonStr)
        return jsonData

BQController()

Code that I used to convert stdout of BQController() into a queryset object that then I pass to my Django template:

def my_view(request):

    subProcessResponse = runShellCommandWithLogReturnedAsString("cd /home/businessai/webapps/blesque_tv/trydjango19/product_upload/controllers/; python bigquery_run.py 'SELECT product_id as id, supplier_name as db_name, image_file as image_url, title, description as clean_content, msrp as marked_up_price, product_sku, price, ship_cost, drop_ship_fee, condition FROM products.doba_inventory LIMIT 10'")
    queryset = eval(subProcessResponse)

    context = {
        "object_list" : queryset,
    }

    return render(request, "template.html", context)

def runShellCommandWithLogReturnedAsString(commandAsStr):
    logToReturn = ""
    try:
        popen = subprocess.Popen(commandAsStr, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, shell=True)
        lines_iterator = iter(popen.stdout.readline, b"")
        for line in lines_iterator:
            logToReturn = logToReturn + str(line)
    except Exception,e:
        print e

    return logToReturn