Combining multiple S3EXPORT calls into a single call

neo_i_am_the_one

I need to export a different set of columns from the same query to different locations on S3 without running the query multiple times.

1. SELECT S3EXPORT(col1, col2 USING PARAMETERS url = 'url-1') OVER () FROM (something-fixed)
2. SELECT S3EXPORT(col3, col4 USING PARAMETERS url = 'url-2') OVER () FROM (something-fixed)
3. SELECT S3EXPORT(col5, col6 USING PARAMETERS url = 'url-3') OVER () FROM (something-fixed)
4. 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?

Jim_Knicely

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!

Jim_Knicely

@neo_i_am_the_one - BTW, I like your Vertica forum username!

neo_i_am_the_one

@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.

neo_i_am_the_one

@Jim_Knicely - About my username - haha, thanks! That movie is one of my favorites of ALL time!