How to create an external Hive table if the field value has comma separated values

1
votes

I had used sqoop-import command to sqoop the data into Hive from teradata. Sqoop-import command is creating a text file with comma(,) as the delimiter.

After Sqooping, I had created an external table as shown below:

CREATE EXTERNAL TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, description String)
COMMENT ‘Employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;

But description column has values like this:"abc,xyz,mnl". Due to this,loading of data into a hive table is not proper. Then how to create a text file with a delimiter other than comma while sqooping.

Then how to delimit the fields while creating an external table of Hive?

1
hivesqoop

1 Answers

2
votes

Use --fields-terminated-by in your Sqoop job if you want to avoid the default delimiter.

--fields-terminated-by - This parameter is used for field separator character in output.

Example: --fields-terminated-by |

and then change fields separator in create table statement by FIELDS TERMINATED BY ‘|’