How create a table with a serde where text file has fixed-width (non-delimited) columns?

Asked Sep 10 '19 at 20:50

Active Nov 06 '19 at 18:43

Viewed 311 times

I'm trying to create a table with a textfile without delimiter.

row exemple:

1000000000168999337200----------030420191455594197981209954------- 00000240000005010000000011800000000000

CREATE EXTERNAL TABLE IF NOT EXISTS p_bi.stg_cob (tp_registro string, seq string, num_a string, dt_chamada string, hr_chamada string, num_b string, pt_interconect string, dur_rel_chamada string, dur_tar_chamada string, tp_servico string, vl_liq_chamada string, vl_brt_chamada string, reserva string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "(.{1})(.{10})(.{21})(.{8})(.{6})(.{20})(.{10})(.{7})(.{7})(.{2})(.{11})(.{11})(.{29}).*") STORED AS TEXTFILE LOCATION '/user/Fin/Bat';

edited Nov 06 '19 at 18:43

dtolnay

9,621
5
41
62

asked Sep 10 '19 at 20:50

lboniotti

this looks like an ok approach, what problem are you having? – maxymoo Sep 10 '19 at 20:53
The table is empty, with null columns – lboniotti Sep 10 '19 at 20:56
1

`,{11}` will *fail* to match. I suspect it is a simple typographical error. – user2864740 Sep 10 '19 at 20:58
While `,{11}` will match eleven commas in a row, you probably want `.{11}` – Sep 10 '19 at 21:05
Btw the minimum total characters needed is 144 according to your regex. And your row example is only 105 characters wide. – Sep 10 '19 at 21:10
That's correct, the last column haven't data. But using .{11} instead of ,{11} solved my problem. Thanks! – lboniotti Sep 10 '19 at 21:15

How create a table with a serde where text file has fixed-width (non-delimited) columns?

0 Answers0