【Hive】hql详解

DML语句

1.创建数据库

create database 数据库名

2.修改数据库

 数据库其他元数据都是不可更改的，包括数据库名和数据库所在目录位置

为数据库的dbproperties设置键值对属性值，来描述数据库属性信息，

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

也可以使用该命令修改数据库的用户或权限

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

3.删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name;

4.使用数据库

user dabase_name;

5.建表

实例：

create table test(

 id int,

 name string,

 age int,

 likes array<string>,

 address map<string,string>

 )

 row format delimited fields terminated by ','

 COLLECTION ITEMS TERMINATED by '-'

 map keys terminated by ':'

 lines terminated by '\n';

以上：规定了导入的数据以‘，’分列。数组以‘-’分。map键值对以‘：’分。自动换行

6.删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

7.重命名表

ALTER TABLE table_name RENAME TO new_table_name;

DML语句

1.插入数据的方式

①本地插入

LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename; 

例如：

这是存在本地的数据文件，里面包含array、map

overwrite是覆盖，不带overwrite参数是追加

②从hdfs插入

LOAD DATA INPATH 'filepath' [OVERWRITE] INTO TABLE tablename; 

与本地插入的差别就是少了[local]参数 

③创建表2，然后从表1中导入数据：

INSERT OVERWRITE TABLE test2 

   SELECT id,name, age From ppt;

④与上一种方式很相似，但是from条件放到了前面，【from放前面好处就是后面可以插入多条语句 】

FROM test t1,test2 t2 
INSERT into TABLE test3  
 SELECT t2.id,t2.name,t2.age,t1.likes,t1.address where t1.id=t2.id
INSERT OVERWRITE TABLE test4
 SELECT t1.id,t2.name,t2.age,t1.likes,t1.address where t1.id=t2.id;

⑤就是常用的sql插入语句，但是hive中不建议用这种，因为数据量很大values写不完，就算数据量不大values也得跑mapreduce任务，很耗资源。

2.查询数据并保存到本地

 insert overwrite local directory '/opt/datas/test_emp'
     row format delimited fields terminated by ','
     select * from test;

查询到test表中的数据并表存到了/opt/datas/test_emp文件夹中

3.查询数据并保存到hdfs

 insert overwrite directory '/user/hive/hive_exp'
     select * from test;

4.在shell中将数据重定向到文件中

hive -e "select * from test.test;" > /opt/datas/test_emp/test.txt

查询数据库test中的test表，并输出重定向覆盖到/opt/datas/test_emp/test.txt中

5.备份数据或还原数据

备份在集群中备份，不只是备份数据，连表结构也就是表的元数据信息也一起备份

备份：export table test.test to '/user/hive/export/emp';

先删除表还原表：

还原表语句：import from '/user/hive/export/emp';

Hive SerDe

Hive SerDe - Serializer and Deserializer  SerDe 用于做序列化和反序列化。

构建在数据存储和执行引擎之间，对两者实现解耦。Hive通过ROW FORMAT DELIMITED以及SERDE进行内容的读写。

 建表的时候要引入org.apache.hadoop.hive.serde2.RegexSerDe接口，并且提前构建好正则表达式。

CREATE TABLE logtbl (
    host STRING,
    identity STRING,
    t_user STRING,
    time STRING,
    request STRING,
    referer STRING,
    agent STRING)
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    "input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
  )
  STORED AS TEXTFILE;

我的数据是这样的：

可以看到，数据存储和执行引擎并没有什么清洗数据的操作就直接读取到了有效数据