You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "bbull77 (via GitHub)" <gi...@apache.org> on 2025/02/10 07:39:13 UTC
[I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
bbull77 opened a new issue, #17709:
URL: https://github.com/apache/druid/issues/17709
### Description
Using SQL "INSERT INTO EXTERN( s3()) AS CSV" as prefix part of deep-storage MSQ query, currently the query output is indeed written to S3 location, but it comes with a cost of needing assembling into one file because the MSQ output are output into multiple partition/parts in the S3 location.
It would be super-useful if there is an option to have the deep-storage MSQ to output into one final CSV file instead.
### Motivation
Please provide the following for the desired feature or change:
- A detailed description of the intended use case, if applicable
The CSV one-file-output stored on S3 can be accessed conveniently, without knowing the assembly details of multiple smaller partition sub-files.
- Rationale for why the desired feature/change would be beneficial
Deep-storage MSQ query is an important contribution to Druid as an alternative to query cold/backup data in a non-urgent timeline. Currently getting the query result back is challenging for tge user community, because each of the following methods has drawbacks:
1. Getting the result through the Druid endpoint “druid/v2/sql/statements/{queryId}/results/page=[page$]&&resultFormat=csv”, is sequential and very time-consuming (one test I did is that it takes 30min to get 1G of query result, using a curl HTTPS call running inside the same AWS region (as the S3 bucket))
2. As explained in the above feature request, using "INSERT INTO EXTERN( s3()) AS CSV". The output is distributed across many smaller files. The deep-query-caller need to know the implementation-details of how these files are named/sequenced to be able to assemble them back into one complete query result. This is neither error-proof nor productive for the Druid user community.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
Posted by "bbull77 (via GitHub)" <gi...@apache.org>.
bbull77 commented on issue #17709:
URL: https://github.com/apache/druid/issues/17709#issuecomment-2677253728
Hi @cryptoe, I tested on Druid 29.0.1, with INSERT INTO () AS CSV and SQL statement ends with a very large limit, so far when the MSQ deep-storage query is finished, I only see one CSV file with partition0.
Not sure since which Druid version, is writing to one single final CSV file in S3 (with LIMIT set in SQL) supported. Thanks for your time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
Posted by "cryptoe (via GitHub)" <gi...@apache.org>.
cryptoe commented on issue #17709:
URL: https://github.com/apache/druid/issues/17709#issuecomment-2654237317
> Getting the result through the Druid endpoint “druid/v2/sql/statements/{queryId}/results/page=[page$]&&resultFormat=csv”, is sequential and very time-consuming (one test I did is that it takes 30min to get 1G of query result, using a curl HTTPS call running inside the same AWS region (as the S3 bucket))
If you could share the flamegraph of the broker while running this, I can debug the cause of slowness.
Regarding 2, the processing engine has various tasks which are distributed. To do what you are saying means effectively having one task in the final stage since we want to preserve ordering and stuff across the tasks which is not a scalable design.
For your use case, if you want to do this, add a limit to the query. It will force the last stage to be a single stage which will result in a single csv file on s3.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm closed issue #17709: deep-storage MSQ query be able to write to one CSV file on S3
URL: https://github.com/apache/druid/issues/17709
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm commented on issue #17709:
URL: https://github.com/apache/druid/issues/17709#issuecomment-2776647745
Looks like you found a solution- good to hear.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] deep-storage MSQ query be able to write to one CSV file on S3 (druid)
Posted by "bbull77 (via GitHub)" <gi...@apache.org>.
bbull77 commented on issue #17709:
URL: https://github.com/apache/druid/issues/17709#issuecomment-2654547850
Thank you Karan very much. I will experiment with option #2.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org