You see the example JSON output when you run the Workload Insights command with the -f JSON option.
In the input directory src/test/resources/demo/queries, a file named queryA.sql
contains queryA.
-
Go to the github directory where you downloaded and built the project.
The Worload Insights (wi) script is located in this directory.
-
Run the script to get a JSON-formatted recommendation.
./wi -i queries/queryA.sql -u jdbc:hive2://localhost:10000 -f JSON
The output
is:
...
{
"recommended_views" : [ {
"name" : "materialized_view_1",
"sql" : "CREATE MATERIALIZED VIEW `materialized_view_1` AS\nSELECT `store_returns`.`sr_item_sk`, SUM(`store_returns`.`sr_net_loss`) AS `$f1`\nFROM `default`.`reason`,\n`default`.`store_returns`\nWHERE `store_returns`.`sr_reason_sk` = `reason`.`r_reason_sk` AND 'Found a better price in a store' = `reason`.`r_reason_desc`\nGROUP BY `store_returns`.`sr_item_sk`;"
} ],
"query_details" : [ {
"name" : "queryA",
"sql" : "SELECT `t0`.`sr_item_sk`, SUM(`t0`.`sr_net_loss`) AS `$f1`\nFROM (SELECT `sr_item_sk`, `sr_reason_sk`, `sr_net_loss`\nFROM `default`.`store_returns`\nWHERE `sr_reason_sk` IS NOT NULL) AS `t0`\nINNER JOIN (SELECT `r_reason_sk`\nFROM `default`.`reason`\nWHERE `r_reason_desc` = 'Found a better price in a store' AND `r_reason_sk` IS NOT NULL) AS `t2` ON `t0`.`sr_reason_sk` = `t2`.`r_reason_sk`\nGROUP BY `t0`.`sr_item_sk`",
"recommendations" : [ "materialized_view_1" ]
} ]
