ETL data from Bigquery to Redshift using Python
<p>I have this script in Python that set a variable with the result of a query, that runs in Google Bigquery (some library I do not use here, but I was testing converting json to a csv file):</p>

<pre><code>import httplib2
import datetime
import json
import csv
import sys
from oauth2client.service_account import ServiceAccountCredentials
from bigquery import get_client


#Set DAY - 1
yesterday = datetime.datetime.now() - datetime.timedelta(days=1)
today = datetime.datetime.now()

#Format to Date
yesterday = '{:%Y-%m-%d}'.format(yesterday)
today = '{:%Y-%m-%d}'.format(today)


# BigQuery project id as listed in the Google Developers Console.
project_id = 'project'

# Service account email address as listed in the Google Developers Console.
service_account = 'email@email.com'


scope = 'https://www.googleapis.com/auth/bigquery'

credentials = ServiceAccountCredentials.from_json_keyfile_name('/path/to/file/.json', scope)

http = httplib2.Http()
http = credentials.authorize(http)


client = get_client(project_id, credentials=credentials, service_account=service_account)

#Synchronous query
try:
    _job_id, results = client.query("SELECT * FROM dataset.table WHERE CreatedAt &gt;= PARSE_UTC_USEC('" + yesterday + "') and CreatedAt &lt; PARSE_UTC_USEC('" + today + "') limit 1", timeout=1000)
except Exception as e:
    print e

print results
</code></pre>

<p>The returned result at variable <strong>results</strong> is something like this:</p>

<pre><code>[
{u'Field1': u'Msn', u'Field2': u'00000000000000', u'Field3': u'jsdksf422552d32', u'Field4': u'00000000000000', u'Field5': 1476004363.421, 
u'Field5': u'message', u'Field6': u'msn', 
u'Field7': None, 
u'Field8': u'{"user":{"field":"j23h4sdfsf345","field":"Msn","field":"000000000000000000","field":true,"field":"000000000000000000000","field":"2016-10-09T09:12:43.421Z"}}', u'Field9': 1476004387.016}
]
</code></pre>

<p>I need to load it at Amazon Redshift, but in this format I can't run a copy from s3 using the .json that it generates...</p>

<p>Is there a way that I can modify this json for Redshift to load? Or return a .csv directly? I don't know a lot from this library from bigquery, or python at all (one of my first scripts).</p>

<p>Thanks a lot!</p>

<p>To remove the 'u' before the fields:</p>

<pre><code>results = json.dumps(results)
</code></pre>

<p>Then, to transform the json variable in a csv file, I created:</p>

<pre><code>#Transform json variable to csv
results = json.dumps(results)

results = json.loads(results)

f = csv.writer(open("file.csv", "w"), delimiter='|')

f.writerow(["field","field","field","field","field","field","field", "field", "field", "field"])

for results in results:
    f.writerow([results["field"],
            results["field"],
            results["field"],
                results["field"],
            results["field"],
            results["field"],
            results["field"],
            results["field"],
            results["field"],
           results["field"]])
</code></pre>

<p>After this, I was able to load the file to Redshift.</p>

python json csv etl