Is there a way to export query results as a tsv file in SQL LAB?

[perceptron01]

Does the SQL Lab UI not support outputting query results in tsv format instead of csv?
Or is it possible to download to tsv if some options are specified in config?

[dosubot[bot]]

Hey @perceptron01! I'm here to assist you with any bugs, questions, or contributions. Let's tackle this together!

I couldn't find specific information about exporting query results as a TSV file in SQL Lab. The current implementation of the export functionality in SQL Lab of Apache Superset is primarily focused on exporting to CSV format. This is handled by the export_csv method in the SqlLabRestApi class, which uses the SqlResultExportCommand to convert query results into a CSV format using pandas ^[1][2].

There is no mention of a configuration option or plugin that allows exporting to TSV format. If you need TSV support, you might need to modify the existing export functionality to handle TSV conversion. This would involve changing the pandas CSV export logic to use a tab delimiter instead of a comma.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[perceptron01]

@dosu I want to create a PR to add a button ‘DOWNLOAD TO TSV’ to the SQL Lab. Please line up any other areas of code that need to be modified to achieve this.

[dosubot[bot]]

To add a 'DOWNLOAD TO TSV' button in SQL Lab, you'll need to modify several areas of the code:

1. Frontend Changes:

   • Add a new button for TSV download: In the ResultSetButtons component, create a new Button component similar to the existing 'Download to CSV' button. This involves setting up a new button with the appropriate structure and logic.
   • Generate TSV download URL: Implement a function similar to getExportCsvUrl to generate the URL for downloading results in TSV format. Use this function in the new button's href attribute.
   • Log the TSV download action: Define a new log action similar to LOG_ACTIONS_SQLLAB_DOWNLOAD_CSV and use it in the onClick handler of the new button.

   Example code snippet for the button:

   const getExportTsvUrl = (clientId: string) =>
     `/api/v1/sqllab/export/${clientId}/?format=tsv`;
   
   ...
   
   {canExportData && (
     <Button
       buttonSize="small"
       href={getExportTsvUrl(query.id)}
       data-test="export-tsv-button"
       onClick={() => {
         logAction(LOG_ACTIONS_SQLLAB_DOWNLOAD_TSV, {});
         if (
           limitingFactor === LimitingFactor.Dropdown &&
           limit === rowsCount
         ) {
           Modal.warning({
             title: t('Download is on the way'),
             content: t(
               'Downloading %(rows)s rows based on the LIMIT configuration. If you want the entire result set, you need to adjust the LIMIT.',
               { rows: rowsCount.toLocaleString() },
             ),
           });
         }
       }}
     >
       <i className="fa fa-file-text-o" /> {t('Download to TSV')}
     </Button>
   )}

2. Backend Changes:

   • Adapt the export_csv method: In the SqlLabRestApi class, create a new method export_tsv similar to export_csv. Modify it to handle TSV format by changing the delimiter from a comma to a tab character.

   Example adaptation:

   @expose("/export_tsv/<string:client_id>/")
   @protect()
   @statsd_metrics
   @event_logger.log_this_with_context(
       action=lambda self, *args, **kwargs: f"{self.__class__.__name__}"
       f".export_tsv",
       log_to_statsd=False,
   )
   def export_tsv(self, client_id: str) -> CsvResponse:
       result = SqlResultExportCommand(client_id=client_id).run()
   
       query, data, row_count = result["query"], result["data"], result["count"]
   
       quoted_tsv_name = parse.quote(query.name)
       response = CsvResponse(
           data, headers=generate_download_headers("tsv", quoted_tsv_name), delimiter='\t'
       )
       event_info = {
           "event_type": "data_export",
           "client_id": client_id,
           "row_count": row_count,
           "database": query.database.name,
           "catalog": query.catalog,
           "schema": query.schema,
           "sql": query.sql,
           "exported_format": "tsv",
       }
       event_rep = repr(event_info)
       logger.debug(
           "TSV exported: %s", event_rep, extra={"superset_event": event_info}
       )
       return response

These changes will enable the TSV download functionality in SQL Lab ^[1][2].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}

[perceptron01]

@dosu Thanks. On backend, export_tsv and export_csv can be quite similar methods. Rather than simply adding one more similar method, I feel that it is better to combine the parts that can be made common. If this is the case, how would it be appropriate to modify them to make them easier to maintain?

[dosubot[bot]]

To make the export_csv and export_tsv methods easier to maintain by combining common parts, you can refactor the code to abstract the shared logic. Here's how you can do it:

1. Abstract Data Export Logic: Create a helper function that handles the data export logic, taking the format as a parameter. This function will handle the retrieval of query results and the creation of the response.

2. Modify the export_csv and export_tsv Methods: Use the helper function in both methods, passing the appropriate format.

Here's an example of how you can implement this:

def export_data(self, client_id: str, format: str) -> CsvResponse:
    """Helper function to export data in the specified format."""
    result = SqlResultExportCommand(client_id=client_id).run()
    query, data, row_count = result["query"], result["data"], result["count"]

    quoted_name = parse.quote(query.name)
    delimiter = '\t' if format == 'tsv' else ','
    response = CsvResponse(
        data, headers=generate_download_headers(format, quoted_name), delimiter=delimiter
    )
    event_info = {
        "event_type": "data_export",
        "client_id": client_id,
        "row_count": row_count,
        "database": query.database.name,
        "catalog": query.catalog,
        "schema": query.schema,
        "sql": query.sql,
        "exported_format": format,
    }
    event_rep = repr(event_info)
    logger.debug(
        f"{format.upper()} exported: %s", event_rep, extra={"superset_event": event_info}
    )
    return response

@expose("/export_csv/<string:client_id>/")
@protect()
@statsd_metrics
@event_logger.log_this_with_context(
    action=lambda self, *args, **kwargs: f"{self.__class__.__name__}.export_csv",
    log_to_statsd=False,
)
def export_csv(self, client_id: str) -> CsvResponse:
    return self.export_data(client_id, 'csv')

@expose("/export_tsv/<string:client_id>/")
@protect()
@statsd_metrics
@event_logger.log_this_with_context(
    action=lambda self, *args, **kwargs: f"{self.__class__.__name__}.export_tsv",
    log_to_statsd=False,
)
def export_tsv(self, client_id: str) -> CsvResponse:
    return self.export_data(client_id, 'tsv')

This refactoring reduces code duplication and makes it easier to maintain and extend the export functionality for additional formats in the future.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other}