INSERT into oracle use case

[Purush]

Hi,

We are evaluating Cascading Linugal for following use case and it is best fit.

1. Import data to HDFS from RDBS 
   
2. Export hadoop output to RDMBS

So we tried PoC from tutorial posted in Cascading website (http://docs.cascading.org/tutorials/lingual-oracle/) and we were able to achieve first scenario and getting following exception for second. It is deleting existing employees table and trying to create every time.

Could you please help us to resolve it? 

Also if i use "insert into "hfsworkflow"."employees" select "last_name", "salary" from "bpoworkflow"."employees";" to write data in HDFS but it is writing two rows though database had single row.

Warning: exception while executing query: could not build flow from assembly: [unable to create table: employees]: unable to create table: employees (state=,code=0)

Error: exception while executing query (state=,code=0)

===========commands=======

lingual catalog --provider -add cascading-jdbc-oracle-2.2.0-wip-dev-provider.jar

lingual catalog --provider

##oracle workflow

lingual catalog -schema bpoworkflow --add

lingual catalog --schema bpoworkflow --stereotype employees --add --columns last_name,salary --types string,int

lingual catalog --schema bpoworkflow --protocol jdbc --add 

--properties tabledesc.tablename=employees,tabledesc.columnnames=last_name:salary,tabledesc.columndefs=varchar:int --provider oracle

lingual catalog --schema bpoworkflow --format oracle --add --provider oracle

lingual catalog --schema bpoworkflow --table employees --stereotype employees 

--add "{JDBC URL}" --protocol jdbc --format oracle

###hfsworkflow to writeoutput

lingual catalog --schema hfsworkflow --add

lingual catalog --schema hfsworkflow --stereotype employees --add --columns last_name,salary --types string,int

lingual catalog --schema hfsworkflow --format psv --add --provider text --properties "delimiter=|"

lingual catalog --schema hfsworkflow --table employees --stereotype employees --add bpooutput/employees --format psv

[Andre Kelpe]

Hi,

are you using lingual 1.2? I assume you have built the provider jar yourself. Which version did you use? The latest 2.6 from github?

If not, please make sure to use the latest versions. If you still see a problem run lingual with —showstacktrace and —verbose and post the output here.

Thanks.

- Andre

> --
> You received this message because you are subscribed to the Google Groups "Lingual User" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to lingual-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
André Kelpe
an...@concurrentinc.com
http://concurrentinc.com

[Purush]

Thanks for your quick response. I am using lingual 1.2 but provider jar built using 2.2. Let me built with latest and get back to you about the results.

Thanks,

Purush

[Purush]

Hi Andre,

 

Still seeing same exception after built JDBC driver from latest version (2.7 WIP). Attached stack trace here.  I assume mode needs to be changed to KEEP instead of REPLACE but I couldn't able to find the target place.

 

Thanks,

Purush

On Thursday, January 15, 2015 at 12:15:43 PM UTC-5, Purush wrote:

[Andre Kelpe]

Hi,

so the error is on the oracle side. Can you confirm that your user has enough permissions to create a new table on the database server?

- Andre

> --
> You received this message because you are subscribed to the Google Groups "Lingual User" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to lingual-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

> <exception.txt>

[Purush]

Ok Andre, I can check that but my requirement is just append the rows into existing table and table shouldn't be delete and created through lingual to avoid lose of existing data. 

Thanks,

Purush

On Thursday, January 15, 2015 at 12:15:43 PM UTC-5, Purush wrote:

[Andre Kelpe]

You can make it write to an existing table by setting the sinkmode property on the protocol to “APPEND”. This is handled in the JDBCFactory, in case you are interested:

https://github.com/Cascading/cascading-jdbc/blob/wip-2.7/cascading-jdbc-core/src/main/java/cascading/jdbc/JDBCFactory.java#L122-L126

- Andre

> --
> You received this message because you are subscribed to the Google Groups "Lingual User" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to lingual-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Purush]

Thanks Andre, Changing sinkmode to UPDATE worked for me.

On Thursday, January 15, 2015 at 12:15:43 PM UTC-5, Purush wrote: