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

HyeontaeJu

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

mosheg

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');

marcothesane

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

HyeontaeJu

@mosheg I mean DDL for create projection

LenoyJ

If you mean using Database Designer, then something like:

--Create a Design
SELECT DESIGNER_CREATE_DESIGN('design1');

--Set DBD to generate projections incrementally (and not drop any existing projections)
SELECT DESIGNER_SET_DESIGN_TYPE('design1','INCREMENTAL');

--Set Design K-Safety
SELECT DESIGNER_SET_DESIGN_KSAFETY('design1', 1);

--Set DBD to optimize for Query Performance
SELECT DESIGNER_SET_OPTIMIZATION_OBJECTIVE('design1','QUERY');

--Set DBD to not provide Replicated Projections (change this according to your usecase)
SELECT DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS('design1', false);

--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
SELECT DESIGNER_WAIT_FOR_DESIGN ('design1');

--View the script with the Projection DDL:
SELECT DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT('design1');

 
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:
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/DatabaseDesigner/DatabaseDesignerFunctions.htm

HyeontaeJu

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

LenoyJ

Works fine for me:

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

HyeontaeJu

@LenoyJ OMG .. Thank you very much

HyeontaeJu

@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' );