Composing a Smart Hive Query (AWS)

Smart Query supports Hive and Presto queries. It provides a graphical interface that helps to compose Hive queries.

Note

As a prerequisite, ensure that the cluster on which you want to run a Hive query is running.

Perform the following steps to compose a Hive query using the Smart Query interface:

  1. Navigate to Smart Query. Smart Query interface for Hive or Presto Query builder is displayed as it depends on which query type was last used.

  2. If Presto Query builder is displayed, select Hive query from the drop-down list that contains Presto Query. The Hive Query builder is displayed as shown in the following figure.

    ../../../_images/SmartQHiveDefault.png

  3. In Column Selection, select a database if you do not want it to be the default database. Select a table from the database. Select a column from the table if you do not want the default * (asterisk) as the column and a function from the drop-down list. Once you select a column, the header appears in the Column Header field. Click +Add Column to add another columns. The following figure shows two selected columns along with the functions.

    ../../../_images/SmartQColumnSelection.png

    Click +Add Table if you want the result from another table. SQL JOINs are supported if two or more tables are selected.

  4. If you want to refine the query result, you can add filters. Click Filters and add the columns by selecting the column from the drop-down list. Select the Operator from the drop-down list and enter values as they are in the table. The following figure shows two columns added as filters along with the operator and values.

    ../../../_images/SmartQFilters.png

    Smart Query supports these logical operators: IN, NOT IN, LIKE, IS NULL, and IS NOT NULL. It supports these comparison operators: = (EQUAL), <=> (EQUAL_NS (Not NULL)), <> (NOT EQUAL), < (LESS THAN), <= (LESS THAN OR EQUAL TO), > (GREATER THAN), and >= (GREATER THAN OR EQUAL TO).

  5. To sort the query result in an ascending/descending order, click SORT and add column that you want to sort. The columns drop-down list contains only the columns selected in step 1. Click +Add to add another column. The following figure shows the setting to sort columns.

    ../../../_images/SmartQSortLimit.png

    Before running a query, you can view the hive query by clicking View Query. The following figure shows a Generated Query.

    ../../../_images/SmartQHiveView.png

  6. Click Run to execute the query. (Run is available in Generated Query and the query builder as well.) The Hive query builder with a successful query execution is as shown in the following figure.

    ../../../_images/SmartQSuccess.png

    A successful Hive query result is as shown in the following figure.

    ../../../_images/SmartQHiveResults.png

    Click Clear to remove the wizard data.