Sqlite transaction

CloverDX Platform
1

Good morning. I’m a new CloverETL Community user. Nice product.

I’m having trouble with a sql transaction completing. I’m using the sqlite db with a jdbc connection adapter. Running the following sql within sqlite3 takes about 6 or 7 seconds on a table containing 100,000 entries. However, running the exact same sql code within a DBExecute never seems to commit. The query has been running for 22 minutes now; it should have completed.

I have tried various transaction sets: one, all, etc… If I code “begin transaction” in the query, I get a sqlite error saying I cannot start a transaction within a transaction.

Any advice for me? What am I missing?


drop table if exists group_keys_indv_cdi;
create table group_keys_indv_cdi as
  select 
    c1.indv indv, 
    c1.seqno x, 
    c2.seqno y
  from cdi_keys c1
  join cdi_keys c2 
    on c2.indv = c1.indv
  where 
    c1.indv = c2.indv 
    and c1.seqno <> c2.seqno
  order by 
    c1.indv;
2

While I am concerned the sqlite query did not function (whether i did something wrong or not), it is obvious that I have to develop more of Clover concept of thinking. I rearranged the processing. Rather than issue the join as a sql query, I pulled the table twice, used an ExtHashJoin to bring the information together, then an ExtFilter to drop records that have x=y, and then write that to the sqlite db.

BULLET FAST! Like it. Very Good!

3

Hello,

if you continue having problems with the SqlLite DB let us know and we’ll try to replicate the issue.

4

I cannot get this query to go. I’ve changed things up. I tried running this sql as a query in a DBInputTable component, and then use the output as a result set. It never seems to complete. It just seems to run and run and run. I’ve killed it after 20 minutes.

I’ve run this within sqlite3 and it takes 212 seconds, yielding 6.5mm rows.

I had also reconfigured this as two DBInputTable components, joining the output, then filtering. But I later identified this as incorrect as I need a join, and the ExtHashJoin component cannot perform a natural join.

Thanks for your time and consideration,
dvn

5

Hello,

I would suggest you split the operation into four separate ones: drop table/create table/pull data/insert data that way you can see where it’s failing, if it does happen.
The graph should:
- drop table in one phase 0
- create table in phase 1
- in phase 2 pull data from the DB using DBInputTable

 select 
    c1.indv indv, 
    c1.seqno x, 
    c2.seqno y
  from cdi_keys c1
  join cdi_keys c2 
    on c2.indv = c1.indv
  where 
    c1.seqno <> c2.seqno
  order by 
    c1.indv
 I removed one condition (c1.indv = c2.indv) from the where clause, since it seems to be redundant to what the join is doing.

- insert data into the DB using the DBOutputTable

I think this approach shouldn’t fail and should be quite efficient, since most processing is done inside the DB.