您的位置: 首页  >  文章  >  Hive的数据类型、文件格式、存储架构和HQL语法

Hive的数据类型、文件格式、存储架构和HQL语法

分类: 文章 • 2023-12-13 15:58:46

Hive的数据类型、文件格式、存储架构和HQL语法

Hive 数据类型和文件格式

一 概述

Hive支持关系型数据库中的大多数基本数据类型,同时也支持关系型数据库中很少出现的3种集合数据类型.

1、基本数据类型

Hive支持多种不同长度的整型和浮点型数据类型,支持布尔类型,也支持无长度限制的字符串类型.

数据类型 长度 例子
TINYINT 1byte 有符号整数 20
SMALLINT 2byte 有符号整数 20
INT 4byte 有符号整数 20
BIGINT 8byte 有符号整数 20
BOOLEAN 布尔类型trueflase TRUE
FLOAT 单精度浮点数 3.14159
DOUBLE 双精度浮点数 3.14159
STRING 字符序列 ‘now is time’
TIMESTAMP 整数 ‘2012-02-03 12:34:56’
BINARY 字节数组  

2 集合数据类型

Hive中列支持使用struct map array集合数据类型.

数据类型 长度 例子
STRUCT 和C语言中的struct类似 struct(‘John’,’Doe’)
MAP MAP是一组键值对集合 map(‘first’,’join’,’last’,’doe’)
ARRAY 数组是一组具有相同类型和名称的变量的集合 Array(‘John’,’Doe’)

我们来看一个简单的集合类型的例子

复制代码0: jdbc:hive2://localhost:10000/default> create table employees(. . . . . . . . . . . . . . . . . . . .> name STRING,. . . . . . . . . . . . . . . . . . . .> salary FLOAT,. . . . . . . . . . . . . . . . . . . .> subordinates ARRAY<STRING>,. . . . . . . . . . . . . . . . . . . .> deductions MAP<STRING,FLOAT>,. . . . . . . . . . . . . . . . . . . .> address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>);OKNo rows affected (1.228 seconds)0: jdbc:hive2://localhost:10000/default>

Hive的存储架构和HQL语法

一 概述

当然，Hive和传统的关系型数据库有很大的区别，Hive将外部的任务解析成一个MapReduce可执行计划，而启动MapReduce是一个高延迟的一件事，每次提交任务和执行任务都需要消耗很多时间，这也就决定Hive只能处理一些高延迟的应用（如果你想处理低延迟的应用，你可以去考虑一下Hbase）。

同时，由于设计的目标不一样，Hive目前还不支持事务；不能对表数据进行修改（不能更新、删除、插入；只能通过文件追加数据、重新导入数据）；不能对列建立索引（但是Hive支持索引的建立，但是不能提高Hive的查询速度。如果你想提高Hive的查询速度，请学习Hive的分区、桶的应用）。

Hive的数据类型、文件格式、存储架构和HQL语法

图片来源网络，侵权删除

Hive的数据类型、文件格式、存储架构和HQL语法

图片来源网络，侵权删除

1主要分为以下几个部分：

  • 用户接口主要有三个：CLI，Client 和 WUI。其中最常用的是CLI，Cli启动的时候，会同时启动一个Hive副本。Client是Hive的客户端，用户连接至Hive Server。在启动 Client模式的时候，需要指出Hive Server所在节点，并且在该节点启动Hive Server。 WUI是通过浏览器访问Hive。

  • Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

  • 解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有MapReduce调用执行。

  • Hive的数据存储在HDFS中，大部分的查询、计算由MapReduce完成（包含的查询，比如selectfrom tbl不会生成MapRedcue任务）。

2 元数据存储模式

Hive中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。由于Hive的元数据需要不断的更新、修改，而HDFS系统中的文件是多读少改的，这显然不能将Hive的元数据存储在HDFS中。目前Hive将元数据存储在数据库中，如Mysql、Derby中。我们可以通过以下的配置来修改Hive元数据的存储方式：配置mysql访问地址，用户名及密码

  1. <property>

  2. <name>javax.jdo.option.ConnectionURL</name>

  3. <value>jdbc:mysql://192.168.1.178:3306/hive?createDatabaseIfNotExist=true</value>

  4. <description>JDBC connect string for a JDBC metastore</description>

  5. </property>

  6. <property>

  7. <name>javax.jdo.option.ConnectionDriverName</name>

  8. <value>com.mysql.jdbc.Driver</value>

  9. <description>Driver class name for a JDBC metastore</description>

  10. </property>

  11. <property>

  12. <name>javax.jdo.option.ConnectionUserName</name>

  13. <value>hive</value>

  14. <description>username to use against metastore database</description>

  15. </property>

  16. <property>

  17. <name>javax.jdo.option.ConnectionPassword</name>

  18. <value>hive</value>

  19. <description>password to use against metastore database</description>

  20. </property>

