[jira] [Comment Edited] (SPARK-38200) [SQL] Spark JDBC Savemode Supports replace

2023-03-27 Thread melin (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-38200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17705765#comment-17705765
 ] 

melin edited comment on SPARK-38200 at 3/28/23 3:00 AM:


upsert sql for different databases， Most databases support merge sql：

sqlserver merge into sql : 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java]

mysql: 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java]

oracle merge into sql : 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java]

postgres: 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java]

postgres merg into sql : 
[https://www.postgresql.org/docs/current/sql-merge.html]

db2 merge into sql : 
[https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-merge]

derby merge into sql: 
[https://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html]

he merg into sql : 
[https://www.tutorialspoint.com/h2_database/h2_database_merge.htm]

[~beliefer] [~cloud_fan] 


was (Author: melin):
upsert sql for different databases， Most databases support merge sql：

sqlserver merge into sql : 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/sqlserver/SqlServerDialect.java]

mysql: 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/mysql/MysqlDialect.java]

oracle merge into sql : 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/oracle/OracleDialect.java]

postgres: 
[https://github.com/apache/incubator-seatunnel/blob/dev/seatunnel-connectors-v2/connector-jdbc/src/main/java/org/apache/seatunnel/connectors/seatunnel/jdbc/internal/dialect/psql/PostgresDialect.java]

db2 merge into sql : 
[https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-merge]

derby merge into sql: 
[https://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html]

he merg into sql : 
[https://www.tutorialspoint.com/h2_database/h2_database_merge.htm]

[~beliefer] [~cloud_fan] 

> [SQL] Spark JDBC Savemode Supports replace
> --
>
> Key: SPARK-38200
> URL: https://issues.apache.org/jira/browse/SPARK-38200
> Project: Spark
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 3.3.0
>Reporter: melin
>Priority: Major
>
> When writing data into a relational database, data duplication needs to be 
> considered. Both mysql and postgres support upsert syntax.
> mysql:
> {code:java}
> replace into t(id, update_time) values(1, now()); {code}
> pg:
> {code:java}
> INSERT INTO %s (id,name,data_time,remark) VALUES ( ?,?,?,? ) ON CONFLICT 
> (id,name) DO UPDATE SET 
> id=excluded.id,name=excluded.name,data_time=excluded.data_time,remark=excluded.remark
>    {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org

[jira] [Comment Edited] (SPARK-38200) [SQL] Spark JDBC Savemode Supports replace

2022-02-20 Thread melin (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-38200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17495322#comment-17495322
 ] 

melin edited comment on SPARK-38200 at 2/21/22, 5:17 AM:
-

[~beliefer]  

oracle: 
[https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlfornosql/adding-table-rows-using-insert-and-upsert-statements.html]


was (Author: melin):
[~beliefer]  

> [SQL] Spark JDBC Savemode Supports replace
> --
>
> Key: SPARK-38200
> URL: https://issues.apache.org/jira/browse/SPARK-38200
> Project: Spark
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 3.3.0
>Reporter: melin
>Priority: Major
>
> When writing data into a relational database, data duplication needs to be 
> considered. Both mysql and postgres support upsert syntax.
> mysql:
> {code:java}
> replace into t(id, update_time) values(1, now()); {code}
> pg:
> {code:java}
> INSERT INTO %s (id,name,data_time,remark) VALUES ( ?,?,?,? ) ON CONFLICT 
> (id,name) DO UPDATE SET 
> id=excluded.id,name=excluded.name,data_time=excluded.data_time,remark=excluded.remark
>    {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)

-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org

[jira] [Comment Edited] (SPARK-38200) [SQL] Spark JDBC Savemode Supports replace

2022-02-20 Thread melin (Jira) 


[ 
https://issues.apache.org/jira/browse/SPARK-38200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17495322#comment-17495322
 ] 

melin edited comment on SPARK-38200 at 2/21/22, 5:17 AM:
-

[~beliefer]  

oracle: 
[https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlfornosql/adding-table-rows-using-insert-and-upsert-statements.html]

db2 or sqlserver
{code:java}
MERGE INTO mytable AS mt USING (
SELECT * FROM TABLE (
VALUES 
(123, 'text')
)
) AS vt(id, val) ON (mt.id = vt.id)
WHEN MATCHED THEN
UPDATE SET val = vt.val
WHEN NOT MATCHED THEN
INSERT (id, val) VALUES (vt.id, vt.val)
; {code}


was (Author: melin):
[~beliefer]  

oracle: 
[https://docs.oracle.com/en/database/other-databases/nosql-database/21.1/sqlfornosql/adding-table-rows-using-insert-and-upsert-statements.html]

> [SQL] Spark JDBC Savemode Supports replace
> --
>
> Key: SPARK-38200
> URL: https://issues.apache.org/jira/browse/SPARK-38200
> Project: Spark
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 3.3.0
>Reporter: melin
>Priority: Major
>
> When writing data into a relational database, data duplication needs to be 
> considered. Both mysql and postgres support upsert syntax.
> mysql:
> {code:java}
> replace into t(id, update_time) values(1, now()); {code}
> pg:
> {code:java}
> INSERT INTO %s (id,name,data_time,remark) VALUES ( ?,?,?,? ) ON CONFLICT 
> (id,name) DO UPDATE SET 
> id=excluded.id,name=excluded.name,data_time=excluded.data_time,remark=excluded.remark
>    {code}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)

-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org