Ocient HTTP Query API

the {{ocienthyperscaledatawarehouse}} provides an http query api that enables the execution of sql statements this page uses command line examples using curl the http query api returns data in json format by default the default header value is accept application/json api documentation the api follows openapi specification ( {{swagger}} ) guidelines, which you can access in these formats from any sql node address {sql node address} yaml format /openapi yaml endpoint json format /openapi json endpoint yaml download example curl https //{sql node address}/openapi yaml json download example curl https //{sql node address}/openapi json you can use these specifications with various openapi tools for code generation, testing, and documentation connection setup before using the api, you must establish an open port for a sql node the connectivity pool defines connection settings for sql nodes, including their open port and address for details, see docid\ c4g2tdqxhblp1aavwmzgq all the examples assume that the openapi port is the standard ssl port 443 if your connectivity pool openapi port has a different port setting, you must specify it in the request url for example, this url specifies using port 8443 https //sql node address 8443/v1/execute/ enable the openapi port t o open a port, execute this docid\ c4g2tdqxhblp1aavwmzgq sql statement on a connectivity pool in your system change the connectivity pool and node values in the example according to your system alter connectivity pool benchmark sql connectivity pool alter participant "benchmark sql0" set openapi port 443; check port configuration verify your port configuration by executing this query select c name, n name, openapi port from sys connectivity pool participants as cp left join sys connectivity pools as c on cp id = c id left join sys nodes as n on cp node id = n id; the output displays all configured connectivity pools with their associated nodes and openapi ports ssl certificate setup the {{ocient}} http query api supports two options for handling ssl certificates secure connection using tls you can use your own certificates for tls secured connections by configuring certificate files in your ocient install for details, see docid\ ffbnx0kvadbdpj bfjsse unsecured connection for quick testing or in non production environments, you can bypass certificate validation by adding the k or insecure flag to your curl command example curl k u 'admin\@system\ admin' https //\<sql node address>/v1/execute/system \\ d '{"statement" "select from sys dummy2;","format" "collection"}' authentication methods the ocient http query api accepts user credentials in various formats designed for different use cases simple api requests for simple queries, you can include user credentials in the request in this example, the request includes credentials after the u flag curl u 'admin\@system\ admin' https //sql node/v1/execute/system \\ d '{"statement" "select from sys dummy2;", "format" "collection"}' bearer token authentication for continuous access, you can obtain an authentication token by making a request to the login endpoint replace the text in this example with the address of your sql node, your username, your password, and the name of your database curl x post https //sql node/v1/login \\ h "content type application/json" \\ d '{ "username" "admin\@system", "password" "admin", "database" "system" }' this request returns an authentication token that you can add to subsequent requests curl x post https //sql node/v1/execute \\ h "authorization bearer test access token" \\ h "content type application/json" \\ d '{ "statement" "select from sys dummy2;", "database" "system", "format" "collection" }' openid connect (sso) for sso authentication, you can use openid connect by targeting the sso authentication endpoint with a callback path replace the text in this example with the name of your instance, the name of your database, and your callback path curl x post https //ocient instance/v1/sso authentication \\ h "content type application/json" \\ d '{ "database" "system", "sso callback path" "/app callback" }' after the redirection and authentication are complete, you can include your token in subsequent requests curl x post https //ocient instance/v1/sso token \\ h "content type application/json" \\ d '{ "id token" "test id token", "database" "system" }' api endpoint documentation the ocient http query api supports these endpoints for api requests, json data must use double quotes ( " ) for all keys and string values single quotes should enclose the json payload, as shown in this example (see the payload section following the d flag) curl x post \\ https //my sql node com/v1/execute \\ h "content type application/json" \\ d '{ "query" "select from my table where status = \\"active\\"", "database" "my database" }' single or double quotes are acceptable for parameters not nested in json objects { "name" "execute", "method" "post", "url" "https //{sql node}/v1/execute/{database}", "description" "executes a sql statement and returns the results supports requests for both regular and streaming responses \n\nwith streaming, results return as they become available using the http chunked transfer encoding each chunk contains a valid json object that you can parse independently \n\nfor details on streaming, including large response configuration, see the header parameters \n\nyou can specify the database in the request body if you do not specify the database, the ocient system defaults to the database specified in the authentication token or system settings if you specify a database in the request, this value overrides any alternate database value specified as a body parameter \n", "tab" "examples", "examples" { "languages" \[ { "id" "ua8fusz1a2nyxnibpfv8y", "language" "curl", "code" "curl u 'admin\@system\ admin' https //my sql node com/v1/execute/system \\\\\n d '{\\"statement\\" \\"select from sys dummy2\\", \\"format\\" \\"collection\\"}'", "customlabel" "" } ], "selectedlanguageid" "ua8fusz1a2nyxnibpfv8y" }, "results" { "languages" \[ { "id" "vy7ewxrcggwmgrsfigt6f", "language" "200", "customlabel" "", "code" "{\n \\"query id\\" \\"ac4dd039 e385 400b a428 fd1fdcd02e30\\",\n \\"status\\" {\n \\"reason\\" \\"the operation completed successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n },\n \\"data\\" \[\n {\n \\"c1\\" 1\n },\n {\n \\"c1\\" 2\n }\n ]\n}" } ], "selectedlanguageid" "vy7ewxrcggwmgrsfigt6f" }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "the ip address or domain of a sql node assigned to a connectivity pool in your system " }, { "name" "{database}", "kind" "optional", "type" "string", "description" "the specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings \n\nif you include a database in the path, this value overrides any alternate database value specified as a body parameter ", "" "the specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings \n\nif you include a database in the path, this value overrides any alternate database value specified as a body parameter " } ], "queryparameters" \[ { "name" "format", "kind" "optional", "type" "string", "description" "sets the format for the requested data \n\nnote this parameter applies only when using content type text/plain when using content type application/json, specify the format in the request body \n\nsupported values are \n\n\\"array\\" — returns schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) \n\nexample format=collection\n\n", "" "sets the format for the requested data \n\nnote this parameter applies only when using content type text/plain when using content type application/json, specify the format in the request body \n\nsupported values are \n\n\\"array\\" — returns schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) \n\nexample format=collection\n\n" }, { "name" "schema", "kind" "optional", "type" "string", "description" "the schema to use for the sql statement \n\nexample schema=mydata\n\nnote this parameter applies only when using content type text/plain when using content type application/json, specify the schema in the request body ", "" "the schema to use for the sql statement \n\nexample schema=mydata\n\nnote this parameter applies only when using content type text/plain when using content type application/json, specify the schema in the request body " } ], "headerparameters" \[ { "name" "authorization", "kind" "required", "type" "string", "description" "an access token to connect to your ocient system \n\nto receive an access token, you must provide your username and password using the login endpoint \n\nthe system passes the credentials using the authorization http header using base64 encoded basic authentication ", "" "an access token to connect to your ocient system \n\nto receive an access token, you must provide your username and password using the login endpoint \n\nthe system passes the credentials using the authorization http header using base64 encoded basic authentication " }, { "name" "content type", "kind" "required", "type" "string", "description" "specifies the request body format options include \n\napplication/json\n\ndescription the standard option for submitting queries and receiving responses as a single json object or array use when submitting queries with relatively small or moderate result sets or when you want the entire result set in one response \n\napplication/stream+json\n\ndescription enables streaming of multiple json objects, allowing the api to send results as they become available use when querying large tables or expecting a large volume of results or when you want to process each row or result as soon as it is received, without waiting for the full response ", "" "specifies the request body format options include \n\napplication/json\n\ndescription the standard option for submitting queries and receiving responses as a single json object or array use when submitting queries with relatively small or moderate result sets or when you want the entire result set in one response \n\napplication/stream+json\n\ndescription enables streaming of multiple json objects, allowing the api to send results as they become available use when querying large tables or expecting a large volume of results or when you want to process each row or result as soon as it is received, without waiting for the full response " }, { "name" "accept encoding", "kind" "optional", "type" "string", "description" "supported compression algorithms for responses you can specify multiple algorithms as a comma separated list \n\nsupported values are \n\\"gzip\\" \n\\"deflate\\" \n\\"be\\" (brotli)\nor combinations like \\"gzip, be\\" \n\nnote for large result sets, compression can impact performance without compression, the result sets can be chunks of rows processed in a streaming fashion with compression, the entire result set must be compressed before it is returned to the client the client side must decompress the entire result set before processing any rows \n", "" "supported compression algorithms for responses you can specify multiple algorithms as a comma separated list \n\nsupported values are \n\\"gzip\\" \n\\"deflate\\" \n\\"be\\" (brotli)\nor combinations like \\"gzip, be\\" \n\nnote for large result sets, compression can impact performance without compression, the result sets can be chunks of rows processed in a streaming fashion with compression, the entire result set must be compressed before it is returned to the client the client side must decompress the entire result set before processing any rows \n" }, { "name" "accept", "kind" "optional", "type" "string", "description" "supported response media types regardless of the accept header, the api returns large query results using chunked encoding also, see the note about compression in the accept encoding parameter \n\nsupported values are \n\n\\"application/json\\" (default)\n\\"application/stream+json\\" \n", "" "supported response media types regardless of the accept header, the api returns large query results using chunked encoding also, see the note about compression in the accept encoding parameter \n\nsupported values are \n\n\\"application/json\\" (default)\n\\"application/stream+json\\" \n" }, { "name" "preferred encoding", "kind" "optional", "type" "string", "description" "indicates the preferred encoding type from those specified in the accept encoding parameter this header provides fine grained control when the accept encoding parameter contains multiple values, allowing the client to specify which encoding is preferred for this specific request explicitly \n\nsupported values are \n\n\\"gzip\\" \n\\"br\\"", "" "indicates the preferred encoding type from those specified in the accept encoding parameter this header provides fine grained control when the accept encoding parameter contains multiple values, allowing the client to specify which encoding is preferred for this specific request explicitly \n\nsupported values are \n\n\\"gzip\\" \n\\"br\\"" }, { "name" "preferred compression level", "kind" "optional", "type" "integer", "description" "specifies the chosen compression level for the selected encoding algorithm values range from 1 (fastest, least compression) to 9 (slowest, maximum compression) the exact behavior depends on the specific compression algorithm ", "" "specifies the chosen compression level for the selected encoding algorithm values range from 1 (fastest, least compression) to 9 (slowest, maximum compression) the exact behavior depends on the specific compression algorithm " } ], "bodydataparameters" \[ { "name" "statement", "kind" "required", "type" "string", "description" "a valid sql statement to execute \n\nexample statement=select from customers limit 10;\n\nyou can include parameters to substitute in the sql statement by including them in the params body parameter ", "" "a valid sql statement to execute \n\nexample statement=select from customers limit 10;\n\nyou can include parameters to substitute in the sql statement by including them in the params body parameter " }, { "name" "database", "kind" "optional", "type" "string", "description" "a specific database in your system for the api call \n\nif you do not specify this parameter, the executed query defaults to the database from the authentication token or system settings ", "" "a specific database in your system for the api call \n\nif you do not specify this parameter, the executed query defaults to the database from the authentication token or system settings " }, { "name" "format", "kind" "optional", "type" "string", "description" "sets the output format of the query result data \n\nsupported values are \n\n\\"array\\" — returns the schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) ", "" "sets the output format of the query result data \n\nsupported values are \n\n\\"array\\" — returns the schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) " }, { "name" "params", "kind" "optional", "type" "object", "description" "parameters to substitute in the sql statement for values enclosed in braces the keys should match the parameter names in the statement (without the enclosing braces) wrap string values in single quotes all other data types do not use quotes \n\nexample if your sql statement is select from orders where order date > {start date}, then your api request should include values in the params body parameter, such as {'start date' '2023 01 01'} ", "" "parameters to substitute in the sql statement for values enclosed in braces the keys should match the parameter names in the statement (without the enclosing braces) wrap string values in single quotes all other data types do not use quotes \n\nexample if your sql statement is select from orders where order date > {start date}, then your api request should include values in the params body parameter, such as {'start date' '2023 01 01'} " }, { "name" "fetch size", "kind" "optional", "type" "integer", "description" "the number of rows to return in each chunk for streaming responses this value must be 0 or greater a value of 0 (default) means the database determines how many rows to return \n\nuse smaller values for interactive applications (100 1000 rows) for batch processing, use larger values (5000 10000 rows) ", "" "the number of rows to return in each chunk for streaming responses this value must be 0 or greater a value of 0 (default) means the database determines how many rows to return \n\nuse smaller values for interactive applications (100 1000 rows) for batch processing, use larger values (5000 10000 rows) " }, { "name" "max rows", "kind" "optional", "type" "integer", "description" "the maximum number of rows to return the default value is 9223372036854776000 \n\nnote adding a limit clause to a sql query is generally a better method to restrict the number of rows, as the query engine still generates the total number of rows, but only returns this value to the client ", "" "the maximum number of rows to return the default value is 9223372036854776000 \n\nnote adding a limit clause to a sql query is generally a better method to restrict the number of rows, as the query engine still generates the total number of rows, but only returns this value to the client " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "body parameter", "value" "bodydataparameters" }, "hastryitout" false, "autogeneratedanchorslug" "execute", "legacyhash" "whskxr4peod ycco3j8ed" } { "name" "execute", "method" "get", "url" "https //{sql node}/v1/execute/{database}", "description" "alternative get method for executing sql statements this method passes parameters as url query parameters this method does not support the params body parameter \n\nspecify which database to access in the query parameters if you do not specify a database, the connection defaults to the database from the authentication token or system settings \n\nthis method is most suitable for simple, read only queries where the statement can be safely included in a url for complex queries or those with parameters, use the post method instead \n", "tab" "examples", "examples" { "languages" \[ { "id" "ua8fusz1a2nyxnibpfv8y", "language" "curl", "code" "curl u 'admin\@system\ admin' \\\\\n 'https //my sql node com/v1/execute?statement=select%20 %20from%20sys dummy2\&database=system\&format=collection'", "customlabel" "" } ], "selectedlanguageid" "ua8fusz1a2nyxnibpfv8y" }, "results" { "languages" \[ { "id" "vy7ewxrcggwmgrsfigt6f", "language" "200", "customlabel" "", "code" "{\n \\"query id\\" \\"2d5af900 cb02 44f4 b974 6264d2133e96\\",\n \\"status\\" {\n \\"reason\\" \\"the operation completed successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n },\n \\"data\\" \[\n {\n \\"c1\\" 1\n },\n {\n \\"c1\\" 2\n }\n ]\n}" } ], "selectedlanguageid" "vy7ewxrcggwmgrsfigt6f" }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node in your system ", "" "the ip address or domain of a sql node in your system " }, { "name" "{database}", "kind" "optional", "type" "string", "description" "the specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings \n\nif you include a database in the path, this value overrides any alternate database value specified as a body parameter ", "" "the specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings \n\nif you include a database in the path, this value overrides any alternate database value specified as a body parameter " } ], "queryparameters" \[ { "name" "statement", "kind" "required", "type" "string", "description" "a valid sql statement to execute the statement must be url encoded, e g , statement=select%20 %20from%20customers%20limit%2010%3b\n\n", "" "a valid sql statement to execute the statement must be url encoded, e g , statement=select%20 %20from%20customers%20limit%2010%3b\n\n" }, { "name" "database", "kind" "optional", "type" "string", "description" "a specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings ", "" "a specific database in your system for the api call \n\nif you do not specify the database, the executed query defaults to the database from the authentication token or system settings " }, { "name" "format", "kind" "optional", "type" "string", "description" "sets the response format \n\nsupported values are \n\n\\"array\\" — returns the schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) ", "" "sets the response format \n\nsupported values are \n\n\\"array\\" — returns the schema and data as arrays (more efficient for large data sets) (default)\n\\"collection\\" — returns data as json objects (more convenient for client processing) " }, { "name" "fetch size", "kind" "optional", "type" "string", "description" "the number of rows to return in each chunk for streaming responses this value must be 0 or greater a value of 0 (default) means the database determines how many rows to return \n\nuse smaller values for interactive applications (100 1000 rows) for batch processing, use larger values (5000 10000 rows) ", "" "the number of rows to return in each chunk for streaming responses this value must be 0 or greater a value of 0 (default) means the database determines how many rows to return \n\nuse smaller values for interactive applications (100 1000 rows) for batch processing, use larger values (5000 10000 rows) " } ], "headerparameters" \[ { "name" "authorization", "kind" "required", "type" "string", "description" "an access token to connect to your ocient system \n\nto receive an access token, you must provide your username and password using the login endpoint \n\nthe system passes the credentials using the authorization http header using base64 encoded basic authentication ", "" "an access token to connect to your ocient system \n\nto receive an access token, you must provide your username and password using the login endpoint \n\nthe system passes the credentials using the authorization http header using base64 encoded basic authentication " }, { "name" "content type", "kind" "required", "type" "string", "description" "specifies the request body format options include \n\napplication/json\n\ndescription the standard option for submitting queries and receiving responses as a single json object or array use when submitting queries with relatively small or moderate result sets or when you want the entire result set in one response \n\napplication/stream+json\n\ndescription enables streaming of multiple json objects, allowing the api to send results as they become available use when querying large tables or expecting a large volume of results or when you want to process each row or result as soon as it is received, without waiting for the full response \n", "" "specifies the request body format options include \n\napplication/json\n\ndescription the standard option for submitting queries and receiving responses as a single json object or array use when submitting queries with relatively small or moderate result sets or when you want the entire result set in one response \n\napplication/stream+json\n\ndescription enables streaming of multiple json objects, allowing the api to send results as they become available use when querying large tables or expecting a large volume of results or when you want to process each row or result as soon as it is received, without waiting for the full response \n" }, { "name" "accept encoding", "kind" "optional", "type" "string", "description" "supported compression algorithms for responses you can specify multiple algorithms as a comma separated list \n\nsupported values are \n'gzip' \n'deflate' \n'br' (brotli)\nor combinations like 'gzip, br' \n\nnote for large result sets, compression can impact performance without compression, the result sets can be chunks of rows processed in a streaming fashion with compression, the entire result set must be compressed before it is returned to the client the client side must decompress the entire result set before processing any rows ", "" "supported compression algorithms for responses you can specify multiple algorithms as a comma separated list \n\nsupported values are \n'gzip' \n'deflate' \n'br' (brotli)\nor combinations like 'gzip, br' \n\nnote for large result sets, compression can impact performance without compression, the result sets can be chunks of rows processed in a streaming fashion with compression, the entire result set must be compressed before it is returned to the client the client side must decompress the entire result set before processing any rows " }, { "name" "accept", "kind" "optional", "type" "string", "description" "supported response media types regardless of the accept header, the api returns large query results using chunked encoding also, see the note about compression in the accept encoding parameter \n\nsupported values are \n\n\\"application/json\\" (default)\n\\"application/stream+json\\"", "" "supported response media types regardless of the accept header, the api returns large query results using chunked encoding also, see the note about compression in the accept encoding parameter \n\nsupported values are \n\n\\"application/json\\" (default)\n\\"application/stream+json\\"" }, { "name" "preferred encoding", "kind" "optional", "type" "string", "description" "indicates the preferred encoding type from those specified in the accept encoding parameter this header provides fine grained control when the accept encoding parameter contains multiple values, allowing the client to specify which encoding is preferred for this specific request explicitly \n\nsupported values are \n\n\\"gzip\\" \n\\"br\\"", "" "indicates the preferred encoding type from those specified in the accept encoding parameter this header provides fine grained control when the accept encoding parameter contains multiple values, allowing the client to specify which encoding is preferred for this specific request explicitly \n\nsupported values are \n\n\\"gzip\\" \n\\"br\\"" }, { "name" "preferred compression level", "kind" "optional", "type" "string", "description" "specifies the chosen compression level for the selected encoding algorithm values typically range from 1 (fastest, least compression) to 9 (slowest, maximum compression) the exact behavior depends on the specific compression algorithm ", "" "specifies the chosen compression level for the selected encoding algorithm values typically range from 1 (fastest, least compression) to 9 (slowest, maximum compression) the exact behavior depends on the specific compression algorithm " } ], "bodydataparameters" \[], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "execute 1", "legacyhash" "p5lahitjm9jfo8uevvsyb" } { "name" "info", "method" "get", "url" "https //{sql node}/v1/info", "description" "returns basic system version information about the ocient system and the http query api server \n\nyou can use this endpoint to verify connectivity and to check compatible versions ", "tab" "examples", "examples" { "languages" \[ { "id" "t6 ikfu1dfpyll ksr6yw", "language" "curl", "code" "curl u 'admin\@system\ admin' \\\\\n 'https //my sql node com/v1/info' ", "customlabel" "" } ], "selectedlanguageid" "t6 ikfu1dfpyll ksr6yw" }, "results" { "languages" \[ { "id" "drxbetb7ys61sm0q52phj", "language" "200", "customlabel" "", "code" "{\n \\"default database\\" \\"system\\",\n \\"openapi version\\" \\"1 1 0\\",\n \\"status\\" {\n \\"vendor code\\" 0,\n \\"sql state\\" \\"00000\\",\n \\"reason\\" \\"the operation completed successfully\\"\n }\n} " } ], "selectedlanguageid" "drxbetb7ys61sm0q52phj" }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "optional", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "{sql node}" } ], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "info", "legacyhash" "tgrdksaizbwrpzcutscvm" } { "name" "login", "method" "post", "url" "https //{sql node}/v1/login", "description" "authenticates a user with a username and password and then returns a token for use in subsequent api calls this request also sets a session cookie \n\ninclude the returned access token in the authorization header for subsequent requests in the format \nauthorization bearer {token} ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/login \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"username\\" \\"admin\\",\n \\"password\\" \\"admin\\",\n \\"database\\" \\"system\\"\n }'\n", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "code" "{\n \\"access token\\" \\"eyjhbgcioijiuzi1niisinr5cci6ikpxvcj9 \\",\n \\"username\\" \\"jdoe\\",\n \\"database\\" \\"retail analytics\\",\n \\"expires in\\" 3600,\n \\"status\\" {\n \\"reason\\" \\"authentication successful\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}", "customlabel" "" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "required" } ], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "username", "kind" "required", "type" "string", "description" "username ", "" "username " }, { "name" "password", "kind" "required", "type" "string", "description" "the password for the username ", "" "the password for the username " }, { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system ", "" "target database name if you do not specify this parameter, the request defaults this value to system " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "body parameter", "value" "bodydataparameters" }, "hastryitout" false, "autogeneratedanchorslug" "login", "legacyhash" "80vcfr7bfibxnr0fg8 pa" } { "name" "logout", "method" "post", "url" "https //{sql node}/v1/logout", "description" "log out from a sql session this clears any associated cookies, but does not invalidate any access tokens \n\nthis endpoint terminates only the cookie based session any bearer tokens that were previously issued continue to work until they expire \n \nyou must discard any stored bearer tokens to complete the log out in a client application ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/logout\n d '{\n \\"database\\" \\"retail analytics\\"\n }", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"status\\" {\n \\"reason\\" \\"logged out successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "optional", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "the ip address or domain of a sql node assigned to a connectivity pool in your system " } ], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system ", "" "target database name if you do not specify this parameter, the request defaults this value to system " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "logout", "legacyhash" "dbrikzperh u5t gjs8gi" } { "name" "sso authentication", "method" "post", "url" "https //{sql node}/v1/sso authentication", "description" "initiates the openid connect authentication process by redirecting to the authorization server \n\nthis endpoint begins the standard openid connect authentication process \n\n1 the client calls this endpoint with a callback path \n2 the server responds with a redirect to the identity provider \n3 the user authenticates with the identity provider \n4 the identity provider redirects back to the callback endpoint \n5 the client can exchange the authorization code for an access token ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/sso authentication \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"database\\" \\"retail analytics\\",\n \\"sso callback path\\" \\"/auth/callback\\",\n \\"do redirect\\" true\n }'", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"status\\" {\n \\"reason\\" \\"query executed successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}" }, { "id" "itgmqznjceqck b6dxtni", "language" "404", "customlabel" "", "code" "{\n \\"message\\" \\"ain't no cake like that \\"\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system ", "" "target database name if you do not specify this parameter, the request defaults this value to system " }, { "name" "sso callback path\t", "kind" "optional", "type" "string", "description" "local path for redirection after successful authentication this path should be registered with your openid provider as a valid redirect uri ", "" "local path for redirection after successful authentication this path should be registered with your openid provider as a valid redirect uri " }, { "name" "do redirect", "kind" "optional", "type" "boolean", "description" "determines whether to issue the redirect to the authentication server immediately \n\nif you set this value to true (default), the api responds with a 302 redirect to the openid provider \n\nif you set this value to false, the api returns the url in the response body, allowing the client to handle the redirect manually \n\nif you have no sso configuration for the database, an error occurs if this parameter value is set to true otherwise, the request returns an empty redirect ", "" "determines whether to issue the redirect to the authentication server immediately \n\nif you set this value to true (default), the api responds with a 302 redirect to the openid provider \n\nif you set this value to false, the api returns the url in the response body, allowing the client to handle the redirect manually \n\nif you have no sso configuration for the database, an error occurs if this parameter value is set to true otherwise, the request returns an empty redirect " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "body parameter", "value" "bodydataparameters" }, "hastryitout" false, "autogeneratedanchorslug" "ssoauthentication", "legacyhash" "fsswpru5m55nlywuo2cvu" } { "name" "callback", "method" "get", "url" "https //{sql node}/v1/callback", "description" "provides the authentication token for the openid connect authentication process the authorization server redirects to callback path after successful authentication \n\nthis endpoint receives the authorization code from the openid provider and exchanges it for a token the provider redirects the user to the application callback url specified in the initial authentication request \n\nthe client application should not call this endpoint directly the openid provider automatically calls this endpoint in the authentication process ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x get \\"https //my sql node com/v1/callback?code=4/p7q7w91a omscelviaqm6btrgtp7\&state=frjfv29f3v39jf39djf93jf\\"\n", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"status\\" {\n \\"reason\\" \\"query executed successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "required" } ], "queryparameters" \[ { "name" "code", "kind" "required", "type" "string", "description" "the authorization code from the openid provider this endpoint exchanges the code for a token \n\nexample code=4/p7q7w91a omscelviaqm6btrgtp7", "" "the authorization code from the openid provider this endpoint exchanges the code for a token \n\nexample code=4/p7q7w91a omscelviaqm6btrgtp7" }, { "name" "state", "kind" "required", "type" "string", "description" "prevents cross site request forgery attacks this value should match the state in the initial authentication request \n\nexample state=frjfv29f3v39jf39djf93jf", "" "prevents cross site request forgery attacks this value should match the state in the initial authentication request \n\nexample state=frjfv29f3v39jf39djf93jf" } ], "headerparameters" \[], "bodydataparameters" \[], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "callback", "legacyhash" "3eqehf67nftvep6pbyv n" } { "name" "sso token", "method" "post", "url" "https //{sql node}/v1/sso token", "description" "exchange an openid connect identifier token or access token for an ocient access token \n\nthis endpoint allows clients to directly exchange tokens without following the full browser based authentication process this exchange is useful for server to server scenarios or when the client already has a valid openid token from another process ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/sso token \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"id token\\" \\"eyjhbgcioijiuzi1niisinr5cci6ikpxvcj9 \\",\n \\"database\\" \\"retail analytics\\"\n }'", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"id token\\" \\"eyjhbgcioijsuzi1niisinr5cci6ikpxvcj9 \\",\n \\"database\\" \\"retail analytics\\"\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "required" } ], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "database", "kind" "required", "type" "string", "description" "target database for connection after successful authentication this database must be accessible to the user identified by the sso token ", "" "target database for connection after successful authentication this database must be accessible to the user identified by the sso token " }, { "name" "id token", "kind" "optional", "type" "string", "description" "openid identifier token received from the openid provider after successful authentication you must provide the id token or access token body parameters, but not both ", "" "openid identifier token received from the openid provider after successful authentication you must provide the id token or access token body parameters, but not both " }, { "name" "access token\t", "kind" "optional", "type" "string", "description" "openid access token received from the openid provider after successful authentication you must provide the id token or access token body parameters, but not both ", "" "openid access token received from the openid provider after successful authentication you must provide the id token or access token body parameters, but not both " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "ssotoken", "legacyhash" "fwspsjon6lvzyojo9efur" } { "name" "sso device grant", "method" "post", "url" "https //{sql node}/v1/sso device grant", "description" "retrieve an openid device grant code that the ocient system can verify and use with the sso device grant verify endpoint \n\nthe device grant process is intended for devices with limited input capabilities or no web browser \n\n1 call this endpoint to retrieve a user code and verification uri \n2 display the user code and verification uri to the user \n3 the user visits the verification uri on another device and enters the code \n4 call the sso device grant verify endpoint to check if the user has completed verification ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/sso device grant \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"database\\" \\"retail analytics\\"\n }'", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"verification uri complete\\" \\"https //auth example com/device?code=bcdfghjk\\",\n \\"verification uri\\" \\"https //auth example com/device\\",\n \\"user code\\" \\"bcdfghjk\\",\n \\"status\\" {\n \\"reason\\" \\"device authorization initiated\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system this value represents the database where the user connects after completing the device grant process ", "" "target database name if you do not specify this parameter, the request defaults this value to system this value represents the database where the user connects after completing the device grant process " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "body parameter", "value" "bodydataparameters" }, "hastryitout" false, "autogeneratedanchorslug" "ssodevicegrant", "legacyhash" "ny6llenx4d7gkzmvhxil9" } { "name" "sso device grant verify", "method" "post", "url" "https //{sql node}/v1/sso device grant verify", "description" "verify a previous device grant request and return an authorization token \n\nafter initiating a device grant process with the sso device grant endpoint, use this endpoint to check if the user has completed the verification process if the verification is successful, the endpoint returns an authorization token that you can use for subsequent api calls \n\nyou can call this endpoint multiple times until one of these outcomes \n\nthe user completes the verification (returns 200 ok with a token) \n\nthe timeout is reached (returns error) \n \nthe verification is canceled (returns error) ", "tab" "examples", "examples" { "languages" \[ { "id" "x 23lp ew2bsqk k57sep", "language" "curl", "code" "curl x post https //my sql node com/v1/sso device grant verify \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"user code\\" \\"bcdfghjk\\",\n \\"timeout\\" 60,\n \\"database\\" \\"retail analytics\\"\n }'", "customlabel" "" } ], "selectedlanguageid" "x 23lp ew2bsqk k57sep" }, "results" { "languages" \[ { "id" "cvhrp0nklrcbgf4lmr6k ", "language" "200", "customlabel" "", "code" "{\n \\"access token\\" \\"eyjhbgcioijiuzi1niisinr5cci6ikpxvcj9 eyjzdwiioijqzg9liiwizgiioijyzxrhawxfyw5hbhl0awnziiwizxhwijoxnjm1ndyxnzeyfq sflkxwrjsmekkf2qt4fwpmejf36pok6yjv adqssw5c\\",\n \\"username\\" \\"jdoe\\",\n \\"database\\" \\"retail analytics\\",\n \\"status\\" {\n \\"reason\\" \\"authentication successful\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}" } ], "selectedlanguageid" "cvhrp0nklrcbgf4lmr6k " }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "required" } ], "queryparameters" \[], "headerparameters" \[], "bodydataparameters" \[ { "name" "user code", "kind" "required", "type" "string", "description" "the user code returned from a previous device grant request using the sso device grant endpoint ", "" "the user code returned from a previous device grant request using the sso device grant endpoint " }, { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system ", "" "target database name if you do not specify this parameter, the request defaults this value to system " }, { "name" "timeout", "kind" "optional", "type" "string", "description" "the verification timeout, in seconds, indicates how long the server waits for the user to complete the device authorization process before returning a timeout error \n\nthe default value is 30 seconds \n\nthe valid range is 1 600 seconds (10 minutes) ", "" "the verification timeout, in seconds, indicates how long the server waits for the user to complete the device authorization process before returning a timeout error \n\nthe default value is 30 seconds \n\nthe valid range is 1 600 seconds (10 minutes) " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "path parameter", "value" "pathparameters" }, "hastryitout" false, "autogeneratedanchorslug" "ssodevicegrantverify", "legacyhash" "uajsdpxioc9zhugvfaesv" } { "name" "token refresh", "method" "post", "url" "https //{sql node}/v1/token refresh", "description" "refreshes an existing access token, extending its validity period call this endpoint before the current token expires to maintain uninterrupted access \n\nuse the expires in value from the login or previous refresh response to determine when to refresh the token a common practice is to refresh when the token has half of its time remaining \n\non success, the endpoint returns the 200 response and a json object containing a new access token and metadata about the refreshed session replace the access token in your client with the new token returned by the endpoint, and use the expires in value to decide when to refresh again \n\n", "tab" "examples", "examples" { "languages" \[ { "id" "xlvujwggwmmr6uotvlruc", "language" "curl", "code" "curl x post https //your ocient instance/v1/token refresh \\\\\n h \\"authorization bearer your current token\\" \\\\\n h \\"content type application/json\\" \\\\\n d '{\n \\"database\\" \\"retail analytics\\"\n }'", "customlabel" "" } ], "selectedlanguageid" "xlvujwggwmmr6uotvlruc" }, "results" { "languages" \[ { "id" "5gd5w 8a boevwzll3f9d", "language" "200", "code" "{\n \\"access token\\" \\"eyjhb \\",\n \\"username\\" \\"jdoe\\",\n \\"database\\" \\"retail analytics\\",\n \\"expires in\\" 3600,\n \\"status\\" {\n \\"reason\\" \\"token refreshed successfully\\",\n \\"sql state\\" \\"00000\\",\n \\"vendor code\\" 0\n }\n}", "customlabel" "" } ], "selectedlanguageid" "5gd5w 8a boevwzll3f9d" }, "request" { "pathparameters" \[ { "name" "{sql node}", "kind" "required", "type" "string", "description" "the ip address or domain of a sql node assigned to a connectivity pool in your system ", "" "required" } ], "queryparameters" \[], "headerparameters" \[ { "name" "authorization", "kind" "required", "type" "string", "description" "the bearer token, which must be a valid access token still within its refresh lifetime configured on the server to obtain a token, call either the /v1/login or /v1/token refresh endpoints ", "" "the bearer token, which must be a valid access token still within its refresh lifetime configured on the server to obtain a token, call either the /v1/login or /v1/token refresh endpoints " }, { "name" "content type", "kind" "required", "type" "string", "description" "specifies the request body format use the value application/json ", "" "specifies the request body format use the value application/json " } ], "bodydataparameters" \[ { "name" "database", "kind" "optional", "type" "string", "description" "target database name if you do not specify this parameter, the request defaults this value to system ", "" "target database name if you do not specify this parameter, the request defaults this value to system " } ], "formdataparameters" \[] }, "currentnewparameter" { "label" "header parameter", "value" "headerparameters" }, "hastryitout" false, "autogeneratedanchorslug" "tokenrefresh", "legacyhash" "eq1ge mtltmd4l1sqpyzd" } http api query examples these examples demonstrate how you can sequence api endpoints for common use cases simple queries this example query format is appropriate for simple read only queries the example provides user credentials admin\@system\ admin in the request the https //sql node/v1/execute/system api call specifies the execute endpoint with the sql node address sql node and the system database in this example, the sql statement is select from sys dummy2; the collection format option returns the result in json format curl u 'admin\@system\ admin' https //sql node/v1/execute/system \\ d '{"statement" "select from sys dummy2;", "format" "collection"}' large result set queries specify the content type as json data with the content type header option when working with large amounts of data, enable streaming using the application/stream+json value with the accept header option the request also compresses the response using gzip compression with the accept encoding header option in this example, the sql statement is select from large table; the example specifies the fetch size value to set the number of rows each chunk of streaming data contains to 1000 curl u 'admin\@system\ admin' https //sql node/v1/execute/system \\ h "content type application/json" \\ h "accept application/stream+json" \\ h "accept encoding gzip" \\ d '{ "statement" "select from large table;", "fetch size" 1000 }' token authentication script this example uses a bash script to retrieve a token for the subsequent curl query use the login endpoint to retrieve the token specify the username, password, and database name then, use the execute endpoint to execute the select from large table; sql statement specify the token with the authorization header option the gunzip command decompresses the gzip compressed api response so it is readable in json format \# retrieve the token token=$(curl s x post https //sql node/v1/login \\ h "content type application/json" \\ d '{ "username" "admin\@system", "password" "admin", "database" "system" }' | jq r ' access token') \# then use the token for a query with streaming and compression curl x post https //sql node/v1/execute \\ h "authorization bearer $token" \\ h "content type application/json" \\ h "accept application/stream+json" \\ h "accept encoding gzip" \\ d '{ "statement" "select from large table;", "database" "system", "fetch size" 1000 }' | gunzip save results to a file this example saves the query results in a tables json file the example uses the select from sys tables; sql statement curl u 'admin\@system\ admin' https //sql node/v1/execute/system \\ h "content type application/json" \\ d '{ "statement" "select from sys tables;", "format" "collection" }' | jq ' data' > tables json use a wrapper script this example specifies the connection parameters in the ocient query sh file to run the file at the command line, you must also include an argument for executing the sql statement (for example /ocient query sh "select from my table;" \#!/bin/bash \# ocient query sh host="sql node com" user="admin" password="admin" database="system" query="$1" curl s u "${user}@${database} ${password}" "https //${host}/v1/execute/${database}" \\ h "content type application/json" \\ d "{ \\"statement\\" \\"${query}\\", \\"format\\" \\"collection\\" }" | jq ' data' related links https //swagger io/specification/ docid\ nw9vavkey2v75moxm muo