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
- Login to arcadia engine through beeline in the source system
- Run ‘show extended analytical views’ to get a list of all analytical views.
- Choose the analytical views you want to migrate.
- Run ‘describe formatted’ on the analytical view name to pick up the partition columns
- In the target systemlogin to arcadia engine through beeline. Run “create analytical view” command using the definitions obtained in steps 3 & 4.
- Run “refresh analytical view” on the newly created views in the target system
Step 1) Assuming we are already on the source system:
[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_1and 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) STORED AS PARQUET AS (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