Filtering out or replacing non English characters from Google BigQuery

user_4999583 0
Viewed 3673

I'm extracting data from a query in Google Biqquery. I'm connecting to Google API, via a python script, executing the query within the python script and writing the results of the query into a CSV file. When I execute the query from the script for a sample data(100 rows), everything looks good. But when I execute the script for the entire data, it fails.

UnicodeEncodeError: 'ascii' codec can't encode character u'\xe7' in position 38: ordinal not in range(128)

I see that this is a python error, but this happens when the script is trying to process records which have non-English characters. I faced the same issue in Hive, but I got around it by using the RLIKE function given below

  (CASE WHEN FIELD1 not rlike '[^a-zA-Z()\\|\\d\\s\\(_)\\-\\(/):]' THEN FIELD1 ELSE 'data' END) AS FIELD1

Is there a similar method or function in Google BigQuery to find and replace non-English characters? Or, can this be handled within the python scripting?

Code snippet:

job_id, _results = MY_CLIENT.query("""select FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE1""", use_legacy_sql=True)
complete, row_count = MY_CLIENT.check_job(job_id)
results = MY_CLIENT.get_query_rows(job_id)
outfile =  open('C:\\Users\\test.csv', 'w')
for row in results:
    for key in row.keys():
        if key == 'FIELD4':
            outfile.write("%s" %str(row[key]))
        else:
            outfile.write("%s," %str(row[key]))
    outfile.write("\n")
outfile.close()

Thanks in advance for you help!

google-bigquery python
2 Answers

You can use below to remove non-ascii chars 

REGEXP_REPLACE(field1, r'([^\p{ASCII}]+)', '')

Below is example you can play with to see how it works 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '12 - Table - Стол - test' AS field1 UNION ALL
  SELECT '23 - Table - الطاولة' UNION ALL
  SELECT '34 - Table - שולחן' 
)
SELECT 
  REGEXP_REPLACE(field1, r'([^\p{ASCII}]+)', '') AS ascii_only,
  field1
FROM `project.dataset.table`

with result 

Row ascii_only          field1   
1   12 - Table - - test 12 - Table - Стол - test     
2   23 - Table -        23 - Table - الطاولة     
3   34 - Table -        34 - Table - שולחן
user_52219440

This issue is related to Python 2.7 and its lack of support for processing unicode so everything is encoded in basic ASCII (7 bits).

One way of not losing those characters is to work explicitly in UTF-8 in your script:

outfile.write("%s" %(row[key].encode('utf-8'))

This is what would happen by default in python 3.x so that's why you would see no error.

Still, maybe best practice would be to first export this data to GCS and then work from there (as well as to migrate to Python 3.x if you can, it's way better ;)!)

user_10022770
Related