Hive Serde-csv, TSV

CSV

hive-0.14.0 built-insupport for CSV Serde, previous versions require the introduction of a third-party library of Jar packages (Http://https://github.com/ogrodnek/csv-serde)
Now there is a text file A.csv (the data exported from the database is usually this format), the contents are as follows:

  
 

   
  

  
[[email protected] ~]$ more a.csv 
   
  

  
‘1‘,‘zhangsan‘,‘20‘,‘beijing,shanghai,shandong‘,1
   
  

  
‘2‘,‘lisi‘,‘22‘,‘guangdong,hainan‘,1
   
  

  
‘3‘,‘wangwu‘,‘23‘,‘shandong,jiangsu,xizang‘,0
  
 

now it needs to be loaded into hive for processing, in the following steps:

1) Introduction of third-party jar packages(versions prior to 0.14)

 
   
  

  
    
   
add jar /xx/yy/zz.jar;
 
   
  

2) Create a table

  
 

   
  
CREATE TABLE T_csv(IDint,namestring,Addressstring,Sex tinyint)row Format Serde' Org.apache.hadoop.hive.serde2.OpenCSVSerde '  withserdeproperties("Separatorchar"=",","QuoteChar"="'","Escapechar"= "\\")stored astextfile;
  
 

Separatorchar is the delimiter between fields
QuoteChar are symbols that include fields, such as single quotes, double quotes
Escapechar is a non-processed character
3) Load Data

 
   
  

  
    
   
load data local inpath ‘${env:HOME}/a.csv‘ into table t_csv;
 
   
  

4) Query

 
   
  

  
    
   
select name, address from t_csv;
 
   
  

TSV
Currently there is no dedicated TSV Serde jar package, but you can use the CSV Serde to set its separatorchar= ' \ t '



From for notes (Wiz)

Hive Serde-csv, TSV