title: "SQL Editor / Notebook" date: 2019-03-13T18:28:09-07:00 draft: false
The goal of Hue's Editor is to make data querying easy and productive.
It focuses on SQL but also supports job submissions. It comes with an intelligent autocomplete, search & tagging of data and query assistance.
Any editor can be starred next to its name so that it becomes the default editor and the landing page when logging in.
Configuration of the connectors is currently done by the Administrator.
Note: To run a query, you must be logged in to Hue as a user that also has a Unix user account on the remote server.
Click Execute. The Query Results window appears with the results of your query.
If there are multiple statements in the query (separated by semi-colons), click Next in the Multi-statement query pane to execute the remaining statements.
When you have multiple statements it's enough to put the cursor in the statement you want to execute, the active statement is indicated with a blue gutter marking.
Note: Use CTRL/Cmd + ENTER to execute queries.
Note: Under the logs panel, you can view any MapReduce or Impala jobs that the query generated.
To get things started, press the export icon, the bottom last element of the action bar to the top left of the results. There are several ways you can export results of a query.
Two of them offer great scalability:
Two of them offer limited scalability:
The pane to the top of the Editor lets you specify the following options:
| DATABASE | The database containing the table definitions. | ||||||||||||||||||||||||||||||||||||||||
| SETTINGS | Override the Hive and Hadoop default settings. To configure a new
setting:
FILE RESOURCES | Make files locally accessible at query execution time available on the
Hadoop cluster. Hive uses the Hadoop Distributed Cache to distribute the
added files to all machines in the cluster at query execution time.
| USER-DEFINED FUNCTIONS | Specify user-defined functions. Click Add to configure a new
setting. Specify the function name in the Name field, and specify
the class name for Classname.
You *must* specify a JAR file for the user-defined functions in FILE RESOURCES.
To include a user-defined function in a query, add a $ (dollar sign)
before the function name in the query. For example, if MyTable is a
user-defined function name in the query, you would type: SELECT $MyTable
| PARAMETERIZATION | Indicate that a dialog box should display to enter parameter values when
a query containing the string $parametername is executed. Enabled by
default. | AutocompleteTo make your SQL editing experience better we've created a new autocompleter for Hue 3.11. The old one had some limitations and was only aware of parts of the statement being edited. The new autocompleter knows all the ins and outs of the Hive and Impala SQL dialects and will suggest keywords, functions, columns, tables, databases, etc. based on the structure of the statement and the position of the cursor. The result is improved completion throughout. We now have completion for more than just SELECT statements, it will help you with the other DDL and DML statements too, INSERT, CREATE, ALTER, DROP etc. Smart column suggestions If multiple tables appear in the FROM clause, including derived and joined tables, it will merge the columns from all the tables and add the proper prefixes where needed. It also knows about your aliases, lateral views and complex types and will include those. It will now automatically backtick any reserved words or exotic column names where needed to prevent any mistakes. Smart keyword completion The new autocompleter suggests keywords based on where the cursor is positioned in the statement. Where possible it will even suggest more than one word at at time, like in the case of IF NOT EXISTS, no one likes to type too much right? In the parts where order matters but the keywords are optional, for instance after FROM tbl, it will list the keyword suggestions in the order they are expected with the first expected one on top. So after FROM tbl the WHERE keyword is listed above GROUP BY etc. UDFs The improved autocompleter will now suggest functions, for each function suggestion an additional panel is added in the autocomplete dropdown showing the documentation and the signature of the function. The autocompleter know about the expected types for the arguments and will only suggest the columns or functions that match the argument at the cursor position in the argument list. Sub-queries, correlated or not When editing subqueries it will only make suggestions within the scope of the subquery. For correlated subqueries the outside tables are also taken into account. All about quality We've fine-tuned the live autocompletion for a better experience and we've introduced some options under the editor settings where you can turn off live autocompletion or disable the autocompleter altogether (if you're adventurous). To access these settings open the editor and focus on the code area, press CTRL + , (or on Mac CMD + ,) and the settings will appear. The autocompleter talks to the backend to get data for tables and databases etc. by default it will timeout after 5 seconds but once it has been fetched it's cached for the next time around. The timeout can be adjusted in the Hue server configuration. We've got an extensive test suite but not every possible statement is covered, if the autocompleter can't interpret a statement it will be silent and no drop-down will appear. If you encounter a case where you think it should suggest something but doesn't or if it gives incorrect suggestions then please let us know. Learn more about it in Autocompleter for Hive and Impala. VariablesVariables are used to easily configure parameters in a query. They can be of two types: Single Valued
select * from web_logs where country_code = "${country_code}"
The variable can have a default value.
select * from web_logs where country_code = "${country_code=US}"
Multi Valued
select * from web_logs where country_code = "${country_code=CA, FR, US}"
In addition, the displayed text for multi valued variables can be changed.
select * from web_logs where country_code = "${country_code=CA(Canada), FR(France), US(United States)}"
For values that are not textual, omit the quotes.
select * from boolean_table where boolean_column = ${boolean_column}
Syntax checkerA little red underline will display the incorrect syntax so that the query can be fixed before submitting. A right click offers suggestions. Query AssistRead more about the Query Assistant with Navigator Optimizer Integration . ChartingThese visualizations are convenient for plotting chronological data or when subsets of rows have the same attribute: they will be stacked together. Read more about extending charts. Risk AlertsThe autocompleter will suggest popular tables, columns, filters, joins, group by, order by etc. based on metadata from Navigator Optimizer. A new “Popular” tab has been added to the autocomplete result dropdown which will be shown when there are popular suggestions available. Risk and suggestions While editing, Hue will run your queries through Navigator Optimizer in the background to identify potential risks that could affect the performance of your query. If a risk is identified an exclamation mark is shown above the query editor and suggestions on how to improve it is displayed in the lower part of the right assistant panel. Presentation ModeTurns a list of semi-colon separated queries into an interactive presentation. It is great for doing demos or basic reporting. SQL DatabasesUse the query editor with any database. HiveImpalaMySQLOracleKSQL / Kafka SQLSolr SQLWith Solr 5+, query collections like we would query a regular Hive or Impala table. As Solr SQL is pretty recent, there are some caveats, notably Solr lacks support of: which prevents a SQL UX experience comparable to the standard other databases (but we track it in HUE-3686). PrestoPresto is a high performance, distributed SQL query engine for big data. PostgreSQLRedshiftBigQueryAWS AthenaSpark SQLPhoenixKylinApache Kylin is an open-source online analytical processing (OLAP) engine. See how to configure the Kylin Query Editor. OthersExtend with SQL Alchemy, JDBC or build your own connectors. JobsThe Editor application enables you to create and submit jobs to the cluster. You can include variables with your jobs to enable you and other users to enter values for the variables when they run your job. All job design settings except Name and Description support the use of variables of the form $variable_name. When you run the job, a dialog box will appear to enable you to specify the values of the variables.
|