[R] Arrow to R fails with embedded nuls in strings

[asfimport]

Apologies if this issue isn't categorized or documented appropriately. Please be gentle! :)

As a heavy R user that normally interacts with parquet files using SparklyR, I have recently decided to try to use arrow::read_parquet() on a few parquet files that were on my local machine rather than in hadoop. I was not able to proceed after several various attempts due to embedded nuls. For example:

try({df <- read_parquet('out_2019-09_data_1.snappy.parquet') })
Error in Table__to_dataframe(x, use_threads = option_use_threads()) :
embedded nul in string: 'INSTALL BOTH LEFT FRONT AND RIGHT FRONT TORQUE ARMS\0 ARMS'

Is there a solution to this?

I have also hit roadblocks with embedded nuls in the past with csvs using data.table::fread(), but readr::read_delim() seems to handle them gracefully with just a warning after proceeding.

Apologies that I do not have a handy reprex. I don't know if I can even recreate a parquet file with embedded nuls using arrow if it won't let me read one in, and I can't share this file due to company restrictions.

Please let me know how I can be of any more help!

Environment: Windows 10
R 3.4.4

Reporter: John Cassil
Assignee: Neal Richardson / @nealrichardson

Related issues:

• function read_parquet(*,as_data_frame=TRUE) fails when embedded nuls present. (is duplicated by)
• [R] Regression from 2.0.0 -> 3.0.0: Null character in string prevents dataset from loading (is related to)
• [R] Consider ways to make arrow.skip_nul option more user-friendly (is related to)

Original Issue Attachments:

• embedded_nul.parquet

PRs and other links:

• GitHub Pull Request #8365

_{Note: This issue was originally created as ARROW-6582. Please see the migration documentation for further details.}

[asfimport]

Neal Richardson / @nealrichardson:
Thanks for the report. A few thoughts:

1. "embedded nul in string" is an error coming from R. Since the error is being thrown in Table__to_dataframe, that means the Parquet file was already read into Arrow memory successfully, and R is failing to read it from Arrow. That helps isolate the issue.

2. Given that, you could play around with the col_select argument to read_parquet and identify which column it is that has the nul, if you don't already know. If you don't happen to need this column for whatever you're trying to do, you could omit it from there and proceed.

3. If you can identify the offending column, it would be interesting to know what Arrow type it is. To do that, do something like

tab <- read_parquet(file, as_tibble=FALSE)
tab$schema

and report back what type that column is.

4. Check your system locale and encoding and make sure it aligns with the data in the file. Googling the error message points to encoding often being implicated.

5. How are these Parquet files generated? Same host? Or different system, platform, etc.? Does that tell you something useful about the locale/encoding you need to set in R to read the data?

6. If any of this leads you to a place where you can write out a sufficiently anonymized/obfuscated file that reproduces the error, that would of course be most helpful.

[asfimport]

John Cassil:
Thanks Neal!

I had previously been able to set as_tibble to FALSE and successfully create the , but didn't know I could use schema to see the columns. Actually, my whole purpose for reading in this particular file to begin with was to see the column names in the file, so problem solved! haha

> df$schema
arrow::Schema 
squishedVin: string
hashedVin: string
tranDate: date32[day]
rawFieldValue: string
displayText: string
odometerReading: double 

I know based on the error that the string is appearing in the rawFieldValue column which it appears that arrow is interpreting as a string type.

As far as encoding issues, I am not strong in understanding encoding issues. To back up just a bit, this happens to be a dump of the raw text that any of hundreds of thousands of sources has given us, so I am guessing that the embedded nul originated due to encoding issues on a partner's server somewhere perhaps in a galaxy far away, many years ago. The file was actually created by a java process that one of our teams built to export it from hadoop. I know very little beyond that, and unfortunately am not sure that I could create anything to reproduce the error yet.

I am curious if you know of any way to use a function to turn this into a dataframe that would bypass Table__to_dataframe...

Thanks so much again!

[asfimport]

Neal Richardson / @nealrichardson:
My guess is that (if you needed to solve this problem, which it sounds like you don't), you could try setting different encodings in your R session and see if that handles the string column correctly. Or there's probably a way to get at that column and dump it as is to disk so that you could use some other means of stripping out the nuls. I'm guessing the ultimate fix is to fix the data generating/ETL process so that there aren't nuls there to begin with, though I recognize that that's not always an option.

I'll keep this open for a bit and think about if there are ways we can make it easier to dump that data from Arrow to a plain text format without going through R first so that one might be able to debug when they get bad data like this, but ultimately the error is coming from R, not arrow.

[asfimport]

John Cassil:
Thank you very much for your thoughtful responses and the solution to my original goal.

That might be an interesting approach. If there were a function to export it from Arrow into text on disk, then a user could essentially use Arrow as a method to unpack/uncompress a parquet file into raw text. I could see that being helpful in other situations beyond this. I'm not sure. Your call!

[asfimport]

Neal Richardson / @nealrichardson:
Issue resolved by pull request 8365
#8365

[asfimport]

Ian Cook / @ianmcook:
For future reference/testing purposes: attached is a tiny uncompressed Parquet file named embedded_nul.parquet that contains a single string column named x with a single record containing a one-byte string consisting of an embedded nul. The file was written with Spark 3.0.1 using this PySpark code:

from pyspark.sql.types import *
schema = StructType([StructField("x", StringType(), True)])
json = '[{"x": "\\u0000"}]'
df = spark.read.schema(schema).json(sc.parallelize([json]))
df.repartition(1).write.parquet('embedded_nul.parquet', compression = 'none')