Sqoop Import RDBMS Table into HDFS

• 
  
• Before moving the RDBMS Table data from Relational data base to Hadoop HDFS, We need to ensure whether table along with the data exists or not.
• use SQOOP IMPORT to import the Relational table data into HDFS.

For example:

1 2 3 4 5 6 7 8

sqoop import  \ --connect 'jdbc:mysql://localhost/StudentInfo' \ --table student \ --username root \ --password cloudera \ --split-by std-id \ --m 1 \ --target-dir '/user/cloudera/student';

Practical Steps

Let us practically try with an example for this scenario.

Step 1: Log in to Mysql database:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[cloudera@quickstart ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.1.66 Source distribution Copyright (c) 2000, 2012, 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>

Step 2: Create a Database called StudentInfo.

1 2 3

mysql> create database StudentInfo     -> ; Query OK, 1 row affected (0.05 sec)

Step 3: Use the newly created Databases

1 2	mysql> use StudentInfo; Database changed

step 4: Create a table called student in Mysql

1 2	mysql> create table student( std_id integer, std_name varchar(43)); Query OK, 0 rows affected (0.06 sec)

step 5: Insert values into student using Mysql INSERT command and then using select check whether the values are properly inserted or not.

1 2 3 4 5 6 7 8 9 10 11 12

mysql> insert into student values (101,'javachainAnto'), (102,'kumar'),(103,'sofia'); mysql> select * from student; +--------+---------------+ | std_id | std_name      | +--------+---------------+ |    101 | javachainAnto | |    102 | kumar         | |    103 | Sofia         | +--------+---------------+ 3 rows in set (0.00 sec)

Step 6: Use SQOOP IMPORT to import the above RDBMS table into HDFS

Use the following SQOOP IMPORT command :

1 2 3 4 5 6 7 8

sqoop import  \ --connect 'jdbc:mysql://localhost/StudentInfo' \ --table student \ --username root \ --password cloudera \ --split-by std-id \ --m 1 \ --target-dir '/user/cloudera/student';

You can see the SQOOP Import execution like something below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

[cloudera@quickstart ~]$ sqoop import --connect 'jdbc:mysql://localhost/StudentInfo' --table student --username root --password cloudera --target-dir '/user/cloudera/student' --split-by std_id; Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation. 15/07/20 15:31:51 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.4.0 15/07/20 15:31:51 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead. 15/07/20 15:31:52 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 15/07/20 15:31:52 INFO tool.CodeGenTool: Beginning code generation 15/07/20 15:31:52 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1 15/07/20 15:31:52 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `student` AS t LIMIT 1 15/07/20 15:31:52 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/lib/hadoop-mapreduce Note: /tmp/sqoop-cloudera/compile/e75f4f6477a8fd6d642b79f42757c3c9/student.java uses or overrides a deprecated API. Note: Recompile with -Xlint:deprecation for details. 15/07/20 15:31:55 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-cloudera/compile/e75f4f6477a8fd6d642b79f42757c3c9/student.jar 15/07/20 15:31:55 WARN manager.MySQLManager: It looks like you are importing from mysql. 15/07/20 15:31:55 WARN manager.MySQLManager: This transfer can be faster! Use the --direct 15/07/20 15:31:55 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path. 15/07/20 15:31:55 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql) 15/07/20 15:31:55 INFO mapreduce.ImportJobBase: Beginning import of student 15/07/20 15:31:55 INFO Configuration.deprecation: mapred.job.tracker is deprecated. Instead, use mapreduce.jobtracker.address 15/07/20 15:31:56 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar 15/07/20 15:31:57 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps 15/07/20 15:31:57 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032 15/07/20 15:32:01 INFO db.DBInputFormat: Using read commited transaction isolation 15/07/20 15:32:01 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`std_id`), MAX(`std_id`) FROM `student` 15/07/20 15:32:01 INFO mapreduce.JobSubmitter: number of splits:3 15/07/20 15:32:01 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1437428820805_0001 15/07/20 15:32:02 INFO impl.YarnClientImpl: Submitted application application_1437428820805_0001 15/07/20 15:32:02 INFO mapreduce.Job: The url to track the job: http://quickstart.cloudera:8088/proxy/application_1437428820805_0001/ 15/07/20 15:32:02 INFO mapreduce.Job: Running job: job_1437428820805_0001 15/07/20 15:32:20 INFO mapreduce.Job: Job job_1437428820805_0001 running in uber mode : false 15/07/20 15:32:21 INFO mapreduce.Job:  map 0% reduce 0% 15/07/20 15:32:53 INFO mapreduce.Job:  map 100% reduce 0% 15/07/20 15:32:54 INFO mapreduce.Job: Job job_1437428820805_0001 completed successfully 15/07/20 15:32:54 INFO mapreduce.Job: Counters: 30 File System Counters FILE: Number of bytes read=0 FILE: Number of bytes written=405708 FILE: Number of read operations=0 FILE: Number of large read operations=0 FILE: Number of write operations=0 HDFS: Number of bytes read=331 HDFS: Number of bytes written=38 HDFS: Number of read operations=12 HDFS: Number of large read operations=0 HDFS: Number of write operations=6 Job Counters Launched map tasks=3 Other local map tasks=3 Total time spent by all maps in occupied slots (ms)=90633 Total time spent by all reduces in occupied slots (ms)=0 Total time spent by all map tasks (ms)=90633 Total vcore-seconds taken by all map tasks=90633 Total megabyte-seconds taken by all map tasks=92808192 Map-Reduce Framework Map input records=3 Map output records=3 Input split bytes=331 Spilled Records=0 Failed Shuffles=0 Merged Map outputs=0 GC time elapsed (ms)=1470 CPU time spent (ms)=2880 Physical memory (bytes) snapshot=315695104 Virtual memory (bytes) snapshot=4515340288 Total committed heap usage (bytes)=182255616 File Input Format Counters Bytes Read=0 File Output Format Counters Bytes Written=38 15/07/20 15:32:54 INFO mapreduce.ImportJobBase: Transferred 38 bytes in 57.0157 seconds (0.6665 bytes/sec) 15/07/20 15:32:54 INFO mapreduce.ImportJobBase: Retrieved 3 records.

