pyspark - 使用 pyspark 连接 Microsoft SQL Server，抛出错误 :

Question

请指导我使用 Pyspark 连接 MS SQL 并从中读取数据的步骤。下面是我的代码和我在尝试从 MS SQL Server 加载数据时收到的错误消息。请指导我。

import urllib
import findspark
findspark.init()
from pyspark import SparkConf, SparkContext

from pyspark.sql import SQLContext

APP_NAME = 'My Spark Application'

conf = SparkConf().setAppName("APP_NAME").setMaster("local[4]")
sc = SparkContext(conf=conf)

sqlcontext = SQLContext(sc)

jdbcDF = sqlcontext.read.format("jdbc")\
  .option("url", "jdbc:sqlserver:XXXX:1433")\
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")\
  .option("dbtable", "dbo.XXXX")\
  .option("user", "XXXX")\
  .option("password", "XXX")\
  .load() 

*********************************错误****************** ***********************

teway.py", line 1133, in __call__
    answer, self.gateway_client, self.target_id, self.name)
  File "C:\spark-2.0.1-bin-hadoop2.6\python\pyspark\sql\utils.py", line 63, in d
eco
    return f(*a, **kw)
  File "C:\spark-2.0.1-bin-hadoop2.6\python\lib\py4j-0.10.3-src.zip\py4j\protoco
l.py", line 319, in get_return_value
    format(target_id, ".", name), value)
py4j.protocol.Py4JJavaError: An error occurred while calling o66.load.
: java.lang.NullPointerException
        at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable
(JDBCRDD.scala:167)
        at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.<init>(J
DBCRelation.scala:117)
        at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.
createRelation(JdbcRelationProvider.scala:53)
        at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation
(DataSource.scala:330)
        at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:149)
        at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:122)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:237)
        at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
        at py4j.Gateway.invoke(Gateway.java:280)
        at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
        at py4j.commands.CallCommand.execute(CallCommand.java:79)
        at py4j.GatewayConnection.run(GatewayConnection.java:214)
        at java.lang.Thread.run(Thread.java:745)

Answer 1

以下解决方案对我有用:

将 mssql-jdbc-7.0.0.jre8.jar 文件包含到 jars 子文件夹中(例如:C:\spark\spark-2.2.2-bin-hadoop2.7\jars)或者您可以根据您的系统粘贴任何 jar 文件。

然后使用以下命令连接到 MS SQL 服务器并创建 Spark Dataframe:

dbData = spark.read.jdbc("jdbc:sqlserver://servername;databaseName:ExampleDB;user:username;password:password","tablename")