| ==== |
| ---- QUERY |
| # Create a simple view without renaming the columns. |
| create view $DATABASE.simple_view as |
| select * from functional.alltypes |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that 'if not exists' swallows the error (view already exists) |
| create view if not exists $DATABASE.simple_view as |
| select * from functional.alltypesagg |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Create another simple view with 'if not exists' on a subset of |
| # alltypes' columns using custom column names and comments |
| create view if not exists |
| $DATABASE.simple_view_sub (x, y comment 'hello', z) as |
| select int_col, string_col, timestamp_col from functional.alltypes |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Create a view on a parquet table (Hive cannot create/read/write parquet) |
| create view $DATABASE.parquet_view as |
| select * from functional_parquet.alltypes where id < 20 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Create a complex view with predicates, joins, aggregates and order by |
| create view $DATABASE.complex_view (abc comment 'agg', xyz comment 'gby') as |
| select count(a.bigint_col), b.string_col from |
| functional.alltypesagg a inner join functional.alltypestiny b |
| on a.id = b.id where a.bigint_col < 50 |
| group by b.string_col having count(a.bigint_col) > 1 |
| order by b.string_col limit 100 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Create a view on a view |
| create view $DATABASE.view_view (aaa, bbb) as |
| select * from $DATABASE.complex_view |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that the views are displayed by 'show tables' |
| show tables in $DATABASE |
| ---- RESULTS |
| 'complex_view' |
| 'parquet_view' |
| 'simple_view' |
| 'simple_view_sub' |
| 'view_view' |
| ==== |
| ---- QUERY |
| # Test that the views can be described |
| describe $DATABASE.simple_view |
| ---- RESULTS |
| 'id','int','' |
| 'bool_col','boolean','' |
| 'tinyint_col','tinyint','' |
| 'smallint_col','smallint','' |
| 'int_col','int','' |
| 'bigint_col','bigint','' |
| 'float_col','float','' |
| 'double_col','double','' |
| 'date_string_col','string','' |
| 'string_col','string','' |
| 'timestamp_col','timestamp','' |
| 'year','int','' |
| 'month','int','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| describe $DATABASE.simple_view_sub |
| ---- RESULTS |
| 'x','int','' |
| 'y','string','hello' |
| 'z','timestamp','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| describe $DATABASE.complex_view |
| ---- RESULTS |
| 'abc','bigint','agg' |
| 'xyz','string','gby' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| describe $DATABASE.parquet_view |
| ---- RESULTS |
| 'id','int','' |
| 'bool_col','boolean','' |
| 'tinyint_col','tinyint','' |
| 'smallint_col','smallint','' |
| 'int_col','int','' |
| 'bigint_col','bigint','' |
| 'float_col','float','' |
| 'double_col','double','' |
| 'date_string_col','string','' |
| 'string_col','string','' |
| 'timestamp_col','timestamp','' |
| 'year','int','' |
| 'month','int','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| describe $DATABASE.view_view |
| ---- RESULTS |
| 'aaa','bigint','' |
| 'bbb','string','' |
| ---- TYPES |
| string,string,string |
| ==== |
| ---- QUERY |
| # Test that the views can be queried. |
| select count(*) from $DATABASE.simple_view |
| ---- RESULTS |
| 7300 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from $DATABASE.simple_view_sub |
| ---- RESULTS |
| 7300 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from $DATABASE.complex_view |
| ---- RESULTS |
| 2 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from $DATABASE.parquet_view |
| ---- RESULTS |
| 20 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| select count(*) from $DATABASE.view_view |
| ---- RESULTS |
| 2 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test dropping a view |
| drop view $DATABASE.simple_view_sub |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that the view is gone |
| show tables in $DATABASE |
| ---- RESULTS |
| 'complex_view' |
| 'parquet_view' |
| 'simple_view' |
| 'view_view' |
| ==== |
| ---- QUERY |
| # Test 'if exists' for dropping a view (view does not exist) |
| drop view if exists $DATABASE.bad_view |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test 'if exists' does not drop a table with same name |
| create table $DATABASE.drop_tbl_test(a int) |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| drop view if exists $DATABASE.drop_tbl_test |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test drop table 'if exists' does not drop a view with same name. |
| # We try to drop a table with name complex_view and it should should |
| # still be listed in the subsequent show tables output (as a view). |
| drop table if exists $DATABASE.complex_view |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that the table is present |
| show tables in $DATABASE |
| ---- RESULTS |
| 'drop_tbl_test' |
| 'complex_view' |
| 'parquet_view' |
| 'simple_view' |
| 'view_view' |
| ==== |
| ---- QUERY |
| # Test renaming a view |
| alter view $DATABASE.view_view rename to $DATABASE.view_on_view |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test renaming a parquet view |
| alter view $DATABASE.parquet_view rename to $DATABASE.new_parquet_view |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that the view was renamed |
| show tables in $DATABASE |
| ---- RESULTS |
| 'drop_tbl_test' |
| 'complex_view' |
| 'new_parquet_view' |
| 'simple_view' |
| 'view_on_view' |
| ==== |
| ---- QUERY |
| # Test altering a with a new definition |
| alter view $DATABASE.new_parquet_view as |
| select bigint_col, string_col from functional_parquet.alltypesagg |
| where bigint_col is null limit 10 |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test querying the altered view |
| select count(bigint_col), count(string_col) from $DATABASE.new_parquet_view |
| ---- RESULTS |
| 0,10 |
| ---- TYPES |
| bigint,bigint |
| ==== |
| ---- QUERY |
| # Create a view on a constant select and try to query it. |
| create view $DATABASE.const_view |
| as select 1, 'a', cast(10.0 as float) |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| select * from $DATABASE.const_view |
| ---- RESULTS |
| 1,'a',10 |
| ---- TYPES |
| tinyint,string,float |
| ==== |
| ---- QUERY |
| # Test that parentheses are preserved in view creation. |
| # If the parentheses were ignored the query would return a count > 0. |
| create view $DATABASE.paren_view as |
| select count(*) from functional.alltypessmall |
| where true and (true or false) and false |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Test that parentheses are preserved in view creation. |
| select * from $DATABASE.paren_view |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Create a view with decimal columns. Regression test for IMPALA-1021. |
| create view $DATABASE.decimal_view as |
| select * from functional.decimal_tbl |
| ---- RESULTS |
| ==== |
| ---- QUERY |
| # Query a view with decimal columns. Regression test for IMPALA-1021. |
| select * from $DATABASE.decimal_view |
| ---- RESULTS |
| 1234,2222,1.2345678900,0.12345678900000000000000000000000000000,12345.78900,1 |
| 12345,333,123.4567890000,0.12345678900000000000000000000000000000,11.22000,1 |
| 12345,333,1234.5678900000,0.12345678900000000000000000000000000000,0.10000,1 |
| 132842,333,12345.6789000000,0.12345678900000000000000000000000000000,0.77889,1 |
| 2345,111,12.3456789000,0.12345678900000000000000000000000000000,3.14100,1 |
| ---- TYPES |
| decimal,decimal,decimal,decimal,decimal,decimal |