create table ..... select 语句(四) ORC

创建表

create table tuser_orc_copy 
row format delimited 
fields terminated by '\t' 
lines terminated by '\n' 
stored as ORC  
as select * from hi_tuser_txt ;

hive> 
    > create table tuser_orc_copy 
    > row format delimited fields terminated by '\t' lines terminated by '\n' 
    > stored as ORC  as select * from hi_tuser_txt ;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20190923224956_7ec94915-0599-4db7-803c-be7693bdfc62
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569239441933_0005, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0005/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-23 22:51:33,277 Stage-1 map = 0%,  reduce = 0%
2019-09-23 22:52:24,626 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 4.07 sec
MapReduce Total cumulative CPU time: 4 seconds 70 msec
Ended Job = job_1569239441933_0005
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/.hive-staging_hive_2019-09-23_22-49-56_608_8611840933582489782-1/-ext-10002
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_orc_copy
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 4.07 sec   HDFS Read: 88458 HDFS Write: 13525 SUCCESS
Total MapReduce CPU Time Spent: 4 seconds 70 msec
OK
Time taken: 157.642 seconds
hive> 

 

显示创建表

hive> 
    > show create table cashmid.tuser_orc_copy;
OK
CREATE TABLE `cashmid.tuser_orc_copy`(
  `fguid` string, 
  `fid` bigint, 
  `fusername` string, 
  `fparentid` int, 
  `fcompanyid` int, 
  `fstatus` tinyint, 
  `ts` bigint, 
  `faddtimedate` string, 
  `fadddate` string, 
  `faddtime` string, 
  `fip` string, 
  `fthreadid` smallint)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\t', 
  'line.delim'='\n', 
  'serialization.format'='\t') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_orc_copy'
TBLPROPERTIES (
  'transient_lastDdlTime'='1569250353')
Time taken: 0.124 seconds, Fetched: 27 row(s)
hive> 

 

表详细信息

hive> 
    > desc formatted tuser_orc_copy;
OK
# col_name                data_type               comment             
          
fguid                   string                                      
fid                     bigint                                      
fusername               string                                      
fparentid               int                                         
fcompanyid              int                                         
fstatus                 tinyint                                     
ts                      bigint                                      
faddtimedate            string                                      
fadddate                string                                      
faddtime                string                                      
fip                     string                                      
fthreadid               smallint                                    
          
# Detailed Table Information          
Database:               cashmid                  
Owner:                  root                     
CreateTime:             Mon Sep 23 22:52:33 CST 2019     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_orc_copy     
Table Type:             MANAGED_TABLE            
Table Parameters:          
    COLUMN_STATS_ACCURATE    {\"BASIC_STATS\":\"true\"}
    numFiles                1                   
    numRows                 677                 
    rawDataSize             423125              
    totalSize               13442               
    transient_lastDdlTime    1569250353          
          
# Storage Information          
SerDe Library:          org.apache.hadoop.hive.ql.io.orc.OrcSerde     
InputFormat:            org.apache.hadoop.hive.ql.io.orc.OrcInputFormat     
OutputFormat:           org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat     
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:          
    field.delim             \t                  
    line.delim              \n                  
    serialization.format    \t                  
Time taken: 0.061 seconds, Fetched: 43 row(s)
hive>