The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Is it possible to get a recommended DDL by submitting query transaction_id?

HyeontaeJuHyeontaeJu Vertica Customer

Is it possible to get a recommended DDL by submitting query transaction_id?


  • moshegmosheg Vertica Employee Administrator
    edited September 2020

    Please elaborate, DDL for what?
    For example, to see the query itself do:
    SELECT request FROM v_monitor.query_requests WHERE transaction_id=45035996273704967 AND statement_id=1;
    To see the create table DDL for a specific table (and its projections) from the query:
    SELECT EXPORT_OBJECTS('New_file_name_for_the_DDL_output','schema_name.table_name','true');

  • marcothesanemarcothesane - Select Field - Administrator

    If you mean to detect a DDL for a not yet existing table that would match a given SELECT, then no.
    Workaround is:

    • run a CREATE TABLE throw_me_away AS SELECT * FROM (<your fullselect) foo WHERE FALSE; . Then, run SELECT EXPORT_OBJECTS('','throw_me_away',FALSE) to get the DDL, and finally, DROP TABLE throw_me_away; .
  • HyeontaeJuHyeontaeJu Vertica Customer

    @mosheg I mean DDL for create projection

  • LenoyJLenoyJ - Select Field - Employee
    edited September 2020

    If you mean using Database Designer, then something like:

    --Create a Design
    --Set DBD to generate projections incrementally (and not drop any existing projections)
    --Set Design K-Safety
    --Set DBD to optimize for Query Performance
    --Set DBD to not provide Replicated Projections (change this according to your usecase)
    --Add your schema/tables here:
    SELECT DESIGNER_ADD_DESIGN_TABLES('design1', 'public.*','false');
    --Put the Transaction ID of your query here
    SELECT DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS ('design1', 'SELECT request as query_text FROM query_requests where transaction_id = 49539595901123186;');
    --Run DBD & generate Scripts. The following does not deploy the scripts.
    SELECT DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY ( 'design1',  '/tmp/design1_projections.sql', '/tmp/design1_deploy.sql', 'true', 'false', 'false', 'false' );
    --Wait for DBD to complete
    --View the script with the Projection DDL:

    Provided that query_requests system table still has the transaction id (this is based on your retention policy)
    Refer the following doc for details on each of the above functions:

  • HyeontaeJuHyeontaeJu Vertica Customer
    edited September 2020

    @LenoyJ The error message occured.. "Design type INCREMENT is invalid" when i was using the query SELECT DESIGNER_SET_DESIGN_TYPE('design1','INCREMENTAL');

  • LenoyJLenoyJ - Select Field - Employee

    Works fine for me:

    Make sure it's "INCREMENTAL", not "INCREMENT"

  • HyeontaeJuHyeontaeJu Vertica Customer

    @LenoyJ OMG .. Thank you very much

  • HyeontaeJuHyeontaeJu Vertica Customer

    @LenoyJ then do i need to create sql file( design1_projections.sql, design1_deploy.sql)for this script??
    SELECT DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY ( 'design1', '/tmp/design1_projections.sql', '/tmp/design1_deploy.sql', 'true', 'false', 'false', 'false' );

Leave a Comment

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