sql-server - 使用 spark sql 在 sqlserver 上执行查询

Question

我正在尝试使用 spark sql 获取 sql server 中架构中所有表的行数和列数。

当我使用 sqoop 执行以下查询时，它给了我正确的结果。

sqoop eval --connect "jdbc:sqlserver://<hostname>;database=<dbname>" \
--username=<username> --password=<pwd> \
--query """SELECT 
ta.name TableName ,
pa.rows RowCnt, 
COUNT(ins.COLUMN_NAME) ColCnt FROM <db>.sys.tables ta INNER JOIN 
<db>.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN 
<db>.sys.schemas sc ON ta.schema_id = sc.schema_id join 
<db>.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 ='<schema>' GROUP BY sc.name, ta.name, pa.rows order by 
TableName"""

但是当我尝试从 spark sql 执行相同的查询时，我收到一个错误“com.microsoft.sqlserver.jdbc.SQLServerException:关键字‘WHERE’附近的语法不正确” 如果有人对此错误有任何想法，请帮助我。

下面是我执行的spark sql命令spark-shell --jars "/var/lib/sqoop/sqljdbc42.jar"

sqlContext.read.format("jdbc").option("url", "jdbc:sqlserver://<hostname>;database=<dbname>;user=<user>;password=<pwd>").option("dbtable", """(SELECT 
ta.name TableName ,pa.rows RowCnt, 
COUNT(ins.COLUMN_NAME) ColCnt FROM <db>.sys.tables ta INNER JOIN 
<db>.sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN 
<db>.sys.schemas sc ON ta.schema_id = sc.schema_id join 
<db>.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 ='<schema>' GROUP BY sc.name,ta.name, pa.rows)""").option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").load()

预期输出:

表名、行数、列数

表A、62、30

表 B, 3846, 76

Answer 1

您的 Spark SQL 命令中的问题在于 dbTable 选项。

dbTable 接受在 SQL 查询的 FROM 子句中有效的任何内容。例如，您也可以在括号中使用子查询，而不是完整的表。但是，当在括号中使用子查询时，它应该有一个别名。因此，您的命令应修改为，

sqlContext
.read
.format("jdbc")
.option("url", "jdbc:sqlserver://<hostname>;database=<dbname>;user=<user>;password=<pwd>")
.option("dbtable", 
    """(SELECT 
    ta.name TableName ,
    pa.rows RowCnt, 
    COUNT(ins.COLUMN_NAME) ColCnt 
    FROM <db>.sys.tables ta 
    INNER JOIN 
    <db>.sys.partitions pa 
    ON pa.OBJECT_ID = ta.OBJECT_ID 
    INNER JOIN 
    <db>.sys.schemas sc 
    ON ta.schema_id = sc.schema_id 
    JOIN 
    <db>.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 ='<schema>' 
    GROUP BY sc.name,ta.name, pa.rows) as TEMP""")
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
.load()

只是一种预感。希望这会有所帮助!