Import RDBMS into Hive table stored as ORC with SQOOP
in the documentation, you can find on the Sqoop part
You can use Sqoop to import data into HDFS or directly into Hive. However, Sqoop can only import data into Hive as a text file or as a SequenceFile. To use the ORC file format, you must use a two-phase approach: first use Sqoop to move the data into HDFS, and then use Hive to convert the data into the ORC file format[...]
However, we can use the Sqoop-HCatalog integration feature, which is a table abstraction.
Let's use the HDP 2.3.2 sandbox, starting with creating our Hive table, stored as ORC:
[root@sandbox ~]# hive
hive> CREATE TABLE cds (id int, artist string, album string) STORED AS ORCFILE;
hive> INSERT INTO TABLE cds values (1,"The Shins","Port of Morrow");
hive> select * from cds;
1 The Shins Port of Morrow
Now let's build our MySQL table
[root@sandbox ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 85
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> CREATE TABLE albums (id INT, artist varchar(255), album varchar(255), primary key(id));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO albums VALUES (2, 'Family Of The Year', 'Loma Vista'), (3, 'Michel Petrucciani', 'Trio in Tokyo');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> GRANT ALL PRIVILEGES ON * . * TO 'newbie'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> exit;
Bye
Now the import part, with the added "--driver com.mysql.jdbc.Driver" to avoid error like
16/01/13 22:04:14 ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@4d574915 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
as described by @Mark Lochbihler in this HCC article
[root@sandbox ~]# sqoop import --connect jdbc:mysql://localhost/test --username newbie --table albums --hcatalog-table cds --driver com.mysql.jdbc.Driver
[...]
16/01/13 22:58:20 INFO mapreduce.ImportJobBase: Beginning import of albums
16/01/13 22:58:21 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM albums AS t WHERE 1=0
16/01/13 22:58:21 INFO hcat.SqoopHCatUtilities: Configuring HCatalog for import job
[...]
16/01/13 23:00:03 INFO mapreduce.ImportJobBase: Retrieved 2 records.
We retrieved our 2 records, let's check that in Hive :
hive> select * from cds;
OK
1 The Shins Port of Morrow
2 Family Of The Year Loma Vista
3 Michel Petrucciani Trio in Tokyo
Time taken: 11.485 seconds, Fetched: 3 row(s)
