Used Sqoop to import Mysql table (Employees) onto HDP. Now, I would like to create a Hive ORC Employees Table based on it with the as is schema and data on HDP. How can I do it?

[#1]

My first step was to create an ORC table on Hive with the same schema as the one imported on hdp :

CREATE TABLE IF NOT EXISTS orcemployees( emp_no int, birth_date date,
 first_name string, last_name string, gender string, hire_date date) 
STORED AS ORC;

My second step was to copy the data from employees into orcemployees:

insert into table orcemployees select * from employees;

The problem is when I execute select * from orcemployees; the schema is displayed and not the associated data.

[#2]

ok @Ancil McBarnett -- Thanks for your ideas. I was able to do a sqoop import of the tables using --hive-import onto hive and then I created a orc table. Is there anyway I can access these imported files on hive? Because I'm able to find the files I imported on hdp but not hive. hdfs-imported-tables.png

[#3]

Create table pointing to this directory and query in the Ambari Hive view @keerthana gajarajakumar

[#4]

@Artem Ervits -- Thanks. Any suggestions how I can do it? because I was unable to read from this location with the code I wrote.

[#5]

@Ancil McBarnett & @Artem Ervits -- Thanks a lot for taking the time to reply. I tried creating an external table to point to the hdfs location I'm getting the following error (please find the image below). Besides, I entered these lines randomly .. FIELDS TERMINATED BY '\t' STORED AS TEXTFILE. I'm not sure if it's tab separated nor saved as a text file. How can I view the mysql tables imported into hive or hdfs?? I'm unable to open it.

error.png

[#6]

I assume this is a completely different question right as you are able to view the hive table since you used the --hive-import Sqoop option, and you are now able to see the ORC table you created subsequently and query the data after accomplishing an "INSERT OVERWRITE TABLE" right?

If you are just asking how can I import an existing HDFS file/ folder into a Hive Table, then @Artem Ervits is correct. Just do for example create external table employee2 LOCATION '/tmp/task/employees' and query using Hive view. For syntax see

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

[#7]

@keerthana gajarajakumar

CREATE EXTERNAL TABLE tweets (

createddate string,

geolocation string,

tweetmessage string,

`user` struct<geoenabled:boolean, id:int, name:string, screenname:string, userlocation:string>)

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'

LOCATION '/user/root/';

[#8]

You can also review the docs here:

http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.4/bk_dataintegration/content/moving_data_from_...

[#9]

I reviewed these links. I can follow those steps if my data has been imported to hdp and I want to create an orctable in hive. But what you're suggesting is to import the data on hive and then create an orc table.

[#10 · Accepted solution]

@keerthana gajarajakumar

your error says you don't have permissions for admin user on that folder. You need to

sudo -u hdfs hdfs dfs -chown -R admin:hdfs /tmp/task/employees

OR

sudo -u hdfs hdfs dfs -chmod -R 777 /tmp/task/employees

[#11]

@Artem Ervits - I was able to execute the query for external table creation.. thanks:)