Updating Analytical Views when base table has been moved to a different database

Let’s take a scenario where we have a table that was mistakenly created in the default database and needs to be moved to a better location (i.e. sales)

ALTER TABLE default.superstore_sales RENAME TO sales.superstore_sales;

Before or after moving your table you might have noticed here were some Analytical Views (i.e default.av_1_ds_1514) associated to the original base table.

Once you’ve moved the table, that Analytical View loses its association to the base table since there’s no mechanism for updating the metadata of the Analytical View after the move. To illustrate this issue, take a look at this example Analytical View and its TBLPROPERTIES where the base table association is specified.

'arcadia.aggregateformatversion'='V2', 'arcadia.allobjects'='`default`.superstore_sales', 
'arcadia.basetables'='`default`.superstore_sales', 'arcadia.crcs'='0x6906754b', 
'arcadia.createquery'='SELECT order_date order_date, sum_no_finalize(sales) `_c1` FROM 
`default`.superstore_sales GROUP BY order_date', 'arcadia.fullsig'='0x6906754b', 
'arcadia.isanalyticalview'='true', 'arcadia.lastattemptedrefresh'='Oct 16 01:11:38 UTC 2019', 
'arcadia.lastsuccesfulrefresh'='Oct 16 01:11:38 UTC 2019', 'arcadia.ndvprecision'='14', 
'arcadia.state'='USABLE', 'arcadia.stepsize'='86400000000', 
shipping_cost.DOUBLE discount.DOUBLE product_sub_category.STRING unit_price.DOUBLE 
customer_segment.STRING product_name.STRING order_date_string.TIMESTAMP sales.DOUBLE 
order_date.TIMESTAMP order_priority.STRING product_container.STRING province.STRING 
ship_mode.STRING order_quantity.BIGINT customer_name.STRING row_id.BIGINT region.STRING 
order_id.BIGINT profit.DOUBLE product_category.STRING ship_date.STRING', 
'arcadia.tablerefs'='`default`.superstore_sales', 'numRows'='1418', 'totalSize'='19420')

To workaround this issue, you can update the TBLPROPERTIES of the Analytical View to specify the new base table location (’sales.superstore_sales):

ALTER TABLE default.av_1_ds_1514
SET TBLPROPERTIES('arcadia.allobjects'='`sales`.superstore_sales',
'arcadia.createquery'='SELECT order_date order_date, sum_no_finalize(sales) `_c1` FROM `sales`.superstore_sales GROUP BY order_date',

After executing this, then make sure to also Refresh the Analytical View afterwards:

REFRESH ANALYTICAL VIEW default.av_1_ds_1514;

Additionally, you can also move the Analytical View to the new database (sales), with these 2 steps:

  1. ALTER TABLE default.av_1_ds_1514 RENAME TO sales.av_1_ds_1514;

  2. REFRESH ANALYTICAL VIEW sales.av_1_ds_1514;

For more information on renaming or moving Analytical Views, check out this post.

1 Like