You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (JIRA)" <ji...@apache.org> on 2016/07/27 09:33:20 UTC
[jira] [Commented] (SPARK-16625) Oracle JDBC table creation fails
with ORA-00902: invalid datatype
[ https://issues.apache.org/jira/browse/SPARK-16625?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15395307#comment-15395307 ]
Yuming Wang commented on SPARK-16625:
-------------------------------------
{code:java}
val jdbcUrl = "jdbc:oracle:thin:@localhost:1521/orcl"
val props = new Properties()
props.put("driver", "oracle.jdbc.driver.OracleDriver")
props.put("oracle.jdbc.mapDateToTimestamp", "false")
val schema = StructType(Seq(
StructField("boolean_type", BooleanType, true),
StructField("integer_type", IntegerType, true),
StructField("long_type", LongType, true),
StructField("float_Type", FloatType, true),
StructField("double_type", DoubleType, true),
StructField("byte_type", ByteType, true),
StructField("short_type", ShortType, true),
StructField("string_type", StringType, true),
StructField("binary_type", BinaryType, true),
StructField("date_type", DateType, true),
StructField("timestamp_type", TimestampType, true)
))
val tableName = "test_oracle_general_types"
val booleanVal = true
val integerVal = 1
val longVal = 2L
val floatVal = 3.0f
val doubleVal = 4.0
val byteVal = 2.toByte
val shortVal = 5.toShort
val stringVal = "string"
val binaryVal = Array[Byte](6, 7, 8)
val dateVal = Date.valueOf("2016-07-26")
val timestampVal = Timestamp.valueOf("2016-07-26 11:49:45")
val data = spark.sparkContext.parallelize(Seq(
Row(
booleanVal, integerVal, longVal, floatVal, doubleVal, byteVal, shortVal, stringVal,
binaryVal, dateVal, timestampVal
)))
val dfWrite = spark.createDataFrame(data, schema)
dfWrite.write.jdbc(jdbcUrl, tableName, props)
{code}
For DataFrame dfWrite, the generated create table statement:
{code:sql}
CREATE TABLE test_oracle_general_types (
"boolean_type" BIT(1),
"integer_type" INTEGER,
"long_type" BIGINT,
"float_Type" REAL,
"double_type" DOUBLE PRECISION,
"byte_type" BYTE,
"short_type" INTEGER,
"string_type" VARCHAR2(255),
"binary_type" BLOB,
"date_type" DATE,
"timestamp_type" TIMESTAMP )
{code}
Obviously, the statement is incorrect for Oracle, I will submit a PR and then the generated create table statement:
{code:sql}
CREATE TABLE test_oracle_general_types (
"boolean_type" NUMBER(1),
"integer_type" NUMBER(10),
"long_type" NUMBER(19),
"float_Type" NUMBER(19, 4),
"double_type" NUMBER(19, 4),
"byte_type" NUMBER(3),
"short_type" NUMBER(5),
"string_type" VARCHAR2(255),
"binary_type" BLOB,
"date_type" DATE,
"timestamp_type" TIMESTAMP )
{code}
> Oracle JDBC table creation fails with ORA-00902: invalid datatype
> -----------------------------------------------------------------
>
> Key: SPARK-16625
> URL: https://issues.apache.org/jira/browse/SPARK-16625
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.6.2
> Reporter: Daniel Darabos
>
> Unfortunately I know very little about databases, but I figure this is a bug.
> I have a DataFrame with the following schema:
> {noformat}
> StructType(StructField(dst,StringType,true), StructField(id,LongType,true), StructField(src,StringType,true))
> {noformat}
> I am trying to write it to an Oracle database like this:
> {code:java}
> String url = "jdbc:oracle:thin:root/rootroot@<ip address>:1521:db";
> java.util.Properties p = new java.util.Properties();
> p.setProperty("driver", "oracle.jdbc.OracleDriver");
> df.write().mode("overwrite").jdbc(url, "my_table", p);
> {code}
> And I get:
> {noformat}
> Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:461)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:402)
> at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1108)
> at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:541)
> at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:264)
> at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:598)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:26)
> at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1241)
> at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1558)
> at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:2498)
> at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:2431)
> at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:975)
> at org.apache.spark.sql.DataFrameWriter.jdbc(DataFrameWriter.scala:302)
> {noformat}
> The Oracle server I am running against is the one I get on Amazon RDS for engine type {{oracle-se}}. The same code (with the right driver) against the RDS instance with engine type {{MySQL}} works.
> The error message is the same as in https://issues.apache.org/jira/browse/SPARK-12941. Could it be that {{Long}} is also translated into the wrong data type? Thanks.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org