create table ..... select 语句(一) textFile

创建表

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

hive> 
    > create table tuser_copy 
    > row format delimited fields terminated by '\t' lines terminated by '\n'  
    > stored as textfile 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_20190923220340_ea49b455-1d7b-4367-9098-08d84fe42cd9
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_1569239441933_0004, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0004/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0004
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-23 22:05:28,298 Stage-1 map = 0%,  reduce = 0%
2019-09-23 22:06:16,429 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.64 sec
MapReduce Total cumulative CPU time: 3 seconds 640 msec
Ended Job = job_1569239441933_0004
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-03-40_234_715623851755198312-1/-ext-10002
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_copy
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 3.64 sec   HDFS Read: 88458 HDFS Write: 87611 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 640 msec
OK
Time taken: 162.218 seconds
hive> 

 

显示创建表 

hive> show create table cashmid.tuser_copy; 
OK
CREATE TABLE `cashmid.tuser_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.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_copy'
TBLPROPERTIES (
  'transient_lastDdlTime'='1569247582')
Time taken: 0.043 seconds, Fetched: 27 row(s)
hive> 

 

表详细信息

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