Autonomous REST connection SQL query
How can I get a simple SQL running when everything is set up . the Autonomous REST connection is success.
But when I try to execute SQL to add new data sets, I am getting this error, and experian documentation here, has not explicitly mentioned anything how to get those running.
I checked documentation there,
already, and the error I am getting is this:
its a simple select.
@Ian Hayden
@Henry Simms let me know.
Br,
HS
Comments
-
Hi Hussain
In the Docs link that you shared and there is a link 'DataDirect Autonomous REST JDBC driver documentation'. Follow that and there are detailed instructions on setting up and using the Autonomous REST connector. Look at the section 'Mapping objects to tables'
There is a discussion here on the complexities of setting up this type of connector:
0 -
Hi @HussainSyed
The Autonomous REST connector maps an API's JSON response into normalized tables. When using the default mappings, which are most commonly used, the tables will not have / need a schema name to be queried.
If you already have a working AutoREST connection you should see tables available to load. In the example below I'm just using the open / auth-free https://api.punkapi.com/v2/beers API:
If that works, you can also use a query to load like this:
Note that if using a .rest config file I could define the name of the table, but in the above connection I'm simply using the hostname to auto-generate the mapping.
If you don't already have a working AutoREST connection that returns tables and values, that would need to be resolved before you can use a SQL query.
Henry
2 -
@Henry Simms ,
we have working connection, but all I see is
configuration table,
Tested fine while setting up autonomous rest.
our settings, before testing the connection.
0 -
the only difference I found in your example configuration is that you are giving the api in rest end point and I am giving it in the server name.
when I try to give it in the rest endpoint it does not succeed.
in servername, if I give the rest end point it succeeds.
your api is auth-free, so it is succeeding in both I think. @Henry Simms0 -
Hi @HussainSyed
From the documentation, one of either the "REST Config File" or "REST endpoint" must be configured. The servername property just allows the host root URL to the rest config file.
The "Test Connection" check is not really a reliable check for AutoREST connections, it won't attempt to connect and authenticate with a REST API, just to check if it's reachable.
It sounds like you might benefit from using the Autonomous REST Composer, built into newer versions of the JDBC driver, to build out your connection along with the "Getting started with Autonomous REST" tutorial guide created by Progress Datadirect, who create the driver.
To run the composer, just double-click on the driver file DSautorest.jar file, which you will find as part of the Aperture Data Studio database, eg in D:\ApertureDataStudio\drivers\jdbc\ or similar. This will pop up a web app, and you can enter "arc.html" in the browser:
From there, the tutorial and UI should help you construct a working AutoREST connection
0 -
I tried opening
DSautorest.jarfile found in\ApertureDataStudio\drivers\jdbc\I cannot open it as webapp, I tried to open in IE, all it asks me is if I want to save the file somewhere else! @Henry Simms
0 -
Hi @HussainSyed , it's possible that you don't have java on your path. Can you try running the jar from the commandline instead:
"C:\Program Files\Experian\Aperture Data Studio 2.11.3\java64\bin\java.exe" -jar DSautorest.jarIn the above, you will likely need to update your path to the java.exe used by your version of Data Studio. Running this should pop up a new browser window, where you can get started with AutoREST config.
0 -
@henry Simms Yes I was able to open the Auto REST connector configuration manager,
and after that when I give the same rest api url in connection setting and auth method is basic with creds,
I just get the query and the dialog saying it passed.
I believe when we tried it from external systems in ads , it only showed _configuration table.
as shown above in this post.
using the query above to add data sets in ads, does not yield to us fetching other tables either,
or maybe I still don t understand how this api calls work.
do I need to go back to the Progress Data Direct portal and do different connection settings than this:
br,
HS0 -
0
-
Hi @HussainSyed - By default, running the jar will pop up the "Configuration Manager" (jdbc.html) for the driver, which allows you to create a working connection string. What you want to do is create the custom model (mappings, auth etc) for your API using the "Composer" (arc.html) to generate a .rest file, as well as the connection string. Edit the URL to browse to arc.html:
You can then create and configure your model
I would recommend following the following guide:
0 -
Hi Henry,
I was able to make the above link and tutorial work a bit, after generating the the .rest file. and using it in my new connection settings. as below.
I was unable to see much of the tables that I was supposed to, mostly audit tables and the tables that we tried to sample are not bringing count more than 1000,
and one of the tables is just bringing count of the rows.
are there settings while creating .rest file in the progress direct composer on arc.html link that determine those things …
or do we need to know about table names beforehand?
one of the table names that we knew, we could not find it while creating .rest ..
test was failing for that table. in progress direct.as below! saying no records available.
for the tables for which it succeeded, it is shown above.
I'd say some progress today, but not the results that would please everyone.
Br,
HS1 -
You're probably only seeing the first 1000 records from the API call because pagination needs to be configured in order to get back additional records via additional calls: https://www.progress.com/tutorials/jdbc/getting-started-with-autonomous-rest-connector-ui#:~:text=Configure%20Pagination
To get other tables, you'll likely need to explicitly add the tables in the Composer using the API's rest resources - these aren't automatically detected.0 -
Hi Henry,
We tried the setting of pagination but first we did not understood it so after wrongful pagination we were getting below error:
then on realizing offset, and limit are to be actual numbers. we tried to increase and decrease pagezie and page size parameter and ended up with no records,
and the tutorial page doesnt explain it much either what should be offset.
keeping in we had 17 pages of data already without any page settings, I changed to 19 to get more rows but could not get anything more than 1000 records and 17 pages.
with no paging we had data, 1000 records, but we believe there are more records as much as 500, 000 .
and tutorial did not explain much row offset paging parameters to be used!
pls let us know.
Br,
HS1 -
@henry_ hi henry, I think with your help and guidance now we are very close, if we can get this pagination working then we can atleast fetch the records from one table and in similar way we can try for the other tables. Looking forward to your response on Hussain's query :)
1 -
@Henry Simms pls have a look on the pagination issue when you have time.
1 -
@henry_ @Henry Simms Can you please help here, we are just stucked with the pagination thing, if that can be done then we are good with atleast 1 table. Looking forward to your response.
0 -
Pagination is very common for APIs that return large volumes of data, checkout LinkedIn, Slack, Twitter etc. so it should be easy to find better explanations than I can give, such as
You would need to read the documentation of the API you are connecting to. I'd suggest playing around with the pagination values outside Data Studio until you know the limits and how you might call all the required data. Then worry about how to set using the JDBC connector, maybe multiple Datasets will be required and/or multiple batches
0 -
Hi @HussainSyed and @Mahulima , if you're still looking for help with API pagination using the Autonomous REST connector, I've just published an article outlining the approaches for different paging types:
0
Categories
- All categories
- 17 Get started
- 486 Get involved
- 19 Support
- 256 Resources
- 3 Events
- Upcoming events
- 3 Event recaps
- 18 Ideas and roadmap
- 97 Community categories