Severalnines Blog
The automation and management blog for open source databases

Archival and Analytics - Importing MySQL data into Hadoop Cluster using Sqoop

Posted in:

We won’t bore you with buzzwords like volume, velocity and variety. This post is for MySQL users who want to get their hands dirty with Hadoop, so roll up your sleeves and prepare for work. Why would you ever want to move MySQL data into Hadoop? One good reason is archival and analytics. You might not want to delete old data, but rather move it into Hadoop and make it available for further analysis at a later stage.

In this post, we are going to deploy a Hadoop Cluster and export data in bulk from a Galera Cluster using Apache Sqoop. Sqoop is a well-proven approach for bulk data loading from a relational database into Hadoop File System. There is also Hadoop Applier available from MySQL labs, which works by retrieving INSERT queries from MySQL master binlog and writing them into a file in HDFS in real-time (yes, it applies INSERTs only).

We will use Apache Ambari to deploy Hadoop (HDP 2.1) on three servers. We have a clustered Wordpress site running on Galera, and for the purpose of this blog, we will export some user data to Hadoop for archiving purposes. The database name is wordpress, we will use Sqoop to import the data to a Hive table running on HDFS. The following diagram illustrates our setup:

The ClusterControl node has been installed with an HAproxy instance to load balance Galera connections and listen on port 33306.


All hosts are running CentOS 6.5 with firewall and SElinux turned off. All servers’ time are using NTP server and synced with each other. Hostname must be FQDN or define your hosts across all nodes in /etc/hosts file. Each host has been configured with the following host definitions:		clustercontrol haproxy mysql		mysql1 galera1		mysql2 galera2		mysql3 galera3		hadoop1		hadoop2		hadoop3

Create an SSH key and configure passwordless SSH on hadoop1 to other Hadoop nodes to automate the deployment by Ambari Server. In hadoop1, run following commands as root:

$ ssh-keygen -t rsa # press Enter for all prompts
$ ssh-copy-id -i ~/.ssh/id_rsa
$ ssh-copy-id -i ~/.ssh/id_rsa
$ ssh-copy-id -i ~/.ssh/id_rsa

On all Hadoop hosts, install and configure NTP as well as openSSL libraries:

$ yum install ntp openssl openssl-devel -y
$ chkconfig ntpd on
$ service ntpd start
$ ntpdate -u

Deploying Hadoop

  1. Install Ambari Server on one of the Hadoop nodes (we chose, this will help us deploy the Hadoop cluster. Configure Ambari repository for CentOS 6 and start the installation:

    $ cd /etc/yum.repos.d
    $ wget
    $ yum -y install ambari-server
  2. Setup and start ambari-server:

    $ ambari-server setup # accept all default values on prompt
    $ ambari-server start

    Give Ambari a few minutes to bootstrap before accessing the web interface at port 8080.

  3. Open a web browser and navigate to Login with username and password ‘admin’. This is the Ambari dashboard, it will guide us through the deployment. Assign a cluster name and click Next.

  4. At the Select Stack step, choose HDP2.1:

  5. Specify all Hadoop hosts in the Target Hosts fields. Upload the SSH key that we have generated in the Prerequisites section during passwordless SSH setup and click Register and Confirm:

  6. This page will confirm that Ambari has located the correct hosts for your Hadoop cluster. Ambri will check those hosts to make sure they have the correct directories, packages, and processes to continue the install. Click Next to proceed.

  7. If you have enough resources, just go ahead and install all services:

  8. In Assign Master page, we let Ambari choose the configuration for us before clicking Next:

  9. In Assign Slaves and Clients page, we’ll enable all clients and slaves on each of our Hadoop hosts:

  10. Hive, Oozie and Nagios might requires further input like database password and administrator email. Specify the needed information accordingly and click Next.

  11. You will be able to review your configuration selection before clicking Deploy to start the deployment:

    When Successfully installed and started the services appears, choose Next. On the summary page, choose Complete. Hadoop installation and deployment is now complete. Verify that all services are running correctly:

We can now proceed to import some data from our Galera cluster as described in the next section.

Single Console for Your Entire Database Infrastructure
Deploy, manage, monitor, scale your databases on the technology stack of your choice!

Importing MySQL Data using Sqoop to Hive

Before importing any MySQL data, we need to create a target table in Hive. This table will have a similar definition as the source table in MySQL as we are importing all columns at the same time. Here is MySQL’s CREATE TABLE statement:

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)

SSH into any Hadoop node (since we installed Hadoop clients on all nodes) and switch to hdfs user:

$ su - hdfs

Enter into Hive console:

$ hive

Create a Hive database and table, similar to our MySQL table (Hive does not support DATETIME data type, so we are going to replace it with TIMESTAMP):

