如何使用 SQL 查询在 dbtable 中定义表? [英] How to use SQL query to define table in dbtable?

问题描述

在

怎么了?

解决方案

由于 dbtable 被用作 SELECT 语句的源，它的形式可以对普通 SQL 查询有效.如果你想使用子查询，你应该在括号中传递一个查询并提供一个别名:

创建临时表 jdbcTable使用 org.apache.spark.sql.jdbc选项 (url "jdbc:postgresql:dbserver",dbtable "(SELECT * FROM mytable) tmp");

它将被传递给数据库:

SELECT * FROM (SELECT * FROM mytable) tmp WHERE 1=0

In JDBC To Other Databases I found the following explanation of dbtable parameter:

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.

When I use the code:

CREATE TEMPORARY TABLE jdbcTable
USING org.apache.spark.sql.jdbc
OPTIONS (
  url "jdbc:postgresql:dbserver",
  dbtable "mytable"
)

everything works great, but the following:

 dbtable "SELECT * FROM mytable"

leads to the error:

What is wrong?

解决方案

Since dbtable is used as a source for the SELECT statement it has be in a form which would be valid for normal SQL query. If you want to use subquery you should pass a query in parentheses and provide an alias:

CREATE TEMPORARY TABLE jdbcTable
USING org.apache.spark.sql.jdbc
OPTIONS (
    url "jdbc:postgresql:dbserver",
    dbtable "(SELECT * FROM mytable) tmp"
);

It will be passed to the database as:

SELECT * FROM (SELECT * FROM mytable) tmp WHERE 1=0

这篇关于如何使用 SQL 查询在 dbtable 中定义表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！