How to read a field comming from a compresed file Hive

Hi everyone,
Well basically I’ve loaded in a field all ino coming from a compresed file so here’s my script:
CREATE TABLE sample
( info string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t’
LINES TERMINATED BY ‘\n’;

LOAD DATA LOCAL INPATH '/home/ingenieroandresangel/datasets/sample.txt.gz’
INTO TABLE sample;

My compresed file is composed by 4 coulmns separated by tab ‘\t’ . Now I would like to perform a query to read just two columns from those 4 but in the table I just have 1 field so i dont know how perform the desired query . That should be somthing like:

SELECT string[0],string[1] FROM sample;

Plz if someone can help I appreciate. thanks.

You have to use combination of substr and instr. Here is the sample. As your data is tab delimited you should not give field delimiter as tab.

CREATE TABLE sample
( info string)
ROW FORMAT DELIMITED
LINES TERMINATED BY '\n';

insert into sample values ("Hello\tworld\thow\tare\tyou");

select substr(info, 1, instr(info, "\t") - 1) first,
substr(substr(info, instr(info, "\t") + 1), 1, instr(substr(info, instr(info, "\t") + 1), "\t") - 1) second
from sample;

Super good thanks so much, I though that maybe I will be able to read the table using array positions. I hope don’t have anything like that in my exam HDPCD. thanks