Skip to navigation
Connection Driver ReferenceJDBC Manual

Commands Supported by the Ocient JDBC CLI Program

View as Markdown

Commands are not case-sensitive.

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.

CANCEL

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

Syntax

CANCEL uuid;
ParameterData TypeDescription
uuidStringA query Universally Unique IDentifier (UUID) that is currently running. Use Commands Supported by the Ocient JDBC CLI Program or search the sys.queries table to find the UUID.

CONNECT

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

Syntax

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

ParameterDescription
node_addressA 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.
portStandard JDBC port number is 4050.
For details about ports, see Ports.
database_nameThe 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=;

ParameterDescription
bulkLoadChunkSizeThe number of rows to include in each JSON data file (chunk) uploaded. The default value is 60000.
bulkLoadCleanupOnFailureIf 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.
bulkLoadFailOnErrorIf 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.
bulkLoadPollIntervalSecondsThe number of seconds to wait between polling the sys.pipelines system catalog table for load status. The default value is 2.
bulkLoadSshKeyPathThe absolute path to the password-less private SSH key file. The default value is ~/.ssh/id_rsa.
bulkLoadSshUserThe SSH username to use when connecting to Loader Nodes.
bulkLoadThresholdThe minimum number of rows in a batch group to trigger a bulk load. The default value is 25000.
defaultSchemaDefault schema.
enableBulkLoadEnables 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.
forceIf set to true (case-sensitive), this parameter disables load-balancing for the connection.
handshakeSpecifies 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.
identityproviderAn SSO integration established in the database. For details, see Cluster and Node Management.
logfileThe filename to use for JDBC tracing.
loglevelIf 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.
longQueryThresholdEstimated query runtime in milliseconds before deeper query optimization runs.
0 — Use database server default.
-1 — Never run deeper optimization.
maxRowsMaximum allowed result set size in the number of rows.
maxTempDiskMaximum allowed temp disk usage as a percentage (0 - 100).
maxThreadsPerResultSetThe 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 property. Defaults to 0, which effectively creates an unbounded Result Set thread pool.
maxTimeMaximum allowed runtime of a query in seconds before it is canceled on the server.
networkTimeoutNetwork connection timeout in milliseconds.
If unspecified, this defaults to 10000 milliseconds.
parallelismLimits a query to running on a specified number of cores on each CPU.
passwordThe password for the user.
priorityQuery priority. This sets the priority for queries to run on the server side.
If unspecified, this defaults to 1.0.
priorityAdjustFactorThe default query priority adjustment value. For details, see Commands Supported by the Ocient JDBC CLI Program.
priorityAdjustTimeThe default frequency to adjust the query priority. For details, see Commands Supported by the Ocient JDBC CLI Program.
serviceClassNameSpecifies the name of the service class to use for the database session.
sparkModeWhen you set this parameter to true, the driver enables Apache® Spark™‑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.
ssoNumericAddressSpecifies the SSO callback URL as 127.0.0.1. When this value is false, the URL is localhost. The default value is false.
ssoOAuthCodeCallbackPortIf ssoOAuthFlow=authorizationCode, this property specifies the port that the SSO authorization uses.
If unspecified, the default value is 7050.
ssoOAuthFlowThis property 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 property is not provided, the Ocient® 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.
ssoDebugModeWhen set to true, the system records additional log messages related to SSO.
ssoOktaNativeTokenPathThe path to the OKTA® Native Token (AES-256GCM JWE). The path is relative to your home directory (i.e. ’~/’).
ssoSslCallbackSpecifies whether to use HTTPS instead of HTTP for the SSO callback URL. The default value is false.
ssoTimeoutSecondsThe number of seconds before the SSO connection request times out. The default value is 60.
statementPoolingℹ️ This property 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
tlsCan be set to unverified or on. Enables SSL/TLS encryption for the connection.
timeoutMillisNumber of milliseconds before cancellable operations are timed out and killed by the driver. 0 means no timeout. Default: 0
userThe identifier of the user.

DESCRIBE TABLE

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

Syntax

DESCRIBE TABLE [schema.]table [verbose]
ParameterDescription
tableA table identifier.
schemaA qualifying schema name for the specified table.
If a qualifying schema is not specified for the table, the default name schema is assumed.
verboseIf 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

DESCRIBE VIEW [schema.]view [verbose]
ParameterDescription
viewA view identifier.
schemaA qualifying schema name for the specified table.
If a qualifying schema is not specified for the table, the default name schema is assumed.
verboseIf 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

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

GET JDBC VERSION

Returns the JDBC version of the driver.

Syntax

GET JDBC VERSION

GET SCHEMA

Retrieves the default name schema for tables.

Syntax

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

KILL uuid
ParameterDescription
uuidA 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

LIST ALL QUERIES

LIST INDEXES

Lists the indexes on the specified table and its columns.

Syntax

LIST INDEXES [schema.]table [verbose]
ParameterDescription
tableA table identifier.
schemaA qualifying schema name for the specified table.
If a qualifying schema is not specified for the table, the default name schema is assumed.
verboseIf 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

LIST SYSTEM TABLES [verbose]
ParameterDescription
verboseIf 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

LIST TABLES [verbose]
ParameterDescription
verboseIf 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

LIST VIEWS [verbose]
ParameterDescription
verboseIf 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

OUTPUT GIS KML <filename>
ParameterDescription
filenameA 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

OUTPUT NEXT QUERY filename [APPEND]
ParameterDescription
filenameA filename for the CSV output.

PERFORMANCE

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

Syntax

PERFORMANCE [ NETWORK | DATABASE | OFF ]
ParameterDescription
OFF
This is the default mode.
The system executes a query through all normal stages, including the database, client network, and client processing.
NETWORKThe 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.
DATABASEThe 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

QUIT

REPORT LOG ON

Enables Apache® Log4J 2™ -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:

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

Syntax

REPORT LOG ON [ interval ]
ParameterDescription
intervalOptional. 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

REPORT LOG OFF

SELECT

Retrieves the appropriate result set of a SQL SELECT statement.

Syntax

SQL select statement

SET MAXROWS

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

Syntax

SET MAXROWS num_of_rows
ParameterDescription
num_of_rowsThe 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

SET ADJUSTFACTOR priorityAdjustFactor
ParameterDescription
priorityAdjustFactorA 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

SET ADJUSTTIME priorityAdjTime
ParameterDescription
priorityAdjTimeThe 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

SET MAXTEMPDISK percentage
ParameterDescription
percentageThe 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

SET MAXTIME seconds
ParameterDescription
secondsThe 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

SET PARALLELISM parallelism
ParameterDescription
parallelismLimits 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

SET PRIORITY priority
ParameterDescription
priorityThe 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

SET SCHEMA schema
ParameterDescription
schemaThe 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

SET SERVICECLASS service_class_name
ParameterDescription
service_class_nameThe 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

SOURCE filename
ParameterDescription
filenameA valid filename or path.

TIMING

Enables or disables reporting the execution time of each query.

Syntax

TIMING { ON | OFF }

JDBC Manual

System Catalog