Kinetica SQL (KiSQL)

KiSQL allows SQL queries to be performed against the data in Kinetica from the command line.

GitHub has the latest release of KiSQL for download.

KiSQL exposes the complete Kinetica SQL interface, as given in the SQL section, including:

For a graphical SQL interface, see SQL Blocks in Workbench Workbooks.

Command Line Interface

KiSQL can be accessed via command line as kisql. The only required parameter to connect is one of --host, --url, or --connectionString; alternatively, the KI_HOST or KI_URL environment variable can be used to set the target database server.

If --user is specified, KiSQL will prompt for the password; alternatively, the KI_PWD environment variable can be used to set the password.

If --trustStore is specified, KiSQL will prompt for the trust store password; alternatively, the KI_TRUSTSTOREPWD environment variable can be used to set the password.

Parameters

Parameter Description
Connection

-h <hostname>

--host <hostname>

Connect to the specified Kinetica host; can also be specified by the KI_HOST environment variable.

-p <port number>

--port <port number>

Connect to the specified Kinetica port; can also be specified by the KI_JDBCPORT environment variable. If not specified, default is 9191.
--url <Kinetica URL> Connect using the specified Kinetica URL; e.g., http://127.0.0.1:9191. Overrides --host & --port. This can also be specified by the KI_URL environment variable. See Secure Connections for specifying SSL connections.
--connectionString "<JDBC URL>" Connect using the specified JDBC URL; e.g., jdbc:kinetica://127.0.0.1:9191. Overrides --host, --port, --url, & primaryUrl; will not override other parameters specified on the command line. See Configuration for the JDBC connection string format and the full list of options able to be specified through it.
--user <user ID> Authenticate with the specified user ID. This can also be specified by the KI_USER environment variable.
--trustStore <filepath> Use the specified Java TrustStore file path for SSL connections. Can also be specified using the KI_TRUSTSTORE environment variable.
--allowHostMismatch If using SSL, don't validate that the server host name matches its certificate.
Input

-f <file path>

--file <file path>

Run any SQL statements found in the given file and exit. Statements must be separated by a semicolon, unless --lineMode is specified, in which case each line of the file will be treated as a separate SQL statement.

--line

--lineMode

Execute each line of input as a separate SQL statement, even if not terminated with a semicolon.
--sql '<SQL statements>' Run the given set of semicolon-separated SQL statements (in quotes) and exit.
Output
--debug [<value>] Output debugging information. If not specified, default is not to output debugging information; if specified, default value is true.
--delim '<delimiter>' Use the given delimiter (in quotes) for delimited output formatting. The --format parameter must be present and set to delim / delimited. Any alphanumeric character or symbol is a valid delimiter. If not specified, default is a comma.
--echoSql [<value>] Echo SQL statements executed. If not specified, default is to not output SQL statements; if specified, default value is true.
--format <format>

Output SQL statement result sets with the specified format. Default is table. Valid format options include:

Format Description

delim

delimited

Uses the delimiter specified with --delim (or the default delimiter) to output records in CSV-style format, including a header row with the corresponding column names.

Note

The output will not be escaped according to the data values and delimiter used. Be sure to select a delimiter not found within any data being output.

Example:

product_id,product_name
1,Clamp-on Lamps
2,Desk Chairs
3,Air Purifiers
table

Outputs records in an ASCII table format.

Example:

+------------+----------------+
| product_id | product_name   |
+------------+----------------+
|          1 | Clamp-on Lamps |
|          2 | Desk Chairs    |
|          3 | Air Purifiers  |
+------------+----------------+
vertical

Outputs each record as a vertical list of key/value pairs.

Example:

--Row 1---------
id: 1
name: Clamp-on Lamps
--Row 2---------
id: 2
name: Desk Chairs
--Row 3---------
id: 3
name: Air Purifiers

-i

--isql

Simulate isql output.
--limit <record count> Limit the number of records returned in each result set to the given number. Use -1 for no limit. If not specified, default is -1.
--logLevel <level>

Set the JDBC logging level. Default logging level is 0 (off). Valid level options include:

Level Description
0 Off
1 Fatal
2 Error
3 Warning
4 Info
5 Debug
6 Trace
--showTime [<value>] Display SQL execution time. If not specified, default is to show execution time; if specified, default value is true.

-tz <time zone ID>

--timeZone <time zone ID>

