[
https://issues.apache.org/jira/browse/SPARK-9078?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14633975#comment-14633975
]
Robert Beauchemin commented on SPARK-9078:
------------------------------------------
Could make it part of the JdbcDialect. Or a string in the JdbcDialect that is
used by tableExists that returns at most 1 row. With the current configuration
("SELECT 1 FROM $table LIMIT 1") being the default. Then you wouldn't have to
change how the query works to determine if the table exists. Your call...
About the config file... my thinking here is that to add a new dialect (say if
I wanted non-standard dialect entries for DB2 JDBC driver) requires a new
release or a modified version of the base source. Unless there's a easy way to
inject a new JdbcDialect that and without creating a whole new data source that
I'm missing. Not all customers want to recompile the source (or run a modified
version of the source) to support a new JDBC data source. And would require a
database of that type to be available for build testing, as I'm guessing MySQL
and Postgres are now. Much less overhead for the build/test team than another
config file (or making it part of current a config file). Allowing separation
of JDBC-specific driver settings from the base code would accomplish that
without custom builds. But you know the target audience better than I do.
Cheers, and thanks,
Bob
> Use of non-standard LIMIT keyword in JDBC tableExists code
> ----------------------------------------------------------
>
> Key: SPARK-9078
> URL: https://issues.apache.org/jira/browse/SPARK-9078
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.3.1, 1.4.0
> Reporter: Robert Beauchemin
> Priority: Minor
>
> tableExists in
> spark/sql/core/src/main/scala/org/apache/spark/sql/jdbc/JdbcUtils.scala uses
> non-standard SQL (specifically, the LIMIT keyword) to determine whether a
> table exists in a JDBC data source. This will cause an exception in many/most
> JDBC databases that doesn't support LIMIT keyword. See
> http://stackoverflow.com/questions/1528604/how-universal-is-the-limit-statement-in-sql
> To check for table existence or an exception, it could be recrafted around
> "select 1 from $table where 0 = 1" which isn't the same (it returns an empty
> resultset rather than the value '1'), but would support more data sources and
> also support empty tables. Arguably ugly and possibly queries every row on
> sources that don't support constant folding, but better than failing on JDBC
> sources that don't support LIMIT.
> Perhaps "supports LIMIT" could be a field in the JdbcDialect class for
> databases that support keyword this to override. The ANSI standard is (OFFSET
> and) FETCH.
> The standard way to check for table existence would be to use
> information_schema.tables which is a SQL standard but may not work for other
> JDBC data sources that support SQL, but not the information_schema. The JDBC
> DatabaseMetaData interface provides getSchemas() that allows checking for
> the information_schema in drivers that support it.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
