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
|
|
|
|
|
|
|
|
JDBC Connection String
|
|
|
|
|
|
|
|
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:
| ||||||||||||||||
-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:
| ||||||||||||||||
--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 time and timestamp data in the time zone with specified ID (e.g., PST). Use system for local system time. If not specified, default is UTC. | ||||||||||||||||
--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
|
|
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
|
|
Connection successful Catalog [Kinetica] Deployment Time: 0.477 s Kinetica =>
List Schemas & Tables
|
|
+-------------+---------------+----------------+--------------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | +-------------+---------------+----------------+--------------+ | Kinetica | SYSTEM | ITER | TABLE | | Kinetica | demo | nyctaxi | TABLE | +-------------+---------------+----------------+--------------+
Run Queries
|
|
+----------+ | EXPR_0 | +----------+ | 500000 | +----------+
Use the CLI Query and Output Format Parameters
|
|
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):
|
|
Upload your file(s) to KiFS:
|
|
Finally, load your file into a table:
|
|