Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb


8.4 Advisor Examples

This section provides Advisor CALL statement examples that you can reference when creating your own statements.

Note

Examples may specify schemas, columns, connection IDs, and other objects that are not be present on your HeatWave instance. Such examples must be modified to work with your data.

Advisor Command-line Help

  • To view Advisor command-line help:

    CALL sys.heatwave_advisor(JSON_OBJECT("output","help"));

    The command-line help provides usage documentation for the Advisor.

Auto Encoding Examples

  • Running Auto Encoding to generate string column encoding recommendations for the tpch schema:

    CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch"),
      "auto_enc",JSON_OBJECT("mode","recommend")));
  • Running Auto Encoding with the fixed_enc option to force variable-length encoding for the tpch.CUSTOMER.C_ADDRESS column. Columns specified by the fixed_enc option are excluded from consideration by the Auto Encoding feature.

    CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch"), 
      "auto_enc",JSON_OBJECT("mode","recommend","fixed_enc", 
      JSON_OBJECT("tpch.CUSTOMER.C_ADDRESS","varlen"))));

Auto Data Placement Examples

  • Invoking Advisor without any options runs the Data Placement Advisor with the default option settings.

    CALL sys.heatwave_advisor(NULL);
  • Running Advisor with only the target_schema option runs the Data Placement Advisor on the specified schemas with the default option settings.

    CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch","employees")));
  • Running the Advisor with the data placement max_combinations and benefit_threshold parameters. For information about these options, see Auto Data Placement Syntax.

    CALL sys.heatwave_advisor(JSON_OBJECT("auto_dp",JSON_OBJECT("max_combinations",100,
      "benefit_threshold",20)));
  • The following example shows how to view the HeatWave query history by querying the performance_schema.rpd_query_stats table, and how to exclude specific queries from Data Placement Advisor analysis using the exclude_query option:

    SELECT query_id, LEFT(query_text,160) FROM performance_schema.rpd_query_stats;
    
    CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch"), 
      "exclude_query",JSON_ARRAY(1,11,12,14)));
  • This example demonstrates how to invoke the Data Placement Advisor with options specified in a variable:

    SET @options = JSON_OBJECT(
                "target_schema", JSON_ARRAY("analytics45","sample_schema"),
                "exclude_query", JSON_ARRAY(12,24),
                "auto_dp", JSON_OBJECT(
                       "benefit_threshold", 12.5,
                       "max_combinations", 100
    ));
    
    CALL sys.heatwave_advisor( @options );
  • This example demonstrates how to invoke Advisor in silent output mode, which is useful if the output is consumed by a script, for example. Auto Data Placement is run by default if no option such as auto_enc or query_insights is specified.

    CALL sys.heatwave_advisor(JSON_OBJECT("output","silent"));

Query Insights Examples

  • To view runtime data for all queries in the HeatWave query history for a particular schema:

    CALL sys.heatwave_advisor(JSON_OBJECT("target_schema",JSON_ARRAY("tpch"),
      "query_insights",TRUE));
  • To view runtime data for queries issued by the current session:

    CALL sys.heatwave_advisor(JSON_OBJECT("query_insights",TRUE, 
      "query_session_id", JSON_ARRAY(connection_id())));
  • To view runtime data for queries issued by a particular session:

    CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE, 
      "query_session_id", JSON_ARRAY(8)));
  • This example demonstrates how to invoke the Query Insights Advisor in silent output mode, which is useful if the output is consumed by a script, for example.

    CALL sys.heatwave_advisor(JSON_OBJECT("query_insights",TRUE,"output","silent"));