Ocient HTTP Query API

the {{ocienthyperscaledatawarehouse}} has the http query api that enables you to execute sql statements this page uses command line examples using curl 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/ 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 to do this, you must assign a sql node as a participant to a connectivity pool the connectivity pool defines connection settings for nodes, including their open port and address for details, see docid\ xga0pas8wadtq33 a x7v enable the openapi port t o open a port, execute this docid\ xga0pas8wadtq33 a x7v 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 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 single or double quotes are valid 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\nsupported values are \n\narray — returns schema and data as arrays (more efficient for large data sets) (default)\ncollection — returns data as json objects (more convenient for client processing) \n\nexample format=collection\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 ", "" "sets the format for the requested data \n\nsupported values are \n\narray — returns schema and data as arrays (more efficient for large data sets) (default)\ncollection — returns data as json objects (more convenient for client processing) \n\nexample format=collection\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 " }, { "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 \n\nexample application/json\n\nfor queries expected to return large result sets, use the streaming response format accept application/stream+json", "" "specifies the request body format \n\nexample application/json\n\nfor queries expected to return large result sets, use the streaming response format accept application/stream+json" }, { "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\\"application/parquet\\"\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\\"application/parquet\\"\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 response format \n\nsupported values are \n\narray — returns the schema and data as arrays (more efficient for large data sets) (default)\ncollection — returns data as json objects (more convenient for client processing) ", "" "sets the response format \n\nsupported values are \n\narray — returns the schema and data as arrays (more efficient for large data sets) (default)\ncollection — 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 } { "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\narray — returns the schema and data as arrays (more efficient for large data sets) (default)\ncollection — returns data as json objects (more convenient for client processing) ", "" "sets the response format \n\nsupported values are \n\narray — returns the schema and data as arrays (more efficient for large data sets) (default)\ncollection — 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 \n\nexample application/json\n\nfor streaming and for queries with large result sets, use this header \naccept application/stream+json \n", "" "specifies the request body format \n\nexample application/json\n\nfor streaming and for queries with large result sets, use this header \naccept application/stream+json \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\\" \n\\"application/parquet\\"", "" "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\\"application/parquet\\"" }, { "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 } { "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 } { "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", "customlabel" "", "code" "{\n \\"access token\\" \\"\<token>\\",\n \\"username\\" \\"admin\\",\n \\"database\\" \\"system\\",\n \\"status\\" {\n \\"vendor code\\" 0,\n \\"sql state\\" \\"00000\\",\n \\"reason\\" \\"the operation completed successfully\\"\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" "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 } { "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 } { "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 } { "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 } { "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 } { "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 } { "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 } 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\ qcf0x9ao4a56x id39pkr