Handling automatic authentication of a python Script that read and write data to google big query using docker

Question

I recently developed a python code in my local machine that read data from mysql database and insert in google big query.I use service account for authentication purpose and my code can be executed successfully without error. Now I am trying to run my script in a docker python container. Once I run my code, receive the a authentication message which I am not sure how to handle it automatically without interaction between user and system.

Message is :

"Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=4xxxxxxxk0tmvj2m941jhre2nbqka17vqxxfxxx.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%xxxxxxxb&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery&state=kszePnO3tOxxxxxx&prompt=consent&access_type=offline Enter the authorization code: Traceback (most recent call last):... .........

EOFError: EOF when reading a line "

My Python code:

import mysql.connector
import pandas as pd
from oauth2client.client import GoogleCredentials
from bigquery import get_client
import os
import urllib.request

service_account = '[email protected]'
key = 'xxxxxxxxx.p12'
project_id = 'xxxxxxxxx'
db = mysql.connector.connect(user=$user, 
password=$password,host=$host,database=$database)
df= pd.read_sql(sql_query,db)
....
client = get_client(project_id, service_account=service_account,  
private_key_file=key, readonly=False)

#Push dataframe to google bigquery
 df.to_gbq('GoogleBQDatbaseName.TableName',projectid,verbose=True,if_exists='append')

Any suggestion how I can handle this authentication issue in my script automatically. Thank you

Vasily Bronsky Vasily Bronsky · Accepted Answer · 2017-10-13T10:52:46

I do the same job, so part of dockerfile with google auth looks like this:

RUN apt-get install -y software-properties-common curl

RUN export CLOUD_SDK_REPO="cloud-sdk-$(lsb_release -c -s)"; echo "deb http://packages.cloud.google.com/apt $CLOUD_SDK_REPO main" | tee -a /etc/apt/sources.list.d/google-cloud-sdk.list

RUN curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | apt-key add -

RUN apt-get update && apt-get -y install google-cloud-sdk

COPY YOUR_JSON_KEY_HERE.json /keys/ANY_NAME.json

ENV GOOGLE_APPLICATION_CREDENTIALS ANY_NAME.json

RUN gcloud auth activate-service-account --key-file=/keys/ANY_NAME.json

So you have to create google service account ang get json key from Google Cloud Console. Also i'm using

FROM ubuntu:16.04

And in this case in your python script you have not to google auth. I mean you don't need this part of code:

client = get_client(project_id, service_account=service_account, private_key_file=key, readonly=False)

Handling automatic authentication of a python Script that read and write data to google big query using docker

1 Answers