Now let us see the output on our Command shell:

1 2 3 4 5 6 7 8 9 10 11

[cloudera@quickstart ~]$ hadoop fs -ls /user/cloudera/student Found 2 items -rw-r--r--   1 cloudera supergroup          0 2015-07-20 15:44 /user/cloudera/student/_SUCCESS -rw-r--r--   1 cloudera supergroup         38 2015-07-20 15:44 /user/cloudera/student/part-m-00000 [cloudera@quickstart ~]$ hadoop fs -cat  /user/cloudera/student/part-m-00000 101,javachainAnto 102,kumar 103,Sofia [cloudera@quickstart ~]$

How to: Oozie + Sqoop + Hive

Importing data directly into Hive is a great way to quickly enable your Hadoop desires. Some times it’s useful to schedule such a job in an Oozie workflow. In this post, we’ll investigate using Sqoop to import data from MySQL into Hive executed by Oozie.

THE BASICS

Using Sqoop to import data into hive via Oozie can be accomplished in 3 easy steps:

1. Copy your hive-site.xml into HDFS
2. Copy your mysql JDBC jar into HDFS
3. Create an Oozie workflow that has a Sqoop action, includes the hive-site.xml in a “file” element of the action, and includes the mysql JDBC jar in a “archive” element of the action
4. Run your Oozie workflow

EXAMPLE

Here is an example workflow:

