Hive建表语句

-- 数据库
-- 创建数据库
CREATE DATABASE IF NOT exists myhivebook;

-- 创建数据库并添加注释
CREATE DATABASE IF NOT exists myhivebook2 COMMENT '数据库测试';

-- 创建数据库并指定路径
CREATE DATABASE IF NOT exists myhivebook3 LOCATION '/hadoop/hive/mydb';
CREATE DATABASE IF NOT exists myhivebook4 LOCATION '/hadoop/hive/myhivebook4';

-- 查询当前所在数据库
select current_database();

-- 切换指定数据库
use myhivebook;

-- 查询所有数据库名称
SHOW DATABASES;

-- 查询指定数据库的元数据
describe DATABASE myhivebook;
DESC DATABASE myhivebook;

-- 删除指定数据库
DROP DATABASE IF exists myhivebook4;

-- 数据表
-- 数据：元数据和业务数据
-- 内部表[MANAGED_TABLE]：元数据归hive管理，业务数据归hive管理
-- 外部表[EXTERNAL_TABLE]：元数据归hive管理，业务数据归hdfs管理

-- 根据已知数据创建（内部）表
CREATE TABLE IF NOT EXISTS employee
(
    name string,
    work_place array<string>,
    sex_age struct<sex:string,age:int>,
    skills_score map<string,int>,
    dept_title map<string,string>
);

-- 查询当前数据库的所有表
show tables;

-- 查询指定数据库的所有表
show tables FROM default;

-- 查询指定表结构
DESC employee;
DESC formatted employee;

-- 添加字段和表的注释
CREATE TABLE IF NOT EXISTS employee2
(
    name string comment '姓名',
    work_place array<string> comment '工作地点',
    sex_age struct<sex:string,age:int> comment '性别年龄',
    skills_score map<string,int> comment '技能评分',
    dept_title map<string,string> comment '部门职称'
) comment '员工信息表';

-- 创建外部表
CREATE external TABLE IF NOT EXISTS external_employee
(
    name string comment '姓名',
    work_place array<string> comment '工作地点',
    sex_age struct<sex:string,age:int> comment '性别年龄',
    skills_score map<string,int> comment '技能评分',
    dept_title map<string,string> comment '部门职称'
) comment '员工信息表'
row FORMAT delimited
fields terminated BY '|'
collection items terminated BY ','
map keys terminated BY ':'
lines terminated BY '\n'
stored AS textfile
location '/hadoop/hive/warehouse/myhivebook.db/external_employee'
;

dfs -put /root/data/employee.txt /hadoop/hive/warehouse/myhivebook.db/external_employee;

SELECT * FROM external_employee;

-- 创建内部表
CREATE TABLE IF NOT EXISTS managed_employee
(
    name string comment '姓名',
    work_place array<string> comment '工作地点',
    sex_age struct<sex:string,age:int> comment '性别年龄',
    skills_score map<string,int> comment '技能评分',
    dept_title map<string,string> comment '部门职称'
) comment '员工信息表'
row FORMAT delimited
fields terminated BY '|'
collection items terminated BY ','
map keys terminated BY ':'
lines terminated BY '\n'
stored AS textfile
location '/hadoop/hive/warehouse/myhivebook.db/managed_employee'
;

dfs -put /root/data/employee.txt /hadoop/hive/warehouse/myhivebook.db/managed_employee;

SELECT * FROM managed_employee;

-- 演示内部表和外部表删除的区别
DROP TABLE managed_employee;
DROP TABLE external_employee;
dfs -rm -r /hadoop/hive/warehouse/myhivebook.db/external_employee;

-- CTAS[Create Table As Select]，既有表结构又有数据
CREATE TABLE managed_employee_2 AS SELECT * FROM managed_employee;

SELECT * FROM managed_employee_2;
 
-- 只有表结构而没有数据
CREATE TABLE managed_employee_3 AS SELECT * FROM managed_employee WHERE 1=0;

SELECT * FROM managed_employee_3;

CREATE TABLE managed_employee_4 LIKE managed_employee;
DESC managed_employee_4;
SELECT * FROM managed_employee_4;

-- 创建临时表
CREATE temporary TABLE managed_employee_5 AS SELECT * FROM managed_employee;
SELECT * FROM managed_employee_5;
!CLOSE
!connect jdbc:hive2://hadoop101:10000
SELECT * FROM managed_employee_5;

-- 修改表名
ALTER TABLE employee3 rename TO employee1;

-- 修改字段
ALTER TABLE employee2 change COLUMN sex_age sex string;

-- 添加字段
ALTER TABLE employee2 ADD columns (age int,nick string);

-- 替换字段
ALTER TABLE employee2 REPLACE columns (name string,age int,nick string);

-- 删除表业务数据但保留元数据
TRUNCATE TABLE managed_employee_3;

-- 删除表包括元数据和业务数据
DROP TABLE managed_employee_2;