Shulou(Shulou.com)06/01 Report--

This article mainly explains "the mutual derivation of data between MySql and HBase". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and study and learn "the mutual derivation of data between MySql and HBase" together!

Before proceeding to the following steps, make sure that your hadoop, hbase, hive, mysql, sqoop are installed and configured properly

My configuration here is as follows:

hadoop-1.2.1

hbase-0.94.18

hive-0.12.0

mysql-5.5.37

sqoop-1.4.4

I. Importing data from mysql into hbase is relatively easy

1. Create a teacher table in mysql and insert 5 groups of data. I didn't build the table too big here. You can change the size of the table according to the size of your own data. The structure of the table is shown in the following figure:

2. Before proceeding to the next step, make sure that jdbc of mysql has been copied to lib directory of sqoop. Here I use mysql-connector-java-5.0.8-bin.jar. At the same time, copy hbase-0.94.18.jar in hbase directory and zookeeper-3.4.5.jar in lib directory of hbase to lib directory of sqoop. The above jar package, as long as it is suitable for your version, is good.

3. Go to the directory of sqoop and execute the command:

$bin/sqoop import --connect jdbc:mysql://slave03/sqoop --username sqoop --password sqoop --table teacher --hbase-table myteacherhbase --column-family info --hbase-create-table

Meaning of the above command line:

$bin/sqoop import --connect jdbc:mysql://slave03 (own machine name, can be own machine ip address)/sqoop (database in mysql)--username username--password password--table teacher (table name in mysql)--hbase-table myteacherhbase (name of table imported into hbase)--column-family info --hbase-create-table (make hbase create table itself)

4.1 Status of tables in hbase before executing the above command:

4.2 Status of tables in hbase after executing sqoop command:

5. So far, the data in mysql database has been successfully imported into hbase.

II. Export data from hbase to mysql database.

Because I have searched a lot of information on the Internet before, I understand that the data in hbase cannot be directly exported to mysql database, so I can only use a mediation to achieve this purpose. I understand that there are two ways to achieve this function:

(1) Export the data in hbase to HDFS platform, and then import it to mysql (I haven't tried this method yet, just learned that some people on the Internet say it can)

(2) Export the data in hbase to hive (mediation), and then import the data in hive into mysql database through sqoop.

Next, I introduce the second method:

Since I imported the teacher table from mysql into hbase, I'll use the myteacherhbase table from hbase to do the following.

Create a new myteacher table in mysql to receive data exported by hbase.

The table structure is shown in the figure below:

Create an external table in hive that joins hbase:

hive> create external table myteacher_external(key int,value string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES ("hbase.columns.mapping" = "info:name") > TBLPROPERTIES("hbase.table.name" = "myteacherhbase");

Here I directly specify the primary key when creating the external table, and set the id to the primary key. As shown in the figure:

You can query the data in hbase and successfully create the external table of hbase.

5. Create an internal table in hive and import the data from hive's external external table into the internal table.

hive>create table myteacher_inner(id int,name string);

Once built, export the data from hbase to hive with the following command:

hive> insert overwrite table myteacher_inner select * from myteacher_external;

As shown below:

There is data in the corresponding internal table, indicating that hbase is successfully exported to hive.

6. Next, use sqoop to import the data in hive into mysql, go to the sqoop directory, and execute the following command:

bin/sqoop export --connect jdbc:mysql://native ip/sqoop --username sqoop --password sqoop -m 1 --table myteacher --export-dir hdfs://slave03:9000/home/hadoop/hive/warehouse/myteacher_inner --input-null-string "\\\N" --input-null-non-string "\\\N" --input-fields-terminated-by "\\01" --input-lines-terminated-by "\\n"

Note: SQOOP after IP address is mysql database, followed by username and password, m1 is a map, table myteacher is an empty table established before receiving hbase data, followed by--export-dir is HDFS address of table in hive of this machine. After this address, it is processed to prevent NULL phenomenon when imported into mysql, because the interval between data stored in hive is inconsistent with that stored in mysql. So it needs to be dealt with. Make mysql recognizable.

SQOOP Command executed successfully.

7. View the data in myteacher at mysql.

Query successful. Import successful, operation completed!

Thank you for your reading, the above is the content of "data mutual conduction between MySql and HBase", after learning this article, I believe that everyone has a deeper understanding of the problem of data mutual conduction between MySql and HBase, and the specific use needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.