Re: REST API Primer

ebarre1 · ‎10-22-2018

What is the Paxata REST API?
Taken straight from our REST API documentation:

To provide programmatic access to the Paxata application, the Paxata application publishes a RESTful web service called the Paxata REST API. The Paxata REST API requires user-level authentication with a valid user account on the server. The Paxata REST API enforces permissions in the same way as the web application user interface. Additionally, for programmatic access the user account must have the AccessRestApi permission. By default, the RemoteAccess and SuperUser roles contain the AccessRestApi permission.

So basically, you can access all the functionalities of Paxata through code using Python, Java or any other modern language.
All access and requests are restricted and submitted to the same auditable and governable processes that our web interface goes through.

Hmm, ok but I don't need it to use Paxata, right?
Correct, think of the REST API as being able to customize and integrate Paxata within your current workflow. However, you might be asked to provide a REST API token in order to authenticate, this token can only be generated by you and can only be seen once but can be re-generated and deleted at will, adding an extra layer of security.

What type of operations can be performed using the REST API?

As mentioned earlier, the REST API provides access to Paxata programmatically, it does so by exposing URLs (called endpoints) to different types of actions, defined by different verbs. GET(), retrieves a list of something, POST(), creates one of those something, PUT(), updates it, DELETE() removes it.

An endpoint can support any number of those verbs, and responses sent from Paxata will also change based on the information provided to the endpoint. For example, requesting a GET() on the library endpoint will return a list of all the library items that you have access to, while doing the same GET() and passing a library ID will return information about this specific dataset/answerset.

What is an example of integration?

A lot of the organizations we work with utilize some type of enterprise scheduler, be it a CRON job, an Autosys or Control-M scripts. Paxata can be called as part of these tool processes and perform actions based on the scripts workflow, I.E. Import my sales data into Paxata to prep it for my projection model as soon as the previous process completed successfully, or, execute the data quality metrics project for the BI Dashboard.

But since the REST API is accessible through code, Paxata can also be integrated with any in-house development efforts.

What is an example of a utility?

The early utilities were built as tools to facilitate our work, such as documenting projects as artifacts for the customer,
other utilities have come from requests from our customers to have a tighter integration with their process.

Where can I get more information on the REST API?
Our documentation site has all that you need to interact with our REST API, if you have questions on how to get started then contact our Customer Services team.

abhijeetmkhambe · ‎10-07-2019

Hi ebarre,

I want to know if it possible to create a dataset in Paxata using Java REST API? If answer is 'Yes' then is there any sample code for the same which can be used as starting point?

Basically our requirement is as below:-
1) We will save few SQL queries in oracle sql table.
2) Using a spring batch job code we will fetch the queries.
3) Send the queries to Paxata through REST API and load the results in paxata.
4) An alternative to step 3, we will want to execute the queires, get the output and send the output to paxata.

Each query will produce millions of rows which we want to display in paxata.

We will be highly thankful if you can guide us on this.

Regards,
Abhijeet

bstephens · ‎10-07-2019

@abhijeetmkhambe

I posted example Java code to perform an import on your other thread for step 3. The example can be updated to leverage the SQL Query form of the URL from my previous Curl examples: https://community.datarobot.com/t5/admin-corner/settings-for-defining-a-connector/m-p/6522

Also, after your first import using a query you might want to switch from HTTP POST (create a new dataset) to HTTP PUT (specifying the dataFileId of the initial import) to add a new version to an existing dataset. This will allow your users to see 1 named dataset with many versions, typically the most recent version.

curl -f -sS -X PUT -u username:password  "http://localhost:8080/rest/datasource/imports/<dfatsourceId>/<datafileId>?query=<URLEncodedQuery>&name=<UserProvidedName>"

An alternative....
If the queries are static, I wouldn't write any code. Instead, I would use Paxata's Automation features to import the latest datasets when they are needed:

1. Create a Paxata Datasource for my Oracle database
2. For each query, import an initial version 1 dataset using your query
3. Depending on your Tenant security, you might need to share the datasets with other users.
4. You/your analysts create Paxata Data Prep Projects based on the datasets in Step 2, transform the data and export AnswerSets
5. Configure Project Automation to automatically import fresh versions of the Oracle datasets when the Project is executed and schedule it according to your needs.

Thanks,
Bill

abhijeetmkhambe · ‎10-09-2019

