[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16893222#comment-16893222
]
Alex Rodoni commented on IMPALA-8156:
-
[~Paul.Rogers] Thank you!
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for Boolean options, the
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16893220#comment-16893220
]
Paul Rogers commented on IMPALA-8156:
-
[~arodoni_cloudera], I don't believe so. The PR was never committed. Maybe this
should be marked as inactive until someone can pick up the PR and push it
through.
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain lev
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16893213#comment-16893213
]
Alex Rodoni commented on IMPALA-8156:
-
Is this still targeted for the 3.3 release?
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for Bo
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16788305#comment-16788305
]
Paul Rogers commented on IMPALA-8156:
-
Right, not in 3.2.
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for Boolean options, the value
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16787249#comment-16787249
]
Alex Rodoni commented on IMPALA-8156:
-
[~Paul.Rogers] Definitely not for 3.2, right?
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16762310#comment-16762310
]
Peter Ebert commented on IMPALA-8156:
-
It would be helpful to know which parts of the plan were part of which view.
Either (1) adding something that indicates the start and end of the view in the
plan like an additional indent, OR (2) a predicate that indicates which view an
operation belongs to (something in the description like view:
real_table.column1, real_table.column2) would be helpful for debugging complex
queries.
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' h4. Rewritten SQL
> The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> Views are expanded only if the user has permissions on the underlying tables.
> Else, to avoid revealing details, views are unexpended if the proper
> privileges are lacking.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAI
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16762011#comment-16762011
]
Paul Rogers commented on IMPALA-8156:
-
Not yet clear. I'm hoping for the next release, but we do have a large backlog
of reviews. Will update the release target once we have the answer.
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for Boolean options, the value is optional and "true" is assumed:
> {code:sql}
> EXPLAIN FORMAT(EXTEN
[jira] [Commented] (IMPALA-8156) Add format options to the EXPLAIN statement
[
https://issues.apache.org/jira/browse/IMPALA-8156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16761313#comment-16761313
]
Alex Rodoni commented on IMPALA-8156:
-
[~Paul.Rogers] Target version?
> Add format options to the EXPLAIN statement
> ---
>
> Key: IMPALA-8156
> URL: https://issues.apache.org/jira/browse/IMPALA-8156
> Project: IMPALA
> Issue Type: Improvement
> Components: Frontend
>Affects Versions: Impala 3.1.0
>Reporter: Paul Rogers
>Assignee: Paul Rogers
>Priority: Minor
>
> The EXPLAIN statement is very basic:
> {code:sql}
> EXPLAIN ;
> {code}
> Example:
> {code:sql}
> EXPLAIN SELECT * FROM alltypes;
> {code}
> Explain does provide some options set as session options:
> {code:sql}
> SET set explain_level=extended;
> EXPLAIN ;
> {code}
> We have often found the need for additional information. For example, it
> would be very useful to obtain the SELECT statement after view substitution.
> We wish to extend EXPLAIN to allow additional options, while retaining full
> backward compatibility. The extended syntax is:
> {code:sql}
> EXPLAIN [FORMAT([opt(, opt)*])] ;
> {code}
> This syntax reuses the existing FORMAT keyword, and allow an unlimited set of
> options to be added in the future without the need to define new keywords.
> Options are in the {{name=value}} form with {{name}} as an identifier and
> {{value}} as a string literal. Both are case-insensitive. Example to set the
> explain level:
> {code:sql}
> EXPLAIN FORMAT(level=extended) SELECT * FROM alltypes;
> {code}
> The two options supported at present are:
> * {{level}} - Sets the explain level.
> * {{rewritten}} - Shows the fully rewritten SQL statement with views expanded.
> The {{level}} option overrides the existing session options. If {{level}} is
> not present, then the session option is used instead. Values are identical to
> that for the {{SET explain_level=' The {{rewritten}} option takes two values: {{true}} or {{false}}. If set,
> {{EXPLAIN}} returns the text of the rewritten SQL instead of the query plan.
> Example:
> {noformat}
> functional> explain format(rewritten) SELECT * FROM view_view;
> ++
> | Explain String |
> ++
> | SELECT * FROM /* functional.view_view */ ( |
> | SELECT * FROM /* functional.alltypes_view */ ( |
> | SELECT * FROM functional.alltypes) |
> | ) |
> ++
> {noformat}
> Here, the names in comments are the view names. Views are then expanded
> inline to show the full extend of the statement. This is very helpful to
> resolve user issues.
> h4. Comparison with Other SQL Dialects
> The ISO SQL standard does not define the {{EXPLAIN}} statement, it is a
> vendor extension. MySQL defines {{EXPLAIN}} as:
> {noformat}
> {EXPLAIN | DESCRIBE | DESC}
> [explain_type]
> {explainable_stmt | FOR CONNECTION connection_id}
> explain_type: {
> FORMAT = format_name
> }
> format_name: {
> TRADITIONAL
> | JSON
> }
> {noformat}
> That is, MySQL also uses the {{FORMAT}} keyword with only two choices.
> SqlServer uses a form much like Impala's present form with no options.
> Postgres uses options and keywords:
> {noformat}
> EXPLAIN [ ( option [, ...] ) ] statement
> EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
> where option can be one of:
> ANALYZE [ boolean ]
> VERBOSE [ boolean ]
> COSTS [ boolean ]
> BUFFERS [ boolean ]
> FORMAT { TEXT | XML | JSON | YAML }
> {noformat}
> Apache Drill uses a series of keywords to express options:
> {noformat}
> explain plan [ including all attributes ]
> [ with implementation | without implementation ]
> for ;
> {noformat}
> We claim that, given the wide variety of vendor implementations, the proposed
> Impala syntax is reasonable.
> h4. Futures
> IMPALA-5973 proposes to add a JSON format for {{EXPLAIN}} output. We propose
> to select JSON output using the "format" option:
> {code:sql}
> EXPLAIN FORMAT(format='json')
> {code}
> The format can be combined other options such as level:
> {code:sql}
> EXPLAIN FORMAT(format='json', level='extended')
> {code}
> h4. Details
> The key/value syntax is very general, but cumbersome for simple tasks. The
> {{FORMAT}} option allows a number of simplifications.
> First, for the explain level, each level can be used as a Boolean option:
> {code:sql}
> EXPLAIN FORMAT(extended='true')
> {code}
> Second, for Boolean options, the value is optional and "true" is assumed:
> {code:sql}
> EXPLAIN FORMAT(EXTENDED)
> {code}
> Third, if only a value is given, the value is assumed to be for the "format"
> key (which is not ye
