Text file to SQL shows null values

Question

My data-:

id      name                    alpha_2 alpha_3
4       Afghanistan             af      afg
8       Albania                 al      alb
12      Algeria                 dz      dza
20      Andorra                 ad      and
24      Angola                  ao      ago
28      Antigua and Barbuda     ag      atg

My code-:

create table futurecart_case_country_details (
  id int, 
  Name varchar(75), 
  Alpha_2 varchar(2), 
  Alpha_3 varchar(2));


load data local infile 'project/futurecart_case_country_details.txt' 
into table edureka_667602_futurecart_case_country_details 
fields terminated by " " 
lines terminated by "\n";

Output in SQL-:

0       null    null    null
4       null    null    null
8       null    null    null
12      null    null    null
20      null    null    null
24      null    null    null
28      and     Ba      null

Please help I don't know what I am doing wrong, seems something trivial.

fields terminated by " " is bad idea with your data ('Antigua and Barbuda') and your formatting — Turo
– Turo, Commented Aug 5, 2020 at 8:40
Last line (id=28) importing result shows that fields terminator is tabular, not space. Investigate your input file in HEX view mode. — Akina
– Akina, Commented Aug 5, 2020 at 9:02
Hi Akina, Thank you It works now but I am getting column header as part of data as well can you tell me how can I ignore reading column headers? — Aekansh Gupta
– Aekansh Gupta, Commented Aug 5, 2020 at 9:30
@AekanshGupta . . . I don't think Hive supports load data infile, so I removed that tag. Tag only with the database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Aug 5, 2020 at 11:38

sathya · Accepted Answer · 2020-08-05 10:31:06Z

this below approach might be helpful to you,

-- input data with '\t'  as a delimiter(tab)
vim /home/sathya/test-datasets/hive-data.txt 

id      name    alpha_2 alpha_3
4       Afghanistan     af      afg
8       Albania al      alb
12      Algeria dz      dza
20      Andorra ad      and
24      Angola  ao      ago
28      Antigua and Barbuda     ag      atg

--in hive shell;
create database storage;
use storage;
create table storage.futurecart_case_country_details(id string, name string, alpha_2 string, alpha_3 string) row format delimited  fields terminated by '\t' lines terminated by '\n' stored as textfile;
alter table storage.futurecart_case_country_details SET TBLPROPERTIES ("skip.header.line.count"="1");

--OK
--Time taken: 0.786 seconds

load data local inpath '/home/sathya/test-datasets/hive-data.txt' into table futurecart_case_country_details;
--Loading data to table storage.futurecart_case_country_details
--Table storage.futurecart_case_country_details stats: [numFiles=1, numRows=0, totalSize=145, rawDataSize=0]
--OK
--Time taken: 1.889 seconds
select * from storage.futurecart_case_country_details;
/*
4       Afghanistan     af      afg
8       Albania al      alb
12      Algeria dz      dza
20      Andorra ad      and
24      Angola  ao      ago
28      Antigua and Barbuda     ag      atg
*/

--Time taken: 0.067 seconds, Fetched: 7 row(s)
select name from storage.futurecart_case_country_details;
/*
Afghanistan
Albania
Algeria
Andorra
Angola
Antigua and Barbuda
Time taken: 0.101 seconds, Fetched: 7 row(s)
*/

select alpha_2 from storage.futurecart_case_country_details;
/*
af
al
dz
ad
ao
ag
Time taken: 0.142 seconds, Fetched: 7 row(s)
*/

select alpha_3 from storage.futurecart_case_country_details;
/*
afg
alb
dza
and
ago
atg
Time taken: 0.054 seconds, Fetched: 7 row(s)
*/

select id from storage.futurecart_case_country_details;
/*
4
8
12
20
24
28
Time taken: 0.193 seconds, Fetched: 7 row(s)
*/

show create table storage.futurecart_case_country_details;

/*
CREATE TABLE `hrdt_all_storage.futurecart_case_country_details`(
  `id` string,
  `name` string,
  `alpha_2` string,
  `alpha_3` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://localhost:9000/apps/hive/warehouse/storage.db/futurecart_case_country_details'
TBLPROPERTIES (
  'last_modified_by'='g.t1hrdtapp.001',
  'last_modified_time'='1596623083',
  'numFiles'='1',
  'numRows'='0',
  'rawDataSize'='0',
  'skip.header.line.count'='1',
  'totalSize'='145',
  'transient_lastDdlTime'='1596623083')
*/

Collectives™ on Stack Overflow

Text file to SQL shows null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related