You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Shyama (JIRA)" <ji...@apache.org> on 2019/05/15 09:37:00 UTC
[jira] [Created] (SPARK-27723) Unable to pull the oracle table data
using patitionColumn date/timeStamp
Shyama created SPARK-27723:
------------------------------
Summary: Unable to pull the oracle table data using patitionColumn date/timeStamp
Key: SPARK-27723
URL: https://issues.apache.org/jira/browse/SPARK-27723
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 2.4.1
Environment: Me using spark-sql-2.4.1v , java8 . ojdbc6.jar
Reporter: Shyama
Fix For: 2.4.0
Reproduction steps :
1. create oracle table
create table schema1.modal_vals(
FAMILY_ID NOT NULL NUMBER,
INSERTION_DATE NOT NULL DATE,
ITEM_VALUE VARCHAR2(4000),
YEAR NUMBER,
QUARTER NUMBER,
LAST_UPDATE_DATE DATE
)
3. insert data into oracle table with date column format like "30-JUN-02"
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-02","bbb-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","b+",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-17","bbb-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","bb",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-02","ccc-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","aa-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-OCT-13","a-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-03","bbb-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","b",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-FEB-03","aa+",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","aa+",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JAN-19","aaa+",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-18","ccc-",2013,2,null);
insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"01-MAY-19","bb-",2013,2,null);
//please fill the respected oracle details
DataFrameReader ora_df_reader = spark.read().format("jdbc")
.option("url", o_url)
.option("driver", Constants.ORACLE_DRIVER)
.option("user", o_userName)
.option("password", o_passwd)
.option("fetchsize",1000);
Dataset<Row> ss = ora_df_reader
.option("inferSchema", true)
.option("schema","schema1")
.option("numPartitions", 20);
.option("partitionColumn", "INSERTION_DATE");
.option("lowerBound", "2002-03-31" )
.option("upperBound", "2019-05-01")
.option("dateFormat", "yyyy-MM-dd" )// Tried all "yyyy-mm-dd" ,"yyyy-MM-dd" "YYYY-MM-DD" "DD-MMM-YY" "dd-MMM-yy"
.option("dbtable", "select * from schema1.modal_vals")
.load();
Error :
{{java.sql.SQLException: ORA-12801: error signaled in parallel query server P001(2) ORA-01861: literal does not match format string}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org