INSERT and MERGE statements

classic Classic list List threaded Threaded
11 messages Options
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

INSERT and MERGE statements

I am running Ignite v.25 and I am trying to populate cache using SQL
statements via DBeaver.

I create a cache called TAXRATE with the following object structure :

SCENARIO             VARCHAR
VALUEDATE           VARCHAR
ENTITY                 VARCHAR
PRODUCTGROUP   VARCHAR
YEAR                    INTEGER
AMOUNT               DOUBLE
KEY                       VARCHAR

Here is an INSERT statement I run :

*INSERT INTO TAXRATE.TAXRATE
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey');*

and I am getting an error :

/SQL Error [1] [50000]: Failed to execute DML statement [stmt=INSERT INTO
TAXRATE.TAXRATE
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey'), params=null]/

Here is an MERGE statement :

*MERGE INTO TAXRATE.TAXRATE (scenario,
VALUEDATE,ENTITY,PRODUCTGROUP,"YEAR",AMOUNT,"KEY")
--KEY (VALUATIONDATE)
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey');*

and here is the same error I am getting :

/SQL Error [1] [50000]: Failed to execute DML statement [stmt=MERGE INTO
TAXRATE.TAXRATE (scenario,
VALUEDATE,ENTITY,PRODUCTGROUP,"YEAR",AMOUNT,"KEY")
--KEY (VALUATIONDATE)
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey'), params=null]/


Is there any way to populate the cache using SQL  INSERT and/or MERGE?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hello!

Can you please quote full stack traces? They are probably in the logs of server nodes.

Regards,
--
Ilya Kasnacheev


вт, 2 июл. 2019 г. в 23:25, vitalys <[hidden email]>:
I am running Ignite v.25 and I am trying to populate cache using SQL
statements via DBeaver.

I create a cache called TAXRATE with the following object structure :

SCENARIO             VARCHAR
VALUEDATE           VARCHAR
ENTITY                 VARCHAR
PRODUCTGROUP   VARCHAR
YEAR                    INTEGER
AMOUNT               DOUBLE
KEY                       VARCHAR

Here is an INSERT statement I run :

*INSERT INTO TAXRATE.TAXRATE
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey');*

and I am getting an error :

/SQL Error [1] [50000]: Failed to execute DML statement [stmt=INSERT INTO
TAXRATE.TAXRATE
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey'), params=null]/

Here is an MERGE statement :

*MERGE INTO TAXRATE.TAXRATE (scenario,
VALUEDATE,ENTITY,PRODUCTGROUP,"YEAR",AMOUNT,"KEY")
--KEY (VALUATIONDATE)
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey');*

and here is the same error I am getting :

/SQL Error [1] [50000]: Failed to execute DML statement [stmt=MERGE INTO
TAXRATE.TAXRATE (scenario,
VALUEDATE,ENTITY,PRODUCTGROUP,"YEAR",AMOUNT,"KEY")
--KEY (VALUATIONDATE)
VALUES ('SCENARIO','12-03-2019','5','99999',2019,3.3,'mykey'), params=null]/


Is there any way to populate the cache using SQL  INSERT and/or MERGE?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

I made INSERT working :


INSERT INTO RBCCTEVAPVP.RBCCTEVAMARGINCALCED (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');

Merge still failing :

*MERGE INTO RBCCTEVAPVP.RBCCTEVAMARGINCALCED (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
KEY (VALUATIONDATE,businessrun,reportingcompany)
VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');*

and here is an error message and stacktrace :

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1001] [42000]:
Invalid column name in KEYS clause of MERGE - it may include only key and/or
affinity columns: VALUATIONDATE
        at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:141)
        at
org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:458)
        at
org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:402)
        at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1679)
        at
org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:400)
        at
org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:839)
        at
org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2700)
        at
org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:102)
        at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1679)
        at
org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:100)
        at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102)
        at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Invalid column name in KEYS clause of
MERGE - it may include only key and/or affinity columns: VALUATIONDATE
        at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
        at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
        at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
        at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:345)
        at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:138)
        ... 11 more



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

In reply to this post by ilya.kasnacheev
I had some issue with the MERGE .. SELECT ..

*MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)

VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );*

That doesn't work and produces this error :

/MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
--KEY (VALUATIONDATE,businessrun,reportingcompany)
--VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');
VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );
 SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _key FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED /

however, If I hard-code the values everything works  fine :

MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hello!

I think it is self-describing: VALUATIONDATE is not a key column (but value field) so it can't be used in MERGE. You can only use key columns in KEYS() I guess.

