Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Table <-> Storage Location

Hi All,

I have a storage location which I'd like to retire and then drop.

  1. How can I findout/show which table is using this storage location?
  2. I've read in the documentation that a storage location should first be retired, then dropped.
    Can anynone comment on important things to check/do before retiring/dropping it?

 

Thanks,

Yarden

Comments

  • Hi , 

     

    First check you storage loactions:

     

     select * from storage_locations;

    - and make sure you have them all labeld.

     

     

    Next query the storage_containers table for any projecitons stored in that storage location:

     

     select * from storage_containers 
    where
    location_label='label of the storage to be retired';

    Or you can check the storage_policies table for any in place policies :

     

     

     select * from storage_policies;

     

     

    If you have objects that are still that storage you need to move them to another storage locaiton:

     

    select set_object_storage_policy('table_name', 'location_label', 'true');

     

    After you move all of your tables to a different locaiton you need ot change the type of the storage:

     

     

    SELECT ALTER_LOCATION_USE('fullpath' , 'node_name' , 'TEMP');

     

    And finally you can drop the location:

    SELECT DROP_LOCATION('fullpath' , 'node_name');

    Next see if the storage is still in your storage_locations table:

    select * from storage_locations;

    hope this helps.

     

     

     

  •  You cannot retire a location if is the only storage location available.

     When running retire location the data contained will be moved by Vertica using mergeouts. 

     Even if you don`t see any storage_containers under the label of your storage some data will be there(don`t know why), the retire location will make sure to move all of it to the default storage location and then you can drop it.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.