hive语法

row format delimited fields terminated by ‘,’
以’，'结尾的行格式分隔字段
row format delimited fields terminated by ‘\036’ stored as parquet;

1、insert into 和 insert overwrite语句两者的区别：
insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。最后表的数据 只有新插入这一条记录。
insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。最后表的数据是原始数据和新插入的。

2、创建表的几种方式

• 加载本地文件创建内部表

hive -e "
use test_bigdata;
drop table data_result;
CREATE table data_result(
c1 String
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’;
LOAD DATA LOCAL INPATH ‘data_id.txt’ INTO TABLE data_result;"

• 创建外部表

hive -e "
use test_bigdata;
drop table data_result;
CREATE table data_result(
c1 String,
c2 string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
LOCATION ‘/output/20200618’;"

• 创建外部分区表

hive -e "
use test_bigdata;
drop table data_result;
CREATE table data_result(
c1 String,
c2 string
)
PARTITIONED BY (
date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
LOCATION ‘/output/’;

alter table data_resultadd if not exists partition(date=20200601)
location ‘/output/20200601’;

• 创建查询表

hive -e "
use test_bigdata;
drop table data_result1;
create table IF NOT EXISTS data_result1 as
select
c1,
c2
from test_bigdata.data_result;"

• 创建查询分区表

hive -e "
use test_bigdata;
drop table data_result1;
create table IF NOT EXISTS data_result1 partition (date=‘20200601’) as
select
c1,
c2
from test_bigdata.data_result;"