[ 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 <stmt>; > {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 <stmt>; > {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)*])] <stmt>; > {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='<value'}} statement. > 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 <query> ; > {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') <stmt> > {code} > The format can be combined other options such as level: > {code:sql} > EXPLAIN FORMAT(format='json', level='extended') <stmt> > {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') <stmt> > {code} > Second, for Boolean options, the value is optional and "true" is assumed: > {code:sql} > EXPLAIN FORMAT(EXTENDED) <stmt> > {code} > Third, if only a value is given, the value is assumed to be for the "format" > key (which is not yet supported): > {code:sql} > EXPLAIN FORMAT('json') <stmt> > {code} > Would, when JSON format is available, emit the plan as JSON. > The astute reader will see opportunities for odd combinations of options. > Rather than enforcing a strict set of rules, when given an odd set of rules, > the {{FORMAT}} option simply does something reasonable. Example: > {code:sql} > EXPLAIN FORMAT(level='standard', extended, verbose='false') <stmt> > {code} > The short answer here is that options are ambiguous, behavior is undefined, > but reasonable. -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-all-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org