You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2021/04/27 20:38:31 UTC

[GitHub] [arrow-datafusion] returnString opened a new pull request #214: Change SQL dialect to PostgreSQL

returnString opened a new pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214


   Some background first! @Dandandan did the initial work here and identified the VarProvider stuff as a potential followup issue in apache/arrow#9541. This PR just implements the original dialect change with the proposed extra change of removing scalar vars - thanks Daniel! 😄
   
   # Which issue does this PR close?
   Closes #183.
   
    # Rationale for this change
   This formalises our decision to use the Postgres SQL dialect.
   
   # What changes are included in this PR?
   - The DataFusion SQL parser now uses the Postgres dialect from `sqlparser-rs`
   - The notion of ScalarVariable exprs and the related VarProvider concept have been removed entirely, as these aren't supported in the Postgres dialect and the use case can be handled with nullary functions [as is done in Postgres](https://www.postgresql.org/docs/current/functions-info.html).
   
   # Are there any user-facing changes?
   DataFusion (unsure if this worked before in Ballista) no longer support user or system variables exposed with the non-standard `@` prefix.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] alamb commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622908712



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       > make the dialect default to Postgres, but be configurable, with a doc warning mentioning the potential risks and edge cases
   
   I think this makes sense
   
   > mention nullary functions as a replacement for scalar variables in SQL in the next set of release notes
   
   👍 
   
   > random bonus thought: maybe expose a unary function to retrieve scalar variables by name from SQL?
   
   This also sounds good -- if you wrote it up as a ticket, I bet it is a good "first issue" type ticket for someone else in the community to do if they were looking for something to learn DataFusion code a bit




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString closed pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString closed pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] alamb commented on pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
alamb commented on pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#issuecomment-853365486


   Ping @returnString  -- is do you mind if I close this PR (to clean up the PR review queue)?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] Dandandan commented on pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
Dandandan commented on pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#issuecomment-827918997


   Cool, thanks @returnString . To me it sounds like a good decision moto remove support for the ScalarVariable. I am not sure there is a user for the functionality?
   What do you think @alamb @andygrove


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] andygrove commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
andygrove commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622267646



##########
File path: datafusion/src/execution/context.rs
##########
@@ -916,35 +898,6 @@ mod tests {
         Ok(())
     }
 
