How to parametrize SWAP_PARTITIONS_BETWEEN_TABLES

avoroninavoronin
edited March 2020 in General Discussion

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

  • marcothesanemarcothesane - Select Field - Administrator

    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;
    
  • marcothesanemarcothesane - Select Field - Administrator

    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;"

  • marcothesanemarcothesane - Select Field - Administrator

    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 2020

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file