https://datahubproject.io logo
Join Slack
Powered by
Superset and Virtual Datasets I have a question re...
# ingestion
m
Superset and Virtual Datasets I have a question regarding the ingestion of Superset and virtual datasets. Superset support 2 types of datasets: • Physical: refers directly to a physical table or a view in a DB somewhere • Virtual: refers to a query on a physical table or view. It doesn't exist on the DB, is only a "thing" in Superset If I'm not mistaken, a virtual dataset could be the equivalent of a Looker Explore for those familiar with Looker. When I ingest Superset dashboards and charts, I see charts that are using physical tables and those are nicely mapped to the proper source in DataHub's lineage. But when a chart is using a virtual dataset, it refers to a urn using the 
table_name
provided by Superset, but that name is the name of the virtual dataset, which doesn't exist in DataHub. I think there should be a connection to the physical dataset like the following: 
Dashboard -> Chart -> Dataset (virtual) -> Dataset (physical)
Right now, the virtual dataset refers to void, so I can't tell which physical dataset is being used for the query. And the query should be a property of the virtual dataset in my opinion. Am I missing something in my deployment, or there is actually a gap in DataHub's Superset source?
In superset.py, here is how the chart input is created:
Copy code
datasource_urn = self.get_datasource_urn_from_id(datasource_id)
...
        chart_info = ChartInfoClass(
            type=chart_type,
            description="",
            title=title,
            lastModified=last_modified,
            chartUrl=chart_url,
            inputs=[datasource_urn] if datasource_urn else None,
            customProperties=custom_properties,
        )
        chart_snapshot.aspects.append(chart_info)
where 
datasource_urn
is created like:
Copy code
def get_datasource_urn_from_id(self, datasource_id):
        dataset_response = self.session.get(
            f"{self.config.connect_uri}/api/v1/dataset/{datasource_id}"
        ).json()
        schema_name = dataset_response.get("result", {}).get("schema")
        table_name = dataset_response.get("result", {}).get("table_name")
        database_id = dataset_response.get("result", {}).get("database", {}).get("id")
        database_name = (
            dataset_response.get("result", {}).get("database", {}).get("database_name")
        )
        database_name = self.config.database_alias.get(database_name, database_name)

        if database_id and table_name:
            platform = self.get_platform_from_database_id(database_id)
            platform_urn = f"urn:li:dataPlatform:{platform}"
            dataset_urn = (
                f"urn:li:dataset:("
                f"{platform_urn},{database_name + '.' if database_name else ''}"
                f"{schema_name + '.' if schema_name else ''}"
                f"{table_name},{self.config.env})"
            )
            return dataset_urn
        return None
When calling the Superset API for the dataset JSON, you get something like this (I have removed some fields to make it shorter):
Copy code
{
    "description_columns": {},
    "id": 146,
    "result": {
      "cache_timeout": null,
      "columns": [...],
      "database": {
        "database_name": "my_db",
        "id": 1
      },
      "datasource_type": "table",
      "default_endpoint": null,
      "description": null,
      "extra": null,
      "fetch_values_predicate": null,
      "filter_select_enabled": false,
      "id": 146,
      "is_sqllab_view": true,
      "main_dttm_col": null,
      "metrics": [...],
      "offset": 0,
      "owners": [...],
      "schema": "my_schema",
      "sql": "select ... from ... group by ...",
      "table_name": "Untitled Query 2 04/28/2021 11:52:53",
      "template_params": "",
      "url": "/tablemodelview/edit/146"
    },
    "show_columns": [...],
    "show_title": "Show Sqla Table"
  }
Should we have a dataset in DataHub that would be named 
Untitled Query 2 04/28/2021 11:52:53
in the 
my_schema
schema, which would be derived from another dataset under 
my_schema
? But typing this made me realize that we don't have the physical table name used by the query... it would need to be extracted I suppose. I don't know if this should be reported to Superset or not. Anyhow, I'd be curious to get your opinion on this.
Some more digging about this requires me to change my original assumptions. A virtual dataset doesn't have to use a physical dataset, so the virtual dataset could be "independent" and the dashboard lineage would stop there. But it would be nice if the SQL could be shown in DataHub's UI. The ideal scenario would be to leverage a library that could parse the SQL and list the tables being referenced. We could then attempt to create the proper urns and create a lineage.
m
Hi @modern-monitor-81461, it does seem like currently the lineage is not handled properly for views. I can discuss this internally with our team and take this as a feature request. We would also welcome any contributions from you in case if you are interested. We have a mode connector that does some sql parsing in case if you want to try it out 🙂 - https://datahubproject.io/docs/metadata-ingestion/source_docs/mode https://github.com/linkedin/datahub/blob/master/metadata-ingestion/src/datahub/ingestion/source/mode.py
m
@miniature-tiger-96062 thanks for the follow-up. While looking at this, I realized that the 
/api/v1/dataset/{pk}
endpoint in Superset does not return the 
kind
of dataset. When you query the 
/api/v1/dataset
, it will return a list and in there, you can find the 
kind
attribute. Its value is 
virtual
or 
physical
. I think this would help solving this issue, or at least improve the tagging/labeling in DataHub (whether the dataset is virtual or physical). We are also contributors to Superset and we are currently creating a ticket and a PR to add 
kind
as an attribute returned by 
/api/v1/dataset/{pk}
. I'll check out the mode source for its SQL parsing. I'm sure I can fix this, but I have quite a few things Datahub-related on the go at the moment! So I wouldn't mind if you could talk about it internally. It's not a blocker for us right now, but would be a nice to have.
b
@modern-monitor-81461 I am also interested in getting Superset virtual datasets ingested to Datahub.
We are also contributors to Superset and we are currently creating a ticket and a PR to add 
kind
as an attribute returned by 
/api/v1/dataset/{pk}
.
Did you raise this feature request? If not, I can start a new discussion now and maybe support with the PR.
m
@bland-room-3136 I'm not sure if we did the fix for 
kind
, but I can tell you that we never got around to add virtual datasets to DataHub. Somehow, we lived with it and that issue never reached the top of our list...
b
@modern-monitor-81461 Thank you for sharing the status. I checked in latest version of Superset and 
kind
is indeed is seen in 
/api/v1/dataset/{pk}
endpoint response.
Also, I realized that Datahub is not importing all virtual datasets. But it is showing the dataset name in Chart Upstream. When I go to Datasets list, I can’t see this dataset there. So this is only seen via a connected chart(and ofcourse relation to actual dataset is not made yet).
When I ingest Superset dashboards and charts, I see charts that are using physical tables and those are nicely mapped to the proper source in DataHub’s lineage.
Not sure if this behavior has changed, but now physical tables are also coming up similar to Virtual dataset above, which does not have any schema information.(not linked to postgres tables in Datahub)
3 Views
Open in Slack
PreviousNext
Powered by