Wsrtjtyk

Is there a way to load a simple text file where fields are separated by "::" into hive table other than replacing those "::" with "," and then load it?
Replacing the "::" with "," is quicker when the text file is small but what if contains millions of records?

Try creating Hive table using Regex serde

Example:

i had file with below text in it.

i::90

w::99

Create Hive table:

hive> create external table default.i

(Id STRING,

Name STRING

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 

WITH SERDEPROPERTIES ('input.regex' = '(.*?)::(.*)')

STORED AS TEXTFILE;

Select from Hive table:

hive> select * from i;

+-------+---------+--+

| i.id  | i.name  |

+-------+---------+--+

| i     | 90      |

| w     | 99      |

+-------+---------+--+

In case if you want to skip the header then use below syntax:

hive> create external table default.i

(Id STRING,

Name STRING

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 

WITH SERDEPROPERTIES ('input.regex' = '(.*?)::(.*)')

STORED AS TEXTFILE

tblproperties ('skip.header.line.count'='1');

UPDATE:

Check is there any older files in your table location.if some files are there then delete them(if you don't want them).

1.Create Hive table as:

create external table <db_name>.<table_name>

(col1 STRING,

col2 STRING,

col3 string,

col4 string

)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 

WITH SERDEPROPERTIES ('input.regex' = '(.*?)::(.*?)::(.*?)::(.*)')

STORED AS TEXTFILE;

2.Then run:

load data local inpath 'Source path' overwrite into table 'Destination table'