<workflow-app name="sqoop-to-hive" xmlns="uri:oozie:workflow:0.4">
    <start to="sqoop2hive"/>
    <action name="sqoop2hive">
        <sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <command>import --connect jdbc:mysql://mysql.example.com/sqoop --username sqoop --password sqoop --table test --hive-import --hive-table test</command>
            <archive>/tmp/mysql-connector-java-5.1.31-bin.jar#mysql-connector-java-5.1.31-bin.jar</archive>
            <file>/tmp/hive-site.xml#hive-site.xml</file>
        </sqoop>
        <ok to="end"/>
        <error to="kill"/>
    </action>
    <kill name="kill">
        <message>Action failed</message>
    </kill>
    <end name="end"/>
</workflow-app>

And its corresponding hive-site.xml:

<configuration>

  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:derby:;databaseName=/var/lib/hive/metastore/metastore_db;create=true</value>
    <description>JDBC connect string for a JDBC metastore</description>
  </property>

  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://sqoop2.example.com:9083</value>
  </property>

</configuration>

KEY POINTS

1. Hive needs to be configured to use a remote metastore. This is because Sqoop will be ran from any node in your MapReduce cluster. hive-site.xml should be included since Sqoop needs to be able to tell Hive which metastore to write to at minimum. To do this, we set hive.metastore.uris in the hive-site.xml. In the above example, hive.metastore.uris is set to “thrift://sqoop2.example.com:9083″.
2. hive-site.xml should be uploaded to HDFS and included in the workflow.xml. In the above example, the hive-site.xml file has been uploaded to /tmp/hive-site.xml in HDFS.
3. The MySQL JDBC jar should be uploaded to HDFS and included in the workflow.xml. In the above example, the MySQL JDBC jar file has been uploaded to /tmp/mysql-connector-java-5.1.31-bin.jar.
4. Sqoop first imports all data into HDFS, then imports that data into Hive. To do so, Sqoop creates a hive script file and calls the the hive command line directly.
5. Make sure to include the share lib by setting oozie.use.system.libpath to “true” in the oozie job configuration.

TROUBLE SHOOTING

Q: FILE ALREADY EXISTS EXCEPTION IN TASK LOGS:

ERROR org.apache.sqoop.tool.ImportTool  - Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory <path> already exists

A: Sqoop imports into hive first transfers the data to ‘/user/<username>/<table>’ in HDFS, then imports the data into Hive. If this process failed before, then the import directory may already exist. The solution is to remove that directory.

Q: TABLE ALREADY EXISTS EXCEPTION IN TASK LOGS:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. AlreadyExistsException(message:Table test already exists)

A: The –create-hive-table option is being used and the table already exists in Hive. The table can either be dropped or the –create-hive-table option can be removed from the command.

Q: HIVE IMPORT IS STARTING, BUT SEEING AN ERROR BEFORE FINISHING.

Typically log messages like:

INFO  org.apache.sqoop.hive.HiveImport  - Loading uploaded data into Hive

before:

Intercepting System.exit(1)

are seen.
A: Normally, the hive-site.xml is missing, not in workflow.xml, or not correctly configured. See the example and “key points” section above.

Q: CLASS NOT FOUND EXCEPTION IN THE LAUNCHER JOB LOGS:

java.lang.ClassNotFoundException: Class org.apache.oozie.action.hadoop.SqoopMain not found

A: The share lib hasn’t been included! It can be included by including oozie.use.system.libpath=true in the job configuration passed when submitting the job.

Q: MISSING DRIVER CLASS IN LAUNCHER JOB LOGS:

ERROR org.apache.sqoop.Sqoop  - Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver

A: Driver can’t be found. Including the JDBC driver jar in “archives” section of your workflow should fix this.

SUMMARY

Topics covered include an overview of “key points”, basic troubleshooting, and a simple walk through to Sqoop data from MySQL to Hive. Hopefully this helps you schedule your sqoop to hive jobs.

The code examples are available at https://github.com/ingesttips/examples.

Importing data from RDBMS into Hive using create-hive-table of sqoop

Importing data from RDBMS into Hive using create-hive-table of sqoop

In the Importing data from RDBMS into Hive i blogged about how to import data from RDBMS into Hive using Sqoop. In that case the import command took care of both creating table in Hive based on RDMBS table as well as importing data from RDBMS into Hive. But Sqoop can also be used to import data stored in HDFS text file into Hive. I wanted to try that out, so what i did is i created the contact table in Hive manually and then used the contact table that i exported as text file into HDFS as input

