Hello,
I have encountered situations in the past where the data that I want to upload requires an amount of cleaning prior to use. Sometimes it needs to be de-duplicated, all NULL values changed to 0, or adjusted to another format (e.g. remove headers and rows that contain table elements). For instance, sometimes I have been provided a file that looks like this:
+-------------------------+-------------------------+-------------------------+-------------------------+ | HEADING | HEADING | HEADING | HEADING | +-------------------------+-------------------------+-------------------------+-------------------------+ | DATA (w/ spaces) | DATA | NULL | DATA |
Obviously, if this was saved in .csv format, I would want to remove the white space and "table-like" formatting so that it is easier to load in applications such as Pig and Hive.
I have heard of people using a staging table and python/R scripts to help in this process. However, I was wondering what tools/methods are available in the Hadoop Environment (perhaps in Hive or Pig) to adjust the data during the upload process. So far I have tried some work-around scripts in Pig, but I think there must be a better solution. For example:
import = LOAD '/path/to/data' using PigStorage('|'); -- only pipe characters seemed to delimit the file
dropfirstcol = FOREACH import GENERATE $1..; -- remove the first column since it will not have data
removeformatrows = FILTER dropfirstcol by $0 != ''; -- remove empty rows that had "+-----+"
removewhitespace = FOREACH removeformatrows GENERATE REPLACE($0, ' ', ''); -- eliminate white spaceIf more clarification is needed, please let me know. I have reviewed this but still am having issues.
Thanks!
