Data transfer between Hive, Sqoop, and MySQL

HDFs to MySQL

Csv/txt files to HDFs

MySQL to HDFs

Mapping of Hive to HDFs:

drop table if exists emp;
CREATE TABLE emp (IDintComment'ID', Emp_namestringComment'name', Jobstring) Comment'Career'row format delimited--stored asrcfile Location'/user/hive/warehouse/emp';

Stored as keyword, hive currently supports three different ways:
1: Is the most common textfile, the data does not compress, the disk overhead is big, the parsing cost is also big
2:squencefile,hadoop API provides a binary API approach, which is easy to use, can be segmented, compressible and so on.
3:rcfile row and column storage in a way, it will first block the data, to ensure that the same record on a block, to avoid reading a record need to read a plurality of blocks. Second, block data columnstore, convenient for data storage and fast column access.
Rcfile because of the use of column-type storage, it is expensive to load, but has a good query response, good compression ratio.
If the created table needs to be partitioned, the statement is as follows:
Here partitioned by indicates what field to split, usually by time

Hadoop commands view data under HDFs

Import the local data file into the HDFs:

Compare data to an HDFS path using Hadoop directives

Create External TableifNot exists EMP (IDintComment'User name', namestringComment'Month', JobstringComment'Number of visits') Comment'User Access Table' row format delimited fields terminated by "\ T" location "/user/hive/warehouse/test.db"; 

--Method 1.  -put/root/part-m-00000 /user/hive/warehouse/test.db

--Method 2.  "/root/part-m-00000" into table test.emp;

The above two methods achieve the same effect; note that the interval symbol "\ t" between data

Method 3: Use Sqoop to transfer data to HDFs < Sqoop is a data transfer tool between MySQL and HDFs >

Sqoop Import--connect Jdbc:mysql://--target-dir/user/hive/warehouse/test.db

Sqoop Import--connect Jdbc:mysql://localhost:3306/test--username root--password 123456--table person-- Append--target-dir/user/hive/warehouse/test.db

Save HDFs data to MySQL

--connect jdbc:mysql://----employee/emp/emp_data

Data transfer between hive, Sqoop, MySQL