問題描述

我想使用 Spark 處理來自 JDBC 源的一些數據.但是首先，我想在JDBC端運行一些查詢來過濾列和連接表，而不是從JDBC讀取原始表，并將查詢結果作為表加載到Spark SQL中.

I want to use Spark to process some data from a JDBC source. But to begin with, instead of reading original tables from JDBC, I want to run some queries on the JDBC side to filter columns and join tables, and load the query result as a table in Spark SQL.

以下加載原始 JDBC 表的語法適用于我:

The following syntax to load raw JDBC table works for me:

df_table1 = sqlContext.read.format('jdbc').options(
    url="jdbc:mysql://foo.com:3306",
    dbtable="mydb.table1",
    user="me",
    password="******",
    driver="com.mysql.jdbc.Driver" # mysql JDBC driver 5.1.41
).load() 
df_table1.show() # succeeded

根據 Spark 文檔(我使用的是 PySpark 1.6.3):

According to Spark documentation (I'm using PySpark 1.6.3):

dbtable:應該讀取的 JDBC 表.請注意，任何有效的可以在 SQL 查詢的 FROM 子句中使用.例如，而不是完整的表，您也可以在括號中使用子查詢.

dbtable: The JDBC table that should be read. Note that 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.

所以只是為了實驗，我嘗試了一些簡單的方法:

So just for experiment, I tried something simple like this:

df_table1 = sqlContext.read.format('jdbc').options(
    url="jdbc:mysql://foo.com:3306",
    dbtable="(SELECT * FROM mydb.table1) AS table1",
    user="me",
    password="******",
    driver="com.mysql.jdbc.Driver"
).load() # failed

它拋出了以下異常:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table1 WHERE 1=0' at line 1

我還嘗試了其他一些語法變體(添加/刪除括號、刪除as"子句、切換大小寫等)，但都沒有成功.那么正確的語法是什么?在哪里可以找到更詳細的語法文檔?此外，錯誤消息中這個奇怪的WHERE 1 = 0"來自哪里?謝謝！

I also tried a few other variations of the syntax (add / remove parentheses, remove 'as' clause, switch case, etc) without any luck. So what would be the correct syntax? Where can I find more detailed documentation for the syntax? Besides, where does this weird "WHERE 1=0" in error message come from? Thanks!

推薦答案

對于在 Spark SQL 中使用 sql 查詢從 JDBC 源讀取數據，您可以嘗試如下操作:

For reading data from JDBC source using sql query in Spark SQL, you can try something like this:

val df_table1 = sqlContext.read.format("jdbc").options(Map(
    ("url" -> "jdbc:postgresql://localhost:5432/mydb"),
    ("dbtable" -> "(select * from table1) as table1"),
    ("user" -> "me"),
    ("password" -> "******"),
    ("driver" -> "org.postgresql.Driver"))
).load()

我用 PostgreSQL 試過了.可以根據MySQL修改.

I tried it using PostgreSQL. You can modify it according to MySQL.

這篇關于如何在 jdbc 數據源中使用 dbtable 選項的子查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！