The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

How to parametrize SWAP_PARTITIONS_BETWEEN_TABLES

avoroninavoronin
edited March 11 in Technical Solutions

I have two dates in a certain table which mark the current change set.
I want to switch partitions. The following queries did not work. What is a proper way of passing partition values to SWAP_PARTITIONS_BETWEEN_TABLES or similar functions?

SELECT SWAP_PARTITIONS_BETWEEN_TABLES('A_MART.SALES_DATA_STAGE', START_DATE, END_DATE, 'A_MART.SALES_DATA')
FROM A_TMP.START_END_DATE;

SELECT SWAP_PARTITIONS_BETWEEN_TABLES(
'A_MART.SALES_DATA_STAGE',
(SELECT START_DATE FROM A_TMP.START_END_DATE LIMIT 1),
(SELECT END_DATE FROM A_TMP.START_END_DATE LIMIT 1),
'A_MART.SALES_DATA');

Answers

  • Try this script with vsql:

    \o | vsql -a
    \a
    \t
    SELECT
      'SELECT SWAP_PARTITIONS_BETWEEN_TABLES(''A_MART.SALES_DATA_STAGE'','
    ||QUOTE_LITERAL(SELECT START_DATE FROM A_TMP.START_END_DATE LIMIT 1)
    ||','||QUOTE_LITERAL(SELECT END_DATE FROM A_TMP.START_END_DATE LIMIT 1)
    ||',''A_MART.SALES_DATA'',TRUE);'
    FROM timerange;
    
  • SWAP_PARTITIONS_BETWEEN_TABLES works with literals, and I never got it working with any expression ....

  • I have done it with SSIS expressions even before this post. Just was interested if its possible to do it without sql injections. Vertica should implement parameters and procedures. That's a long time want already.

    "DROP TABLE IF EXISTS A_TMP.START_END_DATE;
    CREATE TABLE A_TMP.START_END_DATE AS
    SELECT " + (DT_WSTR, 99999)(@[User::StartDateInt]) + " AS START_DATE, " + (DT_WSTR, 99999)(@[User::EndDateInt]) + " AS END_DATE;"

  • Parameters are possible when using vsql. And that's the way it should be, as it is a client's need, not a database platform's need.
    you can set a parameter's name on the command line - and use it in a sql command:
    $ echo 'select :foo;' | vsql -v foo="'bar'"
    ?column?
    ----------
    bar
    (1 row)

  • avoroninavoronin
    edited March 12

    marcothesane, that's certainly not the way it should be. There are various client tools, etl tools, client apps that want to access vertica. MS SQL, ORACLE, SAP BW, MySQL -- all have parameters in ADO.NET or OLEDB drivers. That Vertica should not is a strange policy that actually may limit vertica's use and might avert potential customers. Accessing DB via shell is far from the common database use. I do not come to Linix shell at all and do not want to.

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.