Migrating analytical views from one cluster / environment to another

Unlike visuals & dashboards, analytical views are database objects that are tightly coupled with source system. Currently we do not support exporting & importing them through the UI.

Instead you need to perform the below steps via a script / manually.

Analytical views - Overall Steps

  1. Login to arcadia engine through beeline in the s​ource system
  2. Run ‘show extended analytical views’ to get a list of all analytical views.
  3. Choose the analytical views you want to migrate.
  4. Run ‘describe formatted’ on the analytical view name to pick up the partition columns
  5. In the t​arget system​login to arcadia engine through beeline. Run “create analytical view” command using the definitions obtained in steps 3 & 4.
  6. Run “refresh analytical view” on the newly created views in the target system

Step 1) Assuming we are already on the source system:

Step 2)

[localhost:31050] use tvdata;
[localhost:31050] show extended all analytical views;


Let’s say we wanted tv_viewing_data_v1, so note down the select statement highlighted in green above.

Step 4) Next look at the describe statement or create table for the ​tv_viewing_data_av_1​and note down any partitions used as highlighted in green.

[localhost:31050] show create table tv_viewing_data_av_1;


Step 5) Combining 3 & 4, here’s the final create statement we want to run in target system . This would ideally be run through beeline to arcengine similar to Step 1 but in the target system.

CREATE ANALYTICAL VIEW tv_viewing_data_av_1 PARTITIONED BY (datestring)

(SELECT hour((TA_0.ts)) `_c0`, TA_0.channel channel,
TA_0.program program, sum(1) `_c3`,
datestring datestring 

FROM tvdata.tv_viewing_dataTA_0 GROUP BY 1, 2, 3, 5)

Step 6) To populate the analytical view with data run the refresh command:

refresh analytical view tv_viewing_data_av_1
1 Like