Hive数据操作语言（DML）

1.将数据装载到表中

1.1使用存储在HDFS中的文件装载数据

hive支持从hadoop分布式文件系统上传文件。这是将数据迁移到hive生态系统最基本的方法。

Hive语法如下：

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

LOAD DATA            向Hive装载数据的关键字

LOCAL                    如果包含该关键字，则支持用户从其本地文件装载数据

                                如果省略该关键字，则从hadoop配置变量fs.default.name中设定的路径加载文件

INPATH 'filepath'     如果使用LOCAL:file///user/hive/example/person001.csv

                                如果省略LOCAL:hdfs:///namenode:9000/user/hive/example/person001.csv

OVERWRITE          如果包含，支持用户将数据装载到一个早已建好的表中并且替换原来的数据

                               如果省略，支持用户将数据装载到一个早已建好的表中并且将新数据追加到原来的数据后面

INTO TABLE tablename  tablename是Hive中已经存在的表的名称

                                         使用CREATE TABLE table_name;

$HIVE_HOME/bin/hive                                             //启动hive

CREATE DATABASE census;                                  //创建一个名为census的库

USE census;                                                            //使用该数据库

CREATE TABLE person(persid int,lastname string,firstname string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

LOAD DATA LOCAL INPATH 'file:///hive/sample/person001.csv' OVERWRITE INTO TABLE person;

SELECT persid,lastname,firstname FROM person;

1.2 使用查询装载数据

Hive支持将从已有表查询到的数据装载到Hive生态系统中。

Hive语法如下：

INERT [OVERWRITE] TABLE tablename [IF NOT EXISTS] SELECT fiedls FROM from_statement;

---

INSERT                                 用于将数据装载到Hive表中的关键字

OVERWRITE                        如果包含，支持用户将数据装载到已经建好的表中，并且替换之前的数据

                                              如果省略，支持用户将数据装载到已经建好的表中，并且追加到之前数据后面

TABLE tablename                 tablename 是hive中已有的表名，使用CREATE TABLE tablename语句

IF NOT EXISTS                    如果在命令中包含了IF NOT EXISTS ，那么Hive命令将在当前数据库中创建一个表

                                             如果省略，当该表不存在时将执行失败

INSERT OVERWRITE TABLE person SELECT DISTINCT persid FROM person;

INSERT OVERWRITE TABLE person SELECT DISTINCT persid+1000 FROM person;

1.3 将查询到的数据写入到文件系统

Hive支持将查询到的数据装载到Hadoop分布式文件系统中。

Hive语法如下：

INSERT [OVERWRITE] DIRECTORY directoryname SELECT fields FROM tablename;

---

INSERT                 用于将数据装载到Hive目录中的关键字

OVERWRITE        如果包含，支持用户将数据装载到一个已经创建好的目录中并且替换之前的数据

                              如果省略，支持用户将数据装载到一个已经创建好的目录中并将数据追加到之前的数据后面

DIRECTORY directoryname      directoryname 是hadoop分布式文件系统中已有的目录名称，使用hadoopfs -mkdir directoryname来创建一个目录，这可以是任何针对Hive生态系统的SELECT 命令

hadoop fs -mkdir 'exampleoutput'    //在hdfs上创建一个名字为exampleoutput的目录

INSERT OVERWRITE DIRECTORY 'exampleoutput' ROW FORMAT DELIMITED FIELDS TERMINATED BY "," SELECT persid,lastname,firstname FROM person;

hadoop fs -cat 'exampleoutput/000000_0'    //查看

1.4 直接向表插入数据

Hive支持用一系列静态值直接将数据装载到表中

Hive语法如下：

INSERT INTO tablename VALUES (row_value1),(row_value2);

---

INSERT                            用于将数据装载到Hive目录的关键字

TABLE tablename            tablename是hive中已有的表名称。使用CREATE TABLE tablename 语句

VALUES(row_value1),(row_value2)  值row_value1和row_value2是相同格式的单条记录，而不是表的记录

1.5直接更新表中数据

Hive支持直接将数据更新到表中

Hive语法如下：

UPDATE tablename SET column =value [WHERE expression];

---

UPDATE                           用于更新表值得关键字

tablename                        tablename是hive中已有表的名称。使用CREATE TABLE tablename语句

SET column =value          SET命令更新该列的一个值

[WHERE expression]       WHERE 可用于为不同的查询挑选特定列的值

UPDATE tablename SET lastname ='s' WHERE persid = 0;

1.6 表中直接删除数据

Hive支持直接在表中删除数据

Hive语法如下：

DELETE tablename [WHERE expression];

DELETE                                           用于删除表中值得关键字

tablename                                        tablename是hive中已有标的名称。使用CREATE TABLE tablename语句

[WHERE expression]                       WHERE可以用于挑选查询要删除的特定列的值

CREATE TABLE person(

       persid         int,

       firstname    string,

       lastname    string

) CLUSTERED BY(persid) INTO 1 BUCKETS STORED AS orc TBLPROPERTIES('transactional'='true');

DELETE FROM person WHERE persid =0;

1.7 创建结构相同的表

Hive支持基于一个已有表的结构创建一个表

Hive语法如下：

CREATE TABLE tablename2 LIKE tablename;

2 连接

2.1 使用等值连接来整合表

Hive支持表之间的等值连接，使你能够整合来自两个表的数据

Hive语法如下：

SELECT fields FROM tableone JOIN tabletwo ON(tableone.fields=tabletwo.fields  AND tableone.fields=tabletwo.fields);

---

SELECT fields                             用于从两个表中选取一系列字段的关键字

FROM tableone JOIN tabletwo   罗列出两个为了检索fields而进行连接操作的表

ON(tableone.fields=tabletwo.fields  AND tableone.fields=tabletwo.fields)     列出连接两个表等值规则

首先创建下面两张表：

CREATE TABLE personname(

persid int,

firstname string,

lastname string

) CLUSTERED BY (persid) INTO 1 BUCKETS STORED AS orc TBLPROPERTIES('transactional'='true');

CREATE TABLE personaddress(

persid int,

postname string

) CLUSTERED BY (persid) INTO 1 BUCKETS STORED AS orc TBLPROPERTIES('transactional'='true');

现在执行连接操作:

SELECT personname.firstname,personname.lastname,personaddress.postname FROM personname join personaddress on (personname.persid==personaddress.persid);

2.2 使用外连接

Hive支持采用LEFT,RIGHT和FULL OUTER等连接方式实现表之间的等值连接，其中无匹配的键

Hive语法如下：

SELECT fields FROM tableone [LEFT,RIGHT,FULL OUTER] JOIN tabletwo ON (tableone.fields=tabletwo.fields AND tableone.fields=tabletwo.fields);

---

FROM tableone LEFT JOIN tabletwo  leftL连接产生的结果包含表tableone中匹配where语句的字段值以及表tabletwo中匹配和不匹配where语句的字段值

FROM tableone RIGHT JOIN tabletwo  right连接产生的结果包含表tabletwo中匹配where语句的字段值以及表tableone中匹配和不匹配where语句的字段值

FROM tableone FULL OUTER JOIN tabletwo  full outer join 连接将返回tabletwo和tableone中字段值；当有不匹配where语句的行时，其字段值为null

2.3 使用左半连接

Hive支持表之间的嵌套连接。假设有如下这样的嵌套连接：

SELECT * FROM a WHERE a.name IN (SELECT name from b);

由于采用分布式处理，查询该语句在Hive中会失败。

Hive可以处理查询并且使用SEMT JOIN 命令。

Hive语法如下：

SELECT fields FROM tableone LEFT SEMTJOIN tabletwo ON(tableone.key=tabletwo.key);

---

FROM tableone LEFT SEMT JOIN tabletwo   罗列为检索table_fields而进行半连接操作的两个表 ，作用相当于where in

2.4 用单次MapReduce实现连接

如果在连接中使用了公共键，Hive还支持通过一次MapReduce来连接多个表。

Hive语法如下：

SELECT table.fields FROM tableone JOIN tabletwo ON(tableone.key==tabletwo.key) JOIN tablethree ON(tableone.key==tablethree.key);

2.5 最后使用最大的表

Hive在实施连接时可以先缓存前几个要连接的表，然后针对它们映射最后一个表。总是将最大的表放在后面是一种比较好的实践方法，因为这样做会加速处理过程。

Hive语法如下：

SELECT tableone.key FROM a JOIN tabletwo On(tableone.key==tabletwo.key) JOIN tablethree ON(tableone.key==tablethree.key);

tableone\tabletwo         在内存中缓存

tablethree                     直接从磁盘映射  

2.6 事物处理

Hive支持合乎ACID规定的事务处理。这样通过在Hive数据库确保完整性，使得对事务处理的支持遵从完备性。

注意：对于大多数Hive安装环境来说，这并不是一种默认设置，因为这将带来性能方面的影响，原因在于为确保ACID合规性需要进行额外的处理。

2.7 ACID是什么？以及为什么要用到它？

ACID代表数据库事务处理的4个特征：

1.原子性：一个操作要么成功，要么失败，操作不会在系统中留下未完成的数据

2.一致性：一个操作一旦完成，该操作的结果对随后的每个操作都是可见的

3.隔离性：一个用户完成操作不会对其他用户造成不可预期的负面影响

4.持久性：当一个操作完成，即使机器或系统出现了故障，仍然会保留该操作的效果

2.8 Hive配置

Hive通过设置正确的参数来支持事务处理。

为了支持事务处理，需要设置下面设置项。为了hive中开启事务支持，必须正确设置这些配置参数：

hive.support.concurrency         true

hive.enforce.bucketing             true

hive.exec.dynamic.partition.mode  nonstrict

hive.txn,manager            org.apache.hadoop.hive.ql.lockmgr.DbTxnmanager

hive.compactor.initiator.on        在Thrift元存储服务的一个实例上为true

hive.compactor.worker.threads   对于Thrift 元存储服务的一个实例为10

请使用一下创建表的格式：

CREATE TABLE tableone(

keyField int,

valueFieldone string,

valueFieldTwo string

)

CLUSTERED BY (keyField) INTO x BUCKETS STORED AS orc TBLPROPERTIES('transactional'='true');