Fatal error when creating a chart from a saved_query via API call

imtiaznizami commented 1 month ago

Bug description

This is a repeat of #23189. I am unable to create a chart using saved_query following the superset API. It responds with Fatal error instead. (superset version 4.0.2 using docker compose)

How to reproduce the bug

Create a saved_query (query is based on my setup with valid db_id 2 and a table named car)

curl --location 'http://localhost:8088/api/v1/saved_query/' \
--header 'Authorization: Bearer eyJ...' \
--header 'X-CSRFToken: ImQ...' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: .eJ...' \
--data '{
  "db_id": 2,
  "label": "saved_query",
  "schema": "public",
  "sql": "SELECT * from car"
}
'

This gives back

{
    "id": 11,
    "result": {
        "db_id": 2,
        "description": null,
        "label": "saved_query",
        "schema": "public",
        "sql": "SELECT * from car",
        "template_parameters": null
    }
}

Create the chart

curl --location 'http://localhost:8088/api/v1/chart/' \
--header 'Authorization: Bearer eyJ...' \
--header 'X-CSRFToken: ImQ...' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: .eJ...' \
--data '{
    "datasource_id": 11,
    "datasource_type": "saved_query",
    "slice_name": "some_name",
    "viz_type": "table"
}
'

This gives back

{
    "message": "Fatal error"
}

Expected results Curl command to complete successfully with the creation of the chart from the saved query.

Actual results Curl result is {"message" : "Fatal error"}

superset app stacktrace:

superset_app          | 2024-07-25 06:15:09,186:DEBUG:superset.stats_logger:[stats_logger] (incr) SavedQueryRestApi.post.success
superset_app          | 2024-07-25 06:15:09,186:DEBUG:superset.stats_logger:[stats_logger] (timing) SavedQueryRestApi.post.time | 21.099509991472587 
superset_app          | 172.26.0.1 - - [25/Jul/2024:06:15:09 +0000] "POST /api/v1/saved_query/ HTTP/1.1" 201 191 "-" "PostmanRuntime/7.40.0"
superset_app          | 2024-07-25 06:15:32,776:DEBUG:superset.stats_logger:[stats_logger] (incr) health
superset_app          | 127.0.0.1 - - [25/Jul/2024:06:15:32 +0000] "GET /health HTTP/1.1" 200 2 "-" "curl/7.88.1"
superset_app          | 2024-07-25 06:15:47,916:DEBUG:superset.stats_logger:[stats_logger] (incr) ChartRestApi.post.error
superset_app          | 2024-07-25 06:15:47,917:ERROR:flask_appbuilder.api:'SavedQuery' object has no attribute 'name'
superset_app          | Traceback (most recent call last):
superset_app          |   File "/usr/local/lib/python3.10/site-packages/flask_appbuilder/api/__init__.py", line 110, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 127, in wraps
superset_app          |     raise ex
superset_app          |   File "/app/superset/views/base_api.py", line 121, in wraps
superset_app          |     duration, response = time_function(f, self, *args, **kwargs)
superset_app          |   File "/app/superset/utils/core.py", line 1470, in time_function
superset_app          |     response = func(*args, **kwargs)
superset_app          |   File "/app/superset/utils/log.py", line 255, in wrapper
superset_app          |     value = f(*args, **kwargs)
superset_app          |   File "/app/superset/views/base_api.py", line 93, in wraps
superset_app          |     return f(self, *args, **kwargs)
superset_app          |   File "/app/superset/charts/api.py", line 329, in post
superset_app          |     new_model = CreateChartCommand(item).run()
superset_app          |   File "/app/superset/commands/chart/create.py", line 46, in run
superset_app          |     self.validate()
superset_app          |   File "/app/superset/commands/chart/create.py", line 65, in validate
superset_app          |     self._properties["datasource_name"] = datasource.name
superset_app          | AttributeError: 'SavedQuery' object has no attribute 'name'
superset_app          | 172.26.0.1 - - [25/Jul/2024:06:15:47 +0000] "POST /api/v1/chart/ HTTP/1.1" 500 31 "-" "PostmanRuntime/7.40.0"

Also note that in #23189 the answer by ShaliniIruvuru (answer) mentions query instead of saved_query in the chart creation JSON (which should be a different object).

Screenshots/recordings

No response

Superset version

4.0.2

Python version

I don't know

Node version

I don't know

Browser

Chrome

Additional context

Used the following command to create superset container:

export TAG=4.0.2 && docker compose -f docker-compose-image-tag.yml up

Checklist

[X] I have searched Superset docs and Slack and didn't find a solution to my problem.
[X] I have searched the GitHub issue tracker and didn't find a similar bug report.
[X] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

dosubot[bot] commented 1 month ago

Hello, @imtiaznizami! I'm here to help you with any bugs, questions, or contributions you have for the repository. Let me know how I can assist you!