hive会把所有的源数据存储在mysql上

  1. [email protected]:~$ mysql-u root-p

  2. Enterpassword:

  3. Welcometo theMySQLmonitor.Commandsendwith;or\g.

  4. YourMySQLconnection idis60

  5. Serverversion:5.5.44-0ubuntu0.14.04.1(Ubuntu)

  6. Copyright(c)2000,2015,Oracleand/orits affiliates.Allrights reserved.

  7. Oracleisa registered trademark ofOracleCorporationand/orits

  8. affiliates.Othernames may be trademarks of their respective

  9. owners.

  10. Type'help;'or'\h'forhelp.Type'\c'to clear the current input statement.

  11. mysql>show databases;

  12. +--------------------+

  13. |Database|

  14. +--------------------+

  15. |information_schema|

  16. |hive|

  17. |hive_hadoop|

  18. |hivetestdb|

  19. |mysql|

  20. |performance_schema|

  21. +--------------------+

  22. 6rowsinset(0.03sec)

  23. mysql>usehive;

  24. Readingtable informationforcompletion of tableandcolumn names

  25. Youcan turn offthisfeature togeta quicker startupwith-A

  26. Databasechanged

  27. mysql>show tables;

  28. +---------------------------+

  29. |Tables_in_hive|

  30. +---------------------------+

  31. |BUCKETING_COLS|

  32. |CDS|

  33. |COLUMNS_V2|

  34. |DATABASE_PARAMS|

  35. |DBS|

  36. |FUNCS|

  37. |FUNC_RU|

  38. |GLOBAL_PRIVS|

  39. |PARTITIONS|

  40. |PARTITION_KEYS|

  41. |PARTITION_KEY_VALS|

  42. |PARTITION_PARAMS|

  43. |PART_COL_STATS|

  44. |ROLES|

  45. |SDS|

  46. |SD_PARAMS|

  47. |SEQUENCE_TABLE|

  48. |SERDES|

  49. |SERDE_PARAMS|

  50. |SKEWED_COL_NAMES|

  51. |SKEWED_COL_VALUE_LOC_MAP|

  52. |SKEWED_STRING_LIST|

  53. |SKEWED_STRING_LIST_VALUES|

  54. |SKEWED_VALUES|

  55. |SORT_COLS|

  56. |TABLE_PARAMS|

  57. |TAB_COL_STATS|

  58. |TBLS|

  59. |VERSION|

  60. +---------------------------+

  61. 29rowsinset(0.00sec)

  62. mysql>

二 数据存储

首先，Hive 没有专门的数据存储格式，也没有为数据建立索引，用户可以非常*的组织 Hive 中的表，只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。Hive中所有的数据都存储在HDFS中，存储结构主要包括数据库、文件、表和视图。 默认路径在HDFS的/user/hive/warehouse目录下Hive中包含以下数据模型：Table内部表，External Table外部表，Partition分区，Bucket桶。Hive默认可以直接加载文本文件，还支持sequence file 、RCFile。其次，Hive 中所有的数据都存储在 HDFS 中，Hive 中包含以下数据模型：Table，External Table，Partition，Bucket。

1）Hive数据库

Hive中的数据库的概念本质上仅仅是表的一个目录或者命名空间.然而,对于具有很多组和用户的大集群来说,这是非常有用的,因为这样可以避免命名冲突.通常会使用数据库来将生产表组织成逻辑组.

如果用户没有显式指定数据库,那么将会使用默认的数据库default.

  1. 简单示例命令行

  2. hive>create database test_database;

  3. hive>show databases;

  4. OK

  5. default

  6. test_database

如果数据库finacials已经存在的话,那么将会抛出一个错误信息,使用如下语句可以避免在这种情况下抛出错误信息:

  1. 0:jdbc:hive2://localhost:10000/default> create database if not exists fincials;

