下面是一个我比较满意的python解决方案：
功能定义：

%python
def merge(srcTableName, dstTableName, whereStr, joinStr, cols, selectCols, debug):

  # create the sql string
  sqlString = ""
  sqlString += "merge into " + dstTableName + " t\n"
  sqlString += "using ( \n"
  sqlString += "select \n"
  for str in selectCols[:-1]:
    sqlString += "  " + str + ", \n"
  sqlString += "  " + selectCols[-1] + "\n"
  sqlString += "from " + srcTableName
  sqlString += whereStr + "\n"
  sqlString += ") s\n"
  sqlString += "on \n"
  sqlString += joinStr + "\n"
  sqlString += "when matched then update set \n"
  updateCols = [s + " = s." + s for s in cols]
  for str in updateCols[:-1]:
    sqlString += "  " + str + ", \n"
  sqlString += "  " + updateCols[-1]
  sqlString += """
  when not matched then 
  insert (
  """
  for str in cols[:-1]:
    sqlString += "  " + str + ", \n"
  sqlString += "  " + cols[-1]
  sqlString += """
  )
  values (
  """
  for str in cols[:-1]:
    sqlString += "  " + str + ", \n"
  sqlString += "  " + cols[-1] + "\n"
  sqlString += ")"

  if debug == True:
    print(sqlString)

  spark.sql(sqlString)
  return sqlString

呼叫示例：

# define the tables (FROM MY_PROJECT.DEMO TO DEMO)

srcTableName = "my_project.demo"
dstTableName = "demo"

# define the where clause (ONLY AC DATA)

whereStr = """
where 1=1
  and org = 'ac'
  and org_patient_id is not null
"""

# define the join (MATCH ON PATIENT_ID)

joinStr = "t.patient_id = s.patient_id"

# define the columns (JUST THESE COLUMNS)

cols = [
  'org',
  'data_lot',
  'raw_table',
  'org_patient_id',
  'patient_id',
  'sex',
  'sexual_orientation',
  'year_of_birth',
  'year_of_death',
  'race',
  'ethnicity',
  'city',
  'state',
  'zip'  
]

# create any needed aliases for query string

selectCols = cols
selectCols = [x if x != 'year_of_birth' else '(2020 - age) as year_of_birth' for x in selectCols]

# do the merge

merge(srcTableName, dstTableName, whereStr, joinStr, cols, selectCols)
print("Done.")