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.

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`"

Cleaning up materialized views

We want your opinion

How can we improve this page?