Cleaning up materialized views
You can delete materialized views, not used by your workload. You provide JDBC information about the queries associated with the materialized views using Workload Insights (wi) script and then use command-line JSON processor jq to delete the unused materialized views.
In the following procedure, you delete unused materialized views. First, you get and store information about the unused materialized views in a record, next you use jq to read the record of deleted materialized views, and finally you drop the materialized views.
./wi -i “demo/queries/all/queryA.sql, demo/queries/all/queryB.sql” -u jdbc:hive2://localhost:10000 -n admin -p mypassword -d
Delete materialized views associated with query03.sql from being used by your workload and store the deletion record in a file.
./wi -i "queries/queryA.sql, queries/queryAx19.sql, queries/queryB.sql" -u jdbc:hive2://localhost:10000 -n admin -p mypassword -f JSON -o out_dir -d
Read the deletion record.
jq ‘.unused_views.name’ < out_dir/DELETE.jsonOutput is:
Read the commands for dropping the unused materialized views.
"DROP MATERIALIZED VIEW materialized_view_2;" "DROP MATERIALIZED VIEW materialized_view_9;"
Drop the materialized views using the commands above.
beeline -u jdbc:hive2://localhost:10000 -n admin -p mypassword -e "`jq -r ‘.unused_views.sql’ < out_dir/DELETE.json`"