Kinetica SQL (KiSQL)

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

Command Line Interface

KiSQL can be accessed via command line from /opt/gpudb/bin/kisql. The only required parameter to connect is one of --host, --url, or --connectionString; otherwise, the KI_HOST or KI_URL environment variable must be set.

Parameters

Parameter	Description
Connection
-d <schema> --db <schema>	Set the default schema in which new tables and views will be created. This can also be specified by the KI_DB environment variable. If not specified, default is MASTER.
-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 and Failover Connections for specifying HA 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.
--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.
--pwd <password> --password <password>	Authenticate with the specified password. This can also be specified by the KI_PWD 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.
--trustStorePwd <password> --trustStorePass <password>	Use the specified Java TrustStore password for the TrustStore. 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 for JDBC.
--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 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 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

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 > /tmp/data/my_sql_test.out

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

./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.