question Re: 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? in Archives of Support Questions (Read Only)

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?

keerthanakumar2 — Tue, 02 Feb 2016 06:33:33 GMT

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.

Re: 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?

aervits — Tue, 02 Feb 2016 06:42:09 GMT

Take a look at this example @keerthana gajarajakumar Link

Re: 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?

amcbarnett — Tue, 02 Feb 2016 06:42:13 GMT

@keerthana gajarajakumar

You have two options:

Use Sqoop to save directly as ORC file

$ sqoop import --connect jdbc:mysql://localhost/employees --username hive --password hive --table departments --hcatalog-database default --hcatalog-table my_table_orc --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"

Since you already created the Employee Table then create the ORC table first (employee_orc) and then select from your employee hive table and insert into employee_orc
```
create table employee_orc stored as orc as select * from employee_hive;
```

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 06:49:36 GMT

@Ancil McBarnett - Thanks for your reply. Are you suggesting to do a sqoop import of the employees table onto HDP or Hive?

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 06:50:03 GMT

thanks @Artem Ervits

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 06:55:59 GMT

The reason I'm asking you is , what's the difference between doing a --hive-import of the employees table and then copying that same data into hive orc employees table? I understand that ORC helps in performance improvement but can I see the difference visually when I do a select * from employees vs select * from employees_orc?

Re: 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?

aervits — Tue, 02 Feb 2016 06:57:42 GMT

@keerthana gajarajakumar performance in general is noticeable, orc is the way to go, no questions about it.

Re: 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?

amcbarnett — Tue, 02 Feb 2016 06:59:21 GMT

I just realized you had a comment under your original question. I will edit it so that it is the actual question. I was suggesting you can use Sqoop to DIRECTLY create an ORC Table. Try this also:

INSERT OVERWRITE TABLE orcemployees SELECT * FROM employees;

What is your output after you have done so?

Re: 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?

amcbarnett — Tue, 02 Feb 2016 07:05:06 GMT

You can also review the docs here:

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

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 07:16:23 GMT

@Ancil McBarnett

-- I'm having a bit of a confusion in interpreting your suggestions. Let me know if I have interpreted your answers well.

1. In both the following cases the tables you have referred to as 'employees' and 'employee_hive' is the mysql table imported using sqoop directly onto hive (sqoop import --connect jdbc:mysql://localhost:3306/employees --username xxx--password xxx --table employees --hive-import --driver com.mysql.jdbc.Driver). Am I correct?

2. Once I'm done with importing the employees table on hive mentioned in step 1, I can create an orcemployees table.

Am I right?

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 07:20:43 GMT

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.

Re: 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?

amcbarnett — Tue, 02 Feb 2016 07:41:55 GMT

@keerthana gajarajakumar

Let's see if I can make it clear. You asked how can I sqoop data into HDP/ Hive and then create an ORC table from it. I suggested the following:

INSTEAD of Sqoop Data into a table and THEN creating an ORC table you can simply create the ORC table directly with Sqoop - hence the following options in the Sqoop call
```
--hcatalog-database default --hcatalog-table my_table_orc --create-hcatalog-table --hcatalog-storage-stanza "stored as orcfile"
```
OR, you can use normal Sqoop without the ORC options, create a normal table as you have done and then create an OrcEmployees table.

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 08:02:20 GMT

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

Re: 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?

aervits — Tue, 02 Feb 2016 08:06:20 GMT

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

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 08:20:46 GMT

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

Re: 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?

amcbarnett — Tue, 02 Feb 2016 08:23:59 GMT

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

Re: 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?

aervits — Tue, 02 Feb 2016 08:44:48 GMT

@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/';

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 23:10:47 GMT

@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

Re: 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?

aervits — Tue, 02 Feb 2016 23:13:59 GMT

@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

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

Re: 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?

keerthanakumar2 — Tue, 02 Feb 2016 23:51:19 GMT

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