Sqoop Common Commands

1. List all databases in MySQL database

Sqoop list-databases--connect jdbc:mysql://localhost:3306/-username Dyh-password 000000

2. Connect MySQL and list the tables in the database

Sqoop list-tables--connect jdbc:mysql://localhost:3306/test--username dyh--password 000000

3. Copy the table structure of the relational data into hive

Sqoop create-hive-table--connect jdbc:mysql://localhost:3306/test--table users--username dyh

--password 000000--hive-table users--fields-terminated-by "\0001"--lines-terminated-by "\ n";

parameter Description:

--fields-terminated-by "\0001" is to set the delimiter between each column, "\0001" is 1 in the ASCII code, it is also the default inline delimiter for hive, and the default inline delimiter for Sqoop is ","

--lines-terminated-by "\ n" sets the delimiter between each line, where the newline character is the default delimiter;

Note: Only the structure of the table is copied, the contents of the table are not copied
4. Import data from a relational database into a hive table

Sqoop import--connect jdbc:mysql://localhost:3306/test--username dyh--password 000000

--table users--hive-import--hive-table users-m 2--fields-terminated-by "\0001";

parameter Description:

-M 2 means that two map jobs are executed;

--fields-terminated-by "\0001" should be consistent with the creation of hive tables;

5. Import table data from hive into MySQL database table

Sqoop export--connect jdbc:mysql://192.168.20.118:3306/test--username dyh--password 000000

--table Users--export-dir/user/hive/warehouse/users/part-m-00000

--input-fields-terminated-by ' \0001 '

Attention:

1, before the import, the table Userst in MySQL must have been mentioned created.

2, Jdbc:mysql://192.168.20.118:3306/test in the IP address changed to localhost will report abnormal, specifically see my previous post

6. Import the data from the relational database into the Hive table, using the--query statement

Sqoop import--append--connect jdbc:mysql://192.168.20.118:3306/test--username dyh--password 000000--query "SELECT ID , age,name from Userinfos where \ $CONDITIONS "-M 1--target-dir/user/hive/warehouse/userinfos2--fields-terminated-by", ";

7. Import the data from the relational database into the Hive table,--columns the--where statement using

Sqoop import--append--connect jdbc:mysql://192.168.20.118:3306/test--username dyh--password 000000--table Userinfos --columns "Id,age,name"--where "ID > 3 and (age = in or age =)"-M 1--target-dir/user/hive/warehouse/userinfos 2--fields-terminated-by ",";

Note:--target-dir/user/hive/warehouse/userinfos2 can be replaced with--hive-import--hive-table Userinfos2