Query

Click Query in the top navigation menu to access several data management tools.

SQL Tool

The SQL Tool allows you to perform SQL queries against the data in Kinetica from GAdmin. The SQL Tool works similarly to the SQL Lab in Reveal but has fewer restrictions. To read more about using the SQL Tool from the command line (also known as KiSQL), review Kinetica SQL (KiSQL).

Several actions are available from this tool:

  • Increase or decrease the # of lines to increase or decrease the size of the SQL Statements box.
  • Select Halt on Error to halt SQL query execution if an error occurs. This is particularly useful when executing several statements in sequence as this will break on the first error in that sequence
  • Click Clear History to remove all previously executed queries from the list.
  • You can view and page through your query history using the < Prev and Next > buttons, or view & select (for re-execution) from the entire history using the Queries button in between them. Once the SQL Query History window is open, use the filter box to search the history for a particular value and click a query to re-load it.
  • You can select a Schema to run queries in by default allowing you to query without specifying a fully qualified table name, e.g., FROM schema.table_name. You can query outside of the set schema by providing a different schema in the SQL Statements area.
  • If HA is enabled, you can select a particular HA Cluster Node to run the provided query against.
  • Click Clear to clear both the SQL Statements & Query Result areas.
  • After entering a query into the SQL Statements area, click WMS to create a WMS call from the query, which will place the results in a temporary table and open the WMS window. Once done browsing, close the WMS window to delete the temporary table.
  • After entering a query into the SQL Statements area, click API to generate the Python code necessary to run the same query via the native Python API.
  • After entering one or more semicolon-terminated queries and/or DML/DDL commands into the SQL Statements area, click Explain to output a visual execution plan of the provided query.
  • Select a portion of the text entered into SQL Statements and click Run Highlighted to execute only the selected portion.
  • After entering one or more semicolon-terminated queries and/or DML/DDL commands into the SQL Statements area, click Run SQL to execute.

Note

The WMS and Explain functionality can act on everything in the SQL Statements area or highlighted text, but they cannot be executed if there are multiple statements in the text area with nothing highlighted.

../../images/query_kisql.png

SQL snippets and autocomplete are also available to help expedite writing queries.

../../images/query_kisql_autocomplete.png

Explain

The explain plan visualizer is a graphical complement to using the EXPLAIN keyword in a given query, providing visual detail to the SQL query's execution plan, execution time, and native API translation(s).

Note

The explain plan visualizer cannot be used in conjunction with the EXPLAIN keyword.

Clicking Explain will not execute the query (initially); the database will walk through the native actions necessary to execute the query and show the plan. Once the query plan is output, the query can be executed and analyzed using the Run & Analyze button (with Verbose optionally providing more execution detail).

For example, the executing the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

EXPLAIN
SELECT
    n_begin.ntaname AS boro_begin,
    boro_end,
    COUNT(*) AS total_trips
FROM
(
    SELECT pickup_latitude, pickup_longitude, n_end.ntaname AS boro_end
    FROM demo.nyctaxi t
    JOIN example_geospatial.nyc_neighborhood n_end ON STXY_INTERSECTS(dropoff_longitude, dropoff_latitude, geom)
)
JOIN example_geospatial.nyc_neighborhood n_begin ON STXY_INTERSECTS(pickup_longitude, pickup_latitude, geom)
GROUP BY 1, 2

Yields a text-only explain output:

1
2
3
4
5
6
7
8

+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+
| ID   | ENDPOINT                | INPUT_TABLES                                                                                                                     | OUTPUT_TABLE                                                           | DEPENDENCIES   |
+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+
| 0    | /create/jointable       | demo.nyctaxi AS TableAlias_0_,example_geospatial.nyc_neighborhood AS TableAlias_1_                                               | sys_sql_temp.Join_3_e81f8cbb_f6fb_4932_88d9_c17efe56201d               | -1             |
| 1    | /create/jointable       | sys_sql_temp.Join_3_e81f8cbb_f6fb_4932_88d9_c17efe56201d AS TableAlias_0_,example_geospatial.nyc_neighborhood AS TableAlias_1_   | sys_sql_temp.Join_5_6ff1a2f3_5969_49a6_88e7_c04312e5522d               | 0              |
| 2    | /aggregate/groupby      | sys_sql_temp.Join_5_6ff1a2f3_5969_49a6_88e7_c04312e5522d                                                                         | sys_sql_temp.ShardedAggregate_7_000fd52b_f16e_4ce8_b1dc_16b21d4102da   | 1              |
| 3    | /get/records/bycolumn   | sys_sql_temp.ShardedAggregate_7_000fd52b_f16e_4ce8_b1dc_16b21d4102da                                                             |                                                                        | 2              |
+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+----------------+

