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.