Commands Supported by the Ocient JDBC CLI Program

Commands are not case-sensitive in the JDBC CLI program. For DDL statements, see Data Definition Language (DDL) Statement Reference.

All commands, except QUIT, must end with a semicolon at the command line.

You can cancel any query that does not update data by pressing CTRL+C.

The Ocient JDBC driver supports multi-statement transactions. By default, a connection uses the autocommit mode, in which the database commits each statement immediately. To group several statements into a single transaction, disable autocommit with SET AUTOCOMMIT, and then end the transaction with COMMIT or ROLLBACK.

The HTTP Query API does not support transactions. To use transactions, connect to the Ocient System with the JDBC driver or the pyocient module.

The Connector supports transactions and performs the commit and rollback actions automatically. When you use the Spark Connector, you do not need to issue COMMIT or ROLLBACK commands.

CANCEL

Cancels the current query that is still running on the system. Syntax

Shell

CANCEL uuid;

Parameter	Data Type	Description
uuid	String	A query Universally Unique IDentifier (UUID) that is currently running. Use LIST ALL QUERIES, or search the `sys.queries` table to find the UUID. You must enclose the UUID in single quotes, such as `'c87b506f-66d6-4987-8bf7-1fd4a06b64b1'`.

COMMIT

Commits the current transaction and permanently saves all changes made after the transaction started. This command applies only when you disable autocommit with SET AUTOCOMMIT. When autocommit is enabled, COMMIT has no effect. Syntax

Shell

COMMIT [ TRANSACTION ];

Entering the COMMIT command with no other arguments commits the current transaction on the connection. This action is equivalent to calling the commit() method on the JDBC Connection object.

CONNECT

Connects to a SQL Node. For details on the connection steps, see Connect Using JDBC. Syntax

Shell

CONNECT TO <url> [ USER username USING password ]

The <url> parameter must follow this format:jdbc:ocient://node_address1:port1[,node_address2:port2[,...]]/database_name[;property=value[;...]]

If you do not specify the username and password, then the database uses the default credentials specified at startup.

JDBC URL parameters

Parameter	Description
`node_address`	A hostname or IP address. The database attempts to connect using each `DNSName:Port` pair in the comma-delimited list from left to right until a successful connection happens.
`port`	Standard JDBC port number is `4050`. For details about ports, see Ports.
`database_name`	The identifier used for your database.

Additional JDBC URL Parameters The JDBC URL can include these optional parameters in the key-value format property=value. Separate each parameter pair by semicolons. URL parameters are case-insensitive.

To authenticate using Single Sign-On, set the handshake parameter to SSO and leave the user and password parameters empty. For example: jdbc:ocient://DNSName:Port[,IP2:Port2,...]/databaseName;handshake=SSO;user=;password=;