Hi Bill (bstephens),
Thanks for your inputs. We were struggling with this requirement and your help is making us move ahead. I am giving a try with the sample code you shared. Our queries are dynamic so the alternate solution you suggested may not help us.

Regards,
Abhijeet

abhijeetmkhambe · ‎10-09-2019

Hi Bill,
I tried with the sample code you provided. However, I keep on getting below error.

java.net.URISyntaxException: Illegal character in query at index 111:

the curl command syntax which I was able to execute successfully to create a dataset was as below-
curl --insecure -ksS -f -u ":tokenId" -X POST "https://paxataurl:portnumber/rest/datasource/imports/datasourceId?pretty=true" -F "query=select * from empschema.employee_table"

I wrote java code as below

String dataSourceId="data_source_id";

String query = "select * from empschema.employee_table";

String url = "https://paxataurl:portnumber/rest/datasource/imports/" + dataSourceId + "?pretty=true" + " -F" + " query=" + query;

System.out.println("Url formed ="+url);

HttpClient client = HttpClientBuilder.create().build();

HttpPost postRequest = new HttpPost(url);

String auth = ":12345678";

byte[] encodedAuth = Base64.getEncoder().encode(auth.getBytes(Charset.forName("UTF-8")));

String authHeader = "Basic " + new String(encodedAuth);

postRequest.addHeader(HttpHeaders.AUTHORIZATION, authHeader);

HttpResponse response = client.execute(postRequest);

HttpEntity entity = new BufferedHttpEntity(response.getEntity());

return entity.getContent();

When I execute above code, I get error as -> java.net.URISyntaxException: Illegal character in query at index 111:

Url formed is -> https://paxataurl:portnumber/rest/datasource/imports/dataSourceId?pretty=true -F query=select * from empschema.employee_table

Can you please guide me what is problem here? Does my url needs to match exactly same as curl command url.

Also have a query why we are not able to execute the code via curl command after removing -F

sayyar · ‎10-09-2019

Hi Abhijeet!
Below is a good way of doing HttpPost via Java.

List<NameValuePair> params = new ArrayList<>();

params.add(new BasicNameValuePair("query", query));
HttpPost request = new HttpPost(" https://paxataurl:portnumber/rest/datasource/imports/dataSourceId");

request.setEntity(new UrlEncodedFormEntity(params));
return executeRequest(request, userName, password);

abhijeetmkhambe · ‎10-10-2019

Hi Sayyar,
Thanks for your input. I will do the change as per your suggestion.

Hi @bstephens (Bill);
I keep on getting error as below.

org.apache.http.client.ClientProtocolException: null

at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:186) ~[httpclient-4.3.6.jar:4.3.6]

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:82) ~[httpclient-4.3.6.jar:4.3.6]

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:106) ~[httpclient-4.3.6.jar:4.3.6]

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:57) ~[httpclient-4.3.6.jar:4.3.6]

at com.citi.controller.Test.DatasourceImport(Test.java:81) ~[classes/:na]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]

at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:189) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:660) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882) ~[spring-webmvc-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.1.6.RELEASE.jar:5.1.6.RELEASE]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200) ~[tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415) [tomcat-embed-core-9.0.17.jar:9.0.17]

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.17.jar:9.0.17]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_131]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_131]

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.17.jar:9.0.17]

at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]

Caused by: org.apache.http.ProtocolException: Target host is not specified

at org.apache.http.impl.conn.DefaultRoutePlanner.determineRoute(DefaultRoutePlanner.java:69) ~[httpclient-4.3.6.jar:4.3.6]

at org.apache.http.impl.client.InternalHttpClient.determineRoute(InternalHttpClient.java:124) ~[httpclient-4.3.6.jar:4.3.6]

at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:183) ~[httpclient-4.3.6.jar:4.3.6]

... 58 common frames omitted

My url is as below-
String url = "%22https%3A%2F%2Fpaxataurl%3Aportnumber%2Frest%2Fdatasource%2Fimports%2F1234567%3Fpretty%3Dtrue%22%20-F%20%22query%3Dselect%20%2A%20from%20empschema.emptable%22";

Please someone let me know if I am missing something here.

abhijeetmkhambe · ‎10-10-2019

Hi Sayyar,
Thanks for your input. I will do the change as per your suggestion.

Hi Bill (@bstephens),
I keep on getting error as below-