Five Stars

Create Hive Table using ROW FORMAT SERDE

Hi everyone,

 

I'm working on Hive components at the moment and trying to create bunch of tables using different row formats... so far so good. 

I'm now trying to create a hive table to which I'll load a fixed-width(positional) file. I'm aware that in Hive language all you need to do is select row format as ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'  and give mention length of each column in SerDe properties. Something like this - 

CREATE TABLE USCityZipCode (
USStreet STRING,
USCity STRING,
USState STRING,
USStateID STRING,
Date STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "(.{40})(.{25})(.{25})(.{25})(.{10}).*");

In my Talend job - I'm using tHiveCreateTable component to achieve same thing. But the option to specify SerDe only appears when I select Format as RCFile / ORCFile / SequenceFile / InputFormat and OutputFormat.

The file I want to load to this table is a simple fixed width text file, I was hoping that SerDe option will be available for TextFile as well....

 

  • Big Data
Tags (1)
1 REPLY
Five Stars

Re: Create Hive Table using ROW FORMAT SERDE

Quick Update:

 

I was able to create the table using SerDe with tHiveRow component and load data using tHiveLoad. But I'm really looking for an answer on how to achieve this using CreateTable Component.