Parameter	Description
`bulkLoadChunkSize`	The number of rows to include in each JSON data file (chunk) uploaded. The default value is `60000`.
`bulkLoadCleanupOnFailure`	If you set this parameter value to `true`, the driver deletes the temporary files and pipelines even if the load fails. The default value is `true`.
`bulkLoadFailOnError`	If you set this parameter value to `true`, `executeBatch()` fails if any `INSERT` operation fails. If you set this parameter value to `false`, `executeBatch()` falls back to a standard multi-row `INSERT` operation if any INSERT operation fails. The default value is `false`.
`bulkLoadLoaderNodesCacheSeconds`	The number of seconds to cache the list of active Loader Nodes the system discovers during bulk load. When you set this parameter to a value greater than `0`, the driver caches the Loader Node list for the specified duration and forwards the same value as a server-side `CACHE_MAX_TIME` hint on the lookup query. This cache is -wide and is not specific to individual connections. Do not enable this parameter if your application connects to multiple Ocient clusters from the same JVM. The default value is `0` (disabled).
`bulkLoadMode`	The bulk load transport mode. Set this parameter to `ssh` to stage data using SSH/SFTP to Loader Nodes, or set it to `s3` to stage data in an S3-compatible object store. The default value is `ssh`.
`bulkLoadPollIntervalSeconds`	The number of seconds to wait between polling the `sys.pipelines` system catalog table for load status. The default value is `2`.
`bulkLoadS3AccessKeyId`	The access key identifier for authenticating to the S3 endpoint. This parameter is required when you set the `bulkLoadMode` parameter to `s3`.
`bulkLoadS3ApiCallTimeoutMs`	The timeout, in milliseconds, for individual S3 API calls. The default value is `120000` (two minutes).
`bulkLoadS3Bucket`	The S3 bucket name for staging bulk load data files. This parameter is required when you set the `bulkLoadMode` parameter to `s3`.
`bulkLoadS3EnablePathStyleAccess`	If you set this parameter to `true`, the driver uses path-style access for S3 requests (e.g., `http://endpoint/bucket/key` instead of `http://bucket.endpoint/key`). This parameter is required for S3-compatible services such as MinIO or Ceph. The default value is `true`.
`bulkLoadS3Endpoint`	The S3-compatible endpoint URL for bulk load staging (e.g., `https://s3.us-east-1.amazonaws.com`). This parameter is required when you set the `bulkLoadMode` parameter to `s3`.
`bulkLoadS3MultipartPartSize`	The size, in bytes, of each part in a multipart upload. The minimum value is `5242880` (5 MiB). The default value is `10485760` (10 MiB).
`bulkLoadS3MultipartThreshold`	The size, in bytes, above which the driver uses multipart upload instead of a single PUT request. The default value is `10485760` (10 MiB).
`bulkLoadS3Prefix`	The key prefix for staged objects in S3. The default value is `ocient-bulk-load/`.
`bulkLoadS3Region`	The AWS region for the S3 endpoint. The default value is `us-east-1`.
`bulkLoadS3SecretAccessKey`	The secret access key for authenticating to the S3 endpoint. This parameter is required when you set the `bulkLoadMode` parameter to `s3`.
`bulkLoadS3UploadConcurrency`	The number of data chunks to upload to S3 in parallel. Increasing this value can improve staging throughput for large batches. The default value is `1`.
`bulkLoadSshChannelTimeoutSeconds`	The number of seconds to wait for an SFTP channel to open on an established SSH connection during bulk load. If the channel does not open within this duration, the driver throws an error. The default value is `30`.
`bulkLoadSshConnectTimeoutSeconds`	The number of seconds to wait for the SSH connection and authentication to a Loader Node during bulk load. If the system does not establish a connection within this duration, the driver throws an error. The default value is `10`.
`bulkLoadSshKeyPath`	The absolute path to the password-less private SSH key file. The default value is `~/.ssh/id_rsa`.
`bulkLoadSshHostKeyVerification`	Controls how the JDBC driver verifies an SSH server host key for bulk load connections. Supported values are: `acceptAll` (default) — The driver accepts all host keys. The connector does not check the server host key against the `known_hosts` file and does not output warning logs. `strict` — The driver accepts only hosts with keys present in the `known_hosts` file (see the `bulkLoadSshKnownHostsPath` parameter). The driver rejects unknown hosts or hosts with changed keys. `acceptNew` — The driver accepts new hosts and adds their keys to the `known_hosts` file, but rejects hosts with keys changed from the data in `known_hosts`.
`bulkLoadSshKnownHostsPath`	The path to the `known_hosts` file, which contains SSH verification keys. The system uses this path only if the `bulkLoadSshHostKeyVerification` parameter is set to `strict` or `acceptNew`. The default path is `~/.ssh/known_hosts`.
`bulkLoadSshUser`	The SSH username to use when connecting to Loader Nodes.
`bulkLoadThreshold`	The minimum number of rows in a batch group to trigger a bulk load. The default value is `25000`.
`bulkLoadUseAtomicPipeline`	If you set this parameter to `true`, bulk load uses an atomic pipeline that creates, starts, and monitors the pipeline in a single blocking operation `(CREATE TRANSACTIONAL PIPELINE ... START FOREGROUND)`. This mode requires the Ocient System version 27.1 or later. Set this parameter to `false` to use the legacy create, start, monitor, and drop functionality when connecting to older servers. The default value is `true`.
`defaultSchema`	Default schema.
`enableBulkLoad`	Enables the high-speed bulk load feature. Set this parameter value to `true` to enable this feature. Otherwise, set this parameter to `false` to leave the feature disabled. The default value is `false`.
`force`	If set to true (case-sensitive), this parameter disables load-balancing for the connection.
`handshake`	Specifies the handshake protocol used for the connection. Supported options include: `"CBC", "GCM", "SSO"` `"GCM"` — (Galois/Counter Mode). This is the default encryption and is the recommended password encryption algorithm. `"CBC"` — (Cipher Block Chaining) for password encryption. `"SSO"` — Single Sign-On.
`identityprovider`	An SSO integration established in the database. For details, see CREATE SSO INTEGRATION.
`logfile`	The filename to use for JDBC tracing.
`loglevel`	If set to ERROR (warnings and errors only) or DEBUG (verbose tracing) and logfile is also set, JDBC tracing is enabled. This parameter is case-sensitive.
`longQueryThreshold`	Estimated query runtime in milliseconds before deeper query optimization runs. `0` — Use database server default. `-1` — Never run deeper optimization.
`maxRows`	Maximum allowed result set size in the number of rows.
`maxRowsPerInsertBatch`	The maximum number of rows to combine into a single multi-row `INSERT` SQL statement when executing a batch load. If the total number of rows in a batch group exceeds this limit, the driver splits the group into multiple sub-statements. The default value is 128,000 rows.
`maxTempDisk`	Maximum allowed temp disk usage as a percentage (0 - 100).
`maxThreadsPerResultSet`	The maximum number of threads the client uses to fetch rows from the server per Result Set, as defined in the official JDBC documentation. In this context, a thread represents a logical connection to the database. Applications that create and operate on Statement objects concurrently might find value in setting this parameter. Defaults to `0`, which effectively creates an unbounded Result Set thread pool.
`maxTime`	Maximum allowed runtime of a query in seconds before it is canceled on the server.
`networkTimeout`	Network connection timeout in milliseconds. If unspecified, this defaults to 10000 milliseconds.
`parallelism`	Limits a query to running on a specified number of cores on each CPU.
`password`	The password for the user.
`priority`	Query priority. This sets the priority for queries to run on the server side. If unspecified, this defaults to `1.0`.
`priorityAdjustFactor`	The default query priority adjustment value. For details, see SET ADJUSTFACTOR.
`priorityAdjustTime`	The default frequency to adjust the query priority. For details, see SET ADJUSTTIME.
`serviceClassName`	Specifies the name of the service class to use for the database session.
`sparkMode`	When you set this parameter to `true`, the driver enables -specific JDBC behavior intended to improve compatibility with the Ocient Spark connector. For details, see JDBC Spark Connector. This parameter defaults to `false` if you are connecting directly using the Ocient JDBC driver (e.g., connecting with the `DriverManager` class or CLI). Otherwise, if you are using the Spark connector (catalog or `.format("ocient")`), the default is `true`.
`ssoNumericAddress`	Specifies the SSO callback URL as `127.0.0.1`. When this value is `false`, the URL is `localhost`. The default value is `false`.
`ssoOAuthCodeCallbackPort`	If `ssoOAuthFlow=authorizationCode`, this parameter specifies the port that the SSO authorization uses. If unspecified, the default value is `7050`.
`ssoOAuthFlow`	This parameter is only applicable if SSO authorization is enabled. Forces the driver to use either the “authorizationCode” or “deviceGrant” flow to establish a Single Sign-On session. If this parameter is not provided, the System uses the “authorizationCode” flow when a web browser is available to the client and the “deviceGrant” flow when a web browser is not available to the client.
`ssoDebugMode`	When set to `true`, the system records additional log messages related to SSO.
`ssoOktaNativeTokenPath`	The path to the Native Token (AES-256GCM JWE). The path is relative to your home directory (i.e. ’~/’).
`ssoSslCallback`	Specifies whether to use HTTPS instead of HTTP for the SSO callback URL. The default value is `false`.
`ssoTimeoutSeconds`	The number of seconds before the SSO connection request times out. The default value is `60`.
`statementPooling`	ℹ️ This parameter has been removed as of Ocient JDBC version 2.104 and later. When set to `ON`, recently used statements are cached. Set to `OFF` when using a third-party application that implements statement pooling
`tls`	Can be set to `unverified` or `on`. Enables SSL/TLS encryption for the connection.
`timeoutMillis`	Number of milliseconds before cancellable operations are timed out and killed by the driver. 0 means no timeout. Default: 0
`user`	The identifier of the user.

