dosubot[bot]
commented
2 months ago Hey @xiaoshan1213! 👋 I'm here to help you with bug fixes, questions, and becoming a contributor. Let's squash some bugs together!
To implement the proposed changes in the SIP, you can follow these steps:
1. Modify the /api/v1/saved_query/ POST Function
Update the request parameters to include the results_id field. Here is an example of how to modify the post_data dictionary:
post_data = {
"schema": "schema1",
"label": "label1",
"description": "some description",
"sql": "SELECT col1, col2 from table1",
"db_id": example_db.id,
"results_id": some_results_id, # Add this line
}2. Modify the /api/v1/saved_query/{savedQueryId} GET Function
Update the response parameters to include the results_id field. Here is an example of how to modify the expected result:
expected_result = {
"id": saved_query.id,
"catalog": None,
"database": {"id": saved_query.database.id, "database_name": "examples"},
"description": "cool description",
"changed_by": None,
"changed_on_delta_humanized": "now",
"created_by": {
"first_name": saved_query.created_by.first_name,
"id": saved_query.created_by.id,
"last_name": saved_query.created_by.last_name,
},
"sql": "SELECT col1, col2 from table1",
"sql_tables": [{"catalog": None, "schema": None, "table": "table1"}],
"schema": "schema1",
"label": "label1",
"template_parameters": None,
"results_id": saved_query.results_id, # Add this line
}
data = json.loads(rv.data.decode("utf-8"))
for key, value in data["result"].items():
if key != "changed_on":
assert value == expected_result[key]3. Modify the Saved Query Metadata Database
Add a new results_id field to the saved_query table. Here is an Alembic migration script to achieve this:
from alembic import op
import sqlalchemy as sa
# Add a new column 'results_id' to the 'saved_query' table
def upgrade():
op.add_column('saved_query', sa.Column('results_id', sa.Integer, nullable=True))
# Remove the 'results_id' column from the 'saved_query' table
def downgrade():
op.drop_column('saved_query', 'results_id')References
- Modifying the
/api/v1/saved_query/ POSTfunction to includeresults_id[1]. - Modifying the
/api/v1/saved_query/{savedQueryId} GETfunction to includeresults_id[1]. - Adding a new
results_idfield to thesaved_querytable [2][3].
These changes will ensure that the query results are saved and associated with the sharable link, allowing users to view the results without rerunning the query.
To continue talking to Dosu, mention @dosu.
rusackas
xiaoshan1213
michael-s-molina
Please make sure you are familiar with the SIP process documented here. The SIP will be numbered by a committer upon acceptance.
[SIP] Proposal for Save SQL lab query result and create sharable link
Motivation
Currently, Superset has the function to save the query and create a sharable link, but the query results is not associated with the sharable link, whoever open the sharable link need to rerun the query to get results. It is a waste of compute power and time for our customers. We are proposing to save query result along when user save the query, and associate the result along with the sharable link so whoever open the link can see the result.
Proposed Change
New or Changed Public Interfaces
New dependencies
No
Migration Plan and Compatibility
the metadata db will add a new field results_id, we need to migrate existing db records to include a empty string for this field.
Rejected Alternatives
Another approach not rejected but list here, we can create a new API to save query results and new API to create sharable link to share results so as to not modify the existing APIs.