-    #[tokio::test]
-    async fn create_variable_expr() -> Result<()> {

Review comment:
       I don't think we should remove support for scalar variables in the logical plan. This seems unrelated to changing the default SQL dialect. Users can use ScalarVariable without using SQL.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622139244



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Thinking about it more over lunch: if we enable a bring-your-own-dialect setup, we'd need a decent testing strategy to support this. In this example, `@someval` will be parsed as `UnaryOp { op: PGAbs, expr: Ident("someval") }` for Postgres, so we'd need to decide how we implement DF-specific parsing overrides, e.g. as used to support this var provider system, on a per-dialect basis.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#issuecomment-853366985


   Yeah no worries, I need to set aside some time to write up the takeaways from the review thread as a ticket and followup PR, will close for now :) Thanks for chasing this up!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r623116017



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Yeah, I definitely wouldn't want to impose unnecessary constraints on people who _did_ want to use other dialects :) If people are happy with the ideas in my last comment, I'll close this, get some new issues logged, and start tackling those.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622052668



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       That's interesting approach - we'd have to be careful about our sqlparser => expr conversions, but that could be quite useful as a feature 🤔 




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622406126



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Just to be totally clear, I'm definitely not married to the contents of this PR as they stand; if the consensus is that we _do_ want to support multiple dialects, I'm happy to rework it towards that goal 🙂
   
   If that's a route we want to explore in depth, we could perhaps build some sort of dialect config setup that allows for controlling parser overrides in DF, but that feels a bit _too_ heavyweight if we don't have tonnes of use cases right now (and hence no-one to drive or own that work).
   
   Here's my immediate idea:
   - revert the deletion of all the logical plan stuff here, and just retain the parser override deletion
   - make the dialect default to Postgres, but be configurable, with a doc warning mentioning the potential risks and edge cases
   - mention nullary functions as a replacement for scalar variables in SQL in the next set of release notes
   - random bonus thought: maybe expose a unary function to retrieve scalar variables by name from SQL?
   
   Does that sound vaguely sensible?
   
   Full disclosure: my goal here is to get a DataFusion-powered service queryable via BI tooling, and these tools often use the more esoteric features of the Postgres dialect to bootstrap their UIs (think table listings etc). I still have a fair bit of work to do on this, but I can at least sort of see a path to having it working now. I suspect this will be beneficial for lots of other use cases, but admittedly I don't have any evidence for that claim beyond intuition 😅




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622406126



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Just to be totally clear, I'm definitely not married to the contents of this PR as they stand; if the consensus is that we _do_ want to support multiple dialects, I'm happy to rework it towards that goal 🙂
   
   If that's a route we want to explore in depth, we could perhaps build some sort of dialect config setup that allows for controlling parser overrides in DF, but that feels a bit _too_ heavyweight if we don't have tonnes of use cases right now (and hence no-one to drive or own that work).
   
   Here's my immediate idea:
   - revert the deletion of all the logical plan stuff here, and just retain the parser override deletion
   - make the dialect default to Postgres, but be configurable, with a doc warning mentioning the potential risks and edge cases
   - mention nullary functions as a replacement for scalar variables in SQL in the next set of release notes
   - random bonus thought: maybe expose a unary function to retrieve scalar variables by name from SQL?
   
   Does that sound vaguely sensible?
   
   Full disclosure: my goal here is to get a DataFusion-powered service queryable via BI tooling, and these tools often use the more esoteric features of the Postgres dialect to boostrap their UIs (think table listings etc). I still have a fair bit of work to do on this, but I can at least sort of see a path to having it working now. I suspect this will be beneficial for lots of other use cases, but admittedly I don't have any evidence for that claim beyond intuition 😅




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] returnString commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
returnString commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622052668



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       That's an interesting approach - we'd have to be careful about our sqlparser => expr conversions, but that could be quite useful as a feature 🤔 




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] andygrove commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
andygrove commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622271255



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       This is tricky for sure. I would be fine with having Postgres as the default and officially supported (well tested) dialect, while also allowing users to provide a dialect at their own risk (and have this be well documented) but even that might create an undue burden on maintainers. It would definitely be good to try and find out more about our user's requirements here.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] alamb commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622163195



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       That does sound fairly complicated.... I personally / project wise don't have a need for a MySQL specific mimic (postgres is good enough for us in IOx) but I think perhaps we should let others weigh in here




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] andygrove commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
andygrove commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r623097686



##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Thanks for the continued discussion and progress on this. I think the outcome is good. Just for context, I have built database gateways in the past that mimic MySQL and Hive protocols and dialects (different projects) and although I don't work on any projects these days that need either, I am just trying to keep options open for future users of DataFusion.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [arrow-datafusion] alamb commented on a change in pull request #214: Change SQL dialect to PostgreSQL

Posted by GitBox <gi...@apache.org>.
alamb commented on a change in pull request #214:
URL: https://github.com/apache/arrow-datafusion/pull/214#discussion_r622045340



##########
File path: datafusion/src/execution/context.rs
##########
@@ -916,35 +898,6 @@ mod tests {
         Ok(())
     }
 
-    #[tokio::test]
-    async fn create_variable_expr() -> Result<()> {

Review comment:
       I think these were added in https://github.com/apache/arrow/pull/8135 . Perhaps @wqc200 has some comment about how / if this feature is used?

##########
File path: datafusion/src/sql/parser.rs
##########
@@ -21,7 +21,7 @@
 
 use sqlparser::{
     ast::{ColumnDef, ColumnOptionDef, Statement as SQLStatement, TableConstraint},
-    dialect::{keywords::Keyword, Dialect, GenericDialect},
+    dialect::{keywords::Keyword, Dialect, PostgreSqlDialect},

Review comment:
       Another approach might be to make the SQL dialect be configurable on `ExecutionConfig` so that users could choose what dialect they wanted to mimic: https://github.com/apache/arrow-datafusion/blob/master/datafusion/src/execution/context.rs#L606
   
   I think @andygrove  has said in the past using DataFusion to mimic engines such as MySQL is a good usecase too




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org