Sqoop [put it into practice 02] sqoop latest version full database import + data filtering + field type support description and example code (query parameter and field type forced conversion)

1. Environmental statement

Or the previous environment ：

#  Unnecessary information is no longer posted 
# JDK
[[email protected] ~]# java -version
java version "1.8.0_251"
# MySQL
[[email protected] ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.28
# Hadoop
[[email protected] ~]# hadoop version
Hadoop 3.1.3
# Hive 
[[email protected] ~]# hive --version
Hive 3.1.2
# Sqoop
[[email protected] ~]# sqoop version
Sqoop 1.4.7

2.import-all-tables

Sqoop1 Supported by import-all-tables Command to export the whole database to HDFS/Hive, But there are two limitations to note ：

• All tables must have primary keys ; Or use --autoreset-to-one-mapper , Represents that only one map task, That is, not in parallel .【 If you still want to execute in parallel , You can use --split-by Indicates the reference column for split data 】 ;
• You can't use non default Split Columns , It can't pass WHERE Clause to add any restrictions . Official original You must not intend to use non-default splitting column, nor impose any conditionsvia a WHERE clause.

The default path for import is ：/user/ user name / Several data table names / Data files and _SUCCESS, If you want to specify a directory, you need to use the specified parameters 【 Specific parameters can be used sqoop help import-all-tables see 】

【 example 1️⃣ 】 Import all libraries into HDFS：

#  Be careful ： The specified Import Directory uses  --warehouse-dir  No  --target-dir  Parameters 
sqoop import-all-tables \
--connect jdbc:mysql://tcloud:3306/mysql \
--username root \
--password [email protected]\
--warehouse-dir /sqoop_all \  
--fields-terminated-by '\t' \
--bindir /usr/local/sqoop/lib/ \
-m 1
#  When all tables have primary keys   You can set -m  The parameter of is greater than 1  Otherwise, it can only be  1

【 example 2️⃣ 】 Export the entire library to Hive：

#  Be careful ： Specify that the imported database uses  --hive-database
sqoop import-all-tables \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:mysql://tcloud:3306/mysql \
--username root \
--password [email protected]\
--hive-database sqoop_test \
--hive-import \
--hive-overwrite \
--bindir /usr/local/sqoop/lib/ \
-m 1

3.query

Sqoop1 Support use query Parameter definition query SQL Achieve data filtering , So you can import any desired result set . example ：

sqoop import \
--connect jdbc:mysql://tcloud:3306/mysql \
--username root \
--password [email protected]\
--query 'select * from help_keyword where $CONDITIONS and help_keyword_id < 50' \ 
--delete-target-dir \      
--target-dir /sqoop_hive \
--hive-database sqoop_test \		#  Specify import target database   If it is not specified, it defaults to Hive Medium default library 
--hive-table filter_help_keyword \	#  Specify import target table 
--split-by help_keyword_id \		#  Designated for  split  The column of  
--hive-import \						#  Import to  Hive
--hive-overwrite \    
--bindir /usr/local/sqoop/lib/ \      
-m 1

In the use of query When filtering data , The following three points need to be noted ：

• Must use --hive-table Indicate the target table ;
• If the degree of parallelism -m Not for 1 Or no designation --autoreset-to-one-mapper , You need to use --split-by Indicate the reference column ;
• SQL Of where The words must contain $CONDITIONS , This is a fixed way of writing , The function is dynamic replacement .

4. Field types support

Sqoop1 Most of the field types in the database are supported by default , But some special types are not supported . Unsupported type encountered , The program throws an exception Hive does not support the SQL type for column xxx abnormal , In this case, you can cast the following two parameters ：

• –map-column-java ： rewrite SQL To Java Mapping of types ;
• –map-column-hive ： rewrite Hive To Java Mapping of types .

Examples are as follows , Put the original id The field is forced to String type , value The field is forced to Integer type ：

$ sqoop import ... --map-column-java id=String,value=Integer