Querying flex views

Flex tables offer the ability of dynamic schema through the application of query rewriting. Use flex views to support restricted access to flex tables. As with flex tables, each time you use a select query on a flex table view, internally, Vertica invokes the maplookup() function, to return information on all virtual columns. This query behavior occurs for any flex or columnar table that includes a __raw__ column.

This example illustrates querying a flex view:

1. Create a flex table.

   => CREATE FLEX TABLE twitter();
   

2. Load JSON data into flex table using fjsonparser.

   => COPY twitter FROM '/home/dbadmin/data/flex/tweets_10000.json' PARSER fjsonparser();
   Rows Loaded
   -------------
   10000
   (1 row)
   

3. Create a flex view on top of flex table twitter with constraint retweet_count>0.

   => CREATE VIEW flex_view AS SELECT __raw__ FROM twitter WHERE retweet_count::int > 0;
   CREATE VIEW
   

4. Query the view. First 5 rows are displayed.

   => SELECT retweeted,retweet_count,source FROM (select __raw__ from flex_view) t1 limit 5;
   retweeted | retweet_count |                                        source
   -----------+---------------+--------------------------------------------------------------------------------------
   F         | 1             | <a href="http://blackberry.com/twitter" rel="nofollow">Twitter for BlackBerry®</a>
   F         | 1             | web
   F         | 1             | <a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>
   F         | 23            | <a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a>
   F         | 7             | <a href="http://twitter.com/download/iphone" rel="nofollow">Twitter for iPhone</a>
   (5 rows)