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 2019/05/15 11:56:00 UTC
[jira] [Updated] (SPARK-27723) Unable to pull the oracle table data
using patitionColumn date/timeStamp
[ https://issues.apache.org/jira/browse/SPARK-27723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Yuming Wang updated SPARK-27723:
--------------------------------
Target Version/s: (was: 2.4.1)
> 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
> Priority: Major
> Labels: pull-request-available
> 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