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.

It is also bundled with Kinetica and is located at /opt/gpudb/bin/kisql on default installations.

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

Ingestion of local file data into tables (INSERT INTO ... SELECT ... FROM FILE)
Uploading of local files into the Kinetica File System (KiFS) for later ingestion (UPLOAD FILE)
Downloading of Kinetica File System (KiFS) files to local storage (DOWNLOAD FILE)

For a graphical SQL interface, see GAdmin.

Connecting

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.

KiSQL can connect to Kinetica either by passing a URL and these authentication parameters on the command line or by passing a JDBC connection string containing the corresponding parameters.

Regardless of which style is used, the remainder of the parameters should be appended to the specified connection URL. See ODBC/JDBC Configuration for the full list.

Parameterized Options

HTTP
1
kisql --url http://<db.host>:9191[[;<parameter>=<value>]*] --user <username>
HTTPS w/o Cert Check
1
kisql --url https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*] --user <username>
HTTPS w/ Default Trust Cert Check
1
kisql --url https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*] --user <username> --bypassSslCertCheck false
HTTPS w/ Custom Trust Cert Check
1
kisql --url https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*] --user <username> --bypassSslCertCheck false --trustStore <filepath>

JDBC Connection String

HTTP
1
kisql --cs "jdbc:kinetica://<db.host>:9191[[;<parameter>=<value>]*]" --user <username>
HTTPS w/o Cert Check
1
kisql --cs "jdbc:kinetica:URL=https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*]" --user <username>
HTTPS w/ Default Trust Cert Check
1
kisql --cs "jdbc:kinetica:URL=https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*];BypassSslCertCheck=0" --user <username>
HTTPS w/ Custom Trust Cert Check
1
kisql --cs "jdbc:kinetica:URL=https://<db.host>:8082/gpudb-0[[;<parameter>=<value>]*];BypassSslCertCheck=0;SslCACertPath=<certPath>;SslCACertPassword=<certPass>" --user <username>

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://localhost:9191;RowsPerFetch=20000. Overrides --host & --port. This can also be specified by the KI_URL environment variable. See Secure Connections for specifying SSL connections and Failover Connections for specifying HA connections.

--connectionString "<JDBC URL>"

Connect using the specified JDBC URL; e.g., jdbc:kinetica://localhost:9191;RowsPerFetch=20000. Overrides --host, --port, --url, & primaryUrl; will not override other parameters specified on the command line. See ODBC/JDBC Configuration for the JDBC connection string format and the full list of options able to be specified through it.

Note

UID or PWD specified in the connection string will be ignored; use --user or the KI_USER & KI_PWD environment variables instead.

--primaryUrl <Kinetica URL> Try the specified Kinetica URL first when using --url to specify multiple failover URLs for HA.

--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. See the HTTPD + SSL setup instructions sections for more information on setting up a TrustStore and setting up ODBC for secure connections.

--bypassSslCertCheck [<value>] If using SSL, don't validate the server certificate. If not specified, or if specified with no value, the default is not to validate the 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.

Information

--listAllTimeZones List all supported time zones and exit.

--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 specified by the
                              KI_URL environment variable
...

Connect

1
2
$ kisql --url http://localhost:9191;RowsPerFetch=20000 --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 http://localhost:9191;RowsPerFetch=20000 \
      --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'