The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Combining multiple S3EXPORT calls into a single call

I need to export a different set of columns from the same query to different locations on S3 without running the query multiple times.
- SELECT S3EXPORT(col1, col2 USING PARAMETERS url = 'url-1') OVER () FROM (something-fixed)
- SELECT S3EXPORT(col3, col4 USING PARAMETERS url = 'url-2') OVER () FROM (something-fixed)
- SELECT S3EXPORT(col5, col6 USING PARAMETERS url = 'url-3') OVER () FROM (something-fixed)
- SELECT S3EXPORT(col7, col8 USING PARAMETERS url = 'url-4') OVER () FROM (something-fixed)
where "something-fixed" is the same query for all four and is highly complex.
Can I combine all 4 of the above queries into one query somehow so that the "something-fixed" doesn't have to be re-computed four times?
0
Best Answer
-
Jim_Knicely - Select Field - Administrator
Maybe a temp table?
CREATE TEMP TABLE something_fixed_temp ON COMMIT PRESERVE ROWS AS SELECT * FROM something-fixed; SELECT S3EXPORT(col1, col2 USING PARAMETERS url = 'url-1') OVER () FROM something_fixed_temp; SELECT S3EXPORT(col3, col4 USING PARAMETERS url = 'url-2') OVER () FROM something_fixed_temp; SELECT S3EXPORT(col5, col6 USING PARAMETERS url = 'url-3') OVER () FROM something_fixed_temp; SELECT S3EXPORT(col7, col8 USING PARAMETERS url = 'url-4') OVER () FROM something_fixed_temp; DROP TABLE something_fixed_temp;
For future reference, S3EXPORT is deprecated in Vertica 11, and replaced with the EXPORT TO DELIMITED feature!
1
Answers
@neo_i_am_the_one - BTW, I like your Vertica forum username!
@Jim_Knicely - Thank you so much for the answer! It was exactly what I needed and worked like a charm! Have a pleasant rest of your week and happy holidays
And thanks for the tip about the EXPORT TO DELIMITED feature! Appreciate it.
@Jim_Knicely - About my username - haha, thanks! That movie is one of my favorites of ALL time!