【Scheduler】Getting data from NASA Open API

The first of all, let's go to NASA Open API Page and get an API key.

Once you get an API key, please choose one of the API endpoints provided.
You can get there from Browse APIs page. I chosen Mars Rover Photos at this time.

Click the + icon and show the instructions. You would see more details how you can get data from the endpoint.
I chosen the URL example from the top of the list Example Queries.

Creating the main task in Scheduler

This time, I will use Sharperlight Scheduler to get data from the NASA Open API.
Open Scheduler from Sharperlight Application menu.
Start creating a new task by clicking New button.
Set Code and Group when the Task dialog opens.
Move onto Action tab to define actual actions for this task.
I would define two actions, one retrieves data from a NASA Open API endpoint and another one stores it in a previously prepared local database.

Action: Query Combinations

At first, fetch data from the endpoint by using Query Combinations action.
Select Query Combinations action from the action list which can be opened by clicking New icon.


Create the query to fetch data from the endpoint for this Query Combinations action.

The following query would be defined with the Query Builder opened.

• Mode：Detailed Report
• Product：System
• Table：Web Page Table, CSV, JSON, XML or OData
• URL Link：(see below)

https://api.nasa.gov/mars-photos/api/v1/rovers/curiosity/photos?sol=3495&page={@Page}&api_key=Your API Key

Set 3495 for the parameter sol, and add page parameter to the URL.
The page number passed to page parameter would be passed as the parameter for this task will be mentioned later.
Then set photos to Start Position filter of the query to tell the engine where the start point to analyse the returned JSON dataset.

When the endpoint URL is set to URL Link filter、Sharperlight engine starts getting information from the endpoint and populates the list of selectable fields into the Selection area.

You can select those fields to output.
Then camera field and rover field are still in JSON format so the expression can be used to retrieve values from these JSON dataset.

All values we want can be retrieved with this method.

All output fields have its reference name which start with %. They are used when fetched data is saved to the local database later. e.g %RoverId, %RoverName
Check the query with Preview button.
Data was returned fine.

click OKbutton to save the query.

Action: SQL Statement

As the second action, add SQL statement action.
The Query Combinations action works such like For statement so it can pass a record from fetched data one by one to the other action.
This SQL Statement action would write the records passed from Query Combinations action one by one to the local database.
Although the database connection is required to write data, the connection is already defined by creating the local database and the table and the corresponding Sharperlight datamodel in prior.
Therefore, the connection is selectable from the dropdown list like this.

Finally, here we simply write the SQL statement to write the data from the Query Combinations action to the database.

All values passed from Query Combinations action can be referred with {%xxxxxx}.

UPDATE [dbo].[MarsPhoto]
       SET [Rover_Raw]='{%RoverRaw}'
       ,[Rover_Id]={%RoverId}
       ,[Rover_Name]='{%RoverName}'
       ,[Rover_LandingDate]='{%RoverLandingDate}'
       ,[Rover_LaunchDate]='{%RoverLaunchDate}'
       ,[Rover_Status]='{%RoverStatus}'
       ,[Camera_Raw]='{%CameraRaw}'
       ,[Camera_Id]={%CameraId}
       ,[Camera_RoverId]={%CameraRoverId}
       ,[Camera_Name]='{%CameraName}'
       ,[Camera_FullName]='{%CameraFullName}'
       ,[ImageURL]='{%ImageURL}'
       ,[EarthDate]='{%EarthDate}'
WHERE [Id]={%id};
IF @@ROWCOUNT=0
BEGIN
INSERT INTO [dbo].[MarsPhoto]
           ([Id]
           ,[Rover_Raw]
           ,[Rover_Id]
           ,[Rover_Name]
           ,[Rover_LandingDate]
           ,[Rover_LaunchDate]
           ,[Rover_Status]
           ,[Camera_Raw]
           ,[Camera_Id]
           ,[Camera_RoverId]
           ,[Camera_Name]
           ,[Camera_FullName]
           ,[ImageURL] 
           ,[EarthDate])
     VALUES
           ({%id},'{%RoverRaw}',{%RoverId},'{%RoverName}','{%RoverLandingDate}','{%RoverLaunchDate}','{%RoverStatus}'
              ,'{%CameraRaw}',{%CameraId},{%CameraRoverId},'{%CameraName}','{%CameraFullName}','{%ImageURL}','{%EarthDate}')
END

That's all for this action.

Action: Query Combinations End

Finally add Query Combinations End action.
This action actually does nothing and this is just required to close the loop made by Query Combinations action.

Making the task was completed so click OK to save it.

Creating a parent task that repeats the main task for the number of pages

Next, create another task so that the previously created task can be repeated for each page number.
Create a new task and use the Query Combinations action to define a query that returns page numbers from 1 to 20.

And inside that Query Combinations action loop, execute the the main task created earlier. Pass the page number as an argument.

Don't forget the Query Combinations End action at the end.
Save with OK.

Executing the task

In the Scheduler dialog, select the parent task and choose Execute from the right-click menu.
If the execution ends without error, the data obtained from the NASA Open API should be saved in the specified table in the connected database.

Confirmation of acquired data

Of course I will create a report in Sharperlight and check it.


Open it with the web browser...

Thank you.