1

I have an HIVE table which is AVRO format and one of the column in the table contains complex data type (nested arrays). One of the element in that nested array contains data which contains new line characters. I am using multiple lateral view explode to flatten the data; but because of the new line character in one of the columns the output is not good (means it is mapping wrong value against wrong column). I tried to use regex_replace function in my query by it is told a invalid function. I am using "Hive 1.1.0-cdh5.12.2". Can you please tell me how could I handle this new line issue within the data while querying from the avro table using multiple laterview explode (as there are nested arrays).

Koushik Chandra
  • 1,565
  • 12
  • 37
  • 73
  • I'm not really understanding why newlines are an issue. As long as the Avro schema knows the field is a string, then it's just a string with a newline character... It might *look odd* in Hue or the CLI or other JDBC tool, but the data isn't wrong. Can you please show the Hive table schema and your regex function attempt? – OneCricketeer Sep 13 '18 at 07:27
  • The attempt of replacing newline is "regex_replace(pmt_netwrk.specialinstructions,"\\n","") ". Part of the avro schema is given below. the attribute is "productproductInfomedicalBenefits" on which we are applying multiple lateral view explode – Koushik Chandra Sep 13 '18 at 12:59
  • "name" : "productproductInfomedicalBenefits", "type" : [ { "type" : "array", "items" : [ { "type" : "record", "name" : "productproductInfomedicalBenefits", "namespace" : ".productproductInfomedicalBenefits", "fields" : [ { "name" : "benefitBenefitCategoryID", – Koushik Chandra Sep 13 '18 at 13:02
  • 1
    you should add table schema and queries to make it more clear .. – hlagos Sep 13 '18 at 13:49
  • Please [edit] your question to include the formatted code, not in the comments – OneCricketeer Sep 13 '18 at 14:01
  • And no, regex_replace isn't a function, but you're typing it wrong https://stackoverflow.com/questions/18090481/how-to-replace-characters-in-hive – OneCricketeer Sep 13 '18 at 14:03

0 Answers0