在Upsolver中使用合并命令

Question

我希望在我的新转换中使用Upsolver命令来填充S3/Athena和雪花表。由于雪花支持Upsert命令，所以在定义转换任务时，我是依赖雪花功能并使用Upsolver INSER语句，还是像为Athena那样定义Upsolver合并转换，即

CREATE JOB my_job_upsert
    START_FROM = BEGINNING
    ADD_MISSING_COLUMNS = TRUE
    RUN_INTERVAL = 1 MINUTE
    AS MERGE INTO default_glue_catalog.upsolver_samples.test_upsert_with_merge AS target
    /*
        Use the SELECT statement below to choose your columns and performed the desired transformations.
    
    In this example, we aggregate the sample orders data by customer and filter it to only include repeat purchasers.
    */            
    USING (SELECT field1 AS email, 
         COUNT(DISTINCT field2) AS count
         MIN(field3) AS min_number,
         MAX(date) AS last_date
      FROM default_glue_catalog.upsolver_samples.test_raw_data
      WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
      GROUP BY 1
      HAVING COUNT(DISTINCT field2) > 1) source
          ON (target.email = source.email)--primary key
          WHEN MATCHED THEN REPLACE -- Update if primary keys match
          WHEN NOT MATCHED THEN INSERT MAP_COLUMNS_BY_NAME; -- Insert if primary key is unique (new record)

一般来说，最好知道合并命令语法是否在不同的目标平台上是一致的。

我已经构建了雅典娜转换，它按预期工作。

Answer 1

你也可以用你对雅典娜的方式。Upsolver INSERT命令将插入新键(追加)，如果表定义了主键，则insert命令将更新现有键(Upsert)作为其默认行为。

合并，因为它的定义是为UPSERT，并可以处理删除以及。语法在所有数据库/数据仓库/目录目标之间是一致的。