Logical view>Analytical view invalid state

What is correct sequence to follow?
we have usecase and this is what we are doing but seem may not be correct

  1. nightly trunc and load oracle source using sqoop into hdfs hive driven metadata
  2. invalidate hive tables
  3. select * from eachtable limi1
  4. drop logical and analytical views
  5. create logical and analytical views
  6. refresh analytical views
  7. execute select * from analytical view limit 1 for all AVs
  8. do stats gathering for source tables

could anyone knows what exactly steps we should nightly, right now this is what we are doing still seeing invalid AVs

We also tried refesh full AVs but getting invalid handle error connection timingout.

@kyreddy let me reorganize and annotate a few things here so that its easier to follow.

  1. nightly trunc and load oracle source using sqoop into hdfs hive driven metadata
  2. invalidate hive tables

This drops all metadata for the table in the Catalog

  1. do stats gathering for source tables

This is optional. You would do this in Impala ideally, and Arcadia will get the stats once you execute a query from the table (see next step).

  1. select * from eachtable limi1

This forces a load of the table in your SELECT query. This helps reduce metadata load times when users start running queries, but this may still take a little bit of time depending on how large the table metadata is which is function of the number of partitions, columns ,files, and if stats were computed.

  1. drop logical and analytical views
  2. create logical and analytical views
  3. refresh analytical views

Analytical Views should always be refreshed after table data and metadata are finished loading. They will be in a STALE state until the Refresh is complete because the table data and metadata has changed.

  1. execute select * from analytical view limit 1 for all AVs

This is not necessary. Analytical View metadata will already be present in the Arcadia Catalog.

Your full Refreshes are failing because of a different reason, but that’s being followed up on with support I believe.

Thank you,
Tadd Wood