hive> CREATE SCHEMA wordpress;
hive> USE wordpress;
user_login VARCHAR(60),
user_pass VARCHAR(64),
user_nicename VARCHAR(50),
user_email VARCHAR(100),
user_url VARCHAR(100),
user_registered TIMESTAMP,
user_activation_key VARCHAR(60),
user_status INT,
display_name VARCHAR(250)
hive> exit;

Now we can start to import the wp_users MySQL table into Hive’s users table, connecting to MySQL nodes through HAproxy (port 33306):

$ sqoop import \
--connect jdbc:mysql:// \
--username=wordpress \
--password=password \
--table=wp_users \
--hive-import \
--hive-table=wordpress.users \
--target-dir=wp_users_import \

We can track the import progress from the Sqoop output:

INFO mapreduce.ImportJobBase: Beginning import of wp_users
INFO mapreduce.Job: Job job_1400142750135_0020 completed successfully
Time taken: 10.035 seconds
Loading data to table wordpress.users
Table wordpress.users stats: [numFiles=5, numRows=0, totalSize=240814, rawDataSize=0]
Time taken: 3.666 seconds

You should see that an HDFS directory wp_users_import has been created (as specified in --target-dir in the Sqoop command) and we can browse its files using the following commands:

$ hdfs dfs -ls
$ hdfs dfs -ls wp_users_import
$ hdfs dfs -cat wp_users_import/part-m-00000 | more

Now let’s check our imported data inside Hive:

$ hive -e 'SELECT * FROM wordpress.users LIMIT 10'
Logging initialized using configuration in file:/etc/hive/conf.dist/
2	admin	$P$BzaV8cFzeGpBODLqCmWp3uOtc5dVRb.	admin		2014-05-15 12:53:12		0	admin
5	SteveJones	$P$BciftXXIPbAhaWuO4bFb4LVUN24qay0	SteveJones	demouser2@		2014-05-15 12:57:59		0	Steve
8	JanetGarrett	$P$BEp8IY1zvvrIdtPzDiU9D/br.FtzFa1	JanetGarrett	demouser3@		2014-05-15 12:57:59		0	Janet
11	AnnWalker	$P$B1wix5Xn/15o06BWyHa.r/cZ0rwUWQ/	AnnWalker	demouser4@		2014-05-15 12:57:59		0	Ann
14	DeborahFields	$P$B5PouJkJdfAucdz9p8NaKtS9WoKJu01	DeborahFields	demouser5@		2014-05-15 12:57:59		0	Deborah
17	ChristopherMitchell	$P$Bi/VWI1W4iP7h9mC0SXd4f.kKWnilH/	ChristopherMitchell	demouser6@		2014-05-15 12:57:59		0	Christopher
20	HenryHolmes	$P$BrPHv/ZHb7IBYzFpKgauBl/2WPZAC81	HenryHolmes	demouser7@		2014-05-15 12:58:00		0	Henry
23	DavidWard	$P$BVYg0SFTihdXwDhushveet4n2Eitxp1	DavidWard	demouser8@		2014-05-15 12:58:00		0	David
26	WilliamMurray	$P$Bc8FmkMadsQZCsW4L5Vo8Xax2ex8we.	WilliamMurray	demouser9@		2014-05-15 12:58:00		0	William
29	KellyHarris	$P$Bc85yvlxvWQ4XxkeAgJRugOqm6S6au.	KellyHarris	demouser10@		2014-05-15 12:58:00		0	Kelly
Time taken: 16.282 seconds, Fetched: 10 row(s)

Nice! Now we can see that our data exists both in Galera and Hadoop. You can also use --query option in Sqoop to filter the data that you want to export to Hadoop using an SQL query. This is a basic example of how we can start to leverage Hadoop for archival and analytics. Welcome to big data!


Related Post

Database TCO - Calculating the Total Cost of Ownership for MySQL Management

This blog post will show you how to calculate the total cost of ownership (TCO) for database servers and how to optimize your cost.

MySQL Replication and GTID-based failover - A Deep Dive into Errant Transactions

This blog post covers deployment of replication cluster using ClusterControl. It also discusses in-depth a concept of errant transactions, how to detect them and how to fix issues related to them.

Newly Updated “MySQL Load Balancing with HAProxy” Tutorial

We have recently updated our tutorial on MySQL Load Balancing with HAProxy.  Read about deployment and configuration, monitoring, ongoing maintenance, health check methods, read-write splitting, redundancy with VIP and Keepalived and more.

MySQL Replication failover: Maxscale vs MHA (part 3)

This post describes how you can implement automated failover with MariaDB using Maxscale and MHA and compares the two with each other.

Posted in: