【Scheduler】Getting data from NASA Open API

The first of all, let's go to [NASA Open API Page](https://api.nasa.gov/) and get an API key.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b8f2b54a-3dd4-1ccc-d660-24f20bad7fdf.png)

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d284be2f-563f-291e-86bd-cf09e81fbe04.png)
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`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/828718d8-b0cb-7eee-7b3f-25fde3d7b554.png)

## 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ecbafbe2-36e7-b9b5-19b9-7b5b4faba063.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/270be28a-0715-0eed-0232-58f9e878418e.png)
Create the query to fetch data from the endpoint for this **Query Combinations** action.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e93a2302-f1c4-cfbd-fb00-1f495c0b8002.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3527d56a-7a85-b12e-90d6-b33eab251507.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4bb52ea3-5641-a233-0001-7b33723bc94d.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2bd01d65-0911-aa22-bab1-9f2acb512a7d.png)
All values we want can be retrieved with this method.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/377dde02-25dc-e174-9f8d-ad8fc117a4b0.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/77b5ba9a-c0e1-96c9-1997-891a266e5878.png)
click `OK`button 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/90275c07-bea4-bce9-2ec2-ac2c62f23b8d.png)
Finally, here we simply write the SQL statement to write the data from the **Query Combinations** action to the database.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a28d82b6-c52b-e1f3-8aab-cd878f5d510d.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/83266cca-996b-6563-3eba-52fc17d0c7a7.png)
And inside that **Query Combinations** action loop, execute the **the main task** created earlier. Pass the page number as an argument.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8befafdb-4bcf-dc1a-0570-d067db1f2acf.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c2a5f879-98b2-8f49-56d8-d073a8dcf8a5.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/810c7d9b-f6ca-ec52-895a-6af4756dc53b.png)
Open it with the web browser...
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ca72a7b9-66ae-d6c6-d47a-c32c1bc40e8d.png)

Thank you.