清河县网站建设,廊坊seo外包,wordpress 迁移服务器,网页app生成器最新版目录
建表语句
表数据
Hive建表高阶语句 - CTAS and WITH
CTAS – as select方式建表
CTE (CTAS with Common Table Expression)
LIKE
创建临时表
清空表数据
修改表#xff08;Alter针对元数据#xff09;
改名
修正表文件格式
修改列名
添加列
替换列
动态分…目录
建表语句
表数据
Hive建表高阶语句 - CTAS and WITH
CTAS – as select方式建表
CTE (CTAS with Common Table Expression)
LIKE
创建临时表
清空表数据
修改表Alter针对元数据
改名
修正表文件格式
修改列名
添加列
替换列
动态分区
使用动态分区需设定属性
建表语句 动态分区插入数据
查询分区 添加分区
删除分区 建表语句
create table if not exists employee
(name string,workplace arraystring,gender string,age int,skills_score mapstring,int,depart_title mapstring,string
) row format delimited fields terminated by |collection items terminated by ,map keys terminated by :lines terminated by \n;
表数据 Michael|Montreal,Toronto|Male|30|DB:80|Product:Developer Lead Will|Montreal|Male|35|Perl:85|Product:Lead,Test:Lead Shelley|New York|Female|27|Python:80|Test:Lead,COE:Architect Lucy|Vancouver|Female|57|Sales:89,HR:94|Sales:Lead Hive建表高阶语句 - CTAS and WITH
CTAS – as select方式建表 CREATE TABLE ctas_employee as SELECT * FROM employee; CTAS不能创建partition, external, bucket table CTE (CTAS with Common Table Expression)
CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name Michael),
r2 AS (SELECT name FROM employee WHERE gender Male),
r3 AS (SELECT name FROM employee WHERE gender Female)
SELECT * FROM r1 UNION ALL SELECT * FROM r3;
LIKE CREATE TABLE employee_like LIKE employee; 创建临时表
临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法 表只对当前session有效session退出后自动删除 表空间位于/tmp/hive-user_name(安全考虑) 如果创建的临时表表名已存在实际用的是临时表 CREATE TEMPORARY TABLE tmp_table_name1 (c1 string); CREATE TEMPORARY TABLE tmp_table_name2 AS.. CREATE TEMPORARY TABLE tmp_table_name3 LIKE.. 清空表数据 TRUNCATE TABLE employee; 修改表Alter针对元数据
改名 ALTER TABLE employee RENAME TO new_employee; 修正表文件格式 ALTER TABLE employee SET FILEFORMAT RCFILE; 修改列名 ALTER TABLE employee CHANGE name employee_name STRING; 添加列 ALTER TABLE employee ADD COLUMNS (work string); 替换列 ALTER TABLE employee REPLACE COLUMNS (name string); 创建分区
动态分区
使用动态分区需设定属性 set hive.exec.dynamic.partitiontrue; set hive.exec.dynamic.partition.modenonstrict; 建表语句
create table employee_dt(name string,workplace arraystring,skills_score mapstring,int,depart_title mapstring,string
) partitioned by (gender string,age int )row format delimited fields terminated by ,collection items terminated by -map keys terminated by :lines terminated by \n; 动态分区插入数据
insert into table employee_dt partition (gender,age)
select name, workplace, skills_score, depart_title, gender, age
from employee2;
查询分区 show partitions employee_dt; 添加分区
alter table employee_dtadd partition (gender Male,age 28);删除分区
alter table employee_dtdrop partition (gender Male,age 28);