1. First i used sqoop import command to import content of Contact table into HDFS as text file. By default sqoop will use , for separating columns and newline for separating

   
   sqoop import --connect jdbc:mysql://macos/test --table contact -m 1
   

   After import is done i can see content of the text file by executing hdfs dfs -cat contact/part-m-00000 like this
   
2. After that you can use sqoop to create table into hive based on schema of the CONTACT table in RDBMS. by executing following command

   
   sqoop create-hive-table --connect jdbc:mysql://macos/test --table Address --fields-terminated-by ','
   

3. Last step is to use Hive for loading content of contact text file into contact table. by executing following command.

   
   LOAD DATA INPATH 'contact' into table contact;

OBIEE platform DBUSER password reset

Often you may find that when you come to start and OBIEE environment, it may fail with errors relating to the fact the OBIEE platform and mds accounts are either locked, or the stored passwords within the OBIEE configuration are out of step with whats held in the database.  You may see errors in the bi_server.log file such as :-

creating connection for pool “rtd_datasource”: ORA-28000: the account is locked

<BEA-001129> <Received exception while creating connection for pool “mds-owsm”: ORA-28001: the password has expired

When querying the DBA_USERS table within the database, you may discover the accounts are locked/expired :-

SQL> select username,account_status from dba_users where username like ‘DEV%';
USERNAME                    ACCOUNT_STATUS
—————————— ——————————–
DEV_BIPLATFORM  EXPIRED
DEV_MDS                        EXPIRED

If you do know the password, you can get away with reseting the account with the same password i.e.

alter user DEV_MDS identified by <original_password>;

alter user DEV_BIPLATFORM identified by <original_password>;     

You may not know the password, so you may end up having to reset the password as listed below.

alter user DEV_MDS identified by DEV_MDS;
alter user DEV_BIPLATFORM identified by DEV_BIPLATFORM;

Once reset, you will beable to test via a SQL * Plus connection.
However the various connections configured within OBIEE will need to be reset before you can successfully restart the OBIEE environment. The following steps below take you through the areas you will need to reset the DEV_MDS and DEV_BIPLATFORM password if you have had to reset the password with a new password in the steps above

Login to the Web Logic Console i.e. http://hlcdb01:7001/console
navigate to bifoundation_domain –> Services –> Data Sources. Check each data source for where the passwords are storeed typically you will need to modify these :-

bip_datasource > connection pool tab, reset the password for DEV_BIPLATFORM
EPMSystemRegistry > connection pool tab, reset the password for DEV_BIPLATFORM
mds_owsm > connection pool tab, reset the password for DEV_MDS

Click save and apply, a full restart will be required once all steps are performed.

Next step is to Login to the OBIEE Enterprise Manager Console i.e. http://hlcdb01:7001/em. You will need to reset the stored password for the NQS Scheduler, otherwise the service will fail to start

• Expand “Business intelligence” menu on the left;
• Click the “Deployment” tab;
• Click the “Scheduler” tab under deployment;
• Click “Lock and Edit Configuration” button;
• Set the password and hit “Apply”;
• Click the “Activate Changes” button;

Once all new passwords have been applied, a full restart, and all services should now start successfully

Oracle: Export empty tables

   

When taking a database dump from Oracle 11g recently, I found that the export did not include tables that had 0 rows. The Oracle11g instance implements a space saving measure where if your table has no data, it will not be exported. Space is allocated only when you add data to the table.

A workaround to the issue is to pre-allocate some space to such empty tables. Execute the following oracle queries to allocate space for all tables before taking the export:

select 'alter table '||table_name||' allocate extent;'
from dba_tables
where segment_created = 'NO'
and owner = 'DB_USER;

Running the above query will generate some alter table statements for all tables with empty data. Executing these SQL statements will allocate space to the empty tables and allow you to export and import such tables.