create table ..... select 语句(三) ParquetFile

创建表

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

hive> 
    > create table tuser_par_copy 
    > row format delimited fields terminated by '\t' lines terminated by '\n' 
    > stored as parquetfile 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_20190923201446_eb245911-3669-4809-875c-ab76341c69e9
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_0003, Tracking URL = http://centos02:8088/proxy/application_1569239441933_0003/
Kill Command = /opt/bigdata/hadoop/hadoop-2.8.5/bin/hadoop job  -kill job_1569239441933_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-09-23 20:16:33,698 Stage-1 map = 0%,  reduce = 0%
2019-09-23 20:17:34,904 Stage-1 map = 0%,  reduce = 0%
2019-09-23 20:17:36,495 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 9.03 sec
MapReduce Total cumulative CPU time: 9 seconds 30 msec
Ended Job = job_1569239441933_0003
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_20-14-46_060_8470982613478470845-1/-ext-10002
Moving data to directory hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_par_copy
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 9.03 sec   HDFS Read: 88458 HDFS Write: 59013 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 30 msec
OK
Time taken: 178.855 seconds
hive> 

 

显示创建表

hive> show create table cashmid.tuser_par_copy; 
OK
CREATE TABLE `cashmid.tuser_par_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.parquet.serde.ParquetHiveSerDe' 
WITH SERDEPROPERTIES ( 
  'field.delim'='\t', 
  'line.delim'='\n', 
  'serialization.format'='\t') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  'hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_par_copy'
TBLPROPERTIES (
  'transient_lastDdlTime'='1569241064')
Time taken: 0.262 seconds, Fetched: 27 row(s)
hive> 

 

表详细信息

hive> desc formatted tuser_par_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 20:17:43 CST 2019     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://centos02:9000/opt/bigdata/hive/hive-2.3.4/warehouse/cashmid.db/tuser_par_copy     
Table Type:             MANAGED_TABLE            
Table Parameters:          
    COLUMN_STATS_ACCURATE    {\"BASIC_STATS\":\"true\"}
    numFiles                1                   
    numRows                 677                 
    rawDataSize             8124                
    totalSize               58932               
    transient_lastDdlTime    1569241064          
          
# Storage Information          
SerDe Library:          org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe     
InputFormat:            org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat     
OutputFormat:           org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat     
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:          
    field.delim             \t                  
    line.delim              \n                  
    serialization.format    \t                  
Time taken: 0.145 seconds, Fetched: 43 row(s)
hive>