Kinetica SQL (KiSQL)

KiSQL allows SQL queries to be performed against the data in Kinetica either from Kinetica Administration Application (GAdmin) or the command line. KiSQL comes pre-installed and ready to use.

Command Line Interface

KiSQL can be accesed via command line from /opt/gpudb/bin/kisql. The only required parameter to connect is either -url or -host (otherwise, the KI_URL or KI_HOST environment variables must be set).

Available Parameters:

Parameter	Description
-d <schema>, -db <schema>	Set the default schema in which new tables and views will be created. Default is the ODBC server's ParentSet
-url <url>	JDBC URL to connect to, e.g., jdbc:simba://localhost:9292. Overrides -host / -port
-h <host>, -host <host>	ODBC host to connect to
-port <port>	ODBC connection port; default is 9292
-user <username>	Username required to connect to Kinetica when authentication is enabled.
-pwd <password>, -password <password>	Password required to connect to Kinetica when authentication is enabled
-f <filepath>, -file <filepath>	Run any SQL statements found in the given file and exit. Statements must be separated by a ;
-sql '<sql_statement>'	Run the given SQL statement (in quotes) and exit
-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
-format <format>	Output formatting specification for inline SQL statements or file output; options are table, vertical, or delim / delimited (default delimiter is ,). Default is table
-debug <value>	Prints debugging information. Takes a boolean value (true/1 or false/ 0); default is false
-echoSql <value>	Echo SQL statements executed. Takes a boolean value (true/1 or false/ 0); default is false
-showTime <value>	Display SQL execution time. Takes a boolean value (true/1 or false/ 0); default is false
-truststore <filepath>	File path to the Java trust store 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
-truststorepwd <password>	Password for the trust store. Can also be specified using the KI_TRUSTSTOREPWD environment variable. See the HTTPD + SSL setup instructions section for more information on setting up a truststore and ODBC
-verbose <value>	Displays JDBC class, url (including SSL and truststore information), host, port, user, and password information as well as the given SQL statement. Takes a boolean value (true/1 or false/0); default is false
-?	Display help information

Important

If -user and -password parameters are 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 or -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. 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	Displays the number of records for each table
\d, \tables	If no argument given, displays the tables and schemas to which the user has access. If a table name is provided, displays 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, invokes the vi editor on the last query executed. If a number is provided (with 1 being the least recent entry), invokes vi editor on specific query by number from the history. Upon exiting the editor, the statement will be executed
\f <type>, \format <type>	Sets the output format type; options are table, vertical, or delim / delimited (delimiter is ,; this cannot be changed in interactive mode). The default format is table
\h, \hist, \history	Displays 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

To run a SQL file and redirect the output to another file:

./kisql -host localhost -port 9191 -f /tmp/data/my_sql_test.sql -echoSql true > /tmp/data/my_sql_test.out

To run a SQL statement inline and change the output formatting to be delimited by a pipe |

./kisql -host localhost -sql 'select top 10 vendor_id, fare_amount, passenger_count, dropoff_datetime from nyctaxi' -format delim -delim '|'
Connection successful
Catalog [KINETICA]
Time 0.221
vendor_id|fare_amount|passenger_count|dropoff_datetime
YCAB|20.5|1|2015-04-21 23:40:14.000000
NYC|10.0|1|2015-04-03 01:43:31.000000
YCAB|11.5|1|2015-04-02 22:05:07.000000
NYC|17.5|2|2015-04-21 21:47:20.000000
NYC|8.0|2|2015-04-21 21:32:32.000000
NYC|15.5|1|2015-04-21 21:43:38.000000
YCAB|11.0|1|2015-04-06 13:18:35.000000
YCAB|8.0|1|2015-04-06 13:10:02.000000
NYC|6.5|5|2015-04-14 22:00:52.000000
YCAB|25.5|1|2015-04-29 12:27:31.000000
Exec time 0.051 Fetch time 0.041

GAdmin

Review usage details in the SQL Query Tool documentation.