SQL数据库使用JDBC+参数化SQL查询+Databricks

Question

在Databricks中，我将SQL表读取为

val TransformationRules = spark.read.jdbc(jdbcUrl, "ADF.TransformationRules", connectionProperties)
.select("RuleCode","SourceSystem","PrimaryTable", "PrimaryColumn", "SecondaryColumn", "NewColumnName","CurrentFlag")
.where("SourceSystem = 'QWDS' AND RuleCode = 'STD00003' ")

如何在Where子句中参数化SourceSystem和RuleCode

指的是：https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases

Answer 1

就我正确理解您的问题而言，您想在where子句字符串中插入值吗？也许下面的解决方案可以为您提供：

val TransformationRules = spark.read.jdbc(jdbcUrl, "ADF.TransformationRules", connectionProperties)
.select("RuleCode","SourceSystem","PrimaryTable", "PrimaryColumn", "SecondaryColumn", "NewColumnName","CurrentFlag")
.where("SourceSystem = '{}' AND RuleCode = '{}' ".format(sourceSystem, ruleCode))

Answer 2

如果导入spark隐式，则可以使用美元$插值器创建对列的引用。此外，您还可以使用带有列的API来生成逻辑，它将是这样的

val sourceSystem = "QWDS"
val ruleCode = "STD00003"

import spark.implicits._
val TransformationRules = spark.read.jdbc(jdbcUrl, "ADF.TransformationRules", connectionProperties)
.select("RuleCode","SourceSystem","PrimaryTable", "PrimaryColumn", "SecondaryColumn", "NewColumnName","CurrentFlag")
.where($"SourceSystem" === sourceSystem && $"RuleCode" === ruleCode)

val ssColumn: Column = $"SourceSystem"

正如您所看到的，美元将提供一个列对象，具有合作、强制转换重命名等逻辑。与org.apache.spark.sql.function中的函数相结合，将允许您实现几乎所有需要的功能