You can translate the question and the replies:

Scheduler Admin Back end table Details

Hi Team, Wanted to check if the Scheduler Admin maintains all the field attributes/Metadata in any back end DB, or any other location where it maintains the metadata of the Code. The idea is to query that particular table which contains this Scheduler Admin data by creating a connection from Denodo and using the Job Status and other details. Please help to locate the exact table where this data would be stored!!
user
22-04-2022 03:00:26 -0400
code

7 Answers

Hi, Scheduler is a component that allows time based scheduling of the jobs and it makes use of the embedded database (**Apache Derby**). In general the metadata is encrypted and stored in the Derby Database and cannot be decrypted or accessed. Please note, that it is not recommended to touch the metadata of the Denodo Component servers. However, I would make use of the** Scheduler client API** in order to configure or manage the jobs and data sources programmatically. With the API I would be able to get the job list, job reports, to enable or disable jobs, to execute the job with dependencies, to stop a job from running ect. You can refer to the [Scheduler REST Client API](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/developer_api/rest_api/rest_api) section and the [Scheduler RMI Client API](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/developer_api/scheduler_client_api/scheduler_client_api) section of the Scheduler Administration Guide for more information. Hope this helps!
Denodo Team
22-04-2022 08:15:50 -0400
code
Hi Team, Thanks a lot for the reply. Followed the same in one of our local instance of Denodo hosted on AWS EC2 instance. Below are the findings. Requesting team to kindly analyze the same and help us out, and guide where are we missing from our end! The Swagger Link which we were able to access is http://localhost:9090/webadmin/denodo-scheduler-admin/swagger-ui.html Tried the Get list of Jobs API from the Jobs Section and below are the findings: Using the CURL command from Command Prompt of the EC2 instance: >curl --location --request GET "http://127.0.0.1:9090/webadmin/denodo-scheduler-admin/?uri=//127.0.0.1:8000/jobs" -u "username:password" -H "accept: application/json" The response is **Curl-1.jpg** On changing the url a bit and again trying with Curl, not getting any response from console, the screenshot is **Curl-2.jpg** On a different note, tried to connect a data source in Denodo using the same url obtained from the Swagger and appending the uri part at the end: On creating the 1st data source (**DataSource-1.1**), although the test connection is successful, on trying to create the base view, we are getting the following error: **DataSource-1.2**. If we don't append the uri part and try to create data source directly on the API response from Swagger, the Test connection is also not working, attached in **DataSource-2** In both the above instances of creating the data source in Denodo, we added the basic authentication in the Authorization tab while creating the data source, still we are getting the errors as attached in the screenshots! Requesting team to provide some inputs on the same and also please let us know if you need any other info from our end!! Curl-1: https://ibb.co/2j1vtb5 Curl-2: https://ibb.co/ydcT3PS Datasource 1.1: https://ibb.co/D9n9QGM Datasource 1.2: https://ibb.co/M2yt8vK Datasource 2: https://ibb.co/3r8WbzT
user
25-04-2022 10:42:41 -0400
Hi, The **404 response** of the curl command to get the list of jobs indicates that the web page is not found. Hence, in order to access the Job information using the **Scheduler REST API**, I would ensure that the Scheduler server is up and running. In general it is not possible to access the Derby metadata directly hence you could make use of the [Scheduler client API](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/developer_api/rest_api/rest_api#scheduler-rest-client-api ) without logging into the Virtual Dataport Administration Tool .The Virtual DataPort Server itself does not contain an adapter or a functionality for extracting the Scheduler Job information (or other metadata) out of the box. In order to access the Denodo Scheduler metadata information you can either use an **external database** for storing the Denodo Scheduler metadata information or **Scheduler Client API**. You can refer to the [Database Metadata Settings](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/administration/server_configuration/server_setup#database-metadata-settings ) of the Scheduler Administration Guide for more information. Furthermore, if you still need help and if you are a valid support user then you can raise a new support case in [Denodo Support Site](https://support.denodo.com ) so that our support team can help you. Hope this helps!
Denodo Team
26-04-2022 07:32:00 -0400
code
Hi Team, Thanks a lot for the reply. For the Database Metadata Settings alternative provided where we would mount the metadata to an external database: 1. will the meta table which scheduler Admin would create have all the status and other information in it. 2. Additionally, will the data be normally stored or will it be encrypted in any form? 3. Can we connect to these tables in Denodo to create views and query the status of the jobs. Regarding the 1st point related to 404 error, the scheduler Admin Service is actually up. We have also created few jobs and executed them to load the cache views. Please find the screenshots Denodo Scheduler-1 and Denodo Scheduler-2 screenshots attached in the below link. We have Denodo and its components including the scheduler installed on a EC2 instance , adn we have been trying out all the curl and other alternatives from the Command Prompt/ Chrome browser from the same instance. Denodo Scheduler-1: https://ibb.co/s9Sd4Pc Denodo Scheduler-2: https://ibb.co/HHsTTNg Please let us know if anything else/prerequisite missed from our end because of which are not able to access these Schduler APIs. Thanks in advance!
user
26-04-2022 09:50:09 -0400
Hi, You can view the job details (like start time, job id) by joining the tables SCHED_REPORTS and SCHED_ELEMENTS under your external metadata database. Please, note that you cannot access the metadata in the Virtual Dataport Administration Tool. In order to get the list of jobs using the curl command I would perform the following steps: * I would first confirm if I can ping the Scheduler REST API by executing the following command : `curl --location --request GET 'http://localhost:9090/webadmin/denodo-scheduler-admin/Ping' --header 'URI: //localhost:8000' -i` * To request with basic authentication, I would construct the username-password by concatenating the user-id, a single colon (“:”) character and the password . Then, I would encode the username-password using Base64. * I would, build a header “Authorization” and the value “Basic <BASE64-user-pass>” * Finally I would execute the following command in order to get the list of jobs: `curl --location --request GET 'http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/jobs?uri=//localhost:8000' --header 'Authorization: Basic <encoded_value>'` You can refer to the steps in the [Scheduler REST Client API](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/developer_api/rest_api/rest_api#scheduler-rest-client-api ) section of the Scheduler Administration Guide for more information. Hope this helps!
Denodo Team
27-04-2022 07:40:50 -0400
code
Hi Team! Thanks a lot for the reply. Tried both the alternatives and please find the updates/issues related to this.Requesting to kindly answer to the points enlisted below: Regarding External Metadata Db approach: 1. We have Apache Derby as Metadata DB at present. In order to explore the alternative of adding external DB to store the metadata , can you please help us with the default password of the derby DB in order to revert to the same once we try the same. Pls find the screenshot Scheduler Metadata Configuration for the same. 2. Can we use a schema in snowflake as the External Metadata DB to hold this metadata. 3. Can you please let us know the exact process of switching these metadata Database between external and Derby, where do we need to change the same. (In Scheduler Metadata Configuration or Metadata database management) 4. Is there any way to export this metadata info to an external database through a Scheduler VDP export job. (or changing the configuration in the settings is the only alternative) 5. If we have configure the external metadata DB, can we access the tables SCHED_REPORTS and SCHED_ELEMENTS. Can we connect by making an connection to this external DB and query the status of the job. Regarding the 2nd alternative of accesing through Scheduler admin APIs: We tried both the ping and job list curl command, for ping command, we got some response, but for the job list curl command, we are still getting 404 error. Screenshot attached is Curl Command Issue. Kindly help us out with the same! Cache DB Configuration: https://ibb.co/DfxLS1r Curl Command Issue: https://ibb.co/sJMRtQy Metadata database management: https://ibb.co/LC7x261 Scheduler Metadata Configuration: https://ibb.co/FqfvJ5C
user
28-04-2022 09:55:15 -0400
Hi, In order to configure the external metadata database for Scheduler, I would perform the following steps: * I would take a back up of the current metadata (jobs, data sources, etc) via the Scheduler Administration UI and Execute the scripts from **<DENODO_HOME>/scripts/scheduler/sql** to create the metadata tables used by Scheduler in the new database. * Copy the drivers of the new database to **<DENODO_HOME>/lib/scheduler-extensions**. * Restart the Scheduler server and configure the connection in the **Administration > Database Metadata Settings** of the Scheduler Administration UI. * Restart the Scheduler server again and import the metadata backup. Please, note that snowflake is not a supported database for storing Scheduler metadata. Also the default password of the Derby database is derby and it is not possible to export the metadata of Scheduler using the Scheduler VDP export job since scheduler export is used only to export the results of the view from the Virtual DataPort. Finally, it is not possible to query the metadata in the Virtual DataPort Administration Tool. Please refer to your Scheduler log to find more information related to the error while accessing the Scheduler API using curl command. You can refer to the [Database Metadata Settings](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/administration/server_configuration/server_setup#database-metadata-settings ) of the Scheduler Administration Guide for more information. Furthermore, if you still need help and if you are a valid support user then you can raise a new support case in [Denodo Support Site](https://support.denodo.com) so that our support team can help you. Hope this helps!
Denodo Team
29-04-2022 08:23:59 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here