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.
You get information about unused materialized views associated with a single or
multiple queries, as shown in the following command:
./wi -i “demo/queries/all/queryA.sql, demo/queries/all/queryB.sql” -u jdbc:hive2://localhost:10000 -n admin -p mypassword -d
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.
-
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.json
Output
is:
“materialized_view_2”
“materialized_view_9”
-
Read the commands for dropping the unused materialized views.
Output is:
"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`"