Running the same query (without the EXPLAIN keyword included) but this time clicking Explain would yield the graphical query plan:

../../images/query_explain_plan.png

Tip

Hover over steps in the query plan to view the API call that would occur in the background when executing the original query. Hover over table names to view the table's schema. Click Export JSON to export the text-based explain output to a JSON file.

Subsequently, click Run & Analyze to execute the query and see the execution time for each step in the plan.

../../images/query_explain_plan_analyze.png

The steps are color-coded based on how fast they were in relation to the other steps in the query.

If an error occurs in the query, it will be displayed in the plan at the point it occurred. Mousing over the error will show the details of the call and the error message.

../../images/query_explain_plan_analyze_error.png

SQL Procedures

The SQL Procedures page displays all existing SQL procedures. Review SQL Procedures for more information on SQL procedures.

  • Click Create to create a SQL procedure.
  • Click Execute to manually execute the selected procedure. Note that any returned rows will not be displayed. Execute the procedure manually via the SQL tool to view any output.
  • Click Drop to remove the selected procedure.
  • Click Permissions to adjust the execute permissions for the selected procedure.
  • Click Detail to display the SQL procedure's definition.
../../images/query_sql_procedures.png

Create

The SQL procedure creation process is simplified when using the interface; all that's required to get started is some SQL code.

  1. From the SQL Procedures page, click Create.

  2. Provide a Name for the procedure.

  3. Optionally, provide an Execution Interval. Units can be one of the following:

    • SECOND[S]
    • MINUTE[S]
    • HOUR[S]
    • DAY[S]

  4. Optionally, provide a Start Timestamp. The provided value should be in the following format:

    YYYY-MM-DD [HH:MM[:SS]]

  5. Provide one or more semi-colon-separated SQL statements in the Statements text field. Review the supported statements.

  6. Click Create.

Permissions

While all SQL procedures are able to be viewed by any user, they can only be executed by certain users. To adjust the execute permissions for a particular SQL procedure:

  1. From the SQL Procedures page, select a SQL procedure and click Permissions.
  2. Adjust the permissions as necessary:
    • Edit execute permissions for users:
      • Add: Click a user in the Available list and then click Grant >> to grant the user sql_proc_execute permission on the selected SQL procedure.
      • Remove: Click a user in the SQL Proc-level Granted list and then click << Revoke to revoke the user's sql_proc_execute permission on the selected procedure.
    • Edit execute permissions for roles:
      • Add: Click a role in the Available list and then click Grant >> to grant the role sql_proc_execute permission on the selected SQL procedure.
      • Remove: Click a role in the SQL Proc-level Granted list and then click << Revoke to revoke the role's sql_proc_execute permission on the selected procedure.

API Tool

The API Tool allows you to call any Kinetica REST endpoint with a given set of parameters.

Request Mode allows the choice of two methods for passing parameters in endpoint calls:

  • Builder - assign values to named parameters individually
  • JSON - pass the request as a single block of JSON text

The result of the request is output to the Logs panel. Click Clear at the bottom to clear the output.

The History tab lists previously executed commands and their results. Click Reload to load the selected endpoint and associated parameters into the Builder for subsequent re-execution. Click Clear Saved Queries in the top right-hand corner to delete the query history.

Clicking the API Docs button will print the corresponding documentation entry for the endpoint in the Logs panel to the right.

../../images/query_advanced.png

Builder

The Builder mode of the API Tool displays a user entry control for each of the selected endpoint's parameters. Each control will show the parameter name & type and the option to enter the parameter as Text or to use a parameter from the previous request's Response or Request object. The last set of parameters used in an endpoint call will be saved until the next time the endpoint is queried.

Each request will log the request parameters in JSON format at the top of the Logs panel to the right. This request block can then be fed back into the API Tool in JSON mode, which may facilitate repeated querying, as the entire request can be seen and modified at one time.

Here is an example of a request to the /aggregate/groupby endpoint in Builder mode:

../../images/query_advanced_builder.png

JSON

The JSON mode of the API Tool provides a user entry control to enter the request parameters as a single block of JSON. The last JSON request will be saved until the next time the endpoint is queried.

Here is an example of a request to the /aggregate/groupby endpoint in JSON mode:

../../images/query_advanced_json.png

DDL Tool

The DDL Tool provides the ability to generate DDL for a given table, schema, or all database entities at once.

../../images/query_kiddl.png

Enter a table name, schema name, or *, and click Generate DDL to output the DDL to DDL Output. Click Export DDL to save the generated DDL as a SQL script.

The output from this utility can be copied into the SQL Statements area on the SQL Tool page and run in order to regenerate the database entities whose DDL has been generated.