The JDBC driver also supports system properties that apply globally across all connections. To set these properties, use the -D flag at JVM startup. For details, see JVM System Properties.

DESCRIBE TABLE

Lists the columns of the specified table and its associated data types. Syntax

Shell

DESCRIBE TABLE [schema.]table [verbose]

Parameter	Description
`table`	A table identifier.
`schema`	A qualifying schema name for the specified table. If a qualifying schema is not specified for the table, the default name schema is assumed.
`verbose`	If specified, the Ocient System prints the full column metadata for the specified table as returned by GetColumns.

DESCRIBE VIEW

Returns the query text used to create the specified view. Syntax

Shell

DESCRIBE VIEW [schema.]view [verbose]

Parameter	Description
`view`	A view identifier.
`schema`	A qualifying schema name for the specified table. If a qualifying schema is not specified for the table, the default name schema is assumed.
`verbose`	If specified, the Ocient System prints the full column metadata for the specified table as returned by GetColumns.

EXTRACT

Extracts a result set in delimited files to a specified location. For details about using this command, see Data Extract Tool. Syntax

SQL

EXTRACT TO location
   { LOCAL | S3 }
   [ OPTIONS ( [ param=value [ ,... ] ] ) ] AS query

GET JDBC VERSION

Returns the JDBC version of the driver. Syntax

