Automating the Creation of Denodo Data Marketplace External Elements from Apache Superset
You can translate the document:About External Elements
In this document we will review the different steps you have to complete in order to integrate Apache Superset elements into the Denodo Data Marketplace as External Elements.
The Data Marketplace facilitates the discovery of assets hosted on external tools via browsing or search functionality; these assets are called External Elements. These external elements have all the available information retrieved from the external tool server and the metadata added by other users of the Data Marketplace.
At the moment, there are three types of third-party applications supported:
- Power BI.
- Tableau.
- Custom External Element.
Apache Superset Dashboards are integrated as the last type, Custom External Elements.
Integration
Importing custom external elements into the Data Marketplace involves five main steps that are described in the official Set Up Custom External Tool User Manual:
- Create External Element Types: Define the semantic types of elements you want to ingest (Dashboards in this example, although they can also be Charts or Datasets from Superset).
- Create Provider Types: Register the external platforms or vendors providing the metadata along with their branding icons. Superset in this example
- Register Custom External Tool Servers: Connect the provider to a specific Virtual DataPort (VDP) interface view that holds the metadata contract. This allows to use a single interface view for all the External Element Types that come from the same provider.
- Implement the VDP Interface View: Use VDP to connect to the source repositories where the external element metadata is stored, and then perform all necessary transformations to adapt and map the data to the schema required by the interface view.
- Synchronize the External Tool Servers: Trigger the synchronization process to retrieve external element metadata by querying the interface views and import it into the Data Marketplace.
Step 1: Create External Element Types
There is one External Element Type already created called Reports. This type is the one used by Power BI and Tableau reports. Therefore, there are two options:
- Integrate Apache Superset Dashboards using this created type so they are located in the Denodo Data Marketplace with the reports of other platforms.
- Create a new External Element Type specific to Superset Dashboards.
If you choose to use the existing type, you can proceed directly to Step 2.
However, if you prefer to define a custom External Element Type based on your specific requirements, you must invoke the following endpoint:
|
POST /public/api/external-elements-types |
Set up Basic Authentication with your username and password, and populate the request payload with a JSON body structured with the fields specified in the official documentation: name, description, visualName, iconKey, iconColorCode, reversedIconColorCode. This will be the one used in this example:
|
{ "name": "SUPERSET_DASHBOARD", "description": "Superset dashboards demo description", "visualName": "Superset dashboard", "iconKey": "fas fa-cube", "iconColorCode": "#CFB53B", "reversedIconColorCode": "#fff" } |
Requirements and considerations:
- Authentication: You must authenticate using an account with create/delete permissions on External Elements.
- Remember the name of the new element type, it will be used in the Step 4 in the implementation of the interface view.
Request and response example:
Step 2: Create Provider Type
Once you have decided the type to use, it is necessary to register the external provider you will use. Execute a call to the following endpoint to proceed:
|
POST /public/api/external-providers-types |
Configure Basic Authentication using your credentials, and submit the request payload as multipart/form-data containing two distinct fields:
- request: A JSON composed with the fields name and visualName.
|
{"name": "SUPERSET_PROVIDER", "visualName": "Superset provider"} |
- icon: An image file in .png or .svg format (utilizing the official provider brand icon, Superset in this case, is recommended).
If you want to know more about these fields or the form, review the official documentation.
Requirements and considerations:
- Authentication: You must authenticate with a user with create/delete permissions on External Elements.
- From the response, it is important to save the value of the field externalProviderTypeId, because it will be used in the following step.
Request and Response Example:
Step 3: Register a Custom External Tool Server
Having created the external element type and provider, the next step is to register a custom external tool server and link it to the VDP interface view that will hold the metadata for that provider. For instance, in Apache Superset, integrating both Dashboards and Charts requires implementing a single interface view containing metadata for both external element types, as they share the same provider. Execute the following API request to register the server:
|
POST /public/api/external-tool-servers |
Set up Basic Authentication with your username and password, and populate the request payload with a JSON body structured with the fields specified in the official documentation: type, name, description, externalProviderTypeId (the id saved from the response of the Step 2), databaseName and viewName. You can use an existing database or create a dedicated one for this integration. The database will have to be created prior to running the VQL script in Step 4.The payload shown below is the setup used in this scenario:
|
{ "type": "CUSTOM", "name": "Superset Server", "description": "Superset Server description", "externalProviderTypeId": 3, "databaseName": "superset_external_elements", "viewName": "i_superset" } |
Requirements and considerations:
- Authentication: You must authenticate with a user account with Servers Administration permission.
- Response: In case of success the response will be returned the information sent and the id of the element created. It is important to remember the id value because it will be needed in the following steps.
Request and Response Example:
Step 4: Implement the VDP Interface View
After creating the external element type and provider and linking the provider to the target interface view, you must create the interface view and populate it with Superset metadata.
To retrieve the VQL code for creating the interface view and its required dependencies in your database, send a request to the following endpoint:
|
GET /public/api/external-tool-servers/{externalToolServerId}/vql-metadata |
Note: Replace externalToolServerId with the identifier obtained in Step 3. For authentication, configure Basic Authentication using your credentials.
Example Response:
|
CONNECT DATABASE superset_external_elements;
CREATE OR REPLACE TYPE external_element_association_type AS REGISTER OF (associated_element_id:text, external_tool_server_name:text, associated_element_type:text, direction:text, role:text); CREATE OR REPLACE TYPE external_element_association_array_type AS ARRAY OF external_element_association_type;
CREATE OR REPLACE INTERFACE VIEW i_superset ( id:text, name:text, description:text, external_element_type:text, url:text, created_at:timestamp, updated_at:timestamp, associations:external_element_association_array_type ); |
The interface view acts as a strict metadata contract. Your VDP implementation must populate this view ensuring that the output schema strictly adheres to the data types and requirements specified in the official documentation. As a summary the fields are:
- id: an unique identifier for the external element.
- name: the name of the external element.
- description: A description that will be shown in the Data Marketplace.
- external_element_type: This field must match the exact name field used in the step 1 or REPORT in case of deciding to use the pre-created type.
- url: this link must be the hyperlink to open the external element in the source tool.
- created_at: creation timestamp of the external element in the source tool
- updated_at: The timestamp of the element’s last modification in the external source tool. This field is the one that the Data Marketplace uses to know if the external element information was changed during the synchronization process.
- associations: Array field used to link the external element with other elements of the Data Marketplace (it can be a view from Denodo or other external element created) and create the 360 Graph.
- associated_element_id: The identifier of the associated element. The identifier if the associated element is an external one, if the element is a Denodo one use the format database_name.view_name.
- external_tool_server_name: Name of the server where the associated external element resides. Null in case the associated element is a Denodo element.
- associated_element_type: VIEW or EXTERNAL_ELEMENT.
- direction: possible values IN or OUT. The last one in case the data goes into the external element.
- role: Text to contextualize the 360 Graph.
Creation of views for Dynamic Superset Metadata Integration
Using the Superset API, we can implement the interface view that we just created by providing the metadata for the specific external element types defined in Step 1. Whether you created Dashboards, Charts, or Datasets as external element types, you must ensure the interface view is implemented with the corresponding information for each type. Below is an example on how to create the views to integrate Superset Dashboards dynamically as external elements. To achieve this, you use the Superset API, which facilitates dynamic integration with Denodo by fetching the latest metadata.
-
Data sources creation
We are going to create two distinct JSON data sources: ds_superset_api_token acts as the authentication provider to fetch access tokens, while ds_superset_api serves as the main source for retrieving dashboard and dataset metadata using the token generated by the first source.
Open the Denodo Design Studio, and in the same Virtual Database (VDB) where the interface view was created, create both data sources clicking on New > Data Source and select Json. Configure each one as follows:
ds_superset_api_token: Serves as the authentication provider. It dynamically requests and retrieves the JWT access token required to authenticate all subsequent API calls.
ds_superset_api_token: It will be in charge of dynamically retrieving the required access token and providing it to the other data source calls.
- Data Route: Set to HTTP Client.
- HTTP Method: Choose POST.
- Base URL: Enter http://<SUPERSET_HOSt>:<SUPERSET_PORT>/api/v1/security/login (adjust the <SUPERSET_HOST> and <SUPERSET_PORT> as needed to match your Superset environment).
- Post Body: Enable Post Body and supply the JSON payload below (change the user/pass to the user you want to use):
|
\{ "username":"myuser", "password":"mypassword", "provider":"db" \} |
ds_superset_api: Serves as the primary metadata source. It executes GET requests to retrieve dashboard and dataset metadata from Apache Superset, using the bearer token dynamically supplied by ds_superset_api_token.
- Data Route: Set to HTTP Client.
- HTTP Method: Choose GET.
- Base URL: Enter http://<SUPERSET_HOST>:<SUPERSET_PORT> (adjust the hostname and port as needed to match your Superset environment). To reutilize this data source to call all the different endpoints, you set the base url to the Superset address and in the base view creation process you specify the specific endpoint for each base view.
- HTTP Headers: Click on New and add a header with name Authorization and Value Bearer @token. You use an interpolation variable to fill this token dynamically using the first data source.
-
Base views creation
You will create three base views: one from ds_superset_api_token and two from ds_superset_api (one for dashboard metadata and one for datasets).
Create bv_superset_api_token
- Open ds_superset_api_token and click CREATE BASE VIEW.
- Click Ok, and then click Create Base View at the top right.
- A new base view containing the access_token field will be created.
- Rename this view to bv_superset_api_token.
Create bv_superset_api_dashboard
- Execute bv_superset_api_token and copy the generated access_token value, as it is required to initialize the remaining base views.
- Open ds_superset_api and click CREATE BASE VIEW.
- Set the Relative URL to /api/v1/dashboard/ and click Ok.
- When prompted for the sample token value, paste the copied access_token and confirm.
- In the schema creation window, select Stream output at specified level and check the result array field to expose all metadata directly.
- Save and rename the base view to bv_superset_api_dashboard.
Create bv_superset_api_dataset
- Repeat the creation process for the third base view, but this time, setting the Relative URL to /api/v1/dashboard/@{id_url}/datasets.
- When prompted, supply the copied access_token and enter a valid dashboard ID for the id_url variable (you can retrieve a valid ID by querying bv_superset_api_dashboard).
- Name this view bv_superset_api_dataset.
-
Integration views
You should now have three base views: bv_superset_api_token, bv_superset_api_dashboard and bv_superset_api_dataset. If you execute the dashboard or dataset views, you will notice they require an input token. The first step is to dynamically pass this token by joining each base view with the token view.
Create the Integration Views (iv_dashboard and iv_dataset)
- Click on New > Join and drag bv_superset_api_token and bv_superset_api_dashboard.
- Connect bv_superset_api_token to the dashboard base view and set the join condition to: bv_superset_api_token.access_token = bv_superset_api_dashboard.token.
- Go to the Output tab, rename the view to iv_dashboard, and save it.
- Repeat the process with bv_superset_api_dataset.
Join dashboard and dataset metadata
At this point, our focus will be exclusively to these two integration views: iv_dashboard (it retrieves all the metadata of each dashboard) and iv_dataset (for each dashboard it retrieves all the metadata of the datasets it uses). Because iv_dataset still needs an input parameter (id_url, corresponding to the if of the dashboard), we must join both views. Joining them consolidates the dashboard metadata and the metadata of its underlying data sources into a single unified view, facilitating the creation of the associations array of the i_superset interface view.
- Go to New > Join and drag both integration views.
- Connect iv_dashboard with iv_dataset with the condition: iv_dashboard.id = iv_dataset."ID_URL":
- In this case there are fields with the same name in both views, so you would have to rename those repeated fields in the Output tab to avoid issues (Suggestion: rename those fields that came from iv_dataset).
- Also in the Output tab, create the following new fields, to prepare the interface view’s array field:
- associated_element_id: Simply rename the existing iv_dataset.name field. (The rest of the fields must be created as New > New field).
- external_tool_server_name: Set it to Null. (Associations are Denodo views).
- associated_element_type: Set to ‘VIEWS’.
- direction: Set to ‘OUT’. The data goes from Denodo to Superset.
- role: Set to ‘referenced_by’. Or your preferred role for the 360 Graph.
4. After creating the required fields, rename the view (iv_dashboard_dataset) and save it.
Group the Data (Final View)
Now, you have all the required information for the interface view, but not in the granularity that is needed. We are going to group the data by Dashboard ID to create the final view.
- Click on New > Selection and drag the integration view.
- Go to the Group By tab, enable it, and add id. Important: To avoid validation errors, you must also add all the fields you will use in the Output tab (e.g., dashboard_title, changed_on_utc, url, status, published) to the Group By box, on the right.
- Go to the Output tab. You will see only the grouped by fields. Remove all the fields except for id. And Click New > New Field to create the following:
- name: It must be the name of the report. Use the field
|
iv_dashboard_dataset.dashboard_title. |
- description: You can complete this field with the information you prefer. This is a possible example:
|
(((((((('Last changed '||changed_on_delta_humanized)||' by ')||(changed_by).first_name)||' ')||(changed_by).last_name)||'; Status: ')||status)||case WHEN (published = 'false') THEN '; No published' ELSE '; Published' END) |
- external_element_type: As explained, match the name of the external element type created (e.g. ‘SUPERSET_DASHBOARD’) or the pre-created one (‘REPORT’).
|
‘SUPERSET_DASHBOARD’ |
- url: The hyperlink of the report in superset (update the host and port according to your deployment settings).
|
('http://localhost:8088'|| url) |
- created_at: Superset api does not return the creation timestamp so you can set one manually or as in the example used the updated on.
|
to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ', changed_on_utc) |
- updated_at: The changed date is returned by superset so it is not necessary to do more transformations.
|
to_timestamp('yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ', changed_on_utc) |
- Finally, click New > New Aggregate Field to build the array:
- associations: You have created the necessary fields in the previous view so it is only necessary to build the array using nest function.
|
nest(associated_element_id, external_tool_server_name, associated_element_type, direction, role) |
- Rename the view's name to iv_dashboard_dataset_group_by_id and save it. You can utilize this completed view to implement the interface view.
After completing all the steps and transformations you should have these data sources and views:
Interface view implementation
Open the interface view i_superset, navigate to the Edit tab, and select Implementation. Next, drag and drop the iv_dashboard_dataset_group_by_id view into the workspace. Since field names match, all mappings populate automatically.
Step 5: Synchronize the External Tool Server
After confirming its ability to retrieve metadata and be sure that the views associated with the dashboard are synchronized on the Denodo Data Marketplace, you must initiate the synchronization process. This action triggers the ingestion of custom external elements into the Data Marketplace environment.
In this phase, the Data Marketplace executes the designated VDP interface view to obtain a comprehensive snapshot of the external metadata. Subsequently, it assesses the current status and properties of each individual asset. Consequently, if a dashboard is removed or updated, when you trigger this process it is also removed or updated from the Data Marketplace.
To trigger this process call the following endpoint:
|
POST /public/api/external-tool-servers/synchronize |
Set up Basic Authentication with your username and password, and populate the request payload with a JSON structured with one array field, providing the IDs of the external tool servers (the id received in the Step 3 response) you want to synchronize. In this example we will synchronize only one external server:
|
{ "externalToolServerIds": [1] } |
Requirements:
- Authentication: You must authenticate with a user account with Synchronize permissions.
Request and Response Example:
External Elements in the Data Marketplace
After a successful synchronization of the external elements, the dashboards will be available as seen in the image below ( in this example the decision was to use the pre-created external element type).
Clicking on a specific dashboard displays detailed information, including the description of the external element, categories, tags, and warnings. Additionally, the Actions drop-down menu offers several options depending on user permissions, such as creating a request, asking a question, or endorsing, warning, or deprecating the report. Next to the Actions drop-down, the Open in option allows you to launch and view the report directly within the Superset application.
Another key feature is the 360-degree lineage graph, which provides a clear understanding of the data lineage by illustrating where the report's underlying data originates. It visually maps all dependencies, tracing the data from its initial source all the way to the final dashboard.
Automating External Tool Server Synchronization
With all previous setup steps complete, the interface view that dynamically fetches metadata from Apache Superset will be fully operational. Calling the Step 5 synchronization endpoint updates the Denodo Data Marketplace automatically with any dashboard modifications or newly added dashboards.
You can trigger this synchronization manually as needed, or schedule periodic metadata updates automatically using Denodo Scheduler by following the instructions below:
Create a Data Source and Base View in VDP
To automate execution, start by creating a VDP data source to execute the POST synchronization request.
- Open your target Virtual Database (VDB) with a user account that has Synchronize permissions.
- Navigate to New > Data Source > JSON, and adjust the following settings:
- Data Route: Set to HTTP Client.
- HTTP Method: Choose POST.
- Base URL: Enter http://localhost:9090/denodo-data-catalog/public/api/external-tool-servers/synchronize (adjust the hostname and port as needed to match your Denodo Data Marketplace environment).
- Post Body: Enable Post Body and supply the JSON payload below. In this case we are just synchronizing one external tool but this can be made more general to synchronize all configured tools.
|
\{ "externalToolServerIds": [1] \} |
- Authentication: Select Basic Authentication and specify credentials for a user account with Synchronize privileges.
- After configuring these options, click Create Base View to generate the corresponding base view.
- Rename the final base view and place it in your preferred folder.
Configure and schedule a Job in Scheduler
With the data source and base view created in VDP, you can set up automated execution in the Denodo Scheduler.
- Open Denodo Scheduler Administration Tool.
- Click Add job > Individual Query.
- Provide a title and optional summary for the job.
- Go to the Configuration tab and select the target VDP server under Data source
- Complete Parametrized query parameter with the query below:
select * from <vdb>.<name>
Replace <vdb> with the name of the virtual database where the view was created in the previous step and <name> with the base view name. For additional guidance on configuring jobs, refer to the Creating and Scheduling Jobs User Manual.
- To run the synchronization job regularly, define a schedule in the Triggers tab by clicking Add trigger.
- Use the Cron expression configuration to define the execution schedule. For instance, the setup in the example image executes every Saturday at 00:00:00 (consult the Trigger Cron Expression User Manual for cron syntax details):
- Save the job.
Currently, the job will be executed every Saturday at 00:00:00, so at that moment every change on the information of a dashboard will be automatically propagated to the Denodo Data Marketplace without need of manual execution.
References
External Elements - User Manual
Set Up Custom External Tool - User Manual
Creating and Scheduling Jobs - User Manual
Trigger Cron Expression - User Manual
Creating and Scheduling Jobs - User Manual
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
