Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

query in drop_partitions

Hello. I have a table divided into partitions by date. I want to delete the oldest partition (I calculate the value of this partition so:
SELECT MAX (CAST(time AS DATE)) FROM mytable;

Now I want to delete this partition, I try like this:
SELECT DROP_PARTITIONS ('mytable','SELECT MAX (CAST(time AS DATE)) FROM mytable','SELECT MAX (CAST(time AS DATE)) FROM mytable');

But I get an error:
Failed to convert to internal form: Invalid input syntax for date
ERROR 3721: Invalid partition key

How can I use query in DROP_PARTITIONS?

Tagged:

Best Answer

Answers

  • @maks, are you selecting the partition key column for the drop? (from the error)

    What output you get for your select SQL?

  • Bryan_HBryan_H Employee

    Try this:
    SELECT MAX(DATE(time)) FROM mytable;
    This will simplify the CAST/CONVERT requirement. Partition keys are stored as VARCHAR so another cast step may be needed.

  • What output you get for your select SQL?

    I get date value, for example, 2020-05-23

  • Try this:
    SELECT MAX(DATE(time)) FROM mytable;

    I try but I get the same error Failed to convert to internal form: Invalid input syntax for date

  • Ensure that you are using the right partition key format:
    Try:

    SELECT DISTINCT
      partition_key
    FROM partitions
    JOIN projections USING(projection_id)
    WHERE table_schema='my_schema'  AND anchor_table_name= 'mytable'
    
     partition_key 
    ---------------
     2019-01-01
     2019-01-02
     2019-01-03
     2019-01-04
     2019-01-05
     2019-01-06
     2019-01-07
     2019-01-08
     2019-01-09
     2019-01-10
    
    

    You'll see the format of the keys.
    But:

    SELECT DROP_PARTITIONS('flex.drp_p'                                                 
                          ,(SELECT MIN(prk)::VARCHAR(10) FROM flex.drp_p)
                          ,(SELECT MIN(prk)::VARCHAR(10) FROM flex.drp_p)
                          );
    ERROR 5301:  Unsupported use of sub-queries.
    

    So, SQL generating SQL:

    \t
    \a
    SELECT
      'SELECT DROP_PARTITIONS('||CHR(10)||
      '  ''flex.drp_p'''||CHR(10)||
      ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
      ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
      ');'
    FROM flex.drp_p;
    

    Result:

    SELECT DROP_PARTITIONS(
      'flex.drp_p'
    , '2019-01-01'
    , '2019-01-01'
    );
    
  • Once generated that statement (to file), you just run that file.

  • marcothesane, I'm very sorry but I don't understand how to do from your query real query that delete partitions

  • Here's my full example:
    The table:

    DROP TABLE IF EXISTS drp_p;
    CREATE TABLE IF NOT EXISTS drp_p (
      prk DATE NOT NULL
    , des VARCHAR(32)
    , pr  NUMERIC(5,2)
    ) 
    PARTITION BY prk
    ;
    INSERT INTO drp_p
              SELECT DATE '2019-01-01','nm_10', 1.25 
    UNION ALL SELECT DATE '2019-01-01','2ndnm_01', 1.75
    UNION ALL SELECT DATE '2019-01-02','nm_11', 1.25 
    UNION ALL SELECT DATE '2019-01-02','2ndnm_02', 1.75
    UNION ALL SELECT DATE '2019-01-03','nm_12', 1.25
    UNION ALL SELECT DATE '2019-01-03','2ndnm_03', 1.75
    UNION ALL SELECT DATE '2019-01-04','nm_13', 1.25
    UNION ALL SELECT DATE '2019-01-04','2ndnm_04', 1.75
    UNION ALL SELECT DATE '2019-01-05','nm_14', 1.25
    UNION ALL SELECT DATE '2019-01-05','2ndnm_05', 1.75
    UNION ALL SELECT DATE '2019-01-06','nm_15', 1.25
    UNION ALL SELECT DATE '2019-01-06','2ndnm_06', 1.75
    UNION ALL SELECT DATE '2019-01-07','nm_16', 1.25
    UNION ALL SELECT DATE '2019-01-07','2ndnm_07', 1.75
    UNION ALL SELECT DATE '2019-01-08','nm_17', 1.25
    UNION ALL SELECT DATE '2019-01-08','2ndnm_08', 1.75
    UNION ALL SELECT DATE '2019-01-09','nm_18', 1.25
    UNION ALL SELECT DATE '2019-01-09','2ndnm_09', 1.75
    UNION ALL SELECT DATE '2019-01-10','nm_19', 1.25
    UNION ALL SELECT DATE '2019-01-10','2ndnm_10', 1.75
    ;
    COMMIT;
    

    Here is a query to find out all partition keys of the table drp_p :

    SELECT DISTINCT
      partition_key
    FROM partitions
    JOIN projections USING(projection_id)
    WHERE  anchor_table_name= 'drp_p'
    ORDER BY partition_key
    ;
    partition_key 
    --------------
    2019-01-01
    2019-01-02
    2019-01-03
    2019-01-04
    2019-01-05
    2019-01-06
    2019-01-07
    2019-01-08
    2019-01-09
    2019-01-10
    

    The table is partitioned by prk, which is a DATE. The oldest partition key is 2019-01-01. This would need to be dropped.

    You cannot use subselects in a call to DROP_PARTITIONS() :, as I said in my previous post.

    The solution is to generate a new SQL script, while querying for the oldest partition key.

    -- find the oldest partition key in the table, and embed that into a working SQL statement
    \t
    -- tuples only in output
    \a
    -- do not align the output
    SELECT
      'SELECT DROP_PARTITIONS('||CHR(10)||
      '  ''flex.drp_p'''||CHR(10)||
      ', '||Q``UOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
      ', '||QUOTE_LITERAL(MIN(prk)::VARCHAR(10))||CHR(10)||
      ');'
    FROM flex.drp_p;
    

    If the script above is in a file called generate.sql, you can generate it like so:
    $ vsql -Xqf generate.sql > doit.sql
    Find the output of the query above here below.

    SELECT DROP_PARTITIONS(
      'flex.drp_p'
    , '2019-01-01'
    , '2019-01-01'
    );
    

    Then, go: $ vsql -f doit.sql , to actually drop the partition.

  • marcothesane, thanks a lot. In file doit.sql I have additional strings: first string ?column? and last string (1 row)
    When I try to run doit.sql: vsql -f doit.sql
    I get an error:
    ERROR 4856: Syntax error at or near "?" at character 1 LINE 1: ?column?
    ERROR 4856: Syntax error at or near "1" at character 2 LINE 1: (1 row)

    If I delete this additional string manually the script run correct
    Are these additional strings OK? (I must delete them, for example, with bash/python or can they be deleted somehow in the script generate.sql?

  • You did not add the lines \t and \a to your generator script. They would have removed the ?column? line and the (1 row) line from the report.

  • marcothesane, thanks a lot!!!

Leave a Comment

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

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