在没有PYODBC的数据中查询SQL
#python #database #spark #databricks

好吧,所以这可能是一个小众帖子,但仍然很重要。

我看到的很多人是在问有关如何执行诸如Databricks上的SQL数据库上的任意SQL语句的问题,或者如何执行存储过程。通常,这会导致有关如何与Unix驱动程序一起安装PyoDBC的相同随访,并添加Microsoft的软件包回购,接受Eula等...

它有效,不要误会我的意思,但这是一个faff,尤其是如果您没有许可执行这些事情。加上Microsoft的包装库过去曾经有...问题,并且由于无法连接而突然生产作业失败了。我以前曾经对此有posted,所以有其他方法,但仍然是一个faff。

那么,如果您想连接到SQL并安装PYODBC怎么办?

使用已经可用的东西

是的,您只能使用已经可用的东西来完成此操作,并且可以轻松地归功于PY4J。 PY4J在Spark中很重要,因为Spark在JVM上运行,那么所有这些Pyspark调用如何被执行?好吧,它通过PY4J称为引擎盖下的Java/Scala方法。这不仅适用于火花。

使其与Microsoft SQL JDBC库(以及其他其他人(例如PostgreSQL))寄出的Azure SQL Databricks连接器工作,我们可以在Python中访问它们。

为了使事情尽可能安全,我将使用服务主体来显示如何进行访问,但这也与基于SQL的身份验证同样可以。

怎么运行的

所以首先我要假设一些事情。

  1. 您有一个SQL数据库,您可以连接到
  2. 存在具有权限的服务主体(或SQL帐户)
  3. 您可以访问Databricks
  4. 凭据处于秘密范围(如果不是这样,为什么不!)

我们需要的第一件事是对SQLServerDataSource的引用。

SQLServerDataSource = spark._sc._gateway.jvm.com.microsoft.sqlserver.jdbc.SQLServerDataSource

这就是魔术线,使我们可以从Python访问JVM中的某些东西。因此,我们现在有一个引用此对象的Python变量。现在我们可以使用它。

client_id = dbutils.secrets.get(secret_scope, '<secret name>')
client_secret = dbutils.secrets.get(secret_scope, '<secret name>')

datasource = SQLServerDataSource()
datasource.setServerName(f'{sql_server}.database.windows.net')
datasource.setDatabaseName(database_name)
datasource.setAuthentication('ActiveDirectoryServicePrincipal')
datasource.setAADSecurePrincipalId(client_id)
datasource.setAADSecurePrincipalSecret(client_secret)

在这里,我们将从我们的秘密范围中获取服务主体的应用程序ID和客户秘密。然后,我们正在创建一个SQLServerDataSource实例,并使用基于AAD的身份验证将其配置为连接到我们的数据库(有关其他选项,请参见connection string settings文档)。

现在,我们阅读了执行某件事。因此,让我们进行一个简单的查询以获取用户列表。

connection = datasource.getConnection()
statement = connection.createStatement()

try:
  results = statement.executeQuery('SELECT name FROM sysusers')
  while results.next():
    print(results.getString('name'))
except:
  print('oops')

因此,我们从数据源获得连接,获取语句对象,执行查询,然后在结果上迭代。

就是这样!

还有其他方法可以让您准备以安全的方式使用参数的语句,并且如果您不期望结果(例如调用存储过程时),则可以使用execute方法。或者,也许您想在将数据帧写入SQL之前确保存在数据库架构。

statement.execute("IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = 'MyCoolSchema') BEGIN EXEC('CREATE SCHEMA [MyCoolSchema]') END")

data source sample文档中有一些如何执行此操作的示例。

,但我们不必安装任何新的驱动程序或软件包来实现这一目标。

有问题吗?

是。不要为此疯狂。 Spark是一个用于处理大量数据的大数据平台,它并非旨在进行大量小查询,这可以减少Spark JDBC操作的连接可用性。但是,就像任何东西一样,如果您按照打算使用它们的方式使用这些工具,那么您就不应该有任何问题。