Hive建表语句

-- 单字符分隔符
create table t1
(
a int, 
b string
) 
row format delimited 
fields terminated by ',';

-- 单字符分隔符、跳过首行
create table t1
(
a int, 
b string
) 
row format delimited 
fields terminated by ','
SKIP_HEADER=TRUE;

-- 单字符分隔符、跳过第1行
create table t1
(
a int, 
b string
) 
row format delimited 
fields terminated by ','
SKIP_HEADER=TRUE
tblproperties("skip.header.line.count"="1");

-- 单字符分隔符、双引号括起来
CREATE TABLE t1 
( 
a int, 
b string
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','  
ESCAPED BY '"'; 

CREATE TABLE t1 
( 
a int, 
b string
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
   "separatorChar" = ",", 
   "quoteChar"     = "\"" 
);

-- 多字符分隔符
create external table test02
(
a int, 
b string
)
row format serde
'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
with serdeproperties ("field.delim"=",,");

-- 正则表达式分隔符
create external table test03
(
a int, 
b string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "([^ ,]+),+([^ ,]+),,([^ ,]+)");


-- 指定文件格式
create table t1
(
a int, 
b string
) 
stored as textfile|sequencefile|rcfile|orc|parquet;

-- 指定文件路径
create table t1
(
a int, 
b string
) 
row format delimited 
fields terminated by ','
location '/hivedatatest/t1.csv';

-- 外部表
create EXTERNAL table t1
(
a int, 
b string
) 
row format delimited 
fields terminated by ','
location '/hivedatatest/t1.csv';

-- 分区表
create EXTERNAL table t1
(
a int, 
b string
) 
PARTITIONED BY (filedate string);


-- 增量load数据
LOAD DATA INPATH '/user/test1.csv' INTO TABLE t1 

-- 覆盖load数据
LOAD DATA INPATH '/user/test1.csv' OVERWRITE INTO TABLE t1