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 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!