如果数据库非常多的话,那么可以使用正则表达式匹配来筛选出需要的数据库表名

  1. 0:jdbc:hive2://localhost:10000/default> show databases like 'd.*';

  2. OK

  3. +----------------+--+

  4. |database_name|

  5. +----------------+--+

  6. |default|

  7. +----------------+--+

  8. 1row selected(0.052seconds)

Hive会为每个数据创建一个目录,数据库中的表将会以这个数据库目录的子目录形式存储.有一个例外就是default数据库中的表,因为这个数据库本身没有自已的目录.当我们创建数据库financials时,Hive将会对应地创建一个目录/user/hive/warehouse/financials.db.这里请注意,数据库的文件目录名是以.db结尾的.

  1. [email protected]:~$ hdfs dfs-ls/user/hive/warehouse

  2. Found4items

  3. drwxrwxr-x-hadoop supergroup02016-07-2017:25/user/hive/warehouse/employees

  4. drwxrwxr-x-hadoop supergroup02016-07-2112:53/user/hive/warehouse/fincials.db

  5. drwxrwxr-x-hadoop supergroup02016-07-2009:50/user/hive/warehouse/t_hive

  6. drwxrwxr-x-hadoop supergroup02016-07-2009:54/user/hive/warehouse/t_hive2

用户可以通过如下命令来修改这个默认的位置

  1. 0:jdbc:hive2://localhost:10000/default> create database financials2

  2. ....................>location'/user/hive/warehouse/fincials2.db';

  3. OK

  4. Norows affected(0.053seconds)

基本的数据库操作

  1. 0:jdbc:hive2://localhost:10000/default> show databases;

  2. OK

  3. +----------------+--+

  4. |database_name|

  5. +----------------+--+

  6. |default|

  7. |financials2|

  8. |fincials|

  9. +----------------+--+

  10. 3rows selected(0.029seconds)

  11. 0:jdbc:hive2://localhost:10000/default> use fincials;

  12. OK

  13. Norows affected(0.041seconds)

  14. 0:jdbc:hive2://localhost:10000/default> drop database if exists financials2;

  15. OK

  16. Norows affected(0.238seconds)

  17. 0:jdbc:hive2://localhost:10000/default>

2）内部表

Hive的内部表与数据库中的Table在概念上是类似。每一个Table在Hive中都有一个相应的目录存储数据。

例如一个表pvs，它在HDFS中的路径为/warehouse/pvs，其中wh是在hive-site.xml中由${hive.metastore.warehouse.dir} 指定的数据仓库的目录，所有的Table数据（不包括External Table）都保存在这个目录中。删除表时，元数据与数据都会被删除。

内部表简单示例：

  1. hive>create table  test_inner_table(user_idint,cidstring,ckidstring,usernamestring)row format delimited fields terminatedby'\t'lines terminatedby'\n';

导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。及要我们的文件内容格式：

  1. 100636100890c5c86f4cddc15eb7 yyyvybtvt

  2. 10061210086597cc70d411c18b6fgyvcycy

  3. 100078100087ecd6026a15ffddf5 qa000100

首先在/tmp/目录下面建一个文件load.txt

  1. sudo nano load.txt

  2. hive>load datalocalinpath'/tmp/load.txt'intotable test_inner_table;

  3. hive>select*fromtest_inner_table;

  4. hive>selectcount(*)fromtest_inner_table;

Hive 中的 Table 和数据库中的 Table 在概念上是类似的，每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如，一个表 pvs，它在 HDFS 中的路径为：/warehouse/pvs，其中，warehouse 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录，所有的 Table 数据（不包括 External Table）都保存在这个目录中。

3）外部表

外部表指向已经在HDFS中存在的数据，可以创建Partition。它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。

内部表的创建过程和数据加载过程这两个过程可以分别独立完成，也可以在同一个语句中完成，在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。而外部表只有一个过程，加载数据和创建表同时完成（CREATE EXTERNAL TABLE ……LOCATION），实际数据是存储在LOCATION后面指定的 HDFS 路径中，并不会移动到数据仓库目录中。当删除一个External Table时，仅删除该链接。

外部表简单示例：

  1. hive>create external table  test_external_table(user_idint,cidstring,ckidstring,usernamestring)row format delimited fields terminatedby'\t'lines terminatedby'\n';

