马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
Hive 是基于hadoop 构建的一套数据仓库分析系统,它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据,可以将结构化的数据文件映射为一张数据库表,并提供完整的SQL查询功能,可以将SQL语句转换为MapReduce任务进行运行,通过自己的SQL 去查询分析需要的内容,这套SQL 简称Hive SQL,使不熟悉mapreduce 的用户很方便的利用SQL 语言查询,汇总,分析数据。而mapreduce开发人员可以把己写的mapper 和reducer 作为插件来支持Hive 做更复杂的数据分析。 它与关系型数据库的SQL 略有不同,但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询。HIVE不适合用于联机,也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。 HIVE的特点:可伸缩(在Hadoop的集群上动态的添加设备),可扩展,容错,输入格式的松散耦合。 1. DDL 操作建表:- CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
- [(col_name data_type [COMMENT col_comment], ...)]
- [COMMENT table_comment]
- [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
- [CLUSTERED BY (col_name, col_name, ...)
- [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
- [ROW FORMAT row_format]
- [STORED AS file_format]
- [LOCATION hdfs_path]
CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常 EXTERNAL 关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION) LIKE 允许用户复制现有的表结构,但是不复制数据 COMMENT可以为表与字段增加描述 ROW FORMAT - DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
- [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
- | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。 STORED AS - SEQUENCEFILE
- | TEXTFILE
- | RCFILE
- | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCE 。 - hive> CREATE TABLE pokes (foo INT, bar STRING);
- CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
- page_url STRING, referrer_url STRING,
- ip STRING COMMENT 'IP Address of the User',
- country STRING COMMENT 'country of origination')
- COMMENT 'This is the staging page view table'
- ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
- STORED AS TEXTFILE
- LOCATION '<hdfs_location>';
- CREATE TABLE par_table(viewTime INT, userid BIGINT,
- page_url STRING, referrer_url STRING,
- ip STRING COMMENT 'IP Address of the User')
- COMMENT 'This is the page view table'
- PARTITIONED BY(date STRING, pos STRING)
- ROW FORMAT DELIMITED ‘\t’
- FIELDS TERMINATED BY '\n'
- STORED AS SEQUENCEFILE;
- CREATE TABLE par_table(viewTime INT, userid BIGINT,
- page_url STRING, referrer_url STRING,
- ip STRING COMMENT 'IP Address of the User')
- COMMENT 'This is the page view table'
- PARTITIONED BY(date STRING, pos STRING)
- CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
- ROW FORMAT DELIMITED ‘\t’
- FIELDS TERMINATED BY '\n'
- STORED AS SEQUENCEFILE;
- hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
- CREATE TABLE empty_key_value_store
- LIKE key_value_store;
例子 - create table user_info (user_id int, cid string, ckid string, username string)
- row format delimited
- fields terminated by '\t'
- lines terminated by '\n';
导入数据表的数据格式是:字段之间是tab键分割,行之间是断行。 及要我们的文件内容格式: - 100636 100890 c5c86f4cddc15eb7 yyyvybtvt
- 100612 100865 97cc70d411c18b6f gyvcycy
- 100078 100087 ecd6026a15ffddf5 qa000100
按正条件(正则表达式)显示表, 修改表结构- hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
- hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
- hive> ALTER TABLE events RENAME TO 3koobecaf;
增加、删除分区 - ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec
- [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
- partition_spec:
- : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
- ALTER TABLE table_name DROP partition_spec, partition_spec,...
- ALTER TABLE table_name RENAME TO new_table_name
- ALTER TABLE table_name CHANGE [COLUMN] col_old_name
- col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合 - hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
- hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
- ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
ADD是代表新增一字段,字段位置在所有列后面(partition列前) REPLACE则是表示替换表中所有字段。 - ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
- :[property_name = property_value…..]
用户可以用这个命令向表中增加metadata - ALTER TABLE table_name SET FILEFORMAT file_format
- ?ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS
这个命令修改了表的物理存储属性 - CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...)
- ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT
如果没有提供表名,视图列的名字将由定义的SELECT表达式自动生成 如果修改基本表的属性,视图中不会体现,无效查询将会失败 视图是只读的,不能用LOAD/INSERT/ALTER - show tables;
- ?show databases;
- ?show partitions ;
- ?show functions
- ?describe extended table_name dot col_name
2. DML 操作:元数据存储hive不支持用insert语句一条一条的进行插入操作,也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。 DML包括:INSERT插入、UPDATE更新、DELETE删除 - LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
- [PARTITION (partcol1=val1, partcol2=val2 ...)]
Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置。 filepath - 相对路径,例如: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;
加载本地数据,同时给定分区信息 加载的目标可以是一个表或者分区。如果表包含分区,必须指定每一个分区的分区名 filepath 可以引用一个文件(这种情况下,Hive 会将文件移动到表所对应的目录中)或者是一个目录(在这种情况下,Hive 会将目录中的所有文件移动至表所对应的目录中) LOCAL关键字 指定了LOCAL,即本地 load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径,则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI,比如:file:///user/hive/project/data1.
load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置 3. DQL 操作:数据查询SQL3.1 基本的Select 操作- SELECT [ALL | DISTINCT] select_expr, select_expr, ...
- FROM table_reference
- [WHERE where_condition]
- [GROUP BY col_list [HAVING condition]]
- [ CLUSTER BY col_list
- | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
- [LIMIT number]
使用ALL和DISTINCT选项区分对重复记录的处理。默认是ALL,表示查询所有记录。DISTINCT表示去掉重复的记录 类似我们传统SQL的where 条件 目前支持 AND,OR ,0.9版本支持between Limit 可以限制查询的记录数 下面的查询语句查询销售记录最大的 5 个销售代表。 - SET mapred.reduce.tasks = 1
- SELECT * FROM test SORT BY amount DESC LIMIT 5
- REGEX Column Specification
SELECT 语句可以使用正则表达式做列选择,下面的语句查询除了 ds 和 hr 之外的所有列: - SELECT `(ds|hr)?+.+` FROM test
4. 从SQL到HiveQL应转变的习惯1、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
2、分号字符分号是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;
3、IS [NOT] NULLSQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False. 4、Hive不支持将数据插入现有的表或分区中,仅支持覆盖重写整个表,示例如下:- INSERT OVERWRITE TABLE t1
- SELECT * FROM t2;
|