You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by vitalys <vi...@prudential.com> on 2019/07/02 20:25:16 UTC
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/
Re: INSERT and MERGE statements
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <vi...@prudential.com>:
> 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/
>
Re: INSERT and MERGE statements
Posted by vitalys <vi...@prudential.com>.
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/
Re: INSERT and MERGE statements
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
I think you've parsed this one incorrectly:
{VALUES (...) | select}
Regards,
--
Ilya Kasnacheev
ср, 3 июл. 2019 г. в 20:15, vitalys <vi...@prudential.com>:
> 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/
>
Re: INSERT and MERGE statements
Posted by vitalys <vi...@prudential.com>.
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/
Re: INSERT and MERGE statements
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <vi...@prudential.com>:
> 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/
>
Re: INSERT and MERGE statements
Posted by vitalys <vi...@prudential.com>.
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/
Re: INSERT and MERGE statements
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <vi...@prudential.com>:
> 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/
>
Re: INSERT and MERGE statements
Posted by vitalys <vi...@prudential.com>.
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/
Re: INSERT and MERGE statements
Posted by vitalys <vi...@prudential.com>.
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/
Re: INSERT and MERGE statements
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <vi...@prudential.com>:
> 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/
>