Output timestamp data in the time zone with specified ID (e.g., PST). Use system for local system time. If not specified, default is GMT.
--verbose [<value>] Display JDBC class, url (including SSL and TrustStore information), host, port, user, password, and version information as well as the given SQL statement. If not specified, default is to not use verbose mode; if specified, default value is true.
Execution
--combinePrepareAndExecute [<value>] Combine ODBC prepare and execute phases, or not. This can improve the performance of some queries. If not specified, default is to combine phases; if specified, default value is true.
Information
--version Display the version of kisql and exit.

-?

--help

Display help information

Important

If --user is left blank and authentication is disabled, KiSQL will connect using the Anonymous default user. Review Security Concepts for more information on default users.

Interactive Mode

If neither --sql nor -f / --file are specified while using KiSQL from the command line interface, KiSQL will run in interactive mode. In interactive mode, a ; terminates and executes SQL statements, unless --lineMode is used to modify that behavior. If standard input has been redirected, no prompt is issued and SQL statements are read from stdin.

The following commands can be used at the beginning of a statement while in interactive mode:

Command Description
\counts Display the number of records for each table.

\d

\tables

If no argument given, display the tables and schemas to which the user has access. If a table name is provided, display column information for the table; if the table does not exist, Rows read = 0 is returned.

\e [entry number]

\edit [entry number]

If no argument given, invoke the vi editor on the last query executed. If a number is provided (with 1 being the least recent entry), invoke vi editor on specific query by number from the history. Upon exiting the editor, the statement will be executed.

\f <type>

\format <type>

Set the output format type; options are detailed in the --format parameter description above.

Note

The delimiter, when using the delimited output format cannot be changed during an interactive mode session.

\h

\hist

\history

Display the numbered SQL statement history (with the least recent entry being first).

\r [entry number]

\run [entry number]

If no argument given, run the last SQL statement executed. If a number is provided (with 1 being the least recent entry), a specific query by number from history is run.

\q

\quit

\exit

Exit interactive mode.

Examples

List CLI Options

1
kisql -?
Usage: kisql [-options]

where options include:

Connect:
     --url      <url>         Connect to this GPUdb URL.  Use either this option or -host/-port, but not both.  Can also be specificed by the
                              KI_URL environment variable
...

Connect

1
2
$ kisql --url https://ab-cde-fghijklmnopr.westus2.cloudapp.azure.com/ab-cde-fghijklmnopr/gpudb-0 --user auser
auser password:
Connection successful
Catalog [Kinetica]
Deployment Time: 0.477 s
Kinetica =>

List Schemas & Tables

1
Kinetica()=> \d
+-------------+---------------+----------------+--------------+
| TABLE_CAT   | TABLE_SCHEM   | TABLE_NAME     | TABLE_TYPE   |
+-------------+---------------+----------------+--------------+
| Kinetica    | SYSTEM        | ITER           | TABLE        |
| Kinetica    | demo          | nyctaxi        | TABLE        |
+-------------+---------------+----------------+--------------+

Run Queries

1
Kinetica()=> SELECT COUNT(*) FROM demo.nyctaxi;
+----------+
|   EXPR_0 |
+----------+
|   500000 |
+----------+

Use the CLI Query and Output Format Parameters

1
2
3
4
5
kisql --url https://ab-cde-fghijklmnopr.westus2.cloudapp.azure.com/ab-cde-fghijklmnopr/gpudb-0 \
      --isql \
      --showTime 0 \
      --format delim --delim ',' \
      --sql 'SELECT TOP 10 vendor_id, TRIM(fare_amount) AS fare_amount, passenger_count, dropoff_datetime FROM demo.nyctaxi'
vendor_id,fare_amount,passenger_count,dropoff_datetime
YCAB,20.5,1,2015-04-21 23:40:14
NYC,10.0,1,2015-04-03 01:43:31
YCAB,11.5,1,2015-04-02 22:05:07
NYC,17.5,2,2015-04-21 21:47:20
NYC,8.0,2,2015-04-21 21:32:32
NYC,15.5,1,2015-04-21 21:43:38
YCAB,11.0,1,2015-04-06 13:18:35
YCAB,8.0,1,2015-04-06 13:10:02
NYC,6.5,5,2015-04-14 22:00:52
YCAB,25.5,1,2015-04-29 12:27:31

Load Files

Create a directory, in which to load your file(s):

1
Kinetica()=> CREATE DIRECTORY 'my_directory';

Upload your file(s) to KiFS:

1
Kinetica()=> UPLOAD FILES 'my_data.csv' INTO 'my_directory';

Finally, load your file into a table:

1
Kinetica()=> LOAD INTO ki_home.my_data FROM FILE PATHS 'kifs://my_directory/my_data.csv'