You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Lu Xu (Jira)" <ji...@apache.org> on 2020/12/10 19:13:00 UTC
[jira] [Updated] (SPARK-33743) Change datatype mapping in JDBC
mssqldialect: DATETIME to DATETIME2
[ https://issues.apache.org/jira/browse/SPARK-33743?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lu Xu updated SPARK-33743:
--------------------------
Description:
*datetime v/s datetime2*
Spark datetime type is [timestamptype|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fspark.apache.org%2Fdocs%2Flatest%2Fapi%2Fjava%2Forg%2Fapache%2Fspark%2Fsql%2Ftypes%2FTimestampType.html&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qPPJve%2FGAPeIp%2BI2hjB%2BqoGGN%2FcJQe6CIDjlEdUyASo%3D&reserved=0]. This supports a microsecond resolution.
Sql supports 2 date time types
o *datetime* can support only milli seconds resolution (0 to 999).
o *datetime2* is extension of datetime , is compatible with datetime and supports 0 to 9999999 sub second resolution.
[datetime2 (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime2-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=kvMHreJly6z1b8f2V%2B5Ct%2Fv8p3nrBw4Pv6aH8p20VOc%3D&reserved=0]
[datetime (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=GRjOHfd9h3w3TrH23wU3z0nqdZp8wL%2F2%2FHh63WuXy9Y%3D&reserved=0]
Currently [MsSQLServerDialect|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fspark%2Fblob%2Fbfb257f078854ad587a9e2bfe548cdb7bf8786d4%2Fsql%2Fcore%2Fsrc%2Fmain%2Fscala%2Forg%2Fapache%2Fspark%2Fsql%2Fjdbc%2FMsSqlServerDialect.scala&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=PMT9rA08NJRN0kwHy2ERaloOaDRB6ZsBBd70MZXl%2Bv4%3D&reserved=0] maps timestamptype to datetime. This implies results in errors when writing
*+Current+*
|override def getJDBCType(dt: DataType): Option[JdbcType] = dt match \{ *case TimestampType => Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))* .. }| |
*+Proposal+*
override def getJDBCType(dt: DataType): Option[JdbcType] = dt match
{ *_case TimestampType => if(oldDateTime) \\{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))}
else \{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))}_*
..
}| |
was:
*datetime v/s datetime2*
Spark datetime type is [timestamptype|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fspark.apache.org%2Fdocs%2Flatest%2Fapi%2Fjava%2Forg%2Fapache%2Fspark%2Fsql%2Ftypes%2FTimestampType.html&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qPPJve%2FGAPeIp%2BI2hjB%2BqoGGN%2FcJQe6CIDjlEdUyASo%3D&reserved=0]. This supports a microsecond resolution.
Sql supports 2 date time types
o *datetime* can support only milli seconds resolution (0 to 999).
o *datetime2* is extension of datetime , is compatible with datetime and supports 0 to 9999999 sub second resolution.
[datetime2 (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime2-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=kvMHreJly6z1b8f2V%2B5Ct%2Fv8p3nrBw4Pv6aH8p20VOc%3D&reserved=0]
[datetime (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=GRjOHfd9h3w3TrH23wU3z0nqdZp8wL%2F2%2FHh63WuXy9Y%3D&reserved=0]
Currently [MsSQLServerDialect|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fspark%2Fblob%2Fbfb257f078854ad587a9e2bfe548cdb7bf8786d4%2Fsql%2Fcore%2Fsrc%2Fmain%2Fscala%2Forg%2Fapache%2Fspark%2Fsql%2Fjdbc%2FMsSqlServerDialect.scala&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=PMT9rA08NJRN0kwHy2ERaloOaDRB6ZsBBd70MZXl%2Bv4%3D&reserved=0] maps timestamptype to datetime. This implies results in errors when writing
*+Current+*
|override def getJDBCType(dt: DataType): Option[JdbcType] = dt match {
*case TimestampType => Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))*
..
}| |
|
*+Proposal+*
override def getJDBCType(dt: DataType): Option[JdbcType] = dt match {
*_case TimestampType => if(oldDateTime) \{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))} else \{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))}_*
..
}| |
> Change datatype mapping in JDBC mssqldialect: DATETIME to DATETIME2
> -------------------------------------------------------------------
>
> Key: SPARK-33743
> URL: https://issues.apache.org/jira/browse/SPARK-33743
> Project: Spark
> Issue Type: Request
> Components: Spark Core
> Affects Versions: 2.4.0, 3.0.0
> Reporter: Lu Xu
> Priority: Major
>
> *datetime v/s datetime2*
> Spark datetime type is [timestamptype|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fspark.apache.org%2Fdocs%2Flatest%2Fapi%2Fjava%2Forg%2Fapache%2Fspark%2Fsql%2Ftypes%2FTimestampType.html&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=qPPJve%2FGAPeIp%2BI2hjB%2BqoGGN%2FcJQe6CIDjlEdUyASo%3D&reserved=0]. This supports a microsecond resolution.
>
> Sql supports 2 date time types
> o *datetime* can support only milli seconds resolution (0 to 999).
> o *datetime2* is extension of datetime , is compatible with datetime and supports 0 to 9999999 sub second resolution.
>
> [datetime2 (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime2-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986187437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=kvMHreJly6z1b8f2V%2B5Ct%2Fv8p3nrBw4Pv6aH8p20VOc%3D&reserved=0]
> [datetime (Transact-SQL) - SQL Server | Microsoft Docs|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdata-types%2Fdatetime-transact-sql%3Fview%3Dsql-server-ver15&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=GRjOHfd9h3w3TrH23wU3z0nqdZp8wL%2F2%2FHh63WuXy9Y%3D&reserved=0]
> Currently [MsSQLServerDialect|https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fspark%2Fblob%2Fbfb257f078854ad587a9e2bfe548cdb7bf8786d4%2Fsql%2Fcore%2Fsrc%2Fmain%2Fscala%2Forg%2Fapache%2Fspark%2Fsql%2Fjdbc%2FMsSqlServerDialect.scala&data=04%7C01%7Cluxu1%40microsoft.com%7C39803a0f635646dadd6b08d89010896a%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637417747986197428%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=PMT9rA08NJRN0kwHy2ERaloOaDRB6ZsBBd70MZXl%2Bv4%3D&reserved=0] maps timestamptype to datetime. This implies results in errors when writing
> *+Current+*
> |override def getJDBCType(dt: DataType): Option[JdbcType] = dt match \{ *case TimestampType => Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))* .. }| |
> *+Proposal+*
> override def getJDBCType(dt: DataType): Option[JdbcType] = dt match
> { *_case TimestampType => if(oldDateTime) \\{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))}
> else \{Some(JdbcType("DATETIME", java.sql.Types.TIMESTAMP))}_*
> ..
> }| |
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org