java 调用spark sqlserver,使用spark sql在sqlserver上执行查询

I am trying to get the row count and column count of all the tables in a schema in sql server using spark sql.

when I execute below query using sqoop, it's giving me the correct results.

sqoop eval --connect "jdbc:sqlserver://;database=" \

--username= --password= \

--query """SELECT

ta.name TableName ,

pa.rows RowCnt,

COUNT(ins.COLUMN_NAME) ColCnt FROM .sys.tables ta INNER JOIN

.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN

.sys.schemas sc ON ta.schema_id = sc.schema_id join

.INFORMATION_SCHEMA.COLUMNS ins on ins.TABLE_SCHEMA =sc.name and ins.TABLE_NAME=ta.name

WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and sc.name ='' GROUP BY sc.name, ta.name, pa.rows order by

TableName"""

But when I try to execute the same query from spark sql, I am getting an error that "com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'WHERE'"

Please help me out, if anyone has an idea about this error.

Below is the spark sql command I executed

spark-shell --jars "/var/lib/sqoop/sqljdbc42.jar"

sqlContext.read.format("jdbc").option("url", "jdbc:sqlserver://;database=;user=;password=").option("dbtable", """(SELECT

ta.name TableName ,pa.rows RowCnt,

COUNT(ins.COLUMN_NAME) ColCnt FROM .sys.tables ta INNER JOIN

.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN

.sys.schemas sc ON ta.schema_id = sc.schema_id join

.INFORMATION_SCHEMA.COLUMNS ins on ins.TABLE_SCHEMA =sc.name and ins.TABLE_NAME=ta.name

WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and sc.name ='' GROUP BY sc.name,ta.name, pa.rows)""").option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").load()

expected output:

TableName, RowCnt, ColCnt

table A, 62, 30

table B, 3846, 76

解决方案

The problem in your Spark SQL command is with the dbTable option.

dbTable accepts anything that is valid in a FROM clause of a SQL query can be used. For example, instead of a full table you could also use a subquery in parentheses. However, when using subqueries in parentheses, it should have an alias. Thus your command should be modified as,

sqlContext

.read

.format("jdbc")

.option("url", "jdbc:sqlserver://;database=;user=;password=")

.option("dbtable",

"""(SELECT

ta.name TableName ,

pa.rows RowCnt,

COUNT(ins.COLUMN_NAME) ColCnt

FROM .sys.tables ta

INNER JOIN

.sys.partitions pa

ON pa.OBJECT_ID = ta.OBJECT_ID

INNER JOIN

.sys.schemas sc

ON ta.schema_id = sc.schema_id

JOIN

.INFORMATION_SCHEMA.COLUMNS ins

ON ins.TABLE_SCHEMA = sc.name and ins.TABLE_NAME = ta.name

WHERE ta.is_ms_shipped = 0

AND pa.index_id IN (1,0)

AND sc.name =''

GROUP BY sc.name,ta.name, pa.rows) as TEMP""")

.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")

.load()

Just a hunch. Hope this helps!