导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。及要我们的文件内容格式：

  1. 100636100890c5c86f4cddc15eb7 yyyvybtvt

  2. 10061210086597cc70d411c18b6fgyvcycy

  3. 100078100087ecd6026a15ffddf5 qa000100

首先在/tmp/目录下面建一个文件load.txt

  1. sudo nano load.txt

  2. hive>load datalocalinpath'/tmp/load.txt'intotable test_external_table;

  3. hive>select*fromtest_external_table;

  4. hive>selectcount(*)fromtest_external_table;

4）分区表

数据分区的概念存在已久.其可以有多种形式,但是通常使用分区来水平分散压力,将数据从物理上转移到和使用最频繁的用户更近的地方,以及实现其他目的.

Hive中有分区表的概念.我们可以看到分区表具有重要的性能优势,而且分区表还可以将数据以一种符合逻辑的方式进行组织,比如分层存储.

Partition对应于数据库中的Partition列的密集索引，但是Hive中Partition的组织方式和数据库中的很不相同。在Hive中，表中的一个Partition对应于表下的一个目录，所有的Partition的数据都存储在对应的目录中。

例如logs表中包含ds和city两个Partition，则对应于ds = 20090801, ctry = US 的HDFS子目录为/wh/pvs/ds=20090801/ctry=US；对应于 ds = 20090801, ctry = CA 的HDFS子目录为/wh/pvs/ds=20090801/ctry=CA。

分区表简单示例：

  1. hive>create table logs(ts bigint,linestring)partitionedby(dtstring,countrystring)row format delimited fields terminatedby'$'lines terminatedby'\n';

  2. data.txt内容:

  3. [email protected]:/tmp$ more data1.txt

  4. 1$1

  5. 2$3

  6. 3$4

  7. 加载数据：

  8. hive>load datalocalinpath'/tmp/data.txt'intotable logs partition(dt='2015-01-01',country='zh');

  9. hive>load datalocalinpath'/tmp/data.txt'intotable logs partition(dt='2015-04-05',country='jp');

  10. hive>load datalocalinpath'/tmp/data.txt'intotable logs partition(dt='2015-04-05',country='zh');

  11. 查看数据：

  12. 0:jdbc:hive2://localhost:10000/default> select * from logs;

  13. OK

  14. +----------+------------+-------------+---------------+--+

  15. |logs.ts|logs.line|logs.dt|logs.country|

  16. +----------+------------+-------------+---------------+--+

  17. |1|1|2015-04-05|jp|

  18. |2|3|2015-04-05|jp|

  19. |3|4|2015-04-05|jp|

  20. |NULL|NULL|2015-04-05|jp|

  21. |1|1|2015-04-08|cn|

  22. |2|3|2015-04-08|cn|

  23. |3|4|2015-04-08|cn|

  24. |NULL|NULL|2015-04-08|cn|

  25. +----------+------------+-------------+---------------+--+

  26. 8rows selected(0.091seconds)

Partition 对应于数据库中的 Partition 列的密集索引，但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。

例如：pvs 表中包含 ds 和 city 两个 Partition，则对应于 ds = 20090801, ctry = US 的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；对应于 ds = 20090801, ctry = CA 的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA,我们看一下物理存储的结构

Hive的数据类型、文件格式、存储架构和HQL语法

Hive的数据类型、文件格式、存储架构和HQL语法

Hive的数据类型、文件格式、存储架构和HQL语法

6) 外部分区表

外部表同样可以使用分区.事实上,用户可能会发现,这是管理大型生产型数据最常见的情况.这种结合给用户提供了一个可以和其他工具共享数据的方式,同时也可以优化查询性能.

因为用户可以自已定义目录结构,因此用户对于目录结构的使用具有更多的灵活性.

我们来举一个新例子,非常适合这种场景,即日志文件分析.对于日志文件信息,大多数的组织使用一种标准的格式,其中记录有时间戳,严重程度,也许还包含有服务器名称和进程ID,然后跟着一个可以为任何内容的文本信息.

  1. create external tableifnotexists log_messages

  2. (

  3. hms    INT,

  4. serverity STRING,

  5. server     STRING,

  6. process_id INT,

  7. message    STRING

  8. )partitionedby(yearint,monthint,dayint)

  9. row format delimited fields terminatedby'\t';

  10. alter table log_message add partition(year=2012,month=1,day=2)

  11. location'hdfs://hadoopmaster/data/log/log_message/2012/01/02'

  12. show partitions log_messages;

  13. describe extended log_messages;

