The row limit set for the chart was reached.

Habeeb556 commented 2 months ago

Bug description

I got an error message chart limit when creating a chart from sqllab:

The row limit set for the chart was reached. The chart may show partial data.

How to reproduce the bug

Create a chart from sqllab.

Screenshots/recordings

Superset version

4.0.1

Python version

3.11

Node version

I don't know

Browser

Chrome

Additional context

No response

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 2 months ago

To resolve the error message "The row limit set for the chart was reached. The chart may show partial data" when creating a chart from SQL Lab in Apache Superset, you need to increase the displayLimit or defaultQueryLimit in the ResultSetProps.

Here is an example of how you can adjust these limits:

const ResultSet = ({
  cache = false,
  csv = true,
  database = {},
  displayLimit = 1000, // Increase this value
  height,
  queryId,
  search = true,
  showSql = false,
  showSqlInline = false,
  visualize = true,
  defaultQueryLimit = 1000, // Increase this value
}: ResultSetProps) => {
  // ... rest of the code
};

By increasing the displayLimit and defaultQueryLimit values, you can allow more rows to be displayed in the chart, thus avoiding the error message ^[1].

To continue talking to Dosu, mention @dosu.

Habeeb556 commented 2 months ago

@dosu

How can I adjust this limit? Which file path should I use?

dosubot[bot] commented 2 months ago

To adjust the row limit in Apache Superset, you need to modify the configuration settings in the superset_config.py file and ensure the ResultSetProps in the frontend code reflects these changes.

Locate the superset_config.py file: This file is typically found in the root directory of your Superset installation or in the configuration directory.
Add or update the following configuration settings in the superset_config.py file:

# Set the maximum number of rows to display in the chart
DISPLAY_MAX_ROW = 10000  # Adjust this value as needed

# Set the default query limit
DEFAULT_SQLLAB_LIMIT = 10000  # Adjust this value as needed

Update the frontend code: Ensure that the ResultSetProps in the frontend code reflects these changes. The relevant file is superset-frontend/src/SqlLab/components/ResultSet/index.tsx.

Here is a snippet of the ResultSetProps interface:

export interface ResultSetProps {
  cache?: boolean;
  csv?: boolean;
  database?: Record<string, any>;
  displayLimit: number;
  height: number;
  queryId: string;
  search?: boolean;
  showSql?: boolean;
  showSqlInline?: boolean;
  visualize?: boolean;
  defaultQueryLimit: number;
}

Make sure that the displayLimit and defaultQueryLimit values are set appropriately in the component where ResultSetProps is used.

Restart your Superset server: After making these changes, restart your Superset server to apply the new settings.

By following these steps, you should be able to adjust the row limit and resolve the error message in Apache Superset version 4.0.1 ^[1]^[2].

To continue talking to Dosu, mention @dosu.

Habeeb556 commented 2 months ago

@dosu

It doesn't affect the chart; it impacts SQLLab directly.

dosubot[bot] commented 2 months ago

To adjust the row limit specifically for SQLLab in Apache Superset version 4.0.1, you need to modify the DISPLAY_MAX_ROW configuration setting. This setting is used to limit the number of rows returned by a SQL query in SQLLab.

Locate the superset_config.py file: This file is typically found in the root directory of your Superset installation or in the configuration directory.
Add or update the following configuration setting in the superset_config.py file:

# Set the maximum number of rows to display in SQLLab
DISPLAY_MAX_ROW = 10000  # Adjust this value as needed

Restart your Superset server: After making these changes, restart your Superset server to apply the new settings.

By setting the DISPLAY_MAX_ROW configuration, you can control the number of rows returned in SQLLab, thus avoiding the error message indicating that the row limit has been reached ^[1]^[2].

To continue talking to Dosu, mention @dosu.

Habeeb556 commented 2 months ago

Not solved, still got chart error Limit reached.

dosubot[bot] commented 2 months ago

To resolve the "Limit reached" error in Apache Superset version 4.0.1 when creating a chart from SQLLab, ensure that the following configurations are set correctly in your superset_config.py file:

Set the maximum number of rows to display in SQLLab:

DISPLAY_MAX_ROW = 10000  # Adjust this value as needed

Set the maximum number of rows returned for any analytical database query:

SQL_MAX_ROW = 100000  # Adjust this value as needed

Set the default row limit for SQL Lab queries:

DEFAULT_SQLLAB_LIMIT = 1000  # Adjust this value as needed

After updating these configurations, restart your Superset server to apply the new settings. This should help manage the row limits effectively and resolve the "Limit reached" error ^[1]^[2]^[3].

To continue talking to Dosu, mention @dosu.

Habeeb556 commented 2 months ago

Any suggestions? This is also related to the discussion in option #29411.

dosubot[bot] commented 2 months ago

To resolve the "Limit reached" error in Apache Superset version 4.0.1, ensure that the following configurations are set correctly in your superset_config.py file:

Set the maximum number of rows to display in SQLLab:

DISPLAY_MAX_ROW = 10000  # Adjust this value as needed

Set the maximum number of rows returned for any analytical database query:

SQL_MAX_ROW = 100000  # Adjust this value as needed

Set the default row limit for SQL Lab queries:

DEFAULT_SQLLAB_LIMIT = 1000  # Adjust this value as needed

Additionally, verify that these configurations are being applied correctly in the backend and frontend code:

Backend: Ensure the apply_display_max_row_limit function in superset/views/utils.py and the execute_sql_statement function in superset/sql_lab.py are using these values correctly ^[1]^[2].
Frontend: Ensure the displayLimit and defaultQueryLimit props in the ResultSet component in superset-frontend/src/SqlLab/components/ResultSet/index.tsx are set appropriately ^[3].

After making these changes, restart your Superset server to apply the new settings. This should help manage the row limits effectively and resolve the "Limit reached" error.

Additionally, you might find these similar issues and discussions helpful:

Issue #22485: Suggests modifying the superset_config_docker.py file to increase the row limit for charts.
Issue #28644: Recommends using Apache Superset version 3.1.3 or 4.0.1, as the bug was fixed in these versions.
Issue #26779: Resolved by a pull request that fixed the hardcoded row limit in the UI.
Issue #22569: Suggests updating to the latest version of Superset, as the issue has been fixed in the latest release.

These steps and resources should help you address the row limit issue in SQLLab.

To continue talking to Dosu, mention @dosu.

rusackas commented 2 months ago

The default limit in SQL Lab is 1000... did you change it if needed?

Habeeb556 commented 2 months ago

Yes, I know that, but the limitation is within the chart creation itself. I have already open limit in SQL Lab to 100k, but when creating a chart from result, I encountered the following message indicating that the 1k limit has been reached:

The row limit set for the chart was reached. The chart may show partial data.

rusackas commented 2 months ago

Can you give us a reproducible test case using one of the example datasets? That'll help me (or anyone) more able to validate that it's happening this way on our instance(s) and see if we can find a solution/workaround.

Habeeb556 commented 2 months ago

1- Run any query in SQLLAB fetch 10K rows. 2- Then click on CREATE CHART to explore the result. 3- You will get only 1K rows limit.

Additionally, I am using this method to download the result as an XLSX file from the chart. Therefore, I request a discussion #29411 to add a "Download to XLSX" feature, similar to the "Download to CSV" button.

Habeeb556 commented 2 weeks ago

Any suggestion here....

apache / superset