Hive建表

创建表

create [external] table [if not exists] table_name();
-- [ ] : 中括号代表非必选项

-- | : 表示二选一

-- [external] : 外部表，不添加默认内部表
-- 内部表和外部表的区别：
-- 内部表(管理表)：数据库和HDFS的数据都会被删除
-- 外部表：只会删除元数据，HDFS的数据不会被删除。通过建表语句可以恢复，一般都会用外部表
-- [if not exists] : 如果表存在则不创建

创建表并指定字段之间的分隔符

create  table if not exists test
(id int ,
name string) 
partitioned by (year string,month string,day string) -- 分区，可以是一个字段，也可以是多个字段
row format delimited fields terminated by '\t' -- 指定字段之间的分隔符，'\t' 代表tab键
stored as textfile -- 指定文件格式
location '/user/test'; -- 指定存放路径

更多可选参数

  -- 字段名，字段类型 [COMMENT col_comment] : 字段注释
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  -- 表注释
  [COMMENT table_comment]
  -- 分区表
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  -- 分桶表
  [CLUSTERED BY (col_name, col_name, ...)
  -- 局部排序，num_buckets：分几个桶
  [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  -- 局部排序，避免全表扫描
  [SKEWED BY (col_name, col_name, ...)
  -- 定义行的格式
  [ROW FORMAT row_format]
  -- 指定文件格式，默认text
  [STORED AS file_format]
  -- 指定表在HDFS存放路径
  [LOCATION hdfs_path]
  -- 额外属性，如作者，创建时间等
  [TBLPROPERTIES (property_name=property_value, ...)]
  -- 根据查询语句创建表
  [AS select_statement]

根据查询结果创建表

  create table test2 as select * from test1;

根据其它表结构创建表

  create table test3 like test2;

内外表转换

-- 内部表改成外部表
alter table table_name set tblproperties ('EXTERNAL' = 'TRUE');
-- 外部表改成内部表
alter table table_name set tblproperties ('EXTERNAL' = 'FALSE');
-- 注意：'EXTERNAL' = 'TRUE'，'EXTERNAL' = 'FALSE' 必须为大写

查看表详细信息

-- 查看表详细信息
desc formatted table_name;

从本地文件系统向表中加载数据

-- load data local 表示从本地文件系统加载，文件会拷贝到hdfs上

-- 向表中追加数据
load data local inpath '/export/servers/hivedatas/test.csv' into table test;
-- 覆盖
load data local inpath '/export/servers/hivedatas/test.csv' overwrite  into table test;

从HDFS文件系统向表中加载数据

-- load data 表示从hdfs文件系统加载，文件会直接移动到hive相关目录下,如果加载相同文件名的文件，会被自动重命名

load data inpath '/hivedatas/test.csv' into table test;
-- 加载数据到指定分区,如果表是分区表，load 时不指定分区会报错
load data inpath '/hivedatas/test.csv' into table test partition(cur_date=20201210);
-- 加载数据到多个分区
load data local inpath '/export/servers/hivedatas/test.csv' into table test partition(year='2021',month='12',day='01');

查看表分区

show  partitions  table_name;

添加分区

-- 添加一个分区
alter table test add partition(month='201805');
-- 添加多个分区
alter table test add partition(month='201804') partition(month = '201803');
-- 删除分区
alter table test drop partition(month = '201806');

分区表中插入数据 

-- 直接向分区表中插入数据
insert into table test partition(month ='201807') values ('001','002','100');
-- 通过load方式加载数据
load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table test partition(month='201806');
-- 通过查询的方式插入数据
insert overwrite table test partition(month = '201806') select s_id,c_id,s_score from score1;

hive表中数据导出 

-- 将查询的结果导出到本地
insert overwrite local directory '/export/servers/exporthive' select * from test;
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from test;
 -- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from test;
-- Hadoop命令导出到本地
-- /export/servers/exporthive/000000_0 数据地址
-- /export/servers/exporthive/local.txt 存放地址
dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;
-- hive shell 命令导出
-- 基本语法：（hive -f/-e 执行语句或者脚本 > file）
-- “-e”不进入hive的交互窗口执行sql语句
hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt
-- “-f”执行脚本中sql语句
hive -f export.sh > /export/servers/exporthive/score.txt
-- export导出到HDFS上
export table test to '/export/exporthive/score';