Regards,
--
Ilya Kasnacheev


ср, 3 июл. 2019 г. в 18:26, vitalys <[hidden email]>:
I had some issue with the MERGE .. SELECT ..

*MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)

VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );*

That doesn't work and produces this error :

/MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
--KEY (VALUATIONDATE,businessrun,reportingcompany)
--VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');
VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );
 SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _key FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED /

however, If I hard-code the values everything works  fine :

MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
VALUES ('12-03-2019','5',99999,3.3,4.4,5.5,6.6,7.7,'12-03-2019_5_99999');



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

KEY clause is commented out and not being used :

MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );

that still fails with the message posted above



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hello!

Have you tried instead:
MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED ;

This without VALUES (). Works for me for simpler tables.

Regards,
--
Ilya Kasnacheev


ср, 3 июл. 2019 г. в 19:07, vitalys <[hidden email]>:
KEY clause is commented out and not being used :

MERGE INTO RBCFPVP.RBCFPVPDATAIN (valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,_key)
VALUES (SELECT valuationdate,
BUSINESSRUN,REPORTINGCOMPANY,RBCCTE90MARGIN,RBCCTE90TAXADJRATIO,RBCCTE97MARGIN,RBCCTE97TAXADJRATIO,RBCCASOMARGIN,'12-03-2019_5_99999'
AS _KEY FROM RBCCTEVAPVP.RBCCTEVAMARGINCALCED  );

that still fails with the message posted above



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Yes, it worked. Thank you.

But I was trying to use the tutorial :

https://apacheignite-sql.readme.io/docs/merge

MERGE INTO tableName [(columnName [,...])]
  [KEY (columnName [,...])]
  {VALUES {({ DEFAULT | expression } [,...])} [,...] | select}

I guess the tutorial should be updated :)



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hello!

I think you've parsed this one incorrectly:

  {VALUES (...) | select}

Regards,
--
Ilya Kasnacheev


ср, 3 июл. 2019 г. в 20:15, vitalys <[hidden email]>:
Yes, it worked. Thank you.

But I was trying to use the tutorial :

https://apacheignite-sql.readme.io/docs/merge

MERGE INTO tableName [(columnName [,...])]
  [KEY (columnName [,...])]
  {VALUES {({ DEFAULT | expression } [,...])} [,...] | select}

I guess the tutorial should be updated :)



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
vitalys vitalys
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hi, I have to follow-up.

MERGE INTO works fine when I define matching fields between SOURCE and
DESTINATION caches. However, Merge command nullifies fields in the
DESTINATION table when they are not part of the SOURCE.

for Instance an object in cache DST has 3 fields : field1, field2, field3
with values :1,2,3
an object in cache SRC also has 3 fields : field2, field3, field4 with
values.

When I MERGE an object from SRC cache with an Object in DST cache :

MERGER INTO DST ( field2,field3,_key)
SELECT field2,field3,_key FROM SRC where _key = <somevalue>

it updates fields : field2, field3 in DST cache to 2 and 3, but it also
updates field1 to NULL.

How do I preserve an existing values in the Destionation cache.

I did some research and it seems like MERGE INTO .... WHEN MATCHED ...
construct is not supported by Ignie.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: INSERT and MERGE statements

Hello!

I think you could use MERGE INTO DST SELECT SRC.cols, DST.cols FROM SRC JOIN DST - this way you could fetch proper values for DST columns instead of nullifying. Note that it may have collocation considerations (make sire DST and SRC are collocated).

Regards,
--
Ilya Kasnacheev


пн, 8 июл. 2019 г. в 23:46, vitalys <[hidden email]>:
Hi, I have to follow-up.

MERGE INTO works fine when I define matching fields between SOURCE and
DESTINATION caches. However, Merge command nullifies fields in the
DESTINATION table when they are not part of the SOURCE.

for Instance an object in cache DST has 3 fields : field1, field2, field3
with values :1,2,3
an object in cache SRC also has 3 fields : field2, field3, field4 with
values.

When I MERGE an object from SRC cache with an Object in DST cache :

MERGER INTO DST ( field2,field3,_key)
SELECT field2,field3,_key FROM SRC where _key = <somevalue>

it updates fields : field2, field3 in DST cache to 2 and 3, but it also
updates field1 to NULL.

How do I preserve an existing values in the Destionation cache.

I did some research and it seems like MERGE INTO .... WHEN MATCHED ...
construct is not supported by Ignie.




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/