5）桶

Buckets是将表的列通过Hash算法进一步分解成不同的文件存储。它对指定列计算hash，根据hash值切分数据，目的是为了并行，每一个Bucket对应一个文件。例如将user列分散至32个bucket，首先对user列的值计算hash，对应hash值为0的HDFS目录为/wh/pvs/ds=20090801/ctry=US/part-00000；hash值为20的HDFS目录为/wh/pvs/ds=20090801/ctry=US/part-00020。如果想应用很多的Map任务这样是不错的选择。

  1. 桶的简单示例：

  2. 创建数据文件：test_bucket_table.txt

  3. 创建表：按id划分4个桶

  4. hive>CREATE TABLE bucketed_user(id INT,name STRING)CLUSTERED BY(id)INTO4BUCKETS row format delimited fields terminatedby'\t'lines terminatedby'\n';

  5. 加载数据：insert overwrite table bucketed_userselect*fromusers;

  6. 查看数据：select*frombucket_user;sethive.enforce.bucketing=true;

Buckets 对指定列计算 hash，根据 hash 值切分数据，目的是为了并行，每一个 Bucket 对应一个文件。

将 user 列分散至 32 个 bucket，首先对 user 列的值计算 hash，对应 hash 值为 0 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00000；hash 值为 20 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00020

四 HQL DDL语法

1 前言

Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，

可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户很方便的利用SQL 语言查询，汇总，分析数据。

而mapreduce开发人员可以把己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。它与关系型数据库的SQL 略有不同，但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。

HIVE不适合用于联机（online)事务处理，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。HIVE的特点：可伸缩（在Hadoop的集群上动态的添加设备），可扩展，容错，输入格式的松散耦合。

2 DDL操作

  • 建表

  • 删除表

  • 修改表结构

  • 创建/删除视图

  • 显示命令

建表

1.建立一个简单的表

hive> CREATE TABLE pokes (foo INT, bar STRING);

2.创建表并创建索引字段ds

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

3.复制一个空表

hive> CREATE TABLE empty_key_value_store LIKE testuser;

4.建立一个需要导入数据的表

    1. hive>create table  user_info(user_idint,cidstring,ckidstring,usernamestring)row format delimited fields terminatedby'\t'lines terminatedby'\n';

    2. 导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。

    3. 及要我们的文件内容格式：

    4. 100636100890c5c86f4cddc15eb7 yyyvybtvt

    5. 10061210086597cc70d411c18b6fgyvcycy

    6. 100078100087ecd6026a15ffddf5 qa000100

    7. 首先在/tmp/目录下面建一个文件load.txt

    8. #sudo nano load.txt

    9. hive>load datalocalinpath'/tmp/load.txt'intotable user_info;

    10. hive>select*fromuser_info;

5 显示所有表

hive> show tables;

6 按正则表达式显示表

hive> show tables '.*s';

7 给表增加一列

    1. hive>ALTER TABLE pokes ADD COLUMNS(new_col INT);

    2. OK

    3. Timetaken:0.238seconds

    4. hive>desc pokes;

    5. OK

    6. fooint

    7. barstring

    8. new_colint

    9. Timetaken:0.275seconds,Fetched:3row(s)

8 给表添加一列并添加字段解析

    1. hive>ALTER TABLE invites ADD COLUMNS(new_col2 INT COMMENT'a comment');

    2. OK

    3. Timetaken:0.151seconds

    4. hive>desc invites;

    5. OK

    6. fooint

    7. barstring

    8. new_col2inta comment

    9. dsstring

    10. # Partition Information

    11. # col_name                data_type               comment

    12. dsstring

    13. Timetaken:0.163seconds,Fetched:9row(s)

9 更改表名字

    1. hive>ALTER TABLE invites RENAME TO3koobecaf;

    2. OK

    3. Timetaken:0.189seconds

    4. hive>show tables;

    5. OK

    6. 3koobecaf

    7. empty_key_value_store

    8. pokes

    9. testuser

    10. user_info

    11. Timetaken:0.045seconds,Fetched:5row(s)

10 删除表

hive> hive> DROP TABLE pokes;

11 创建数据库

hive> CREATE DATABASE chu888chu888;

