You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by yogesh dhari <yo...@live.com> on 2012/07/04 07:39:02 UTC
Hive upload
Hi all,
I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouseI want it to be present into hive, I used this command
sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new
And another thing is,If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table,
If I run command Select * from NewTable;it reflects
Null Null Null Null
although the real data is
Yogesh 4 Bangalore 1234
Please Suggest and help
RegardsYogesh Kumar
Re: Hive upload
Posted by Ruslan Al-Fakikh <ru...@jalent.ru>.
Hi,
Regarding the sqoop import: I noticed you wrote -table instead of
--table (1 dash instead of 2)
Ruslan
On Wed, Jul 4, 2012 at 12:41 PM, Bejoy Ks <be...@yahoo.com> wrote:
> Hi Yogesh
>
> To add on, looks like the table definition doesn't match with data as well.
>
> Your table defn has 4 columns defined, with 4th column as int
>
> describe formatted letstry;
> OK
> # col_name data_type comment
>
> rollno int None
> name string None
> numbr int None
> sno int None
>
>
> But the data has 5 columns with the 4th column as String
>
> 1,John,123,abc,2
>
>
> Also When you create the table, make sure to specify the right field
> separator
>
> ....
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
> STORED AS TEXTFILE
>
>
> Regards
> Bejoy KS
>
> ________________________________
> From: Bejoy Ks <be...@yahoo.com>
> To: "user@hive.apache.org" <us...@hive.apache.org>
> Sent: Wednesday, July 4, 2012 1:59 PM
> Subject: Re: Hive upload
>
> Hi Yogesh
>
> Looks like Sqoop import from rdbms to hdfs is getting successful but is
> failing at hive create table. You are seeing data in hive ware house because
> you have specified that as your target dir in sqoop import (--target-dir
> /user/hive/warehouse/new). It is recommended to use a different target dir
> while doing sqoop import other than the hive warehouse dir.
>
> Can you post in the full console log of sqoop with --verbose logging
> enabled. It can give some clues.
>
>
> With the second issue, You already have your data in
> '/user/hive/warehouse/letstry/' which is the location for the hive table
> 'letstry'. Why you still want to do a LOAD DATA again in there?
>
> If you are doing a SQOOP import of that, Again it is recommended to use a
> different target dir other than hive ware house dir. It'll help you avoid
> some confusions as well.
>
>
> ________________________________
> From: yogesh dhari <yo...@live.com>
> To: hive request <us...@hive.apache.org>
> Sent: Wednesday, July 4, 2012 1:40 PM
> Subject: RE: Hive upload
>
>
> Hi Bejoy,
>
> Thank you very much for your response,
>
> 1)
>
> A) When I run command show tables it doesn't show newhive table.
> B) Yes the the newhive directory is present into /user/hive/warehouse and
> also containing the values imported from RDBMS
>
> Please suggest and give me an example for the sqoop import command according
> to you for this case.
>
>
> 2)
>
> A) Here is the command
>
> describe formatted letstry;
> OK
> # col_name data_type comment
>
> rollno int None
> name string None
> numbr int None
> sno int None
>
> # Detailed Table Information
> Database: default
> Owner: mediaadmin
> CreateTime: Tue Jul 03 17:06:27 GMT+05:30 2012
> LastAccessTime: UNKNOWN
> Protect Mode: None
> Retention: 0
> Location: hdfs://localhost:9000/user/hive/warehouse/letstry
> Table Type: MANAGED_TABLE
> Table Parameters:
> transient_lastDdlTime 1341315550
>
> # Storage Information
> SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
> InputFormat: org.apache.hadoop.mapred.TextInputFormat
> OutputFormat:
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
> Compressed: No
> Num Buckets: -1
> Bucket Columns: []
> Sort Columns: []
> Storage Desc Params:
> serialization.format 1
> Time taken: 0.101 seconds
>
>
> B) hadoop dfs -ls /user/hive/warehouse/letstry/
> Found 1 items
> -rw-r--r-- 1 mediaadmin supergroup 17 2012-07-02 12:05
> /user/hive/warehouse/letstry/part-m-00000
>
> hadoop dfs -cat /user/hive/warehouse/letstry/part-m-00000
> 1,John,123,abc,2
>
>
>
>
> Here data is present but when I upload it to Hive it gets deleted from HDFS
> and in Hive value appers NULL instead of ( 1,John,123,abc,2). and I didn't
> understad your point regarding correct data format? ( this data was imported
> from Mysql table)
> And what kind of confugration neede in sqoop
>
> Please suggest and help
>
>
> Greetings
> Yogesh Kumar
>
>
>
>
>
> ________________________________
> Subject: Re: Hive upload
> To: user@hive.apache.org
> From: bejoy_ks@yahoo.com
> Date: Wed, 4 Jul 2012 05:58:41 +0000
>
> Hi Yogesh
>
> The first issue (sqoop one).
> 1) Is the table newhive coming when you list tables using 'show table'?
> 2) Are you seeing a directory 'newhive' in your hive warte house dir(usually
> /usr/hive/warehouse)?
>
> If not sqoop is failing to create hive tables /load data into them. Only
> sqoop import to hdfs is getting successful the hive part is failing.
>
> If hive in stand alone mode works as desired you need to check the sqoop
> configurations.
>
> Regarding the second issue, can you check the storage location of NewTable
> and check whether there are files within. If so then do a 'cat' of those
> files and see whether it has the correct data format.
>
> You can get the location of your table from the following command
> describe formatted NewTable;
> Regards
> Bejoy KS
>
> Sent from handheld, please excuse typos.
> ________________________________
> From: yogesh dhari <yo...@live.com>
> Date: Wed, 4 Jul 2012 11:09:02 +0530
> To: hive request<us...@hive.apache.org>
> ReplyTo: user@hive.apache.org
> Subject: Hive upload
>
> Hi all,
>
> I am trying to upload the tables from RDBMS to hive through sqoop, hive
> imports successfully. but i didn't find any table in hive that imported
> table gets uploaded into hdfs idr /user/hive/warehouse
> I want it to be present into hive, I used this command
>
> sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1
> --password SQOOP1 -table newone --hive-table newhive --create-hive-table
> --hive-import --target-dir /user/hive/warehouse/new
>
>
> And another thing is,
> If I upload any file or table from HDFS or from Local then its uploads but
> data doesn't show in Hive table,
>
> If I run command
> Select * from NewTable;
> it reflects
>
> Null Null Null Null
>
>
> although the real data is
>
> Yogesh 4 Bangalore 1234
>
>
> Please Suggest and help
>
> Regards
> Yogesh Kumar
>
>
>
>
--
Best Regards,
Ruslan Al-Fakikh
Re: Hive upload
Posted by Bejoy Ks <be...@yahoo.com>.
Hi Yogesh
To add on, looks like the table definition doesn't match with data as well.
Your table defn has 4 columns defined, with 4th column as int
describe formatted letstry;
OK
# col_name data_type comment
rollno int None
name string None
numbr int
None
sno int None
But the data has 5 columns with the 4th column as String
1,John,123,abc,2
Also When you create the table, make sure to specify the right field separator
....
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
Regards
Bejoy KS
________________________________
From: Bejoy Ks <be...@yahoo.com>
To: "user@hive.apache.org" <us...@hive.apache.org>
Sent: Wednesday, July 4, 2012 1:59 PM
Subject: Re: Hive upload
Hi Yogesh
Looks like Sqoop import from rdbms to hdfs is getting successful but is failing at hive create table. You are seeing data in hive ware house because you have specified that as your target dir in sqoop import (--target-dir /user/hive/warehouse/new). It is recommended to use a different target dir while doing sqoop import other than the hive warehouse dir.
Can you post in the full console log of sqoop with --verbose logging enabled. It can give some clues.
With the second issue, You already have your data in '/user/hive/warehouse/letstry/' which is the location for the hive table 'letstry'. Why you still want to do a LOAD DATA again in there?
If you are doing a SQOOP import of that, Again it is recommended to use a different target dir other than hive ware house dir. It'll help you avoid some confusions as well.
________________________________
From: yogesh dhari <yo...@live.com>
To: hive request <us...@hive.apache.org>
Sent: Wednesday, July 4, 2012 1:40 PM
Subject: RE: Hive upload
Hi Bejoy,
Thank you very much for your response,
1)
A) When I run command show tables it doesn't show newhive table.
B) Yes the the newhive directory is present into /user/hive/warehouse and also containing the values imported from RDBMS
Please suggest and give me an example for the sqoop import command according to you for this case.
2)
A) Here is the command
describe formatted letstry;
OK
# col_name data_type comment
rollno int None
name string None
numbr int
None
sno int None
# Detailed Table Information
Database: default
Owner: mediaadmin
CreateTime: Tue Jul 03 17:06:27 GMT+05:30 2012
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost:9000/user/hive/warehouse/letstry
Table Type:
MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1341315550
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed:
No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format
1
Time taken: 0.101 seconds
B) hadoop dfs -ls /user/hive/warehouse/letstry/
Found 1 items
-rw-r--r-- 1 mediaadmin supergroup 17 2012-07-02 12:05 /user/hive/warehouse/letstry/part-m-00000
hadoop dfs -cat /user/hive/warehouse/letstry/part-m-00000
1,John,123,abc,2
Here data is present but when I upload it to Hive it gets deleted from HDFS and in Hive value appers NULL instead of ( 1,John,123,abc,2). and I didn't understad your point regarding correct data format? ( this data was imported from Mysql table)
And what kind of confugration neede in sqoop
Please suggest and help
Greetings
Yogesh Kumar
________________________________
Subject: Re: Hive upload
To: user@hive.apache.org
From: bejoy_ks@yahoo.com
Date: Wed, 4 Jul 2012 05:58:41 +0000
Hi Yogesh
The first issue (sqoop one).
1) Is the table newhive coming when you list tables using 'show table'?
2) Are you seeing a directory 'newhive' in your hive warte house dir(usually /usr/hive/warehouse)?
If not sqoop is failing to create hive tables /load data into them. Only sqoop import to hdfs is getting successful the hive part is failing.
If hive in stand alone mode works as desired you need to check the sqoop configurations.
Regarding the second issue, can you check the storage location of NewTable and check whether there are files within. If so then do a 'cat' of those files and see whether it has the correct data format.
You can get the location of your table from the following command
describe formatted NewTable;
Regards
Bejoy KS
Sent from handheld, please excuse typos.
________________________________
From: yogesh dhari <yo...@live.com>
Date: Wed, 4 Jul 2012 11:09:02 +0530
To: hive request<us...@hive.apache.org>
ReplyTo: user@hive.apache.org
Subject: Hive upload
Hi all,
I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouse
I want it to be present into hive, I used this command
sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new
And another thing is,
If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table,
If I run command
Select * from NewTable;
it reflects
Null Null Null Null
although the real data is
Yogesh 4 Bangalore 1234
Please Suggest and help
Regards
Yogesh Kumar
Re: Hive upload
Posted by Bejoy Ks <be...@yahoo.com>.
Hi Yogesh
Looks like Sqoop import from rdbms to hdfs is getting successful but is failing at hive create table. You are seeing data in hive ware house because you have specified that as your target dir in sqoop import (--target-dir /user/hive/warehouse/new). It is recommended to use a different target dir while doing sqoop import other than the hive warehouse dir.
Can you post in the full console log of sqoop with --verbose logging enabled. It can give some clues.
With the second issue, You already have your data in '/user/hive/warehouse/letstry/' which is the location for the hive table 'letstry'. Why you still want to do a LOAD DATA again in there?
If you are doing a SQOOP import of that, Again it is recommended to use a different target dir other than hive ware house dir. It'll help you avoid some confusions as well.
________________________________
From: yogesh dhari <yo...@live.com>
To: hive request <us...@hive.apache.org>
Sent: Wednesday, July 4, 2012 1:40 PM
Subject: RE: Hive upload
Hi Bejoy,
Thank you very much for your response,
1)
A) When I run command show tables it doesn't show newhive table.
B) Yes the the newhive directory is present into /user/hive/warehouse and also containing the values imported from RDBMS
Please suggest and give me an example for the sqoop import command according to you for this case.
2)
A) Here is the command
describe formatted letstry;
OK
# col_name data_type comment
rollno int None
name string None
numbr int None
sno int None
# Detailed Table Information
Database: default
Owner: mediaadmin
CreateTime: Tue Jul 03 17:06:27 GMT+05:30 2012
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost:9000/user/hive/warehouse/letstry
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1341315550
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.101 seconds
B) hadoop dfs -ls /user/hive/warehouse/letstry/
Found 1 items
-rw-r--r-- 1 mediaadmin supergroup 17 2012-07-02 12:05 /user/hive/warehouse/letstry/part-m-00000
hadoop dfs -cat /user/hive/warehouse/letstry/part-m-00000
1,John,123,abc,2
Here data is present but when I upload it to Hive it gets deleted from HDFS and in Hive value appers NULL instead of ( 1,John,123,abc,2). and I didn't understad your point regarding correct data format? ( this data was imported from Mysql table)
And what kind of confugration neede in sqoop
Please suggest and help
Greetings
Yogesh Kumar
________________________________
Subject: Re: Hive upload
To: user@hive.apache.org
From: bejoy_ks@yahoo.com
Date: Wed, 4 Jul 2012 05:58:41 +0000
Hi Yogesh
The first issue (sqoop one).
1) Is the table newhive coming when you list tables using 'show table'?
2) Are you seeing a directory 'newhive' in your hive warte house dir(usually /usr/hive/warehouse)?
If not sqoop is failing to create hive tables /load data into them. Only sqoop import to hdfs is getting successful the hive part is failing.
If hive in stand alone mode works as desired you need to check the sqoop configurations.
Regarding the second issue, can you check the storage location of NewTable and check whether there are files within. If so then do a 'cat' of those files and see whether it has the correct data format.
You can get the location of your table from the following command
describe formatted NewTable;
Regards
Bejoy KS
Sent from handheld, please excuse typos.
________________________________
From: yogesh dhari <yo...@live.com>
Date: Wed, 4 Jul 2012 11:09:02 +0530
To: hive request<us...@hive.apache.org>
ReplyTo: user@hive.apache.org
Subject: Hive upload
Hi all,
I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouse
I want it to be present into hive, I used this command
sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new
And another thing is,
If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table,
If I run command
Select * from NewTable;
it reflects
Null Null Null Null
although the real data is
Yogesh 4 Bangalore 1234
Please Suggest and help
Regards
Yogesh Kumar
RE: Hive upload
Posted by yogesh dhari <yo...@live.com>.
Hi Bejoy,
Thank you very much for your response,
1)
A) When I run command show tables it doesn't show newhive table.B) Yes the the newhive directory is present into /user/hive/warehouse and also containing the values imported from RDBMS
Please suggest and give me an example for the sqoop import command according to you for this case.
2)
A) Here is the command
describe formatted letstry;
OK
# col_name data_type comment
rollno int None
name string None
numbr int None
sno int None
# Detailed Table Information
Database: default
Owner: mediaadmin
CreateTime: Tue Jul 03 17:06:27 GMT+05:30 2012
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost:9000/user/hive/warehouse/letstry
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1341315550
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.101 seconds
B) hadoop dfs -ls /user/hive/warehouse/letstry/
Found 1 items
-rw-r--r-- 1 mediaadmin supergroup 17 2012-07-02 12:05 /user/hive/warehouse/letstry/part-m-00000
hadoop dfs -cat /user/hive/warehouse/letstry/part-m-00000
1,John,123,abc,2
Here data is present but when I upload it to Hive it gets deleted from HDFS and in Hive value appers NULL instead of ( 1,John,123,abc,2). and I didn't understad your point regarding correct data format? ( this data was imported from Mysql table)And what kind of confugration neede in sqoop
Please suggest and help
GreetingsYogesh Kumar
Subject: Re: Hive upload
To: user@hive.apache.org
From: bejoy_ks@yahoo.com
Date: Wed, 4 Jul 2012 05:58:41 +0000
Hi Yogesh
The first issue (sqoop one).
1) Is the table newhive coming when you list tables using 'show table'?
2) Are you seeing a directory 'newhive' in your hive warte house dir(usually /usr/hive/warehouse)?
If not sqoop is failing to create hive tables /load data into them. Only sqoop import to hdfs is getting successful the hive part is failing.
If hive in stand alone mode works as desired you need to check the sqoop configurations.
Regarding the second issue, can you check the storage location of NewTable and check whether there are files within. If so then do a 'cat' of those files and see whether it has the correct data format.
You can get the location of your table from the following command
describe formatted NewTable;
Regards
Bejoy KS
Sent from handheld, please excuse typos.From: yogesh dhari <yo...@live.com>
Date: Wed, 4 Jul 2012 11:09:02 +0530To: hive request<us...@hive.apache.org>ReplyTo: user@hive.apache.org
Subject: Hive upload
Hi all,
I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouseI want it to be present into hive, I used this command
sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new
And another thing is,If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table,
If I run command Select * from NewTable;it reflects
Null Null Null Null
although the real data is
Yogesh 4 Bangalore 1234
Please Suggest and help
RegardsYogesh Kumar
Re: Hive upload
Posted by Bejoy KS <be...@yahoo.com>.
Hi Yogesh
The first issue (sqoop one).
1) Is the table newhive coming when you list tables using 'show table'?
2) Are you seeing a directory 'newhive' in your hive warte house dir(usually /usr/hive/warehouse)?
If not sqoop is failing to create hive tables /load data into them. Only sqoop import to hdfs is getting successful the hive part is failing.
If hive in stand alone mode works as desired you need to check the sqoop configurations.
Regarding the second issue, can you check the storage location of NewTable and check whether there are files within. If so then do a 'cat' of those files and see whether it has the correct data format.
You can get the location of your table from the following command
describe formatted NewTable;
Regards
Bejoy KS
Sent from handheld, please excuse typos.
-----Original Message-----
From: yogesh dhari <yo...@live.com>
Date: Wed, 4 Jul 2012 11:09:02
To: hive request<us...@hive.apache.org>
Reply-To: user@hive.apache.org
Subject: Hive upload
Hi all,
I am trying to upload the tables from RDBMS to hive through sqoop, hive imports successfully. but i didn't find any table in hive that imported table gets uploaded into hdfs idr /user/hive/warehouseI want it to be present into hive, I used this command
sqoop import --connect jdbc:mysql://localhost:3306/Demo --username sqoop1 --password SQOOP1 -table newone --hive-table newhive --create-hive-table --hive-import --target-dir /user/hive/warehouse/new
And another thing is,If I upload any file or table from HDFS or from Local then its uploads but data doesn't show in Hive table,
If I run command Select * from NewTable;it reflects
Null Null Null Null
although the real data is
Yogesh 4 Bangalore 1234
Please Suggest and help
RegardsYogesh Kumar