Data synchronization between a relational database and a non-Relational Database
1. Without sqoop
MySQL --> hive
1. Use naivacat (Tool) to export tables in the database (primary TAB/T should be used for export)
2. Use winscp (Tool) to upload data to a specified Linux directory
3. Create Table in hive first (idfa string) Row format delimited fields terminated by '\ t '"
4. hive-e "load data local inpath 't1.txt 'into Table T1"
(If there is data in the table, truncate table hive table name is required. Execute 4)
Truncate table T1; (delete table data only)
Or hive-e "load data local inpath 't1.txt 'overwrite into Table T1 ";
Hive --> MySQL
1. hive-e "SQL statement;> name.txt" Export in home/dev
2. Then download to the local device using winscp
Ii. When sqoop is used
1. Decompress sqoop and configure the environment variables:
Add in/etc/profile: (cannot be modified without the root permission, so it can only be started in the sqoop/bin path)
Export $ sqoop_home =/home/hadoop/sqoop-1.4.3.
Export $ Path = $ sqoop_home/bin: $ path
After the configuration is complete, run source etc/profile
2. Decompress MySQL and put the mysql-connector-java-5.1.24-bin.jar in $ sqoop_home/lib.
After extracting sqoop, add the sqoop-1.4.3.jar under the root directory to the hadoop-2.2.0/lib.
3. list all database commands in the MySQL database
./Sqoop list-databases -- connect JDBC: mysql: // 222.99.11.52: 33221/-- username Dev -- password 1234
4. Copy the table structure of relational data to hive
./Sqoop create-hive-table -- connect JDBC: mysql: // 222.99.11.52: 33221/sampledata -- table T1 -- username Dev -- password 1234 -- hive-table/hive/warehouse/T1
5. Import files from a relational database to hive
./Sqoop import -- connect JDBC: mysql: /222.206.163.139: 33221/sampledata -- username Dev -- password mysql-Password -- table T1 -- hive-Import
6. Import table data in hive to MySQL
. /Sqoop export -- connect JDBC: mysql: // 222.206.163.139: 33221/sampledata -- username Dev -- password admin -- table uv_info -- export-DIR/user/hive/warehouse/UV/dt = Date
