Support Questions
Find answers, ask questions, and share your expertise

MULTILINE COLUMN from Oracle showing the NULLS in HIVE table stored as avro data using sqoop

MULTILINE COLUMN from Oracle showing the NULLS in HIVE table stored as avro data using sqoop

New Contributor

Hi ,

Can only help on the below Multiline issue...

1. I Have the table TEST1 contain the 3 columns as ID , NAME , DESCR

2. I Have inserted the data as below , the DESCR column contains the data as newline format in the TEST1 table from Oracle.

Inserted:

(1 , ABC , 'H

E

L

L

O')

*** record inserted

3. Now I written sqoop to store this table data as avro==> sqoop import <DB CONN> --table TEST1 --as-avrodatafile --delete-target-dir --target-dir '/dev/eorp/demo/TEST1' -m 1

4. Created the HIVE EXT table:

CREATE EXTERNAL TABLE TEST1

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'

STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'

LOCATION '/dev/eorp/demo/TEST1/'

TBLPROPERTIES ('avro.schema.url'='hdfs:///dev/eorp/demo/avsc/TEST1.avsc');

HIVE OUTPUT

-------------------

1 ABC H

E NULL

L NULL

O NULL

5. I used -select ID , NAME , regexp_replace(loc ,'\\n' ,'')as loca from test1;

now I got the correct:

1 ABC HELLO

How to fix this issue perminant ? I want access this hive table through tableau ? Same problem at tableau side also ? Please advice ...