Shell

GET JDBC VERSION

GET SCHEMA

Retrieves the default name schema for tables. Syntax

Shell

GET SCHEMA

KILL

Issues a hard kill command to the virtual machine to terminate a query running on the system. Use only if CANCEL fails. Syntax

Shell

KILL uuid

Parameter	Description
`uuid`	A query UUID that is currently running. Use LIST ALL QUERIES, or search the `sys.queries` table to find the UUID.

LIST ALL QUERIES

List all the currently executing queries on the database. This is the equivalent of executing SELECT * FROM sys.queries. Syntax

Shell

LIST ALL QUERIES

LIST INDEXES

Lists the indexes on the specified table and its columns. Syntax

Shell

LIST INDEXES [schema.]table [verbose]

Parameter	Description
`table`	A table identifier.
`schema`	A qualifying schema name for the specified table. If a qualifying schema is not specified for the table, the default name schema is assumed.
`verbose`	If specified, the Ocient System prints the full column metadata for the specified table as returned by getIndexInfo.

LIST SYSTEM TABLES

Lists all system catalog tables in the database. Syntax

Shell

LIST SYSTEM TABLES [verbose]

Parameter	Description
`verbose`	If verbose is specified, the full table metadata, as returned by getTables, is printed.

LIST TABLES

Lists all tables (in all schemata) in the database. Syntax

Shell

LIST TABLES [verbose]

Parameter	Description
`verbose`	If verbose is specified, the full table metadata, as returned by getTables, is printed.

LIST VIEWS

Lists all views (in all schemata) in the database. Syntax

Shell

LIST VIEWS [verbose]

Parameter	Description
`verbose`	If verbose is specified, the full table metadata, as returned by getViews, is printed.

OUTPUT GIS KML

Outputs the next query in KML format to the specified filename. This flag affects only the next query. This syntax prints out all GIS types to the KML file, and non-GIS types are added to the description of elements in the same row. You can upload this KML file to certain visualization tools. Syntax

Shell

OUTPUT GIS KML <filename>

Parameter	Description
`filename`	A filename for the KML output.

OUTPUT NEXT QUERY

Output the next query in CSV format to the specified filename. This flag only affects the next query. All subsequent queries print out their results. Add APPEND to add output to the specified file instead of overwriting it. Syntax

Shell

OUTPUT NEXT QUERY filename [APPEND]

Parameter	Description
`filename`	A filename for the CSV output.

PERFORMANCE

Enables different levels of query result output for measuring benchmarks by using different performance options. Syntax

Shell

PERFORMANCE [ NETWORK | DATABASE | OFF ]

