create table ..... select 语句(二) SequenceFile

创建表

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

hive (cashmid)> 
              > create table tuser_seq_copy 
              > row format delimited fields terminated by '\t' lines terminated by '\n' 
              > stored as sequencefile 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_20190923172211_b0f0c7ae-774f-4f4f-953f-1479ab545d08
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1569230191584_0001, Tracking URL = http://centos02:8088/proxy/application_1569230191584_0001/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569230191584_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-23 17:25:26,308 Stage-1 map = 0%,  reduce = 0%
2019-09-23 17:26:26,399 Stage-1 map = 0%,  reduce = 0%
2019-09-23 17:26:34,407 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.47 sec
MapReduce Total cumulative CPU time: 2 seconds 470 msec
Ended Job = job_1569230191584_0001
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_17-22-11_008_4054616679662360617-1/-ext-10002
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_seq_copy
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 2.47 sec   HDFS Read: 93636 HDFS Write: 97101 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 470 msec
OK
hi_tuser_txt.fguid    hi_tuser_txt.fid    hi_tuser_txt.fusername    hi_tuser_txt.fparentid    hi_tuser_txt.fcompanyid    hi_tuser_txt.fstatus    hi_tuser_txt.ts    hi_tuser_txt.faddtimedate    hi_tuser_txt.fadddate    hi_tuser_txt.faddtime    hi_tuser_txt.fip    hi_tuser_txt.fthreadid
Time taken: 273.329 seconds
hive (cashmid)> 

 

显示创建表

hive (cashmid)> show create table cashmid.tuser_seq_copy; 
OK
createtab_stmt
CREATE TABLE `cashmid.tuser_seq_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.serde2.lazy.LazySimpleSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\t', 
  'line.delim'='\n', 
  'serialization.format'='\t') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.SequenceFileInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
LOCATION
  'hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_seq_copy'
TBLPROPERTIES (
  'transient_lastDdlTime'='1569230803')
Time taken: 0.316 seconds, Fetched: 27 row(s)
hive (cashmid)> 

 

表详细信息

hive (cashmid)> desc formatted tuser_seq_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 17:26:41 CST 2019     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_seq_copy     
Table Type:             MANAGED_TABLE            
Table Parameters:          
    COLUMN_STATS_ACCURATE    {\"BASIC_STATS\":\"true\"}
    numFiles                1                   
    numRows                 677                 
    rawDataSize             86856               
    totalSize               97019               
    transient_lastDdlTime    1569230803          
          
# Storage Information          
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe     
InputFormat:            org.apache.hadoop.mapred.SequenceFileInputFormat     
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat     
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:          
    field.delim             \t                  
    line.delim              \n                  
    serialization.format    \t                  
Time taken: 0.726 seconds, Fetched: 43 row(s)
hive (cashmid)>