一、建表语句

1、创建内部表

建表：

CREATE TABLE phone_info(id int,name String,storage String,price double)
ROW FORMAT DELIMITED //代表一行是一条记录
FIELDS TERMINATED BY '\t'//列是按照table键分开
lines terminated by '\n'

将数据加进表中：

hive> load data local inpath 'xxx/a.txt' into table phone_info

2、创建外部表

第一步：在hdfs上建一个文件夹

hive> hdfs dfs -mkdir /test/hive/phone

第二步：上传本地的.txt文档到刚刚建的文件夹里

hive> hdfs dfs -put a.txt /test/hive/phone/

第三步：写建表语句
表结构如下：

create external table empolyee_id(
name string,
id int,
address array<string>,
genderAndAge struct<gender:string,age:int>,
jobAndSalary map<string,int>,
depAndLvl map<string,string>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as Textfile
location "/test/hive/empolyee_id/";

这样数据就添加进表中了

3、建表高阶语句 CTAS 和 WITH

第一种： CATS — as select (会复制表结构和表数据）

create employee_tmp as select * from employee;

第二种： 从已有表中选择出需要的列
CTE (CTAS with Common Table Expression)

create table employee_tmp as 
with
tmp1 as (select name, address from employee where gender='Male'),
tmp2 as (select name, address from employee where gender='Female')
select tmp1.name name1,tmp2.name name2 from tmp1,tmp2;

第三种： Like只复制表结构

create table employee_like like employee;

4、向临时表中插入原表中的数据

insert into employee_like
select 
name,address
from employee where gender="Male";

5、创建分区表

1. 创建一级分区表
   先建表：

create external table employee(
name string,
address array<string>,
genderAndAge struct<gender:string,age:int>,
jobAndSalary array<string>,
depAndLvl array<string>
)
partitioned by(year int,month int)
row format delimited fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
location "/test/hive/employee/"

再加载数据：

load data local inpath "/hivedata/employee.txt" into table employee partition(year=2020,month=12);