Creating Database Views
You can create database views using the Spark client when connected to a VAST Database. Views allow you to save queries and re-use them in other queries.
Database views are stored queries that present a part of a VAST Database. They are created and managed using the client application and stored in a table in the database itself. They can be used in other queries on the database.
Action | Example |
|---|---|
Create a view | CREATE VIEW ndb.mybucket.myschema.myview (v_col1 COMMENT "column with a comment", v_col2) comment "view comment" as select col1, col2 from ndb.mybucket.myschema.mytable where col1 > 0 |
List views | SHOW VIEWS FROM ndb.mybucket.myschema |
Show details for a view | DESCRIBE TABLE EXTENDED ndb.mybucket.myschema.myview |
Rename a view | ALTER VIEW ndb.mybucket.myschema.myview RENAME TO mybucket.myschema.mynewview ALTER VIEW ndb.mybucket.myschema.myview RENAME TO mynewview |
Redefine a view | ALTER VIEW ndb.mybucket.myschema.myview as select col1, col2 from ndb.mybucket.myschema.mytable where col1 > 0 and col2 > 0 |
Using a view in a query | select * from ndb.mybucket.myschema.myview where v_col2 > 5 order by v_col1 |
Limitations
View properties are not supported
Full table names must be used in view queries
User-defined column names and comments are lost if the schema of the query changes when redefining a view