You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@paimon.apache.org by lz...@apache.org on 2023/11/14 08:00:09 UTC

(incubator-paimon) branch master updated: [spark] Support UPDATE command (#2268)

This is an automated email from the ASF dual-hosted git repository.

lzljs3620320 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-paimon.git


The following commit(s) were added to refs/heads/master by this push:
     new bb2f7d836 [spark] Support UPDATE command (#2268)
bb2f7d836 is described below

commit bb2f7d836ac07dcc2d723bc7de7344c4012d91cf
Author: Zouxxyy <zo...@alibaba-inc.com>
AuthorDate: Tue Nov 14 16:00:05 2023 +0800

    [spark] Support UPDATE command (#2268)
---
 docs/content/engines/spark3.md                     |  20 +-
 docs/content/how-to/writing-tables.md              |  35 +++-
 .../extensions/PaimonSparkSessionExtensions.scala  |   7 +-
 .../paimon/spark/schema/SparkSystemColumns.scala   |   2 +-
 .../sql/RowLevelOp.scala}                          |  25 +--
 .../analysis/AssignmentAlignmentHelper.scala       | 119 +++++++++++
 .../analysis/RewriteRowLevelCommands.scala         | 117 +++++++++++
 .../optimizer/RewriteRowLeverCommands.scala        |  82 --------
 .../logical/DeleteFromPaimonTableCommand.scala     |   9 +-
 ...ommand.scala => UpdatePaimonTableCommand.scala} |  28 ++-
 .../paimon/spark/sql/DeleteFromTableTest.scala     |   4 +-
 .../apache/paimon/spark/sql/UpdateTableTest.scala  | 230 +++++++++++++++++++++
 12 files changed, 551 insertions(+), 127 deletions(-)

diff --git a/docs/content/engines/spark3.md b/docs/content/engines/spark3.md
index 414679bb7..07ffd1ffc 100644
--- a/docs/content/engines/spark3.md
+++ b/docs/content/engines/spark3.md
@@ -221,18 +221,18 @@ dataset.show()
 
 ## Update Table
 
-For now, Paimon does not support `UPDATE` syntax. But we can use `INSERT INTO` syntax instead for changelog tables.
-
-```sql
-INSERT INTO my_table VALUES (1, 'Hi Again'), (3, 'Test');
+{{< hint info >}}
+Important table properties setting:
+1. Only [primary key table]({{< ref "concepts/primary-key-table" >}}) supports this feature.
+2. [MergeEngine]({{< ref "concepts/primary-key-table#merge-engines" >}}) needs to be [deduplicate]({{< ref "concepts/primary-key-table#deduplicate" >}}) or [partial-update]({{< ref "concepts/primary-key-table#partial-update" >}}) to support this feature.
+   {{< /hint >}}
 
-SELECT * FROM my_table;
+{{< hint warning >}}
+Warning: we do not support updating primary keys.
+{{< /hint >}}
 
-/*
-1	Hi Again
-2	Hello
-3	Test
-*/
+```sql
+UPDATE my_table SET v = 'new_value' WHERE id = 1;
 ```
 
 ## Streaming Write
diff --git a/docs/content/how-to/writing-tables.md b/docs/content/how-to/writing-tables.md
index a2b56dc76..adbfc2c76 100644
--- a/docs/content/how-to/writing-tables.md
+++ b/docs/content/how-to/writing-tables.md
@@ -329,8 +329,6 @@ For more information of drop-partition, see
 
 ## Updating tables
 
-Currently, Paimon supports updating records by using `UPDATE` in Flink 1.17 and later versions. You can perform `UPDATE` in Flink's `batch` mode.
-
 {{< hint info >}}
 Important table properties setting:
 1. Only [primary key table]({{< ref "concepts/primary-key-table" >}}) supports this feature.
@@ -345,6 +343,8 @@ Warning: we do not support updating primary keys.
 
 {{< tab "Flink" >}}
 
+Currently, Paimon supports updating records by using `UPDATE` in Flink 1.17 and later versions. You can perform `UPDATE` in Flink's `batch` mode.
+
 ```sql
 -- Syntax
 UPDATE table_identifier SET column1 = value1, column2 = value2, ... WHERE condition;
@@ -366,6 +366,37 @@ UPDATE MyTable SET b = 1, c = 2 WHERE a = 'myTable';
 
 {{< /tab >}}
 
+{{< tab "Spark" >}}
+
+To enable update needs these configs below:
+
+```text
+--conf spark.sql.catalog.spark_catalog=org.apache.paimon.spark.SparkGenericCatalog
+--conf spark.sql.extensions=org.apache.paimon.spark.extensions.PaimonSparkSessionExtensions
+```
+
+spark supports update PrimitiveType and StructType, for example:
+
+```sql
+-- Syntax
+UPDATE table_identifier SET column1 = value1, column2 = value2, ... WHERE condition;
+
+CREATE TABLE T (
+  id INT, 
+  s STRUCT<c1: INT, c2: STRING>, 
+  name STRING)
+TBLPROPERTIES (
+  'primary-key' = 'id', 
+  'merge-engine' = 'deduplicate'
+);
+
+-- you can use
+UPDATE T SET name = 'a_new' WHERE id = 1;
+UPDATE T SET s.c2 = 'a_new' WHERE s.c1 = 1;
+```
+
+{{< /tab >}}
+
 {{< /tabs >}}
 
 ## Deleting from table
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/extensions/PaimonSparkSessionExtensions.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/extensions/PaimonSparkSessionExtensions.scala
index 3c9f7a76b..d5006ea64 100644
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/extensions/PaimonSparkSessionExtensions.scala
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/extensions/PaimonSparkSessionExtensions.scala
@@ -18,8 +18,7 @@
 package org.apache.paimon.spark.extensions
 
 import org.apache.spark.sql.SparkSessionExtensions
-import org.apache.spark.sql.catalyst.analysis.{CoerceArguments, PaimonAnalysis, ResolveProcedures}
-import org.apache.spark.sql.catalyst.optimizer.RewriteRowLeverCommands
+import org.apache.spark.sql.catalyst.analysis.{CoerceArguments, PaimonAnalysis, ResolveProcedures, RewriteRowLevelCommands}
 import org.apache.spark.sql.catalyst.parser.extensions.PaimonSparkSqlExtensionsParser
 import org.apache.spark.sql.catalyst.plans.logical.PaimonTableValuedFunctions
 import org.apache.spark.sql.execution.datasources.v2.ExtendedDataSourceV2Strategy
@@ -35,9 +34,7 @@ class PaimonSparkSessionExtensions extends (SparkSessionExtensions => Unit) {
     extensions.injectResolutionRule(sparkSession => new PaimonAnalysis(sparkSession))
     extensions.injectResolutionRule(spark => ResolveProcedures(spark))
     extensions.injectResolutionRule(_ => CoerceArguments)
-
-    // optimizer extensions
-    extensions.injectOptimizerRule(_ => RewriteRowLeverCommands)
+    extensions.injectPostHocResolutionRule(_ => RewriteRowLevelCommands)
 
     // table function extensions
     PaimonTableValuedFunctions.supportedFnNames.foreach {
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala
index bae79127c..3b42dba1b 100644
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala
@@ -25,7 +25,7 @@ object SparkSystemColumns {
   // for assigning bucket when writing
   val BUCKET_COL = "_bucket_"
 
-  // for row lever operation
+  // for row level operation
   val ROW_KIND_COL = "_row_kind_"
 
   val SPARK_SYSTEM_COLUMNS_NAME: Seq[String] = Seq(BUCKET_COL, ROW_KIND_COL)
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/RowLevelOp.scala
similarity index 60%
copy from paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala
copy to paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/RowLevelOp.scala
index bae79127c..620ab814a 100644
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/paimon/spark/schema/SparkSystemColumns.scala
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/RowLevelOp.scala
@@ -15,22 +15,23 @@
  * See the License for the specific language governing permissions and
  * limitations under the License.
  */
-package org.apache.paimon.spark.schema
+package org.apache.spark.sql
 
-import org.apache.spark.sql.types.StructType
+import org.apache.paimon.CoreOptions.MergeEngine
 
-/** System columns for paimon spark. */
-object SparkSystemColumns {
+sealed trait RowLevelOp {
+  val supportedMergeEngine: Seq[MergeEngine]
+}
 
-  // for assigning bucket when writing
-  val BUCKET_COL = "_bucket_"
+case object Delete extends RowLevelOp {
+  override def toString: String = "delete"
 
-  // for row lever operation
-  val ROW_KIND_COL = "_row_kind_"
+  override val supportedMergeEngine: Seq[MergeEngine] = Seq(MergeEngine.DEDUPLICATE)
+}
 
-  val SPARK_SYSTEM_COLUMNS_NAME: Seq[String] = Seq(BUCKET_COL, ROW_KIND_COL)
+case object Update extends RowLevelOp {
+  override def toString: String = "update"
 
-  def filterSparkSystemColumns(schema: StructType): StructType = {
-    StructType(schema.fields.filterNot(field => SPARK_SYSTEM_COLUMNS_NAME.contains(field.name)))
-  }
+  override val supportedMergeEngine: Seq[MergeEngine] =
+    Seq(MergeEngine.DEDUPLICATE, MergeEngine.PARTIAL_UPDATE)
 }
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/AssignmentAlignmentHelper.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/AssignmentAlignmentHelper.scala
new file mode 100644
index 000000000..33bec58da
--- /dev/null
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/AssignmentAlignmentHelper.scala
@@ -0,0 +1,119 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.spark.sql.catalyst.analysis
+
+import org.apache.spark.sql.catalyst.SQLConfHelper
+import org.apache.spark.sql.catalyst.expressions.{Alias, Attribute, AttributeReference, CreateNamedStruct, Expression, GetStructField, Literal, NamedExpression}
+import org.apache.spark.sql.catalyst.plans.logical.Assignment
+import org.apache.spark.sql.types.StructType
+
+trait AssignmentAlignmentHelper extends SQLConfHelper {
+
+  private lazy val resolver = conf.resolver
+
+  /**
+   * @param ref
+   *   attribute reference seq, e.g. a => Seq["a"], s.c1 => Seq["s", "c1"]
+   * @param expr
+   *   update expression
+   */
+  private case class AttrUpdate(ref: Seq[String], expr: Expression)
+
+  /**
+   * Align update assignments to the given attrs, only supports PrimitiveType and StructType. For
+   * example, if attrs are [a int, b int, s struct(c1 int, c2 int)] and update assignments are [a =
+   * 1, s.c1 = 2], will return [1, b, struct(2, c2)].
+   * @param attrs
+   *   target attrs
+   * @param assignments
+   *   update assignments
+   * @return
+   *   aligned update expressions
+   */
+  protected def alignUpdateAssignments(
+      attrs: Seq[Attribute],
+      assignments: Seq[Assignment]): Seq[Expression] = {
+    val attrUpdates = assignments.map(a => AttrUpdate(toRefSeq(a.key), a.value))
+    recursiveAlignUpdates(attrs, attrUpdates)
+  }
+
+  def toRefSeq(expr: Expression): Seq[String] = expr match {
+    case attr: Attribute =>
+      Seq(attr.name)
+    case GetStructField(child, _, Some(name)) =>
+      toRefSeq(child) :+ name
+    case other =>
+      throw new UnsupportedOperationException(
+        s"Unsupported update expression: $other, only support update with PrimitiveType and StructType.")
+  }
+
+  private def recursiveAlignUpdates(
+      targetAttrs: Seq[NamedExpression],
+      updates: Seq[AttrUpdate],
+      namePrefix: Seq[String] = Nil): Seq[Expression] = {
+
+    // build aligned updated expression for each target attr
+    targetAttrs.map {
+      targetAttr =>
+        val headMatchedUpdates = updates.filter(u => resolver(u.ref.head, targetAttr.name))
+        if (headMatchedUpdates.isEmpty) {
+          // when no matched update, return the attr as is
+          targetAttr
+        } else {
+          val exactMatchedUpdate = headMatchedUpdates.find(_.ref.size == 1)
+          if (exactMatchedUpdate.isDefined) {
+            if (headMatchedUpdates.size == 1) {
+              // when an exact match (no nested fields) occurs, it must be the only match, then return it's expr
+              exactMatchedUpdate.get.expr
+            } else {
+              // otherwise, there must be conflicting updates, for example:
+              // - update the same attr multiple times
+              // - update a struct attr and its fields at the same time (e.g. s and s.c1)
+              val conflictingAttrNames =
+                headMatchedUpdates.map(u => (namePrefix ++ u.ref).mkString(".")).distinct
+              throw new UnsupportedOperationException(
+                s"Conflicting updates on attrs: ${conflictingAttrNames.mkString(", ")}"
+              )
+            }
+          } else {
+            targetAttr.dataType match {
+              case StructType(fields) =>
+                val fieldExprs = fields.zipWithIndex.map {
+                  case (field, ordinal) =>
+                    Alias(GetStructField(targetAttr, ordinal, Some(field.name)), field.name)()
+                }
+                val newUpdates = updates.map(u => u.copy(ref = u.ref.tail))
+                // process StructType's nested fields recursively
+                val updatedFieldExprs =
+                  recursiveAlignUpdates(fieldExprs, newUpdates, namePrefix :+ targetAttr.name)
+
+                // build updated struct expression
+                CreateNamedStruct(fields.zip(updatedFieldExprs).flatMap {
+                  case (field, expr) =>
+                    Seq(Literal(field.name), expr)
+                })
+              case _ =>
+                // can't reach here
+                throw new UnsupportedOperationException("")
+            }
+          }
+        }
+    }
+  }
+
+}
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommands.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommands.scala
new file mode 100644
index 000000000..b5cf04c49
--- /dev/null
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/analysis/RewriteRowLevelCommands.scala
@@ -0,0 +1,117 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.spark.sql.catalyst.analysis
+
+import org.apache.paimon.CoreOptions.MERGE_ENGINE
+import org.apache.paimon.options.Options
+import org.apache.paimon.spark.SparkTable
+import org.apache.paimon.table.Table
+
+import org.apache.spark.sql.{AnalysisException, Delete, RowLevelOp, Update}
+import org.apache.spark.sql.catalyst.expressions.{Attribute, Expression, PredicateHelper, SubqueryExpression}
+import org.apache.spark.sql.catalyst.plans.logical._
+import org.apache.spark.sql.catalyst.rules.Rule
+import org.apache.spark.sql.connector.catalog.SupportsDelete
+import org.apache.spark.sql.execution.datasources.DataSourceStrategy
+import org.apache.spark.sql.execution.datasources.v2.DataSourceV2Relation
+
+import java.util
+
+object RewriteRowLevelCommands extends Rule[LogicalPlan] with PredicateHelper {
+
+  override def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperators {
+    case d @ DeleteFromTable(ResolvesToPaimonTable(table), condition) =>
+      validateRowLevelOp(Delete, table.getTable, Option.empty)
+      if (canDeleteWhere(d, table, condition)) {
+        d
+      } else {
+        DeleteFromPaimonTableCommand(d)
+      }
+
+    case u @ UpdateTable(ResolvesToPaimonTable(table), assignments, _) =>
+      validateRowLevelOp(Update, table.getTable, Option.apply(assignments))
+      UpdatePaimonTableCommand(u)
+  }
+
+  private object ResolvesToPaimonTable {
+    def unapply(plan: LogicalPlan): Option[SparkTable] =
+      EliminateSubqueryAliases(plan) match {
+        case DataSourceV2Relation(table: SparkTable, _, _, _, _) => Some(table)
+        case _ => None
+      }
+  }
+
+  private def validateRowLevelOp(
+      op: RowLevelOp,
+      table: Table,
+      assignments: Option[Seq[Assignment]]): Unit = {
+    val options = Options.fromMap(table.options)
+    val primaryKeys = table.primaryKeys()
+    if (primaryKeys.isEmpty) {
+      throw new UnsupportedOperationException(
+        s"table ${table.getClass.getName} can not support $op, because there is no primary key.")
+    }
+
+    if (op.equals(Update) && isPrimaryKeyUpdate(primaryKeys, assignments.get)) {
+      throw new UnsupportedOperationException(s"$op to primary keys is not supported.")
+    }
+
+    val mergeEngine = options.get(MERGE_ENGINE)
+    if (!op.supportedMergeEngine.contains(mergeEngine)) {
+      throw new UnsupportedOperationException(
+        s"merge engine $mergeEngine can not support $op, currently only ${op.supportedMergeEngine
+            .mkString(", ")} can support $op.")
+    }
+  }
+
+  private def canDeleteWhere(
+      d: DeleteFromTable,
+      table: SparkTable,
+      condition: Expression): Boolean = {
+    table match {
+      case t: SupportsDelete if !SubqueryExpression.hasSubquery(condition) =>
+        // fail if any filter cannot be converted.
+        // correctness depends on removing all matching data.
+        val filters = DataSourceStrategy
+          .normalizeExprs(Seq(condition), d.output)
+          .flatMap(splitConjunctivePredicates(_).map {
+            f =>
+              DataSourceStrategy
+                .translateFilter(f, supportNestedPredicatePushdown = true)
+                .getOrElse(throw new AnalysisException(s"Exec update failed:" +
+                  s" cannot translate expression to source filter: $f"))
+          })
+          .toArray
+        t.canDeleteWhere(filters)
+      case _ => false
+    }
+  }
+
+  private def isPrimaryKeyUpdate(
+      primaryKeys: util.List[String],
+      assignments: Seq[Assignment]): Boolean = {
+    assignments.exists(
+      a => {
+        a.key match {
+          case attr: Attribute => primaryKeys.contains(attr.name)
+          case _ => false
+        }
+      })
+  }
+
+}
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteRowLeverCommands.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteRowLeverCommands.scala
deleted file mode 100644
index 807b851e8..000000000
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/optimizer/RewriteRowLeverCommands.scala
+++ /dev/null
@@ -1,82 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements.  See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership.  The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License.  You may obtain a copy of the License at
- *
- *     http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package org.apache.spark.sql.catalyst.optimizer
-
-import org.apache.paimon.CoreOptions.{MERGE_ENGINE, MergeEngine}
-import org.apache.paimon.options.Options
-import org.apache.paimon.spark.SparkTable
-import org.apache.paimon.table.Table
-
-import org.apache.spark.sql.AnalysisException
-import org.apache.spark.sql.catalyst.expressions.{Expression, PredicateHelper, SubqueryExpression}
-import org.apache.spark.sql.catalyst.plans.logical.{DeleteFromPaimonTableCommand, DeleteFromTable, LogicalPlan}
-import org.apache.spark.sql.catalyst.rules.Rule
-import org.apache.spark.sql.connector.catalog.SupportsDelete
-import org.apache.spark.sql.execution.datasources.DataSourceStrategy
-import org.apache.spark.sql.execution.datasources.v2.DataSourceV2Relation
-
-object RewriteRowLeverCommands extends Rule[LogicalPlan] with PredicateHelper {
-  override def apply(plan: LogicalPlan): LogicalPlan = plan.resolveOperators {
-    case d @ DeleteFromTable(r: DataSourceV2Relation, condition) =>
-      validateDeletable(r.table.asInstanceOf[SparkTable].getTable)
-      if (canDeleteWhere(r, condition)) {
-        d
-      } else {
-        DeleteFromPaimonTableCommand(r, condition)
-      }
-  }
-
-  private def validateDeletable(table: Table): Boolean = {
-    val options = Options.fromMap(table.options)
-    if (table.primaryKeys().isEmpty) {
-      throw new UnsupportedOperationException(
-        String.format(
-          "table '%s' can not support delete, because there is no primary key.",
-          table.getClass.getName))
-    }
-    if (!options.get(MERGE_ENGINE).equals(MergeEngine.DEDUPLICATE)) {
-      throw new UnsupportedOperationException(
-        String.format(
-          "merge engine '%s' can not support delete, currently only %s can support delete.",
-          options.get(MERGE_ENGINE),
-          MergeEngine.DEDUPLICATE))
-    }
-    true
-  }
-
-  private def canDeleteWhere(relation: DataSourceV2Relation, condition: Expression): Boolean = {
-    relation.table match {
-      case t: SupportsDelete if !SubqueryExpression.hasSubquery(condition) =>
-        // fail if any filter cannot be converted.
-        // correctness depends on removing all matching data.
-        val filters = DataSourceStrategy
-          .normalizeExprs(Seq(condition), relation.output)
-          .flatMap(splitConjunctivePredicates(_).map {
-            f =>
-              DataSourceStrategy
-                .translateFilter(f, supportNestedPredicatePushdown = true)
-                .getOrElse(throw new AnalysisException(s"Exec update failed:" +
-                  s" cannot translate expression to source filter: $f"))
-          })
-          .toArray
-        t.canDeleteWhere(filters)
-      case _ => false
-    }
-  }
-
-}
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala
index f380533a8..92833cf1a 100644
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala
@@ -25,20 +25,23 @@ import org.apache.paimon.table.FileStoreTable
 import org.apache.paimon.types.RowKind
 
 import org.apache.spark.sql.{Dataset, Row, SparkSession}
-import org.apache.spark.sql.catalyst.expressions.Expression
+import org.apache.spark.sql.catalyst.analysis.EliminateSubqueryAliases
 import org.apache.spark.sql.execution.command.LeafRunnableCommand
 import org.apache.spark.sql.execution.datasources.v2.DataSourceV2Relation
 import org.apache.spark.sql.functions.lit
 
-case class DeleteFromPaimonTableCommand(relation: DataSourceV2Relation, condition: Expression)
-  extends LeafRunnableCommand {
+case class DeleteFromPaimonTableCommand(d: DeleteFromTable) extends LeafRunnableCommand {
 
   override def run(sparkSession: SparkSession): Seq[Row] = {
+    val relation = EliminateSubqueryAliases(d.table).asInstanceOf[DataSourceV2Relation]
+    val condition = d.condition
+
     val filteredPlan = if (condition != null) {
       Filter(condition, relation)
     } else {
       relation
     }
+
     val df = Dataset
       .ofRows(sparkSession, filteredPlan)
       .withColumn(ROW_KIND_COL, lit(RowKind.DELETE.toByteValue))
diff --git a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/UpdatePaimonTableCommand.scala
similarity index 67%
copy from paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala
copy to paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/UpdatePaimonTableCommand.scala
index f380533a8..c9ec48a16 100644
--- a/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/DeleteFromPaimonTableCommand.scala
+++ b/paimon-spark/paimon-spark-common/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/UpdatePaimonTableCommand.scala
@@ -25,23 +25,31 @@ import org.apache.paimon.table.FileStoreTable
 import org.apache.paimon.types.RowKind
 
 import org.apache.spark.sql.{Dataset, Row, SparkSession}
-import org.apache.spark.sql.catalyst.expressions.Expression
+import org.apache.spark.sql.catalyst.analysis.{AssignmentAlignmentHelper, EliminateSubqueryAliases}
+import org.apache.spark.sql.catalyst.expressions.{Alias, Expression, If}
+import org.apache.spark.sql.catalyst.expressions.Literal.TrueLiteral
 import org.apache.spark.sql.execution.command.LeafRunnableCommand
 import org.apache.spark.sql.execution.datasources.v2.DataSourceV2Relation
 import org.apache.spark.sql.functions.lit
 
-case class DeleteFromPaimonTableCommand(relation: DataSourceV2Relation, condition: Expression)
-  extends LeafRunnableCommand {
+case class UpdatePaimonTableCommand(u: UpdateTable)
+  extends LeafRunnableCommand
+  with AssignmentAlignmentHelper {
 
   override def run(sparkSession: SparkSession): Seq[Row] = {
-    val filteredPlan = if (condition != null) {
-      Filter(condition, relation)
-    } else {
-      relation
-    }
+
+    val relation = EliminateSubqueryAliases(u.table).asInstanceOf[DataSourceV2Relation]
+
+    val updatedExprs: Seq[Alias] =
+      alignUpdateAssignments(relation.output, u.assignments).zip(relation.output).map {
+        case (expr, attr) => Alias(expr, attr.name)()
+      }
+
+    val updatedPlan = Project(updatedExprs, Filter(u.condition.getOrElse(TrueLiteral), relation))
+
     val df = Dataset
-      .ofRows(sparkSession, filteredPlan)
-      .withColumn(ROW_KIND_COL, lit(RowKind.DELETE.toByteValue))
+      .ofRows(sparkSession, updatedPlan)
+      .withColumn(ROW_KIND_COL, lit(RowKind.UPDATE_AFTER.toByteValue))
 
     WriteIntoPaimonTable(
       relation.table.asInstanceOf[SparkTable].getTable.asInstanceOf[FileStoreTable],
diff --git a/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/DeleteFromTableTest.scala b/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/DeleteFromTableTest.scala
index 2008d7775..7623e08ab 100644
--- a/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/DeleteFromTableTest.scala
+++ b/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/DeleteFromTableTest.scala
@@ -20,6 +20,7 @@ package org.apache.paimon.spark.sql
 import org.apache.paimon.CoreOptions
 import org.apache.paimon.spark.PaimonSparkTestBase
 
+import org.apache.spark.sql.Delete
 import org.assertj.core.api.Assertions.{assertThat, assertThatThrownBy}
 
 class DeleteFromTableTest extends PaimonSparkTestBase {
@@ -39,7 +40,6 @@ class DeleteFromTableTest extends PaimonSparkTestBase {
     mergeEngine =>
       {
         test(s"test delete with merge engine $mergeEngine") {
-          val supportUpdateEngines = Seq("deduplicate")
           val options = if ("first-row".equals(mergeEngine.toString)) {
             s"'primary-key' = 'id', 'merge-engine' = '$mergeEngine', 'changelog-producer' = 'lookup'"
           } else {
@@ -52,7 +52,7 @@ class DeleteFromTableTest extends PaimonSparkTestBase {
 
           spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22')")
 
-          if (supportUpdateEngines.contains(mergeEngine.toString)) {
+          if (Delete.supportedMergeEngine.contains(mergeEngine)) {
             spark.sql("DELETE FROM T WHERE name = 'a'")
           } else
             assertThatThrownBy(() => spark.sql("DELETE FROM T WHERE name = 'a'"))
diff --git a/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/UpdateTableTest.scala b/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/UpdateTableTest.scala
new file mode 100644
index 000000000..52f83d757
--- /dev/null
+++ b/paimon-spark/paimon-spark-common/src/test/scala/org/apache/paimon/spark/sql/UpdateTableTest.scala
@@ -0,0 +1,230 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.paimon.spark.sql
+
+import org.apache.paimon.CoreOptions
+import org.apache.paimon.spark.PaimonSparkTestBase
+
+import org.apache.spark.sql.Update
+import org.assertj.core.api.Assertions.{assertThat, assertThatThrownBy}
+
+class UpdateTableTest extends PaimonSparkTestBase {
+
+  test(s"test update append only table") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22')")
+
+    assertThatThrownBy(() => spark.sql("UPDATE T SET name = 'a_new' WHERE id = 1"))
+      .hasMessageContaining("can not support update, because there is no primary key")
+  }
+
+  CoreOptions.MergeEngine.values().foreach {
+    mergeEngine =>
+      {
+        test(s"test update with merge engine $mergeEngine") {
+          val options = if ("first-row".equals(mergeEngine.toString)) {
+            s"'primary-key' = 'id', 'merge-engine' = '$mergeEngine', 'changelog-producer' = 'lookup'"
+          } else {
+            s"'primary-key' = 'id', 'merge-engine' = '$mergeEngine'"
+          }
+          spark.sql(s"""
+                       |CREATE TABLE T (id INT, name STRING, dt STRING)
+                       |TBLPROPERTIES ($options)
+                       |""".stripMargin)
+
+          spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22')")
+
+          if (Update.supportedMergeEngine.contains(mergeEngine)) {
+            spark.sql("UPDATE T SET name = 'a_new' WHERE id = 1")
+            val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+            assertThat(rows.toString).isEqualTo("[[1,a_new,11], [2,b,22]]")
+          } else
+            assertThatThrownBy(() => spark.sql("UPDATE T SET name = 'a_new' WHERE id = 1"))
+              .isInstanceOf(classOf[UnsupportedOperationException])
+        }
+      }
+  }
+
+  test(s"test update with primary key") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id', 'merge-engine' = 'deduplicate')
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    assertThatThrownBy(() => spark.sql("UPDATE T SET id = 11 WHERE name = 'a'"))
+      .hasMessageContaining("update to primary keys is not supported")
+  }
+
+  test(s"test update with no where") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (id)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    spark.sql("UPDATE T SET name = 'a_new'")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,a_new,11], [2,a_new,22], [3,a_new,33]]")
+  }
+
+  test(s"test update with alias") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (id)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    spark.sql("UPDATE T AS t SET t.name = 'a_new' where id = 1")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,a_new,11], [2,b,22], [3,c,33]]")
+  }
+
+  test(s"test update with alias assignment") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, c1 INT, c2 INT)
+                 |TBLPROPERTIES ('primary-key' = 'id', 'merge-engine' = 'deduplicate')
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 1, 11), (2, 2, 22), (3, 3, 33)")
+
+    spark.sql("UPDATE T set c1 = c1 + 1, c2 = c2 + 1 where id = 1")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,2,12], [2,2,22], [3,3,33]]")
+  }
+
+  test(s"test update with in condition and not in condition") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    spark.sql("UPDATE T set name = 'in_new' WHERE id IN (1)")
+    val rows1 = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows1.toString).isEqualTo("[[1,in_new,11], [2,b,22], [3,c,33]]")
+
+    spark.sql("UPDATE T set name = 'not_in_new' WHERE id NOT IN (2)")
+    val rows2 = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows2.toString).isEqualTo("[[1,not_in_new,11], [2,b,22], [3,not_in_new,33]]")
+  }
+
+  test(s"test update with in subquery") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    import testImplicits._
+    val df = Seq(1, 2).toDF("id")
+    df.createOrReplaceTempView("updated_ids")
+    spark.sql("UPDATE T set name = 'in_new' WHERE id IN (SELECT * FROM updated_ids)")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,in_new,11], [2,in_new,22], [3,c,33]]")
+  }
+
+  test(s"test update with self subquery") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, name STRING, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 'a', '11'), (2, 'b', '22'), (3, 'c', '33')")
+
+    spark.sql("UPDATE T set name = 'in_new' WHERE id IN (SELECT id + 1 FROM T)")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,a,11], [2,in_new,22], [3,in_new,33]]")
+  }
+
+  test(s"test update with various column references") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, c1 INT, c2 INT, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql("INSERT INTO T VALUES (1, 1, 10, '11'), (2, 2, 20, '22'), (3, 3, 300, '33')")
+
+    spark.sql("UPDATE T SET c1 = c2 + 1, c2 = 1000")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,11,1000,11], [2,21,1000,22], [3,301,1000,33]]")
+  }
+
+  test(s"test update with struct column") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, s STRUCT<c1: INT, c2: STRING>, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql(
+      "INSERT INTO T VALUES (1, struct(1, 'a'), '11'), (2, struct(2, 'b'), '22'), (3, struct(3, 'c'), '33')")
+
+    spark.sql("UPDATE T SET s.c2 = 'a_new' WHERE s.c1 = 1")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo("[[1,[1,a_new],11], [2,[2,b],22], [3,[3,c],33]]")
+  }
+
+  test(s"test update with map column") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, m MAP<INT, STRING>, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql(
+      "INSERT INTO T VALUES (1, map(1, 'a'), '11'), (2, map(2, 'b'), '22'), (3, map(3, 'c'), '33')")
+
+    assertThatThrownBy(() => spark.sql("UPDATE T SET m.key = 11 WHERE id = 1"))
+      .hasMessageContaining("Unsupported update expression")
+
+    spark.sql("UPDATE T SET m = map(11, 'a_new') WHERE id = 1")
+    val rows = spark.sql("SELECT * FROM T ORDER BY id").collectAsList()
+    assertThat(rows.toString).isEqualTo(
+      "[[1,Map(11 -> a_new),11], [2,Map(2 -> b),22], [3,Map(3 -> c),33]]")
+  }
+
+  test(s"test update with conflicted column") {
+    spark.sql(s"""
+                 |CREATE TABLE T (id INT, s STRUCT<c1: INT, c2: STRING>, dt STRING)
+                 |TBLPROPERTIES ('primary-key' = 'id, dt', 'merge-engine' = 'deduplicate')
+                 |PARTITIONED BY (dt)
+                 |""".stripMargin)
+
+    spark.sql(
+      "INSERT INTO T VALUES (1, struct(1, 'a'), '11'), (2, struct(2, 'b'), '22'), (3, struct(3, 'c'), '33')")
+
+    assertThatThrownBy(
+      () => spark.sql("UPDATE T SET s.c2 = 'a_new', s = struct(11, 'a_new') WHERE s.c1 = 1"))
+      .hasMessageContaining("Conflicting updates on attrs: s.c2, s")
+  }
+}