五 DML DQL操作

hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。

1 DML包括：

  • INSERT插入、UPDATE更新、DELETE删除

  • 向数据表内加载文件

  • 将查询结果插入到Hive表中

  • 0.8新特性 insert into

向数据表内加载文件

  • 相对路径，例如：project/data1

  • 绝对路径，例如： /user/hive/project/data1

  • 包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1

例如：hive> LOAD DATA LOCAL INPATH ‘./examples/files/kv1.txt’ OVERWRITE INTO TABLE pokes;

2 DQL操作

基本的Select 操作

  • 使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL，表示查询所有记录。DISTINCT表示去掉重复的记录

  • Where 条件

  • 类似我们传统SQL的where 条件

  • 目前支持 AND,OR ,0.9版本支持between

  • IN, NOT IN

  • 不支持EXIST ,NOT EXIST

ORDER BY与SORT BY的不同

  • ORDER BY 全局排序，只有一个Reduce任务

  • SORT BY 只在本机做排序

Limit

  • Limit 可以限制查询的记录数 SELECT * FROM t1 LIMIT 5

  • 实现Top k 查询

  • 下面的查询语句查询销售记录最大的 5 个销售代表。

  • SET mapred.reduce.tasks=1

  • SELECT*FROM test SORT BY amount DESC LIMIT5

REGEX Column Specification

  1. SELECT语句可以使用正则表达式做列选择，下面的语句查询除了ds和hr之外的所有列：

  2. SELECT`(ds|hr)?+.+`FROM test

  3. 按先件查询

  4. hive>SELECT a.foo FROM invites a WHERE a.ds='<DATE>';

  5. 将查询数据输出至目录：

  6. hive>INSERT OVERWRITE DIRECTORY'/tmp/hdfs_out'SELECT a.*FROM invites a WHERE a.ds='<DATE>';

  7. 将查询结果输出至本地目录：

  8. hive>INSERT OVERWRITE LOCAL DIRECTORY'/tmp/local_out'SELECT a.*FROM pokes a;

  9. 选择所有列到本地目录：

  10. hive>INSERT OVERWRITE TABLE events SELECT a.*FROM profiles a;

  11. hive>INSERT OVERWRITE TABLE events SELECT a.*FROM profiles a WHERE a.key<100;

  12. hive>INSERT OVERWRITE LOCAL DIRECTORY'/tmp/reg_3'SELECT a.*FROM events a;

  13. hive>INSERT OVERWRITE DIRECTORY'/tmp/reg_4'selecta.invites,a.pokes FROM profiles a;

  14. hive>INSERT OVERWRITE DIRECTORY'/tmp/reg_5'SELECT COUNT(1)FROM invites a WHERE a.ds='<DATE>';

  15. hive>INSERT OVERWRITE DIRECTORY'/tmp/reg_5'SELECT a.foo,a.bar FROM invites a;

  16. hive>INSERT OVERWRITE LOCAL DIRECTORY'/tmp/sum'SELECT SUM(a.pc)FROM pc1 a;

  17. 将一个表的统计结果插入另一个表中：

  18. hive>FROM invites a INSERT OVERWRITE TABLE events SELECT a.bar,count(1)WHERE a.foo>0GROUP BY a.bar;

  19. hive>INSERT OVERWRITE TABLE events SELECT a.bar,count(1)FROM invites a WHERE a.foo>0GROUP BY a.bar;

  20. JOIN

  21. hive>FROM pokes t1 JOIN invites t2 ON(t1.bar=t2.bar)INSERT OVERWRITE TABLE events SELECT t1.bar,t1.foo,t2.foo;

  22. 将多表数据插入到同一表中：

  23. FROM src

  24. INSERT OVERWRITE TABLE dest1 SELECT src.*WHERE src.key<100

  25. INSERT OVERWRITE TABLE dest2 SELECT src.key,src.value WHERE src.key>=100andsrc.key<200

  26. INSERT OVERWRITE TABLE dest3 PARTITION(ds='2008-04-08',hr='12')SELECT src.key WHERE src.key>=200andsrc.key<300

  27. INSERT OVERWRITE LOCAL DIRECTORY'/tmp/dest4.out'SELECT src.value WHERE src.key>=300;

  28. 将文件流直接插入文件：

  29. hive>FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo,a.bar)AS(oof,rab)USING'/bin/cat'WHERE a.ds>'2008-08-09';