Parameter	Description
`OFF`	This is the default mode. The system executes a query through all normal stages, including the database, client network, and client processing.
`NETWORK`	The system executes a query through the database and client network, but discards rows before they are processed by the client (nothing is printed to stdout). Use this mode when you think the client network is hindering performance.
`DATABASE`	The system executes a query through the database only. The database does not send any matching rows to the client. Use this mode when you think the database query execution is affecting performance.

PERFORMANCE ON mode has been removed as of JDBC version 3.0.

QUIT

Closes the command-line interface. Syntax

Shell

QUIT

REPORT LOG ON

Enables -based metrics collection and redirects reporting events to the configured appenders.

The REPORT LOG ON command requires JDBC driver version 2.88 or later.

The Log4j configuration determines the destination for report logs. The Ocient logger name is com.ocient.util.ReportLogger. For details about configuring Log4j, see the Log4j documentation. The default Log4j configuration routes report logs to the RollingFileAppender. The default configuration includes these settings: Properties

fileName="logs/${sys:ocient.jdbc.sessionId:-unknown}/report.log"
filePattern="logs/${sys:ocient.jdbc.sessionId:-unknown}/%d{yyyy-MM-dd}-%i-report.log.gz"

Rollover Policies

TimeBasedTriggeringPolicy
SizeBasedTriggeringPolicy (100MB)

You can retrieve the default configuration from the ocient-jdbc4 jar file by using an archive utility like unzip. For example:

Shell

unzip -p <path-to-ocient-jdbc4> log4j2.xml

Syntax

Shell

REPORT LOG ON [ interval ]

Parameter	Description
`interval`	Optional. Sets the Log4j reporting interval in seconds. If unspecified, defaults to `30`.

REPORT LOG OFF

Disables file-based metrics collection.

The REPORT LOG OFF command requires JDBC driver version 2.88 or later.

Syntax

Shell

REPORT LOG OFF

ROLLBACK

Rolls back the current transaction and discards all changes made after the transaction started. This command applies only when you disable autocommit with SET AUTOCOMMIT. When autocommit is enabled, ROLLBACK has no effect. Syntax

Shell

ROLLBACK [ TRANSACTION ];

Entering ROLLBACK with no other arguments rolls back the current transaction on the connection. This action is equivalent to calling the rollback() method on the JDBC Connection object.

SELECT

Retrieves the appropriate result set of a SQL SELECT statement. Syntax

Shell

SQL select statement

SET AUTOCOMMIT

Enables or disables the autocommit mode for the connection. When autocommit is enabled, the database commits each statement immediately. This mode is the default. When autocommit is disabled, the database groups statements into a transaction that you complete with the COMMIT or ROLLBACK commands. When you change autocommit from OFF to ON, the database commits any pending transactions. Syntax

Shell

SET AUTOCOMMIT { ON | OFF }

Enables or disables autocommit mode with values ON or OFF, respectively. When you enter the ON value, the database commits each statement immediately. This value is the default. When you enter the OFF value, the database groups subsequent statements into a transaction until you enter the COMMIT or ROLLBACK command.

SET MAXROWS

Sets the maximum number of rows allowed in a result set. Applies only to the statement object that executed the command. Syntax

Shell

SET MAXROWS num_of_rows

Parameter	Description
`num_of_rows`	The maximum number of rows allowed in a result set. If a query returns more rows than the specified value, the result set is truncated by `num_of_rows`. To disable this setting, you can either set the maximum rows to 0 or use the `RESET` keyword (e.g., `SET MAXROWS RESET;`).

SET ADJUSTFACTOR

Adjusts the query priority by a specified percentage amount at every interval set by the SET ADJUSTTIME command.

The SET ADJUSTFACTOR command requires JDBC driver version 2.52 or later.

Syntax

Shell

SET ADJUSTFACTOR priorityAdjustFactor

Parameter	Description
`priorityAdjustFactor`	A percentage amount to raise or lower the query priority. Accepted values are in the range of [-1,1]. At every interval set by the `priorityAdjTime`, the query priority is multiplied by `1 + priorityAdjustFactor` to get a new priority value.

SET ADJUSTTIME

Modifies how frequently the query priority is adjusted during the course of execution.

The SET ADJUSTTIME command requires JDBC driver version 2.52 or later.

Syntax

Shell

SET ADJUSTTIME priorityAdjTime

Parameter	Description
`priorityAdjTime`	The frequency for adjusting the query priority in seconds.

