hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kiran <gskiran....@gmail.com>
Subject Hive Table Displaying NULLS for a FIXED length file
Date Wed, 27 Sep 2017 13:02:54 GMT
Hello Developers,

I am trying to select data from a hive table created on top of a fixed
length file. Below are different DDL's tried and nothing worked.


Trial-1) With the *.** at the end of regex after all the column lengths are
declared and also without *.**
Trial-2) With output Expression as  second DDL below.
Trial-3) Tried with load data INPATH and without as well.

*File Properties:*

35 Columns in total.
Every line ends with a new line '\n'.
All the columns are being read as strings.
Total row count is 25K(+).
Files are under the hdfs dir: /data/source/raw/land/
File name: my_file1.txt
------------------------------------------------------------
----------------------
*HIVE DDL Trial-1:*

CREATE EXTERNAL TABLE all_files.MY_TABLE_1
(COL1 string,
COL2 string,
COL3 string,
COL4 string,
.
.
.
COL35 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "(.{13})(.{10})(.{10})..so on till
35th col.....(.{3}).*" )
STORED AS TEXTFILE
LOCATION 'hdfs:///data/source/raw/land';
-------
OK
Time taken 0.165 seconds

hive> select * from all_files.MY_TABLE_1 limit 5;
result: NULLS displayed
------------------------------------------------------------
-----------------------------------------
*HIVE DDL Trial-2:*

CREATE EXTERNAL TABLE all_files.MY_TABLE_1
(COL1 string,
COL2 string,
COL3 string,
COL4 string,
.
.
.
COL35 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "(.{13})(.{10})(.{10})..so on till 35th col.....(.{3})",
"output.format.string" = "%1$s %2$s %3$s..so on till 35th col...%35$s"
)
STORED AS TEXTFILE
LOCATION 'hdfs:///data/source/raw/land';

OK
Time taken 0.165 seconds

hive> select * from all_files.MY_TABLE_1 limit 5;
result: NULLS displayed

------------------------------------------------------------
--------------------------------

Any help in this regard is much appreciated.

Thanks,
Srikiran.

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message