无法基于Microsoft Azure SQL数据库表在Microsoft Azure Databricks中创建表

Question

我想从我的Microsoft Azure Databricks笔记本连接到Microsoft Azure SQL Server和Microsoft Azure SQL数据库，并执行选择和插入操作。

假设我有一个名为dev-sql-srv的Microsoft SQL Server，可访问主机名为dev-sql-srv.database.windows.net。

%sql
CREATE TABLE CustomerT1
USING org.apache.spark.sql.jdbc
OPTIONS (
  url "jdbc:sqlserver://dev-sql-srv.database.windows.net:1433",
  table "dev-sql-srv-db1.CUSTOMER",
  user "myAdmin",
  password "myPassword"
)

当我在笔记本中执行此操作时，我收到以下错误：

SQL语句中的错误：IllegalArgumentException：要求失败：选项'dbtable'是必需的。com.databricks.backend.common.rpc.databricksExceptions$sqlexecutionException:java.lang.IllegalArgumentException:需求失败：选项'dbtable'是必需的。在scala.predef$.require(predef.scala:224)在org.apache.spark.sql.execution.datasources.jdbc.jdbcoptions.(jdbcoptions.scala:68)在org.apache.spark.sql.execution.datasources.jdbc.c.jdbcoptions.(jdbcoptions.scala:35)在org.apache.spark.sql.execution.datasources.jdbcoptions.（在org.apache.spark.sql.execution.command.createDataSourceTableCommand.run(createDataSourceTables.scala:80)在org.apache.spark.sql.execution.command.executed.executedCommandExec.sideeffectResult$lzycompute(commands.scala:72)在.在org.apache.spark.sql.dataset$$anonfun$6.在org.apache.spark.sql.d应用(dataset.scala:194)Ataset$$AnonFun$53.在org.apache.spark.sql.execution.sqlexecution$.在org.apache.spark.sql.execution.sqlexecution$.应用(dataset.scala:3320).在org.apache.spark.execution.sqlexecutionenv(sqlexecution.scala:88)在org.apache.spark.sql.execution.sqlexecution$.在org.apache.spark.sql.execution.sqlexecution sql.sparksession.sql(sparksession.scala:639)在org.apache.spark.sql.sqlcontext.sql(sqlcontext.scala:707)在com.databricks.backend.daemon.driver.sqldriverlocal$$anonfun$1.apply(sqldriverlocal.scala:87)

这个例子有什么问题？我的参数肯定都没问题。

Answer 1

您必须将databasename添加到JDBC URL中：

%sql
CREATE TABLE CustomerT1
USING org.apache.spark.sql.jdbc
OPTIONS (
  url "jdbc:sqlserver://dev-sql-srv.database.windows.net:1433;database=dev-sql-srv-db1",
  dbtable "dbo.CUSTOMER",
  user "myAdmin",
  password "myPassword"
)