Customizing Row Separators in Hive Tables

Scenario

In most cases, a carriage return character is used as the row delimiter in Hive tables stored in text files, that is, the carriage return character is used as the terminator of a row during queries. However, some data files are delimited by special characters instead of carriage return characters.

MRS Hive allows you to use different characters or character combinations to delimit rows of Hive text data. When creating a table, set inputformat to SpecifiedDelimiterInputFormat, and set the following parameter before each search. Then the table data is queried by the specified delimiter.

set hive.textinput.record.delimiter='';

• The Hue component of the current version does not support setting multiple separators when files are imported to a Hive table.
• This section applies to MRS 3.x or later.

Procedure

1. Specify inputFormat and outputFormat when creating a table.

   CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ...)] [ROW FORMAT row_format] STORED AS inputformat 'org.apache.hadoop.hive.contrib.fileformat.SpecifiedDelimiterInputFormat' outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

2. Specify the delimiter before search.

   set hive.textinput.record.delimiter='!@!';

   Hive will use '!@!' as the row delimiter.