Saving all output to "!!{outputDirectory}!!/create_view.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/create_view.q >>> DROP VIEW view1; No rows affected >>> DROP VIEW view2; No rows affected >>> DROP VIEW view3; No rows affected >>> DROP VIEW view4; No rows affected >>> DROP VIEW view5; No rows affected >>> DROP VIEW view6; No rows affected >>> DROP VIEW view7; No rows affected >>> DROP VIEW view8; No rows affected >>> DROP VIEW view9; No rows affected >>> DROP VIEW view10; No rows affected >>> DROP VIEW view11; No rows affected >>> DROP VIEW view12; No rows affected >>> DROP VIEW view13; No rows affected >>> DROP VIEW view14; No rows affected >>> DROP VIEW view15; No rows affected >>> DROP VIEW view16; No rows affected >>> DROP TEMPORARY FUNCTION test_translate; No rows affected >>> DROP TEMPORARY FUNCTION test_max; No rows affected >>> DROP TEMPORARY FUNCTION test_explode; No rows affected >>> >>> >>> SELECT * FROM src WHERE key=86; 'key','value' '86','val_86' 1 row selected >>> CREATE VIEW view1 AS SELECT value FROM src WHERE key=86; 'value' No rows selected >>> CREATE VIEW view2 AS SELECT * FROM src; 'key','value' No rows selected >>> CREATE VIEW view3(valoo) TBLPROPERTIES ("fear" = "factor") AS SELECT upper(value) FROM src WHERE key=86; '_c0' No rows selected >>> SELECT * from view1; 'value' 'val_86' 1 row selected >>> SELECT * from view2 where key=18; 'key','value' '18','val_18' '18','val_18' 2 rows selected >>> SELECT * from view3; 'valoo' 'VAL_86' 1 row selected >>> >>> -- test EXPLAIN output for CREATE VIEW >>> EXPLAIN CREATE VIEW view0(valoo) AS SELECT upper(value) FROM src WHERE key=86; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_CREATEVIEW (TOK_TABNAME view0) (TOK_TABCOLNAME (TOK_TABCOL valoo TOK_NULL)) (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION upper (TOK_TABLE_OR_COL value)))) (TOK_WHERE (= (TOK_TABLE_OR_COL key) 86)))))' '' 'STAGE DEPENDENCIES:' ' Stage-0 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-0' ' Create View Operator:' ' Create View' ' if not exists: false' ' or replace: false' ' columns: valoo string' ' expanded text: SELECT `_c0` AS `valoo` FROM (SELECT upper(`src`.`value`) FROM `create_view`.`src` WHERE `src`.`key`=86) `view0`' ' name: view0' ' original text: SELECT upper(value) FROM src WHERE key=86' '' '' 18 rows selected >>> >>> -- make sure EXPLAIN works with a query which references a view >>> EXPLAIN SELECT * from view2 where key=18; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME view2))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (= (TOK_TABLE_OR_COL key) 18))))' '' 'STAGE DEPENDENCIES:' ' Stage-1 is a root stage' ' Stage-0 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' view2:src ' ' TableScan' ' alias: src' ' Filter Operator' ' predicate:' ' expr: (key = 18)' ' type: boolean' ' Select Operator' ' expressions:' ' expr: key' ' type: string' ' expr: value' ' type: string' ' outputColumnNames: _col0, _col1' ' Select Operator' ' expressions:' ' expr: _col0' ' type: string' ' expr: _col1' ' type: string' ' outputColumnNames: _col0, _col1' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' '' ' Stage: Stage-0' ' Fetch Operator' ' limit: -1' '' '' 44 rows selected >>> >>> SHOW TABLES 'view.*'; 'tab_name' 'view1' 'view2' 'view3' 3 rows selected >>> DESCRIBE view1; 'col_name','data_type','comment' 'value','string','' 1 row selected >>> DESCRIBE EXTENDED view1; 'col_name','data_type','comment' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:view1, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:value, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT value FROM src WHERE key=86, viewExpandedText:SELECT `src`.`value` FROM `create_view`.`src` WHERE `src`.`key`=86, tableType:VIRTUAL_VIEW)','' 3 rows selected >>> DESCRIBE FORMATTED view1; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT value FROM src WHERE key=86','' 'View Expanded Text: ','SELECT `src`.`value` FROM `create_view`.`src` WHERE `src`.`key`=86','' 27 rows selected >>> DESCRIBE view2; 'col_name','data_type','comment' 'key','string','' 'value','string','' 2 rows selected >>> DESCRIBE EXTENDED view2; 'col_name','data_type','comment' 'key','string','' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:view2, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT * FROM src, viewExpandedText:SELECT `src`.`key`, `src`.`value` FROM `create_view`.`src`, tableType:VIRTUAL_VIEW)','' 4 rows selected >>> DESCRIBE FORMATTED view2; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT * FROM src ','' 'View Expanded Text: ','SELECT `src`.`key`, `src`.`value` FROM `create_view`.`src`','' 28 rows selected >>> DESCRIBE view3; 'col_name','data_type','comment' 'valoo','string','' 1 row selected >>> DESCRIBE EXTENDED view3; 'col_name','data_type','comment' 'valoo','string','' '','','' 'Detailed Table Information','Table(tableName:view3, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:valoo, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!, fear=factor}, viewOriginalText:SELECT upper(value) FROM src WHERE key=86, viewExpandedText:SELECT `_c0` AS `valoo` FROM (SELECT upper(`src`.`value`) FROM `create_view`.`src` WHERE `src`.`key`=86) `view3`, tableType:VIRTUAL_VIEW)','' 3 rows selected >>> DESCRIBE FORMATTED view3; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'valoo ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','fear ','factor ' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT upper(value) FROM src WHERE key=86','' 'View Expanded Text: ','SELECT `_c0` AS `valoo` FROM (SELECT upper(`src`.`value`) FROM `create_view`.`src` WHERE `src`.`key`=86) `view3`','' 28 rows selected >>> >>> ALTER VIEW view3 SET TBLPROPERTIES ("biggest" = "loser"); No rows affected >>> DESCRIBE EXTENDED view3; 'col_name','data_type','comment' 'valoo','string','' '','','' 'Detailed Table Information','Table(tableName:view3, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:valoo, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{last_modified_by=!!ELIDED!!, last_modified_time=!!UNIXTIME!!, biggest=loser, transient_lastDdlTime=!!UNIXTIME!!, fear=factor}, viewOriginalText:SELECT upper(value) FROM src WHERE key=86, viewExpandedText:SELECT `_c0` AS `valoo` FROM (SELECT upper(`src`.`value`) FROM `create_view`.`src` WHERE `src`.`key`=86) `view3`, tableType:VIRTUAL_VIEW)','' 3 rows selected >>> DESCRIBE FORMATTED view3; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'valoo ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','biggest ','loser ' '','fear ','factor ' '','last_modified_by ','!!{user.name}!! ' '','last_modified_time ','!!UNIXTIME!! ' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT upper(value) FROM src WHERE key=86','' 'View Expanded Text: ','SELECT `_c0` AS `valoo` FROM (SELECT upper(`src`.`value`) FROM `create_view`.`src` WHERE `src`.`key`=86) `view3`','' 31 rows selected >>> >>> CREATE TABLE table1 (key int); No rows affected >>> >>> -- use DESCRIBE EXTENDED on a base table and an external table as points >>> -- of comparison for view descriptions >>> DESCRIBE EXTENDED table1; 'col_name','data_type','comment' 'key','int','' '','','' 'Detailed Table Information','Table(tableName:table1, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:int, comment:null)], location:!!{hive.metastore.warehouse.dir}!!/create_view.db/table1, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)','' 3 rows selected >>> DESCRIBE EXTENDED src1; 'col_name','data_type','comment' 'key','string','' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:src1, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value, type:string, comment:null)], location:!!{hive.metastore.warehouse.dir}!!/create_view.db/src1, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{numPartitions=0, numFiles=1, transient_lastDdlTime=!!UNIXTIME!!, totalSize=216, numRows=0, rawDataSize=0}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)','' 4 rows selected >>> >>> -- use DESCRIBE EXTENDED on a base table as a point of comparison for >>> -- view descriptions >>> DESCRIBE EXTENDED table1; 'col_name','data_type','comment' 'key','int','' '','','' 'Detailed Table Information','Table(tableName:table1, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:int, comment:null)], location:!!{hive.metastore.warehouse.dir}!!/create_view.db/table1, inputFormat:org.apache.hadoop.mapred.TextInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE)','' 3 rows selected >>> >>> >>> INSERT OVERWRITE TABLE table1 SELECT key FROM src WHERE key = 86; '_col0' No rows selected >>> >>> SELECT * FROM table1; 'key' '86' 1 row selected >>> CREATE VIEW view4 AS SELECT * FROM table1; 'key' No rows selected >>> SELECT * FROM view4; 'key' '86' 1 row selected >>> DESCRIBE view4; 'col_name','data_type','comment' 'key','int','' 1 row selected >>> ALTER TABLE table1 ADD COLUMNS (value STRING); No rows affected >>> SELECT * FROM table1; 'key','value' '86','' 1 row selected >>> SELECT * FROM view4; 'key' '86' 1 row selected >>> DESCRIBE table1; 'col_name','data_type','comment' 'key','int','' 'value','string','' 2 rows selected >>> DESCRIBE view4; 'col_name','data_type','comment' 'key','int','' 1 row selected >>> >>> CREATE VIEW view5 AS SELECT v1.key as key1, v2.key as key2 FROM view4 v1 join view4 v2; 'key1','key2' No rows selected >>> SELECT * FROM view5; 'key1','key2' '86','86' 1 row selected >>> DESCRIBE view5; 'col_name','data_type','comment' 'key1','int','' 'key2','int','' 2 rows selected >>> >>> -- verify that column name and comment in DDL portion >>> -- overrides column alias in SELECT >>> CREATE VIEW view6(valoo COMMENT 'I cannot spell') AS SELECT upper(value) as blarg FROM src WHERE key=86; 'blarg' No rows selected >>> DESCRIBE view6; 'col_name','data_type','comment' 'valoo','string','I cannot spell' 1 row selected >>> >>> -- verify that ORDER BY and LIMIT are both supported in view def >>> CREATE VIEW view7 AS SELECT * FROM src WHERE key > 80 AND key < 100 ORDER BY key, value LIMIT 10; 'key','value' No rows selected >>> >>> SELECT * FROM view7; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' '85','val_85' '86','val_86' '87','val_87' '90','val_90' '90','val_90' 10 rows selected >>> >>> -- top-level ORDER BY should override the one inside the view >>> -- (however, the inside ORDER BY should still influence the evaluation >>> -- of the limit) >>> SELECT * FROM view7 ORDER BY key DESC, value; 'key','value' '90','val_90' '90','val_90' '87','val_87' '86','val_86' '85','val_85' '84','val_84' '84','val_84' '83','val_83' '83','val_83' '82','val_82' 10 rows selected >>> >>> -- top-level LIMIT should override if lower >>> SELECT * FROM view7 LIMIT 5; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' 5 rows selected >>> >>> -- but not if higher >>> SELECT * FROM view7 LIMIT 20; 'key','value' '82','val_82' '83','val_83' '83','val_83' '84','val_84' '84','val_84' '85','val_85' '86','val_86' '87','val_87' '90','val_90' '90','val_90' 10 rows selected >>> >>> -- test usage of a function within a view >>> CREATE TEMPORARY FUNCTION test_translate AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDFTestTranslate'; No rows affected >>> CREATE VIEW view8(c) AS SELECT test_translate('abc', 'a', 'b') FROM table1; '_c0' No rows selected >>> DESCRIBE EXTENDED view8; 'col_name','data_type','comment' 'c','string','' '','','' 'Detailed Table Information','Table(tableName:view8, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:c, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT test_translate('abc', 'a', 'b') ','' 'FROM table1, viewExpandedText:SELECT `_c0` AS `c` FROM (SELECT `test_translate`('abc', 'a', 'b') ','','' 'FROM `create_view`.`table1`) `view8`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view8; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'c ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT test_translate('abc', 'a', 'b') ','' 'FROM table1','','' 'View Expanded Text: ','SELECT `_c0` AS `c` FROM (SELECT `test_translate`('abc', 'a', 'b') ','' 'FROM `create_view`.`table1`) `view8`','','' 29 rows selected >>> SELECT * FROM view8; 'c' 'bbc' 1 row selected >>> >>> -- test usage of a UDAF within a view >>> CREATE TEMPORARY FUNCTION test_max AS 'org.apache.hadoop.hive.ql.udf.UDAFTestMax'; No rows affected >>> set hive.map.aggr=false; No rows affected >>> -- disable map-side aggregation >>> CREATE VIEW view9(m) AS SELECT test_max(length(value)) FROM src; '_c0' No rows selected >>> DESCRIBE EXTENDED view9; 'col_name','data_type','comment' 'm','int','' '','','' 'Detailed Table Information','Table(tableName:view9, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:m, type:int, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT test_max(length(value)) ','' 'FROM src, viewExpandedText:SELECT `_c0` AS `m` FROM (SELECT `test_max`(length(`src`.`value`)) ','','' 'FROM `create_view`.`src`) `view9`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view9; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'm ','int ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT test_max(length(value)) ','' 'FROM src','','' 'View Expanded Text: ','SELECT `_c0` AS `m` FROM (SELECT `test_max`(length(`src`.`value`)) ','' 'FROM `create_view`.`src`) `view9`','','' 29 rows selected >>> SELECT * FROM view9; 'm' '7' 1 row selected >>> DROP VIEW view9; No rows affected >>> set hive.map.aggr=true; No rows affected >>> -- enable map-side aggregation >>> CREATE VIEW view9(m) AS SELECT test_max(length(value)) FROM src; '_c0' No rows selected >>> DESCRIBE EXTENDED view9; 'col_name','data_type','comment' 'm','int','' '','','' 'Detailed Table Information','Table(tableName:view9, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:m, type:int, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT test_max(length(value)) ','' 'FROM src, viewExpandedText:SELECT `_c0` AS `m` FROM (SELECT `test_max`(length(`src`.`value`)) ','','' 'FROM `create_view`.`src`) `view9`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view9; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'm ','int ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT test_max(length(value)) ','' 'FROM src','','' 'View Expanded Text: ','SELECT `_c0` AS `m` FROM (SELECT `test_max`(length(`src`.`value`)) ','' 'FROM `create_view`.`src`) `view9`','','' 29 rows selected >>> SELECT * FROM view9; 'm' '7' 1 row selected >>> >>> -- test usage of a subselect within a view >>> CREATE VIEW view10 AS SELECT slurp.* FROM (SELECT * FROM src WHERE key=86) slurp; 'key','value' No rows selected >>> DESCRIBE EXTENDED view10; 'col_name','data_type','comment' 'key','string','' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:view10, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT slurp.* FROM (SELECT * FROM src WHERE key=86) slurp, viewExpandedText:SELECT `slurp`.`key`, `slurp`.`value` FROM (SELECT `src`.`key`, `src`.`value` FROM `create_view`.`src` WHERE `src`.`key`=86) `slurp`, tableType:VIRTUAL_VIEW)','' 4 rows selected >>> DESCRIBE FORMATTED view10; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT slurp.* FROM (SELECT * FROM src WHERE key=86) slurp','' 'View Expanded Text: ','SELECT `slurp`.`key`, `slurp`.`value` FROM (SELECT `src`.`key`, `src`.`value` FROM `create_view`.`src` WHERE `src`.`key`=86) `slurp`','' 28 rows selected >>> SELECT * FROM view10; 'key','value' '86','val_86' 1 row selected >>> >>> -- test usage of a UDTF within a view >>> CREATE TEMPORARY FUNCTION test_explode AS 'org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode'; No rows affected >>> CREATE VIEW view11 AS SELECT test_explode(array(1,2,3)) AS (boom) FROM table1; 'boom' No rows selected >>> DESCRIBE EXTENDED view11; 'col_name','data_type','comment' 'boom','int','' '','','' 'Detailed Table Information','Table(tableName:view11, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:boom, type:int, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT test_explode(array(1,2,3)) AS (boom) ','' 'FROM table1, viewExpandedText:SELECT `test_explode`(array(1,2,3)) AS (`boom`) ','','' 'FROM `create_view`.`table1`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view11; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'boom ','int ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT test_explode(array(1,2,3)) AS (boom) ','' 'FROM table1','','' 'View Expanded Text: ','SELECT `test_explode`(array(1,2,3)) AS (`boom`) ','' 'FROM `create_view`.`table1`','','' 29 rows selected >>> SELECT * FROM view11; 'boom' '1' '2' '3' 3 rows selected >>> >>> -- test usage of LATERAL within a view >>> CREATE VIEW view12 AS SELECT * FROM src LATERAL VIEW explode(array(1,2,3)) myTable AS myCol; 'key','value','mycol' No rows selected >>> DESCRIBE EXTENDED view12; 'col_name','data_type','comment' 'key','string','' 'value','string','' 'mycol','int','' '','','' 'Detailed Table Information','Table(tableName:view12, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value, type:string, comment:null), FieldSchema(name:mycol, type:int, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT * FROM src LATERAL VIEW explode(array(1,2,3)) myTable AS myCol, viewExpandedText:SELECT `src`.`key`, `src`.`value`, `mytable`.`mycol` FROM `create_view`.`src` LATERAL VIEW explode(array(1,2,3)) `myTable` AS `myCol`, tableType:VIRTUAL_VIEW)','' 5 rows selected >>> DESCRIBE FORMATTED view12; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value ','string ','None ' 'mycol ','int ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT * FROM src LATERAL VIEW explode(array(1,2,3)) myTable AS myCol','' 'View Expanded Text: ','SELECT `src`.`key`, `src`.`value`, `mytable`.`mycol` FROM `create_view`.`src` LATERAL VIEW explode(array(1,2,3)) `myTable` AS `myCol`','' 29 rows selected >>> SELECT * FROM view12 ORDER BY key ASC, myCol ASC LIMIT 1; 'key','value','mycol' '0','val_0','1' 1 row selected >>> >>> -- test usage of LATERAL with a view as the LHS >>> SELECT * FROM view2 LATERAL VIEW explode(array(1,2,3)) myTable AS myCol ORDER BY key ASC, myCol ASC LIMIT 1; 'key','value','mycol' '0','val_0','1' 1 row selected >>> >>> -- test usage of TABLESAMPLE within a view >>> CREATE VIEW view13 AS SELECT s.key FROM srcbucket TABLESAMPLE (BUCKET 1 OUT OF 5 ON key) s; 'key' No rows selected >>> DESCRIBE EXTENDED view13; 'col_name','data_type','comment' 'key','int','' '','','' 'Detailed Table Information','Table(tableName:view13, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:int, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT s.key ','' 'FROM srcbucket TABLESAMPLE (BUCKET 1 OUT OF 5 ON key) s, viewExpandedText:SELECT `s`.`key` ','','' 'FROM `create_view`.`srcbucket` TABLESAMPLE (BUCKET 1 OUT OF 5 ON `key`) `s`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view13; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','int ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT s.key ','' 'FROM srcbucket TABLESAMPLE (BUCKET 1 OUT OF 5 ON key) s','','' 'View Expanded Text: ','SELECT `s`.`key` ','' 'FROM `create_view`.`srcbucket` TABLESAMPLE (BUCKET 1 OUT OF 5 ON `key`) `s`','','' 29 rows selected >>> SELECT * FROM view13 ORDER BY key LIMIT 12; 'key' '0' '0' '0' '0' '0' '5' '5' '5' '5' '10' '10' '15' 12 rows selected >>> >>> -- test usage of JOIN+UNION+AGG all within same view >>> CREATE VIEW view14 AS SELECT unionsrc1.key as k1, unionsrc1.value as v1, unionsrc2.key as k2, unionsrc2.value as v2 FROM (select 'tst1' as key, cast(count(1) as string) as value from src s1 UNION ALL select s2.key as key, s2.value as value from src s2 where s2.key < 10) unionsrc1 JOIN (select 'tst1' as key, cast(count(1) as string) as value from src s3 UNION ALL select s4.key as key, s4.value as value from src s4 where s4.key < 10) unionsrc2 ON (unionsrc1.key = unionsrc2.key); 'k1','v1','k2','v2' No rows selected >>> DESCRIBE EXTENDED view14; 'col_name','data_type','comment' 'k1','string','' 'v1','string','' 'k2','string','' 'v2','string','' '','','' 'Detailed Table Information','Table(tableName:view14, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:k1, type:string, comment:null), FieldSchema(name:v1, type:string, comment:null), FieldSchema(name:k2, type:string, comment:null), FieldSchema(name:v2, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT unionsrc1.key as k1, unionsrc1.value as v1, ','' 'unionsrc2.key as k2, unionsrc2.value as v2 ','','' 'FROM (select 'tst1' as key, cast(count(1) as string) as value from src s1 ','','' 'UNION ALL ','','' 'select s2.key as key, s2.value as value from src s2 where s2.key < 10) unionsrc1 ','','' 'JOIN ','','' '(select 'tst1' as key, cast(count(1) as string) as value from src s3 ','','' 'UNION ALL ','','' 'select s4.key as key, s4.value as value from src s4 where s4.key < 10) unionsrc2 ','','' 'ON (unionsrc1.key = unionsrc2.key), viewExpandedText:SELECT `unionsrc1`.`key` as `k1`, `unionsrc1`.`value` as `v1`, ','','' '`unionsrc2`.`key` as `k2`, `unionsrc2`.`value` as `v2` ','','' 'FROM (select 'tst1' as `key`, cast(count(1) as string) as `value` from `create_view`.`src` `s1` ','','' 'UNION ALL ','','' 'select `s2`.`key` as `key`, `s2`.`value` as `value` from `create_view`.`src` `s2` where `s2`.`key` < 10) `unionsrc1` ','','' 'JOIN ','','' '(select 'tst1' as `key`, cast(count(1) as string) as `value` from `create_view`.`src` `s3` ','','' 'UNION ALL ','','' 'select `s4`.`key` as `key`, `s4`.`value` as `value` from `create_view`.`src` `s4` where `s4`.`key` < 10) `unionsrc2` ','','' 'ON (`unionsrc1`.`key` = `unionsrc2`.`key`), tableType:VIRTUAL_VIEW)','','' 24 rows selected >>> DESCRIBE FORMATTED view14; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'k1 ','string ','None ' 'v1 ','string ','None ' 'k2 ','string ','None ' 'v2 ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT unionsrc1.key as k1, unionsrc1.value as v1, ','' 'unionsrc2.key as k2, unionsrc2.value as v2 ','','' 'FROM (select 'tst1' as key, cast(count(1) as string) as value from src s1 ','','' 'UNION ALL ','','' 'select s2.key as key, s2.value as value from src s2 where s2.key < 10) unionsrc1 ','','' 'JOIN ','','' '(select 'tst1' as key, cast(count(1) as string) as value from src s3 ','','' 'UNION ALL ','','' 'select s4.key as key, s4.value as value from src s4 where s4.key < 10) unionsrc2 ','','' 'ON (unionsrc1.key = unionsrc2.key)','','' 'View Expanded Text: ','SELECT `unionsrc1`.`key` as `k1`, `unionsrc1`.`value` as `v1`, ','' '`unionsrc2`.`key` as `k2`, `unionsrc2`.`value` as `v2` ','','' 'FROM (select 'tst1' as `key`, cast(count(1) as string) as `value` from `create_view`.`src` `s1` ','','' 'UNION ALL ','','' 'select `s2`.`key` as `key`, `s2`.`value` as `value` from `create_view`.`src` `s2` where `s2`.`key` < 10) `unionsrc1` ','','' 'JOIN ','','' '(select 'tst1' as `key`, cast(count(1) as string) as `value` from `create_view`.`src` `s3` ','','' 'UNION ALL ','','' 'select `s4`.`key` as `key`, `s4`.`value` as `value` from `create_view`.`src` `s4` where `s4`.`key` < 10) `unionsrc2` ','','' 'ON (`unionsrc1`.`key` = `unionsrc2`.`key`)','','' 48 rows selected >>> SELECT * FROM view14 ORDER BY k1; 'k1','v1','k2','v2' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '0','val_0','0','val_0' '2','val_2','2','val_2' '4','val_4','4','val_4' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '5','val_5','5','val_5' '8','val_8','8','val_8' '9','val_9','9','val_9' 'tst1','500','tst1','500' 23 rows selected >>> >>> -- test usage of GROUP BY within view >>> CREATE VIEW view15 AS SELECT key,COUNT(value) AS value_count FROM src GROUP BY key; 'key','value_count' No rows selected >>> DESCRIBE EXTENDED view15; 'col_name','data_type','comment' 'key','string','' 'value_count','bigint','' '','','' 'Detailed Table Information','Table(tableName:view15, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:key, type:string, comment:null), FieldSchema(name:value_count, type:bigint, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT key,COUNT(value) AS value_count ','' 'FROM src ','','' 'GROUP BY key, viewExpandedText:SELECT `src`.`key`,COUNT(`src`.`value`) AS `value_count` ','','' 'FROM `create_view`.`src` ','','' 'GROUP BY `src`.`key`, tableType:VIRTUAL_VIEW)','','' 8 rows selected >>> DESCRIBE FORMATTED view15; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'key ','string ','None ' 'value_count ','bigint ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT key,COUNT(value) AS value_count ','' 'FROM src ','','' 'GROUP BY key','','' 'View Expanded Text: ','SELECT `src`.`key`,COUNT(`src`.`value`) AS `value_count` ','' 'FROM `create_view`.`src` ','','' 'GROUP BY `src`.`key`','','' 32 rows selected >>> SELECT * FROM view15 ORDER BY value_count DESC, key LIMIT 10; 'key','value_count' '230','5' '348','5' '401','5' '469','5' '138','4' '169','4' '277','4' '406','4' '468','4' '489','4' 10 rows selected >>> >>> -- test usage of DISTINCT within view >>> CREATE VIEW view16 AS SELECT DISTINCT value FROM src; 'value' No rows selected >>> DESCRIBE EXTENDED view16; 'col_name','data_type','comment' 'value','string','' '','','' 'Detailed Table Information','Table(tableName:view16, dbName:create_view, owner:!!{user.name}!!, createTime:!!UNIXTIME!!, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:value, type:string, comment:null)], location:null, inputFormat:org.apache.hadoop.mapred.SequenceFileInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:null, parameters:{}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{})), partitionKeys:[], parameters:{transient_lastDdlTime=!!UNIXTIME!!}, viewOriginalText:SELECT DISTINCT value ','' 'FROM src, viewExpandedText:SELECT DISTINCT `src`.`value` ','','' 'FROM `create_view`.`src`, tableType:VIRTUAL_VIEW)','','' 5 rows selected >>> DESCRIBE FORMATTED view16; 'col_name','data_type','comment' '# col_name ','data_type ','comment ' '','','' 'value ','string ','None ' '','','' '# Detailed Table Information','','' 'Database: ','create_view ','' 'Owner: ','!!{user.name}!! ','' 'CreateTime: ','!!TIMESTAMP!!','' 'LastAccessTime: ','UNKNOWN ','' 'Protect Mode: ','None ','' 'Retention: ','0 ','' 'Table Type: ','VIRTUAL_VIEW ','' 'Table Parameters:','','' '','transient_lastDdlTime','!!UNIXTIME!! ' '','','' '# Storage Information','','' 'SerDe Library: ','null ','' 'InputFormat: ','org.apache.hadoop.mapred.SequenceFileInputFormat','' 'OutputFormat: ','org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat','' 'Compressed: ','No ','' 'Num Buckets: ','-1 ','' 'Bucket Columns: ','[] ','' 'Sort Columns: ','[] ','' '','','' '# View Information','','' 'View Original Text: ','SELECT DISTINCT value ','' 'FROM src','','' 'View Expanded Text: ','SELECT DISTINCT `src`.`value` ','' 'FROM `create_view`.`src`','','' 29 rows selected >>> SELECT * FROM view16 ORDER BY value LIMIT 10; 'value' 'val_0' 'val_10' 'val_100' 'val_103' 'val_104' 'val_105' 'val_11' 'val_111' 'val_113' 'val_114' 10 rows selected >>> >>> -- HIVE-2133: DROP TABLE IF EXISTS should ignore a matching view name >>> DROP TABLE IF EXISTS view16; No rows affected >>> DESCRIBE view16; 'col_name','data_type','comment' 'value','string','' 1 row selected >>> >>> -- Likewise, DROP VIEW IF EXISTS should ignore a matching table name >>> DROP VIEW IF EXISTS table1; No rows affected >>> DESCRIBE table1; 'col_name','data_type','comment' 'key','int','' 'value','string','' 2 rows selected >>> >>> -- this should work since currently we don't track view->table >>> -- dependencies for implementing RESTRICT >>> >>> >>> DROP VIEW view1; No rows affected >>> DROP VIEW view2; No rows affected >>> DROP VIEW view3; No rows affected >>> DROP VIEW view4; No rows affected >>> DROP VIEW view5; No rows affected >>> DROP VIEW view6; No rows affected >>> DROP VIEW view7; No rows affected >>> DROP VIEW view8; No rows affected >>> DROP VIEW view9; No rows affected >>> DROP VIEW view10; No rows affected >>> DROP VIEW view11; No rows affected >>> DROP VIEW view12; No rows affected >>> DROP VIEW view13; No rows affected >>> DROP VIEW view14; No rows affected >>> DROP VIEW view15; No rows affected >>> DROP VIEW view16; No rows affected >>> DROP TEMPORARY FUNCTION test_translate; No rows affected >>> DROP TEMPORARY FUNCTION test_max; No rows affected >>> DROP TEMPORARY FUNCTION test_explode; No rows affected >>> !record