如何在Databricks SQL连接器中访问含特殊字符的Schema？

方法1：直接在dbtable参数中指定完整带括号的表路径 #

无需单独设置schemaname，将数据库、schema、表名组合成带方括号的格式直接传给dbtable，彻底规避转义问题：

hostname = "XXXX"
username = "XXXX"
password = "XXXX"
# 用双反斜杠转义，或使用Python原始字符串r"[DB_NAME].[XX\YY].[xx]"
full_table_name = "[DB_NAME].[XX\\YY].[xx]"

df_nbpl = (spark.read
       .format("sqlserver")
       .option("host", hostname)
       .option("user", username)
       .option("trustServerCertificate", "True")
       .option("encrypt", "True")
       .option("password", password)
       .option("dbtable", full_table_name)
       .load()
    )

方法2：调整schemaname的转义规则 #

若坚持分开设置schemaname和tablename，可尝试两种转义方式：

hostname = "XXXX"
username = "XXXX"
password = "XXXX"
# 方式A：使用Python原始字符串，避免自动转义
schemaname = r"XX\YY"
# 方式B：用四重反斜杠，适配Python+JDBC的双层转义
# schemaname = "XX\\\\YY"
tablename = "xx"

df_nbpl = (spark.read
       .format("sqlserver")
       .option("host", hostname)
       .option("user", username)
       .option("trustServerCertificate", "True")
       .option("encrypt", "True")
       .option("password", password)
       .option("database", databasename)
       .option("schemaname", schemaname)
       .option("dbtable", tablename)
       .load()
    )

方法3：通过query参数执行自定义SQL #

如果上述方法仍无效，直接用query参数执行查询，完全绕开schemaname的解析逻辑：

hostname = "XXXX"
username = "XXXX"
password = "XXXX"
# 同样注意反斜杠转义，或用原始字符串
query = "SELECT * FROM [DB_NAME].[XX\\YY].[xx]"

df_nbpl = (spark.read
       .format("sqlserver")
       .option("host", hostname)
       .option("user", username)
       .option("trustServerCertificate", "True")
       .option("encrypt", "True")
       .option("password", password)
       .option("query", query)
       .load()
    )

内容的提问来源于stack exchange，提问作者Bjarne Pedersen