1.在pom文件添加
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.34</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.13</artifactId>
<version>${spark.version}</version>
</dependency>
2.完整代码如下
import org.apache.spark.sql.SparkSession
object Hive2Mysql {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder()
.appName("Hive2Mysql")
.master("local")
.config("spark.executor.memory", "1g")
.enableHiveSupport() // 启用Hive支持
.getOrCreate()
//定义MySQL的连接信息
val mysqlUrl = "jdbc:mysql://localhost:3306/my_database"
val mysqlUser = "root"
val mysqlPassword = "root"
//定义SQL语句
val sql = "INSERT INTO TABLE my_mysql_table " +
"SELECT * FROM my_hive_table"
//使用SQL语句查询Hive表格数据,并将结果保存到DataFrame中
val df = spark.sql(sql)
//将DataFrame中的数据写入到MySQL表格中,并覆盖已有数据
df.write.format("jdbc")
.option("url", mysqlUrl)
.option("dbtable", "my_mysql_table")
.option("user", mysqlUser)
.option("password", mysqlPassword)
.mode("overwrite")
.save()
}
}
注意,在执行之前需要确认用户有足够的权限来访问MySQL数据库。
3.注意问题
使用mode("overwrite")
选项之后,Spark会将MySQL表格的所有数据删掉并重新创建,但是表格的结构(包括字段名称和数据类型)会保持不变。换句话说,Spark并不会通过mode("overwrite")
选项改变表格的结构信息,表结构只会受到用户主动修改的影响。注意,在重新创建表格时,如果定义的数据类型和MySQL中的数据类型不匹配,例如,Spark中的String类型对应MySQL中的Text类型,Spark会自动将类型转换为MySQL支持的类型。
这就会导致如果我的hive表出于方便都是String类型, 使用spark导入mysql后会把mysql表所有字段都变成text格式.
MySQL JDBC 驱动中的问题。比如有些 JDBC 驱动可能会将 SparkSQL 中的某些类型从字符串类型映射到MySQL中的TEXT
类型,因此在插入时需要使用jdbcType
参数显式地指定 JDBC 驱动中的映射类型。
下面是Spark数据类型和MySQL数据类型的对应关系:
Spark数据类型 | MySQL数据类型 |
---|---|
StringType | VARCHAR, TEXT |
IntegerType | INT, INTEGER |
LongType | BIGINT |
DoubleType | DOUBLE, FLOAT |
FloatType | FLOAT |
ShortType | SMALLINT |
ByteType | TINYINT |
BooleanType | BOOLEAN, BIT |
BinaryType | BLOB, VARBINARY |
TimestampType | DATETIME, TIMESTAMP |
DateType | DATE |
解决方案:
在 SparkSQL 中,字符串类型被定义为 StringType
,而在 MySQL 中,字符串类型被定义为 VARCHAR
。因此,将 SparkSQL 中的字符串类型映射到 MySQL 中的 VARCHAR
数据类型,只需在 properties 参数中增加 jdbcType
参数即可,示例如下:
import org.apache.spark.sql.SaveMode
import java.util.Properties
import java.sql.Types.{VARCHAR, INTEGER ,DECIMAL}
val df = Seq(
("Tom", 15,152.2),
("Jerry", 16,2353.145)
).toDF("name", "age", "qty")
// 配置连接参数
val url="jdbc:mysql://localhost:3306/test"
val properties = new Properties()
properties.setProperty("user", "test")
properties.setProperty("password", "test123")
// 写入 MySQL 数据库
df.write
.mode(SaveMode.Append)
.option("createTableColumnTypes", "name VARCHAR(255), age INT,qty decimal(19,4)") // 明确指定 MySQL 数据库中字段的数据类型
.option("batchsize", "10000")
.option("truncate", "false")
.option("jdbcType", s"name=${VARCHAR}, age =${INTEGER},qty=${DECIMAL}") // 显式指定 SparkSQL 中的数据类型和 MySQL 中的映射关系
.jdbc(url, "persons", properties)
在上述代码中,我们定义了 SparkSQL 中的 STRING
类型映射到 MySQL 中的 VARCHAR类型,INTEGER
类型映射到 MySQL 中的 INTEGER
类型。
还有一些其他可用的映射类型,这里列举一下:
STRING
对应于java.sql.Types.VARCHAR
BYTE
对应于java.sql.Types.TINYINT
SHORT
对应于java.sql.Types.SMALLINT
INT
对应于java.sql.Types.INTEGER
LONG
对应于java.sql.Types.BIGINT
FLOAT
对应于java.sql.Types.FLOAT
DOUBLE
对应于java.sql.Types.DOUBLE
DECIMAL
对应于java.sql.Types.DECIMAL
DATE
对应于java.sql.Types.DATE
TIMESTAMP
对应于java.sql.Types.TIMESTAMP
需要注意的是,最终的表格结构和数据类型可能会受到写入操作的驱动器连接字符串和数据源的限制,因此还是需要根据实际情况进行数据类型转换和清洗处理。