To resolve the fatal error when trying to create a chart from a saved_query via an API call in Superset version 4.0.2 using Docker Compose, you should apply the fix provided in the pull request #27644. This fix provides more inclusive error handling for saved queries.

Here is the relevant change from the pull request:

- [#27644](https://github.com/apache/superset/pull/27644) fix: Provide more inclusive error handling for saved queries (@john-bodley)

Ensure that your Superset instance includes this fix by updating to the latest version that includes this pull request or manually applying the changes from the pull request to your local instance ^[1].

Steps to Update Superset to Include the Fix

Backup Your Data: Before performing any upgrade, ensure you have a backup of your current Superset data and configuration.
Update Superset:
- If you are using pip, you can update Superset by running:
```
pip install apache-superset==4.0.0
```
- If you are using Docker, update your docker-compose.yml to use the new version:
```
version: '3.1'
services:
superset:
 image: apache/superset:4.0.0
 ...
```
  Then, pull the new image and restart the services:
```
docker-compose pull
docker-compose up -d
```
Run Database Migrations: After updating the Superset version, you need to run the database migrations to ensure your database schema is up-to-date:
```
superset db upgrade
```
Restart Superset: Finally, restart the Superset service to apply the changes:
```
superset run
```

By following these steps, you will update Superset to version 4.0.0, which includes the fix from pull request #27644 ^[1].

To continue talking to Dosu, mention @dosu.

imtiaznizami commented 1 month ago

@dosu As mentioned in my original post, I am using 4.0.2 which is more recent than 4.0.0 suggested by you. Also, the issue you referenced has 4.0.2 label as well.

dosubot[bot] commented 1 month ago

To resolve the fatal error when trying to create a chart from a saved_query via an API call in Superset version 4.0.2, you should use datasource_type as table instead of saved_query. This workaround has been suggested in a similar issue and should help you avoid the error ^[1].

Here is the modified curl command for creating the chart:

curl --location 'http://localhost:8088/api/v1/chart/' \
--header 'Authorization: Bearer eyJ...' \
--header 'X-CSRFToken: ImQ...' \
--header 'Accept: application/json' \
--header 'Content-Type: application/json' \
--header 'Cookie: .eJ...' \
--data '{
    "datasource_id": 11,
    "datasource_type": "table",
    "slice_name": "some_name",
    "viz_type": "table"
}'

This should help you create the chart successfully.

To continue talking to Dosu, mention @dosu.

imtiaznizami commented 1 month ago

@dosu The data-source table should have completely different set of IDs than data-source saved_queries.

However, I tried your suggestion, and the chart was created with an entirely different set of columns (which compliments what I wrote above).

dosubot[bot] commented 1 month ago

To resolve the issue of creating a chart from a saved_query via an API call in Superset version 4.0.2, you need to ensure that the backend correctly handles saved_query objects. The error you're encountering ('SavedQuery' object has no attribute 'name') indicates that the SavedQuery object is missing the name attribute, which is expected by the CreateChartCommand.

Here are the steps to modify the backend to support saved_query:

Modify the CreateChartCommand to handle saved_query: Update the validate method in CreateChartCommand to correctly handle saved_query objects. Specifically, you need to extract the necessary information from the SavedQuery object.

def validate(self) -> None:
   exceptions = []
   datasource_type = self._properties["datasource_type"]
   datasource_id = self._properties["datasource_id"]
   dashboard_ids = self._properties.get("dashboards", [])
   owner_ids: Optional[list[int]] = self._properties.get("owners")

   # Validate/Populate datasource
   try:
       datasource = get_datasource_by_id(datasource_id, datasource_type)
       if datasource_type == "saved_query":
           self._properties["datasource_name"] = datasource.label
       else:
           self._properties["datasource_name"] = datasource.name
   except ValidationError as ex:
       exceptions.append(ex)

   # Validate/Populate dashboards
   dashboards = DashboardDAO.find_by_ids(dashboard_ids)
   if len(dashboards) != len(dashboard_ids):
       exceptions.append(DashboardsNotFoundValidationError())
   for dash in dashboards:
       if not security_manager.is_owner(dash):
           raise DashboardsForbiddenError()
   self._properties["dashboards"] = dashboards

   try:
       owners = self.populate_owners(owner_ids)
       self._properties["owners"] = owners
   except ValidationError as ex:
       exceptions.append(ex)
   if exceptions:
       raise ChartInvalidError(exceptions=exceptions)

Ensure get_datasource_by_id supports saved_query: The get_datasource_by_id function already supports saved_query as a datasource type, so no changes are needed here ^[1].
Update the frontend if necessary: Ensure that the frontend correctly handles the saved_query type when creating charts. This involves making sure that the fetchSlices function and parseResult function correctly process saved_query objects ^[2].

By making these changes, you should be able to create charts from saved_query objects without encountering the fatal error.

To continue talking to Dosu, mention @dosu.

imtiaznizami commented 1 month ago

@dosu This did not work. I am getting the same error. I think the error is happening at SQLAlchemy level.

apache / superset