SET MAXTEMPDISK

Sets the maximum percentage of temporary disk used by queries. Applies only to the statement object that executed the command. Syntax

Shell

SET MAXTEMPDISK percentage

Parameter	Description
`percentage`	The maximum percentage of temporary disk used by queries. Any queries exceeding this threshold are killed. To disable this setting, use the `RESET` keyword (e.g., `SET MAXTEMPDISK RESET;`).

SET MAXTIME

Sets the maximum query time. Applies only to the statement object that executed the command. Syntax

Shell

SET MAXTIME seconds

Parameter	Description
`seconds`	The maximum query time. Queries longer than this are killed. To disable this setting, you can either set the maximum time to 0 or use the `RESET` keyword (e.g., `SET MAXTIME RESET;`).

SET PARALLELISM

Sets a limit on the number of cores on each CPU that can be allocated to running the query. Applies only to the statement object that executed the command. The parallelism value limits a query to running on the specified number of cores on each CPU. Syntax

Shell

SET PARALLELISM parallelism

Parameter	Description
`parallelism`	Limits a query to running on a specified number of cores on each CPU. To disable this setting, you can either set the `parallelism` value to -1 or use the `RESET` keyword (e.g., `SET PARALLELISM RESET;`).

SET PRIORITY

Sets the scheduling priority to use for queries. Applies only to the statement object that executed the command. Syntax

Shell

SET PRIORITY priority

Parameter	Description
`priority`	The scheduling priority to use for queries. Set from 0 to 100. To disable this setting, you can either set the priority to -1 or use the `RESET` keyword. (e.g., `SET PRIORITY RESET;`).

SET SCHEMA

Changes the default schema for tables. Applies only to the statement object that executed the command. Syntax

Shell

SET SCHEMA schema

Parameter	Description
`schema`	The default schema for new tables.

SET SERVICECLASS

Limits a query to using the specified service class. Applies only to the statement object that executed the command. Syntax

Shell

SET SERVICECLASS service_class_name

Parameter	Description
`service_class_name`	The service class that a query is limited to using. To disable this setting, you can use the `RESET` keyword. (e.g., `SET SERVICECLASS RESET;`).

SOURCE

Reads and executes commands found in the specified file. Syntax

Shell

SOURCE filename

Parameter	Description
`filename`	A valid filename or path.

TIMING

Enables or disables reporting the execution time of each query. Syntax

Shell

TIMING { ON | OFF }

JDBC Manual System Catalog

​CANCEL

​COMMIT

​CONNECT

​DESCRIBE TABLE

​DESCRIBE VIEW

​EXTRACT

​GET JDBC VERSION

​GET SCHEMA

​KILL

​LIST ALL QUERIES

​LIST INDEXES

​LIST SYSTEM TABLES

​LIST TABLES

​LIST VIEWS

​OUTPUT GIS KML

​OUTPUT NEXT QUERY

​PERFORMANCE

​QUIT

​REPORT LOG ON

​REPORT LOG OFF

​ROLLBACK

​SELECT

​SET AUTOCOMMIT

​SET MAXROWS

​SET ADJUSTFACTOR

​SET ADJUSTTIME

​SET MAXTEMPDISK

​SET MAXTIME

​SET PARALLELISM

​SET PRIORITY

​SET SCHEMA

​SET SERVICECLASS

​SOURCE

​TIMING

​Related Links

CANCEL

COMMIT

CONNECT

DESCRIBE TABLE

DESCRIBE VIEW

EXTRACT

GET JDBC VERSION

GET SCHEMA

KILL

LIST ALL QUERIES

LIST INDEXES

LIST SYSTEM TABLES

LIST TABLES

LIST VIEWS

OUTPUT GIS KML

OUTPUT NEXT QUERY

PERFORMANCE

QUIT

REPORT LOG ON

REPORT LOG OFF

ROLLBACK

SELECT

SET AUTOCOMMIT

SET MAXROWS

SET ADJUSTFACTOR

SET ADJUSTTIME

SET MAXTEMPDISK

SET MAXTIME

SET PARALLELISM

SET PRIORITY

SET SCHEMA

SET SERVICECLASS

SOURCE

TIMING

Related Links