Support UTF-8 in Google BigQuery results

[andrewryno]

Given that the entire Google BigQuery API returns UTF-8, it would make sense to handle UTF-8 output from BigQuery in the gbq.read_gbq IO module.

I'd love to do a pull request but I'm not sure the preferred way of handling this. I'd assume that this line should be changed to field_value = field_value.decode('utf-8'). I made that change and tests passed but figuring out how to properly test UTF-8 encoding is giving me some trouble (keeps making my test addition fail).

[jacobschaer]

That's a good point - I think we shelved that at the time as low priority, but you're correct that it should be fairly simple. Can you post what your test was that's failing? Also, was this for Python 2.7, since I thought str() would work with UTF8 automagically in Python 3.

Note, unless I'm mistaken, we'll still have issues with UTF8 Headers per line 236:
col_names.append(field['name'].encode('ascii', 'ignore'))

[andrewryno]

I changed the test to this:

def test_type_conversion(self):
    # All BigQuery Types should be cast into appropriate numpy types
    sample_input = [('1.095292800E9', 'TIMESTAMP'),
             ('false', 'BOOLEAN'),
             ('2', 'INTEGER'),
             ('3.14159', 'FLOAT'),
             ('Hello World', 'STRING'),
             ('éü', 'STRING')]
    actual_output = [gbq._parse_entry(result[0],result[1]) for result in sample_input]
    sample_output = [np.datetime64('2004-09-16T00:00:00.000000Z'),
              np.bool(False),
              np.int('2'),
              np.float('3.14159'),
              u'Hello World',
              u'éü']
    self.assertEqual(actual_output, sample_output, 'A format conversion failed')

Keep in mind I'm new to the whole ascii/unicode problem in Python so the sample_input addition with éü should probably be done differently.

Removing the optional message this is the error I'm getting:

======================================================================
FAIL: test_type_conversion (__main__.TestGbq)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/andrewryno/Sites/venv/lib/python2.7/site-packages/pandas/io/tests/test_gbq.py", line 199, in test_type_conversion
    self.assertEqual(actual_output, sample_output)
AssertionError: Lists differ: [numpy.datetime64('2004-09-15T... != [numpy.datetime64('2004-09-15T...

First differing element 5:
\xe9\xfc
\xc3\xa9\xc3\xbc

  [numpy.datetime64('2004-09-15T17:00:00.000000-0700'),
   False,
-  2.0,
?   --

+  2,
   3.14159,
   u'Hello World',
-  u'\xe9\xfc']
+  u'\xc3\xa9\xc3\xbc']

Decode isn't working properly, which I attribute to my test.

[andrewryno]

Just noticed I made a mistake in the initial post. Should be encode, not decode.

I also just edited this manually on our install and it is working fine. I would just need to figure out how to write a proper test for it.

[jacobschaer]

Something like this?
jacobschaer@a50fce0

I'll have to see what it returns from BigQuery as... part of the problem is you're not supposed to have Unicode literals in your source code: http://www.python.org/dev/peps/pep-0263/ . We might have to be careful here, since I'm not sure what I picked was normalized: http://en.wikipedia.org/wiki/Unicode_equivalence

[andrewryno]

Yeah that works. Didn't know about the Unicode literals PEP, so that's good to know.

Edit: actually, that may not work. str() is what is throwing the error. It needs to be something like: field_value.encode('utf-8'). Though I'm not sure about compatibility with Python 3.

[jacobschaer]

I didn't know about PEP either - I just found it when I was messing around with your test. str() is definitely a source of issues. I'll try that.

[jreback]

to embed unicode literal a use the 'u' function

from pandas.compat import u

see pandas/tests/test_format.py for some examples

[jacobschaer]

That's a handy method :-) Looks like this works fine... it looks like Google returns a normalized version, so "\xc3\xa9\xc3\xbc" becomes "\xe9\xfc".

Something like this?
jacobschaer@1eb852a

You should be able to clone mine and install it with the 'python setup.py develop' to test it if you have a dataset in mind.

[jacobschaer]

Let me know if you can confirm this works:
https://github.com/jacobschaer/pandas/compare/GBQ_Unicode_Support

If so, we'll wrap this into a pull request.

EDIT: Moved to branch

[andrewryno]

I haven't had a chance to try that specific branch yet, I'll try to get to it this week.

I've been running field_value = field_value.encode('UTF-8') in production for a couple weeks now as a temporary fix and haven't encountered any problems.

[jreback]

@jacobschaer

not sure if you guys follow pandas on SO:

http://stackoverflow.com/questions/21886742/convert-pandas-dtypes-to-bigquery-type-representation

[andrewryno]

@jacobschaer I've been running your code for the past few weeks and it's been running smoothly. Only remaining UTF-8 issues are within my own code.

[jreback]

@jacobschaer

it appears that the bigquery installation fails under setuptools >= 3.0 (which I think is now public ish).
works fine on 2.2. This has to do with the setup of the dependencies

you can see here: https://travis-ci.org/pydata/pandas/jobs/20387092
(open the install tab)

worked around in pandas...maybe could report upstream....

[azbones]

@jreback @jacobschaer I'll talk to their developer relations people and see if they can help us resolve this- seems like it should be an easy fix for them...I'll let you know what I read back.