Hhive

在hive中的语句HQL的练习：
hive进去命令行：
看库：show databases;
建库/删库：create/drop schema hive 不是databases？
进库：use ku
看表：show tables;
建表：create table employee(id int, name string);不需要指定长度mysql需要
显示表结构：desc employee;
显示创建表的信息：show create table employee;
显示表名的分区show partitions 表名; 必须是分区表
删除库：库里有表时删不了库 一个方法是先删除表再删除库 第二种方法drop database 名字+ cascade
按住alt复制不复制尖头
create table employee(
name string,
address array,
personalInfo array,
technol map<string,int>,
jobs array<map<string,string>>)
row format delimited
fields terminated by ‘|’
collection items terminated by ‘,’
map keys terminated by ‘:’
lines terminated by ‘\n’;
上面有逗号 下面都没有
拖到左边 将表上传：hdfs dfs -put employee.txt /opt/hive/warehouse/a.db/employee
create table jobs_detail as
with
tmp as (select jobs from employee_partiton where info.gender=‘Male’),
t2 as (select jobs from employee_partiton where info.gender=‘female’)
select tmp.jobs male_job,t2.jobs female_job from tmp,t2;

临时表：temporary退出即删除文件夹 show create table 查看是否为临时表 desc 不行
create temporary table jobs_detail as
with
tmp as (select jobs from employee_partiton where info.gender=‘Male’),
t2 as (select jobs from employee_partiton where info.gender=‘female’)
select tmp.jobs male_job,t2.jobs female_job from tmp,t2;

create database hive_demo;
use hive_demo;
create external table employee(
name string,
id int,
phone string,
data string)
row format delimited
fields terminated by ‘|’
lines terminated by ‘\n’;

show create table employee;

create temporary table employee(
name string,
id int,
phone string,
data string)
row format delimited
fields terminated by ‘|’
lines terminated by ‘\n’;
location ’要去的文件夹‘！！！！！！！！

partitioned!!!
查看分区：show partitions 表
create table b (
name string,
id int,
phone string,
data string)
partitioned by (year string,month int)
row format delimited
fields terminated by ‘|’
lines terminated by ‘\n’;