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

Question

我想从我的 Microsoft Azure Databricks Notebook 连接到 Microsoft Azure SQL Server 和 Microsoft Azure SQL 数据库，然后执行 SELECT 和 INSERT。

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

然后我有一个名为 dev-sql-srv-db1 的 SQL 数据库。例如，该表名为 CUSTOMER。

我在 https://docs.azuredatabricks.net/spark/latest/data-sources/sql-databases.html#spark-sql-example 的原始 Azure Databricks 文档中找到了这个.

当我做

%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"
)

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

Error in SQL statement: IllegalArgumentException: requirement failed: Option 'dbtable' is required. com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: java.lang.IllegalArgumentException: requirement failed: Option 'dbtable' is required. at scala.Predef$.require(Predef.scala:224) at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:68) at org.apache.spark.sql.execution.datasources.jdbc.JDBCOptions.(JDBCOptions.scala:35) at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:34) at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:349) at org.apache.spark.sql.execution.command.CreateDataSourceTableCommand.run(createDataSourceTables.scala:80) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:72) at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:70) at org.apache.spark.sql.execution.command.ExecutedCommandExec.executeCollect(commands.scala:81) at org.apache.spark.sql.Dataset$$anonfun$6.apply(Dataset.scala:194) at org.apache.spark.sql.Dataset$$anonfun$6.apply(Dataset.scala:194) at org.apache.spark.sql.Dataset$$anonfun$53.apply(Dataset.scala:3320) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:88) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:124) at org.apache.spark.sql.Dataset.withAction(Dataset.scala:3319) at org.apache.spark.sql.Dataset.(Dataset.scala:194) at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:78) at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:639) at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:707) at com.databricks.backend.daemon.driver.SQLDriverLocal$$anonfun$1.apply(SQLDriverLocal.scala:87)

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

Answer 1

您必须将数据库名称添加到 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"
)