Saving dataset with a stored procedure.

[praveer38]

How can i save a dataset that has a proc execution for the query? The database server being used has version: Azure SQL Edge 2.0.0 SQL Engine build: 16.0.5100.7245

[dosubot[bot]]

To save a dataset in Superset that includes a stored procedure execution, follow these steps:

1. Open the Save Dataset Modal: In the Superset UI, navigate to the SQL Lab and run your SQL query that includes the stored procedure. Click on the "Save" button to open the Save Dataset Modal.

2. Fill in Dataset Details: In the modal, choose to save the dataset as a new dataset or overwrite an existing one. Provide a name for the dataset and other necessary details.

3. Create or Overwrite Dataset:

   • Save as New: If saving as a new dataset, fill in the dataset name and other details, then click the "Save" button.
   • Overwrite Existing: If overwriting an existing dataset, select the dataset from the dropdown, confirm the overwrite action, and then click the "Overwrite" button.

4. Include Stored Procedure in SQL: When saving the dataset, ensure that your SQL query, which includes the stored procedure, is correctly placed in the sql parameter.

Here is a relevant code snippet that shows how the SQL query is handled during the dataset creation process:

const handleSaveInDataset = () => {
    setLoading(true);
    const selectedColumns = datasource?.columns ?? [];

    let templateParams;
    if (isString(datasource?.templateParams)) {
      const p = JSON.parse(datasource.templateParams);
      if (p._filters) {
        delete p._filters;
        templateParams = JSON.stringify(p);
      }
    }

    dispatch(
      createDatasource({
        sql: datasource.sql,
        dbId: datasource.dbId || datasource?.database?.id,
        catalog: datasource?.catalog,
        schema: datasource?.schema,
        templateParams,
        datasourceName: datasetName,
      }),
    )
      .then((data: { id: number }) =>
        postFormData(data.id, 'table', {
          ...formDataWithDefaults,
          datasource: `${data.id}__table`,
          ...(defaultVizType === 'table' && {
            all_columns: selectedColumns.map(column => column.column_name),
          }),
        }),
      )
      .then((key: string) => {
        setLoading(false);
        const url = mountExploreUrl(null, {
          [URL_PARAMS.formDataKey.name]: key,
        });
        createWindow(url);
        setDatasetName(getDefaultDatasetName());
        onHide();
      })
      .catch(() => {
        setLoading(false);
        addDangerToast(t('An error occurred saving dataset'));
      });
  };

By following these steps, you can save a dataset in Superset that includes the execution of a stored procedure ^[1][2].

To continue talking to Dosu, mention @dosu.

[dosubot[bot]]

No, you cannot enable saving queries that have more than just SELECT statements in Superset ^[1][2].

To continue talking to Dosu, mention @dosu.

[praveer38]

how can i call a procedure from superset and save the dataset if the sql version is Azure SQL Edge 2.0.0 SQL Engine build: 16.0.5100.7245

[dosubot[bot]]

To call a procedure from Superset and save the dataset when using Azure SQL Edge 2.0.0 SQL Engine build: 16.0.5100.7245, you can use the execute_sql_statements function provided in the superset/sql_lab.py file. Here is an example of how you can do this:

1. Define the SQL statement to call the procedure:

   EXEC your_procedure_name @param1 = value1, @param2 = value2;

2. Use the execute_sql_statements function:

   query_id = 1  # Replace with your actual query ID
   rendered_query = "EXEC your_procedure_name @param1 = value1, @param2 = value2;"
   return_results = True
   store_results = True
   start_time = None
   expand_data = False
   log_params = None
   
   result = execute_sql_statements(
       query_id=query_id,
       rendered_query=rendered_query,
       return_results=return_results,
       store_results=store_results,
       start_time=start_time,
       expand_data=expand_data,
       log_params=log_params,
   )
   
   if result:
       print("Query executed successfully. Results saved.")
   else:
       print("Query execution failed.")

This code will execute the stored procedure and save the results if the store_results flag is set to True. The execute_sql_statements function handles the execution and storage of the results ^[1].

To continue talking to Dosu, mention @dosu.

[praveer38]

Why can't i use openquery?

[dosubot[bot]]

You can't use OPENQUERY in Superset because it is not supported by the database engine specifications in Superset. The document lists various features and capabilities of different database engines, but there is no mention of support for OPENQUERY ^[1].

To continue talking to Dosu, mention @dosu.