Creating a Storage Policy for HDFS Storage Locations

After you create an HDFS storage location, you assign database objects to the location by setting storage policies. Based on these storage policies, database objects such as partition ranges, individual tables, whole schemas, or even the entire database store their data in the HDFS storage location. Use the SET_OBJECT_STORAGE_POLICY function to assign objects to an HDFS storage location. In the function call, supply the label you assigned to the HDFS storage location as the location label argument. You do so using the CREATE LOCATION statement's LABEL keyword.

The following example demonstrates using SET_OBJECT_STORAGE_POLICY to store a table in an HDFS storage location. The example statement sets the policy for an existing table, named messages, to store its data in an HDFS storage location, named coldstorage.

=> SELECT SET_OBJECT_STORAGE_POLICY('messages', 'coldstorage');

This table's data is moved to the HDFS storage location with the next merge-out. Alternatively, you can have Vertica move the data immediately by using the enforce_storage_move parameter.

You can query the STORAGE_CONTAINERS system table and examine the location_label column to verify that Vertica has moved the data:

=> SELECT node_name, projection_name, location_label, total_row_count FROM V_MONITOR.STORAGE_CONTAINERS 
   WHERE projection_name ILIKE 'messages%';
    node_name     | projection_name | location_label | total_row_count
------------------+-----------------+----------------+-----------------
 v_vmart_node0001 | messages_b0     | coldstorage    |          366057
 v_vmart_node0001 | messages_b1     | coldstorage    |          366511
 v_vmart_node0002 | messages_b0     | coldstorage    |          367432
 v_vmart_node0002 | messages_b1     | coldstorage    |          366057
 v_vmart_node0003 | messages_b0     | coldstorage    |          366511
 v_vmart_node0003 | messages_b1     | coldstorage    |          367432
(6 rows)

See Creating Storage Policies in the Administrator's Guide for more information about assigning storage policies to objects.