3 基于Partition的查询

  • 一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性

  • Hive 当前的实现是，只有分区断言出现在离 FROM 子句最近的那个WHERE 子句中，才会启用分区剪枝

Join

  • Hive 只支持等值连接（equality joins）、外连接（outer joins）和（left semi joins）。Hive 不支持所有非等值的连接，因为非等值连接非常难转化到 map/reduce 任务

  • LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况

  • LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

  • join 时，每次 map/reduce 任务的逻辑是这样的：reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统

  • 实践中，应该把最大的那个表写在最后

join 查询时，需要注意几个关键点

  • 只支持等值join

  • SELECT a.* FROM a JOIN b ON (a.id = b.id)

  • SELECT a.* FROM a JOIN bON (a.id = b.id AND a.department = b.department)

  • 可以 join 多于 2 个表，例如

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务LEFT，RIGHT和FULL OUTER

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)

  • 如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写

  • 容易混淆的问题是表分区的情况

SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key) WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘

如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关.解决办法

SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMI JOIN

  • LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

  1. SELECT a.key,a.value

  2. FROM a

  3. WHERE a.keyin

  4. (SELECT b.key

  5. FROM B);

  6. 可以被重写为：

  7. SELECT a.key,a.val

  8. FROM a LEFT SEMI JOIN b on(a.key=b.key)

UNION ALL

用来合并多个select的查询结果，需要保证select中字段须

传统SQL与HQL的差别

Hive 视图与一般数据库视图Hive视图与一般数据库视图作用角色相同，都是基于数据规模缩减或者基于安全机制下的某些条件查询下的数据子集。Hive视图只支持逻辑视图，不支持物化视图，即每次对视图的查询hive都将执行查询任务，因此视图不会带来性能上的提升。作为Hive查询优化的一部分，对视图的查询条件语句和视图的定义查询条件语句将会尽可能的合并成一个条件查询。

Hive索引与一般数据库索引相比于传统数据库，Hive只提供有限的索引功能，通过在某些字段上建立索引来加速某些操作。通常当逻辑分区太多太细，partition无法满足时，可以考虑建立索引。Hive1.2.1版本目前支持的索引类型有CompactIndexHandler和Bitmap。

CompactIndexHandler 压缩索引通过将列中相同的值得字段进行压缩从而减小存储和加快访问时间。需要注意的是Hive创建压缩索引时会将索引数据也存储在Hive表中。对于表tb_index (id int, name string) 而言，建立索引后的索引表中默认的三列一次为索引列（id）、hdfs文件地址(_bucketname)、偏移量(offset)。特别注意，offset列类型为array。

Bitmap 位图索引作为一种常见的索引，如果索引列只有固定的几个值，那么就可以采用位图索引来加速查询。利用位图索引可以方便的进行AND/OR/XOR等各类计算，Hive0.8版本开始引入位图索引，位图索引在大数据处理方面的应用广泛，比如可以利用bitmap来计算用户留存率（索引做与运算，效率远好于join的方式）。如果Bitmap索引很稀疏，那么就需要对索引压缩以节省存储空间和加快IO。Hive的Bitmap Handler采用的是EWAH（https://github.com/lemire/javaewah）压缩方式。

Hive不支持等值连接

  • SQL中对两表内联可以写成：

select * from dual a,dual b where a.key = b.key;

  • Hive中应为

select * from dual a join dual b on a.key = b.key;

而不是传统的格式：

SELECT t1.a1 as c1, t2.b1 as c2FROM t1, t2 WHERE t1.a2 = t2.b2

分号字符

  • 分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：select concat(key,concat(';',key)) from dual;

  • 但HiveQL在解析语句时提示：

FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification

  • 解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成select concat(key,concat('\073',key)) from dual;

 

IS [NOT] NULL

SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False.

Hive不支持将数据插入现有的表或分区中，

仅支持覆盖重写整个表，示例如下：

  1. INSERT OVERWRITE TABLE t1

  2. SELECT*FROM t2;INSERT OVERWRITE TABLE t1SELECT*FROM t2;

hive不支持INSERT INTO, UPDATE, DELETE操作

这样的话，就不要很复杂的锁机制来读写数据。INSERT INTO syntax is only available starting in version 0.8。INSERT INTO就是在表或分区中追加数据。

相关推荐