JDBC Manual

the {{ocient}} jdbc driver and command line interface (cli) enable you to connect to ocient using a jdbc connection ensure that you meet the prerequisites before using the ocient jdbc driver then, invoke the cli program, configure options, and connect to a database using the driver you can also use the data extract tool to extract a result set to delimited files in the target location for details about data extracting, see docid 1 ls6prwuefq it66b4ee for a list of commands available in the jdbc cli, see docid\ zyojxip3zkuvr9skpiyxo prerequisites this software is required for the jdbc driver software version ocient use the latest ocient system version operating system (os) {{windows}} , {{linux}} , or {{macos}} use the latest version of each os {{java}} see the docid\ rkvsbniz0hzgg7aaqxwxo driver features the ocient jdbc connector supports these features as of the current version unicode support utf 8 isolation levels ocient does not support transactions at this time data types supports all ocient docid\ czxgepf3prq9ufwhwbiuy security / encryption uses ssl/tls to connect to the ocient system tls protocol is available as a docid apnndn tjqmjdd5oqdvd invoke the ocient jdbc cli program if your system meets all the necessary prerequisites, you can run the jdbc cli by using the ocient jdbc jar file to do this, follow these steps go to the ocient {{maven}} https //mvnrepository com/artifact/com ocient/ocient jdbc4 for all jdbc versions for more information on which version to pick, see the docid\ rkvsbniz0hzgg7aaqxwxo page for the jdbc version you want to use, download the jar file with dependencies this jar file follows the format ocient jdbc4 \<version number> jar with dependencies jar move this jar file to the directory where your ocient system is installed from the shell terminal, run this command to launch the jdbc cli java classpath \<path to jar with dependencies> com ocient cli cli \[\<username> \[\<password>]] this example runs jdbc version 2 10 java classpath /ocient jdbc4 2 10 jar with dependencies jar com ocient cli cli testuser testpassword after launching, the jdbc cli prompts you to enter your username and password username admin\@system password admin the interface changes to the ocient cli ocient> connect to your system from the jdbc using a connection string assuming the standard port 4050 , a self signed certificate, and the sql node ip address 10 10 1 1 , you can connect to the system database with the following connecting string ocient> connect to jdbc\ ocient //10 10 1 1 4050/system; the cli responds with a connection message connected to jdbc\ ocient //10 10 1 1 4050/system ocient> now that you are connected to your system, you can execute any queries or commands for java version 1 8 0 144, https //www oracle com/java/technologies/javase jce8 downloads html and install the java cryptography extension (jce) unlimited strength jurisdiction policy files 8 jdbc cli configuration options the jdbc cli reads a configuration file consisting of key value pairs located at / ocient cli configuration with this format key1=value1 key2=value2 keyn=valuen the jdbc cli supports these options option description default value cliidletimeoutminutes configures the idle timeout for the cli the cli rejects subsequent commands and exits the process after cliidletimeoutminutes minutes of inactivity 0 if left unspecified or the configuration file does not exist printuuid accepts values on or off if you set the printuuid option to on , the system prints the query identifier for each query that you execute in the cli off for supported commands, see docid\ zyojxip3zkuvr9skpiyxo use the ocient jdbc driver in java programs first, you must load the ocient driver class with this statement in a java program using the jdbc driver class forname("com ocient jdbc jdbcdriver"); the driver class is located in the jdbc driver jar file named ocient jdbc4 jar and must be available in the classpath defined for the program connect using jdbc the ocient jdbc driver supports connection properties that can be supplied using the docid\ zyojxip3zkuvr9skpiyxo command in the jdbc cli or as a properties object passed in a java application jdbc url example this connection string includes various connection properties that trail the login credentials connect to 'jdbc\ ocient //db example com 6432/salesdb;user=admin\@system;password=admin;loglevel=info;networktimeout=15000;enablebulkload=true;bulkloadthreshold=50000'; java drivermanager example alternatively, you can use the drivermanager class to provide connection properties if you are using a jdbc connection as part of a java application import java sql connection; import java sql drivermanager; import java sql sqlexception; import java util properties; public class ocientconnectionexample { public static void main(string\[] args) throws sqlexception { // base jdbc url host, port, and database string url = "jdbc\ ocient //db example com 6432/salesdb"; // jdbc connection properties properties props = new properties(); props setproperty("user", "analytics user"); // ocient username props setproperty("password", "strongpassw0rd!"); // ocient password props setproperty("loglevel", "info"); // driver logging verbosity props setproperty("networktimeout", "15000"); // 15s network timeout (ms) props setproperty("enablebulkload", "true"); // use bulk load for large batches props setproperty("bulkloadthreshold", "50000"); // min rows for bulk loa try (connection conn = drivermanager getconnection(url, props)) { } } } supported jdbc connection properties the jdbc driver supports these connection parameters 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 bulkloadpollintervalseconds the number of seconds to wait between polling the sys pipelines system catalog table for load status the default value is 2 bulkloadsshkeypath the absolute path to the password less private ssh key file the default value is / ssh/id rsa 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 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 docid\ c4g2tdqxhblp1aavwmzgq 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 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 https //docs oracle com/javase/7/docs/api/java/sql/resultset html 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 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 docid\ zyojxip3zkuvr9skpiyxo priorityadjusttime the default frequency to adjust the query priority for details, see docid\ zyojxip3zkuvr9skpiyxo serviceclassname sp ecifies the name of the service class to use for the database session sparkmode when you set this parameter to true , the driver enables {{spark}} ‑specific jdbc behavior intended to improve compatibility with the ocient spark connector for details, see docid\ pp91aew4 1hy1pft3f4zs 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 property specifies the port that the sso authorization uses if unspecified, the default value is 7050 ssooauthflow this 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 ssodebugmode when set to true , the system records additional log messages related to sso ssooktanativetokenpath the path to the {{okta}} 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 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 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 jdbc bulk loading for very large batches, the ocient jdbc driver provides a high speed bulk load path when you enable bulk loading, the driver bypasses the standard multi row insert operation and instead streams the batch data directly to the loader nodes in the cluster using ssh/sftp the data loads in the in memory https //jsonlines org/ format using a temporary, system generated ocient pipeline, providing a massive performance boost for large scale data ingestion configuration follow these steps to configure bulk loading in your jdbc driver ssh key access and node jdbc bulk loading does not use password authentication instead, you must use ssh access for all loader nodes in the cluster to point the driver to your private ssh key file, use the bulkloadsshkeypath jdbc connection property the client application must be able to read this file the driver automatically discovers available loader nodes by querying the docid\ tbjvq0xtd tcxq17hm nc and docid\ tbjvq0xtd tcxq17hm nc system catalog tables data type mapping the jdbc driver supports all standard scalar types for complex types, java sql struct ( java sql struct ) types load as ocient tuple types, and java array ( java sql array ) types load as ocient array types enable bulk loading the jdbc driver disables bulk loading by default to enable it, you must meet these conditions set the enablebulkload connection property to true use a parameterized insert statement that uses placeholders for values, e g , insert into customers (id, name, status) values (?, ?, ?) for details, see docid apnndn tjqmjdd5oqdvd the total number of rows in the batch group meets or exceeds the bulkloadthreshold property for recommended configuration settings for spark workloads, see docid\ pp91aew4 1hy1pft3f4zs set up parameterized insert statements these steps demonstrate how to use a single parameterized insert statement using the java preparedstatement class the parameterized statement binds different values for each row create the preparedstatement object ps with ? placeholders string sql = "insert into customers (id, name, status) values (?, ?, ?)"; preparedstatement ps = conn preparestatement(sql); bind parameter values by position ps setlong(1, 123l); // first ? ps setstring(2, "alice"); // second ? ps setstring(3, "active"); // third ? execute a single insert ps executeupdate(); or, add multiple rows as a batch // first row ps setlong(1, 123l); ps setstring(2, "alice"); ps setstring(3, "active"); ps addbatch(); // second row ps setlong(1, 124l); ps setstring(2, "bob"); ps setstring(3, "inactive"); ps addbatch(); // send them together int\[] results = ps executebatch(); close the resources ps close(); connection encryption (ssl/tls) the jdbc driver can use ssl/tls to connect to ocient, causing all traffic to be encrypted specify the tls property on the connect statement to enable tls support the tls property supports these values unverified traffic on the connection is encrypted, but no verification is done on the certificate received from the ocient system on traffic is encrypted, and the jdbc client must be able to verify the certificate received from the ocient system the tls on mode requires that the client knows the certificate authority that signed the certificate provided by the ocient system typically, this mode requires either that the certificate is signed by a well known certificate authority, or the certificate authority certificate has been imported into the truststore of the java system the java keytool utility is used to manipulate a java truststore docid\ ffbnx0kvadbdpj bfjsse discusses how you can configure user defined certificates for the ocient system sample java program using the ocient jdbc driver this sample program demonstrates how to utilize the ocient jdbc driver to establish a connection to a database, construct a prepared sql statement, execute the query, and iterate through the result set public class ocientjdbcexample { public static void main(final string args\[]) { class forname("com ocient jdbc jdbcdriver"); properties props = new properties(); props setproperty("user", "username"); props setproperty("password", "pwd"); props setproperty("force", "true"); string url = "jdbc\ ocient //192 168 121 82 4050/db"; connection conn = drivermanager getconnection(url, props); preparedstatement pstmt = conn preparestatement( "select l orderkey from tpch lineitem where l linenumber = ?"); pstmt setint(1, 4); resultset rs = pstmt executequery(); while(rs next()){ // do something with row } rs close(); pstmt close(); conn close(); return; } } for supported classes and methods, see docid\ vknnjxbrekwndt3kpt3ln related links docid 1 p8y vgpzkd8k 0hxqd7 docid 1 ls6prwuefq it66b4ee docid\ zyojxip3zkuvr9skpiyxo docid\ vknnjxbrekwndt3kpt3ln {{linux}} is the registered trademark of linus torvalds in the u s and other countries