// HiveQL
a) 数据定义语言
1 数据库
表的一个目录或命名空间,如果用户没有指定数据库的话,那么将会使用默认的数据库default
-----创建数据库
CREATE DATABASE guoyongrong;// 给每个数据库创建了一个目录，数据库的文件目录名是以.db结尾的
CREATE DATABASE IF NOT EXISTS guoyongrong; // 避免在数据库存在的创建错误
CREATE DATABASE guoyongrong LOCATION '/my/preferred/directory'; // 修改目录的位置
CREATE DATABASE guoyongrong COMMENT 'some comments';// 增加描述信息
CREATE DATABASE guoyongrong WITH DBPROPERTIES('creator'='gyr','date'='2015-07-21'); // 增加一些和其相关的键值对属性信息
-----查看Hive中包含的数据库
SHOW DATABASES;
SHOW DATABASES LIKE 'h.*';//通过正则表达式筛选查找数据库
-----描述数据库信息
DESCRIBE DATABASE guoyongrong;
DESCRIBE DATABASE EXTENDED guoyongrong;
-----用于将某个数据库设置为用户当前的工作数据库
USE guoyongrong;
-----设置该属性值来在提示符里面显示当前所在的数据库
set hive.cli.print.current.db=true;
set hive.cli.print.current.db=false;
-----删除数据库
DROP DATABASE IF EXISTS guoyongrong; // 避免因数据库不存在而抛出的警告信息
DROP DATABASE IF EXISTS guoyongrong CASCADE;// Hive是不允许用户删除一个包含有表的数据库的。删除该数据库下的所有表后再删除该数据库
DROP DATABASE IF EXISTS guoyongrong RESTRICT; // 和默认情况一样，想删除数据库，那么必须要删除该数据库中的所有表
-----修改数据库
数据库的其他元数据信息都是不可更改的
ALTER DATABASE guoyongrong set DBPROPERTIES('edited-by'='gyr');// 修改DBPROPERTIES，没有办法删除或者重置数据库属性
2 表
-----表创建
CREATE TABLE IF NOT EXISTS guoyongrong.employees( <------ 如果用户当前所处的数据库并非是目标数据库,可以在表名上加数据库名指定
name STRING COMMENT 'name',
salary FLOAT COMMENT 'salary',
subordinates ARRAY<STRING> COMMENT 'names of subordinates', <------ 为每个字段添加一个注释
deductions MAP<STRING,FLOAT> COMMENT 'keys are deductions names,values are percentages',
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT> COMMENT 'home address'
)
COMMENT 'table descriptions' <------ 为表本身添加一个注释
TBLPROPERTIES('creator'='gyr','create_at'='2015-07-21 10:00:00',...) <------ 自定义一个或多个表属性(按键值对的格式为表增加额外的说明),Hive会自动增加2个表属性:last_modified_by和last_modified_time
LOCATION '/user/hive/warehouse/guoyongrong.db/employees' <------ 为表中的数据指定一个存储路径 数据仓库路劲地址/数据库目录/表目录 格式
-----拷贝一张已存在的表的表模式(无需拷贝数据)
CREATE TABLE IF NOT EXISTS guoyongrong.employees2
LIKE guoyongrong.employees
LOCATION '/path/to/data'; <------ LOCATION语句是可选的
-----列举出当前数据库下所有的表
SHOW TABLES;
-----列举出某个指定数据库下所有的表
SHOW TABLES IN guoyongrong;
-----使用正则表达式过滤出所需的表
SHOW TABLES 'empl.*';
-----列举出某表的TBLPROPERTIES属性信息
SHOW TBLPROPERTIES guoyongrong.employees;
-----查看表的详细表结构信息
DESCRIBE EXTENDED guoyongrong.employees;
-----提供更加可读和冗长的输出信息
DESCRIBE FORMATTED guoyongrong.employees;
-----查看某一个列的信息
DESCRIBE guoyongrong.employees.salary;
-----管理表,也称为内部表
Hive会控制 数据 的生命周期,默认情况下,将数据存储在配置项hive.metastore.warehouse.dir所定义的目录的子目录下
表会删除,数据也会删除
管理表不方便和其他工作(pig等)共享数据
-----外部表
表会删除,数据不会删除
管理表和外部表小的区别是HiveQL语法结构并不适用于外部表.
-----管理表和外部表的选择
如果数据会被多个工具共享,那么就创建外部表,明确对数据的所有权
-----创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS stocks( <----- EXTERNAL告诉hive这个表示外部的
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks' <----- 用于告诉Hive数据位于分布式文件系统的/data/stocks目录
-----可以查看表是否是管理表或外部表
DESCRIBE EXTENDED stocks;
...tableType:MANAGED_TABLE <----------管理表
...tableType:EXTERNAL_TABLE <----------外部表
----- 对一张存在的表进行表结构复制(不会复制数据)
CREATE EXTERNAL TABLE IF NOT EXISTS guoyongrong.employees3
LIKE guoyongrong.employees
LOCATION '/path/to/data'; <------ LOCATION语句是可选的
-----分区表(将数据以一种符合逻辑的方式进行组织,比如分层存储)
分区管理表
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
PARTITIONED BY (country STRING, state STRING) <------ 改变了Hive对数据存储的组织方式.分区字段为country和state一旦创建好了,就和普通的字段一致
分区结构的子目录
..../guoyongrong.db/employees/country=CA/state=AB
..../guoyongrong.db/employees/country=CA/state=BC
..../guoyongrong.db/employees/country=US/state=AL
..../guoyongrong.db/employees/country=US/state=AK
-----以下的查询仅仅需要扫描一个目录下的内容就可以了.提高了性能
SELECT * FROM employees
WHERE country='US' AND state='IL'; <-----------在where语句中增加了分区值进行过滤,称为分区过滤器
-----如果表中的数据以及分区个数都非常大的话,执行这样一个包含有所有分区的查询可能会触发一个巨大的MapReduce任务,安全措施是将Hive设置为strict(严格)模式
hive> set hive.mapred.mode=strict;<---------对分区表进行查询而WHERE字句没有加分区过滤器的话,将会禁止提交该任务
SELECT e.name,e.salary FROM employees e LIMIT 100;<------FAILED
hive> set hive.mapred.mode=nonstrict;
-----查看表中存在的所有分区
SHOW PARTITIONS employees;
-----查看表中存在的特定分区
SHOW PARTITIONS employees PARTITION(country='US');
SHOW PARTITIONS employees PARTITION(country='US',state='AK');
-----也会显示出分区键
DESCRIBE EXTENDED employees;
-----在管理表中用户可以通过载入数据的方式创建分区
LOAD DATA LOCAL INPATH '/home/hadoop/hive/guoyongrong/employees' <--------- 本地目录
INTO TABLE employees
PARTITION (country='US',state='CA');<--------- 创建一个US和CA分区,用户需要为每个分区字段指定一个值
外部分区表(管理大型生产数据集最常见的情况)
给用户提供了一个可以和其他工具共享数据的方式,同时也可以优化查询性能
用户可以自定义目录结构,因此用户对于目录结构的使用具有更多的灵活性
-----创建外部分区表
CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
hms INT,
serverity STRING,
server STRING,
process_id INT,
message STRING
)
PARTITIONED BY (year INT,month INT,day INT) <------------按天划分
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';
-----对于外部分区表,通过ALTER TABLE语句可以单独进行增加分区.这个语句需要为每一个分区键指定一个值
ALTER TABLE log_message ADD PARTITION(year=2012,month=1,day=2) <------------ 增加一个2012年1月2日的分区
LOCATION 'hdfs://Master.hadoop:9000/data/hive/warehouse/guoyongrong.db/log_message/2012/01/02'；<------------指定了数据路径
-----我们可以使用Amazon S3的存储设备存储旧的数据,同时保存较新的数据到HDFS中
例如:每天可以使用如下的处理过程将一个月前的旧数据转移到S3中
1 将分区下的数据copy到Amazon S3
hadoop distcp /data/log_message/2011/12/02 s3n://ourbucket/logs/2011/12/02
2 修改表，将分区路径指向s3路径
ALTER TABLE log_message PARTITION(year=2011,month=12,day=2)
LOCATION 's3n://ourbucket/logs/2011/12/02';
3 删除hdfs中的这个分区数据
hadoop fs -rmr /data/log_message/2011/12/02
-----可以查看一个外部表的分区
SHOW PARTITIONS log_messages;
-----将分区键作为表的模式一部分,和partitionKeys列表的内容同时进行显示
DESCRIBE EXTENDED log_messages;
-----查看分区数据所在的路径
DESCRIBE EXTENDED log_messages PARTITION(year=2011,month=12,day=2);
-----自定义表的存储格式
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' 01' <------- 字段分隔符
COLLECTION ITEMS TERMINATED BY ' 02' <------- 数组元素的分隔符
MAP KEYS TERMINATED BY ' 03' <------- MAP 键的分隔符
LINES TERMINATED BY '
' <------- 记录行分隔符
STORED AS TEXTFILE; <-------TEXTFILE意味着所有字段都使用字母，数字，字符编码，也意味着每一行都被认为是一个单独的记录
可以替换为SEQUENCEFILE和RCFILE，这个两种文件格式都是使用二进制编码和压缩来优化磁盘空间使用以及IO带宽性能的
CREATE TABLE kst
PARTITIONED BY (ds STRING)
ROW FORMAT SERDE 'com.linkedin.haivvreo.AvroSerDe' <------ 指定使用的SerDe
WITH SERDEPROPERTIES ('schema.url'='http://schema_provider/kst.avsc') <------ 允许用户传递配置信息给SerDe
STORED AS
INPUTFORMAT 'com.linkedin.haivvreo.AvroContainerInputFormat' <------ 指定用于输入格式的java类
OUTPUTFORMAT 'com.linkedin.haivvreo.AvroContainerOutputFormat' <------ 指定用于输出格式的java类
用DESCRIBE EXTENDED kst命令会在DETAILED TABLE INFORMATION部分列举出输入和输出格式以及SerDe的属性信息
CREATE EXTERNAL TABLE IF NOT EXISTS stocks(
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT
)
CLUSTERED BY (exchange,symbol) <------- 用来优化某些特定的查询
SORTED BY (ymd ASC) <------- 可选
INTO 96 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks' <--- 分布式文件系统的/datastocks目录
-----删除表
DROP TABLE IF EXISTS employees;
对于管理表，表的元数据信息和表内的数据都会被删除
对于外部表，表的元数据信息会被删除，但是表中的数据不会被删除
-----修改表(修改表的元数据，不会修改数据本身)
----------重命名
ALTER TABLE log_messages RENAME TO logmsgs;
----------增加表分区
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year=2011,month=1,day=1) LOCATION '/logs/2011/01/01' <------- 增加(year=2011,month=1,day=1)的分区 数据存放的路径 '/logs/2011/01/01'
PARTITION (year=2011,month=1,day=2) LOCATION '/logs/2011/01/02'
PARTITION (year=2011,month=1,day=3) LOCATION '/logs/2011/01/03';
----------修改分区路径
ALTER TABLE log_messages PARTITION (year=2011,month=1,day=1) <------- 修改(year=2011,month=1,day=1)分区的数据存放的路径为 's3n://ourbucket/logs/2011/01/02'
SET LOCATION 's3n://ourbucket/logs/2011/01/02';
----------删除分区
ALTER TABLE log_messages DROP IF EXISTS PARTITION (year=2011,month=1,day=1);
对于管理表，即使是使用ALTER TABLE ... ADD PARTITION增加的分区，分区内的数据也是会同时和元数据信息一起被删除的。
对于外部表，分区内数据不会被删除
----------修改列信息
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT <--------- 即使字段名护照地段类型没有改变,用户也要完全自定旧的字段名,并给出新的字段名及新的字段类型
COMMENT 'The hours,minutes, and seconds part of the timestamp'
AFTER serverity;<--------- 如果用户想将这个字段移动到第一个位置,那么只需用FIRST 关键字 代替AFTER other_column字句即可
----------增加列
ALTER TABLE log_messages ADD COLUMNS (
app_name STRING COMMENT 'xxx',
session_id LONG COMMENT 'xxxx'
)
----------删除或替换列
ALTER TABLE log_messages REPLACE COLUMNS (
hours_mins_secs INT COMMENT 'xxx',
serverity STRING COMMENT 'xxx',
message STRING COMMENT 'xxx'
)
----------修改表属性,可以增加和修改表属性，但不能删除
ALTER TABLE log_messages SET TBLPROPERTIES(
'notes'='xxxx'
)
----------修改存储属性
ALTER TABLE log_messages
PARTITION(year=2012,month=1,day=1) <-----将(year=2012,month=1,day=1)分区的存储格式修改成SEQUENCEFILE
SET FILEFORMAT SEQUENCEFILE;
ALTER TABLE table_using_JSON_storage
SET SERDE 'com.example.JSONSerDe' <------指定新的SerDe
WITH SERDEPROPERTIES( <----------该属性会被传递给SerDe模块
'prop1'='value1',
'prop2'='value2',
)
ALTER TABLE table_using_JSON_storage
SET SERDEPROPERTIES( <----------给已经存在的SerDe增加新的SERDEPROPERTIES属性
'prop3'='value3',
'prop4'='value4',
)
ALTER TABLE stocks
CLUSTERED BY (exchange,symbol)
SORTED BY (symbol) <-------- 该语句可选
INTO 48 BUCKETS;
----------触发钩子 当表中存储的文件在HIVE之外被修改了，就会触发钩子的执行
ALTER TABLE log_messages TOUCH
PARTITION(year=2012,month=1,day=1)
----------将某分区内的文件打成一个hadoop压缩包(HAR)文件，仅降低文件系统的文件数以及减轻NameNode的压力，不会减少任何的存储空间
ALTER TABLE log_messages ARCHIVE
PARTITION(year=2012,month=1,day=1)
----------防止分区被删除
ALTER TABLE log_messages
PARTITION(year=2012,month=1,day=1)
ENABLE NO_DROP; <----- DISABLE
----------防止分区被查询
ALTER TABLE log_messages
PARTITION(year=2012,month=1,day=1)
ENABLE OFFLINE; <----- DISABLE
3 视图 <----- 可以允许保存一个查询并像对待表一样对这个查询进行操作;不能作为INSERT或LOAD语句的目标表
------使用视图来降低查询复杂度
FROM(
SELECT * FROM people INNER JOIN cart
ON (cart.people_id=people.id)
WHERE firstname='john'; <----- 嵌套查询
) a
SELECT a.lastname
WHERE a.id=3;
------创建视图
CREATE VIEW shorter_join AS
SELECT * FROM people INNER JOIN cart
ON (cart.people_id=people.id)
WHERE firstname='john';
SELECT lastname FROM shorter_join WHERE id=3;
------使用视图来限制基于条件过滤的数据
CREATE TABLE userinfo(
firstname STRING,lastname STRING,ssn STRING,password STRING
);
CREATE VIEW safer_user_info AS <----- 通过视图来限制数据访问可以用来保护信息不被随意查询
SELECT firstname,lastname FROM userinfo;
CREATE TABLE employee(
firstname STRING,lastname STRING,ssn STRING,password STRING,department STRING)
);
CREATE VIEW techops_employee AS <----- 通过WHERE字句显示数据访问只暴露来自特定部门的员工信息
SELECT firstname,lastname,ssn FROM employee
WHERE department='techops'
CREATE EXTERNAL TABLE dynamictable(cols MAP<STRING,STRING>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' 04'
COLLECTION ITEMS TERMINATED BY ' 01'
MAP KEYS TERMINATED BY ' 02'
STORED AS TEXTFILE;
CREATE VIEW orders(state,city,part) AS
SELECT cols["state"],cols["city"],cols["part"]
FROM dynamictable
WHERE cols["type"] = 'request';
CREATE VIEW IF NOT EXISTS shipments(time,part) <----- IF NOT EXISTS 字句是可选的
COMMET 'Time and parts for shipments' <----- COMMET 字句是可选的
TBLPROPERTIES('creator'='me') <----- 通过定义TBLPROPERTIES来定义视图属性信息
AS SELECT ....;
------CREATE TABLE ... LIKE ...复制视图
CREATE TABLE shipments2
LIKE shipments;<----- shipments是视图名
------删除视图
DROP VIEW IF EXISTS shipments;
------SHOW TABLES 查看到视图,没有SHOW VIEWS命令
------DESCRIBE EXTENDED 可以显示视图的元数据信息,输出信息中的"Detailed Table Information"部分会有一个tableType字段,字段值显示的是"VIRTUAL_VIEW"
------修改视图
ALTER VIEW shipments SET TBLPROPERTIES('created_at'='some_timestamp');<---------- 视图是只读的,只允许改变元数据中TBLPROPERTIES属性信息;
4 索引
------创建索引
CREATE INDEX employees_index
ON TABLE employees(country)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' <--------- AS ... 语句指定了索引处理器,也就是一个实现了索引接口的JAVA类
WITH DEFERRED REBUILD <--------- 如果用户指定了 DEFERRED REBUILD,那么新索引将呈现空白状态
IDXPROPERTIES ('creator'='me','created_at'='some_time')
IN TABLE employees_index_table <---------要求索引处理器在一张新表中保留索引数据. IN TABLE语句可选
PARTITIONED BY(country,name) <--------- 如果省略掉PARTITIONED BY语句的话,那么索引将会包含原始表的所有分区
COMMENT 'Employees indexed by country and name.'
CREATE INDEX employees_index
ON TABLE employees(country)
AS 'BITMAP' <--------- 内置的bitmap索引处理器.bitmap索引普遍应用于排重后值较少的列
WITH DEFERRED REBUILD
IDXPROPERTIES ('creator'='me','created_at'='some_time')
IN TABLE employees_index_table
PARTITIONED BY(country,name) <--------- 如果省略掉PARTITIONED BY语句的话,那么索引将会包含原始表的所有分区
COMMENT 'Employees indexed by country and name.'
------重建索引
ALTER INDEX employees_index
ON TABLE employees
PARTITON (country = 'US') <---- 如果省略掉PARTITION,那么将会对所有分区进行重建索引
REBUILD;
------显示索引
SHOW FORMATTED INDEX ON employees;<---- FROMATTED是可选的,可以使输出中包含有列名称
SHOW FORMATTED INDEXES ON employees;<---- 可以列举出多个索引信息
------删除索引
DROP INDEX IF EXISTS employees_index ON TABLE employees;<---- 不允许用户直接使用DROP TABLE 语句之前删除索引表
如果被索引的表被删除了,那么其对应的索引和索引表也会被删除.同样的,如果原始表的某个分区被删除了,那么这个分区对应的分区索引页同时会被删除
b) 数据操作语言
-----向管理表中装载数据
LOAD DATA LOCAL INPATH '${env:HOME}/california-employees' <---------如果指定local那么是本地路径，否则是分布式文件系统的路径,这个路径下不可以包含任何文件夹，同时Hive会验证文件格式是否和表结构定义一致
OVERWRITE INTO TABLE employees <--------- OVERWRITE使得目标文件夹中之前存在的数据将会被先删除，如果没有这个关键字，仅仅会把新增的文件增加到目标文件
PARTITION(country='US',state='CA'); <--------- 如果分区目录不存在的话，会先创建分区目录，然后将数据copy到该目录，如果是非分区表，PARTITION字句可以省略
-----通过查询语句向表中插入数据
INSERT OVERWRITE TABLE employees <--------- 如果没有OVERWRITE关键字或者使用INTO关键字的话，那么Hive将会以追加的方式写入数据而不会覆盖掉之前已经存在的内容
PARTITION (country='US',state='OR')
SELECT * FROM staged_employees se
WHERE se.cnty='US' AND se.st='OR';
-----可以只扫描一次表数据
FROM staged_employees se <--------- 从staged_employees表中读取的每条记录都会经过一条SELECT ... WHERE ... 句子进行判断。如果满足某个的话没，就被写入到指定的表和分区中
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='OR')
SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='CA')
SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE TABLE employees
PARTITION (country='US',state='IL')
SELECT * WHERE se.cnty='US' AND se.st='IL' ;
-----动态分区插入(Hive根据SELECT语句最后2列来确定分区字段country和state的值)
INSERT OVERWRITE TABLE employees
PARTITION (country,state)
SELECT ..., se.cnty, se.st
FROM staged_employees se;
-----混合使用动态和静态分区插入(country字段是静态的US，而state是动态值)
INSERT OVERWRITE TABLE employees
PARTITION (country = 'US',state) <--------- 静态分区键必须出现在动态分区键之前
SELECT ..., se.cnty, se.st
FROM staged_employees se
WHERE se.cnty ='US';
动态分区功能默认是没有开启，开启后，默认是以 严格 模式执行的。 这种模式下要求至少有一列分区字段是静态的。有助于因设计错误导致查询产生大量的分区
动态分区属性
hive.exec.dynamic.partition false 设置成true,表示开启动态分区功能
hive.exec.dynamic.partition.mode strict 设置成nonstrict,表示允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode 100 每个mapper或reducer可以创建的最大动态分区个数.如果某个mapper或reducer尝试创建大于这个值的分区的话则会抛出一个致命错误信息
hive.exec.max.dynamic.partitions +1000 一个动态分区创建语句可以创建的最大动态分区个数.如果超过这个值则会抛出一个致命错误信息
hive.exec.max.created.files 100000 全局可以创建额最大文件个数.有一个hadoop计数器会跟踪记录创建了多少个文件,如果超过这个值则会抛出一个致命错误信息
hive>set hive.exec.dynamic.partition = true;
hive>set hive.exec.dynamic.partition.mode = nonstrict;
hive>set hive.exec.max.dynamic.partitions.pernode = 1000;
INSERT OVERWRITE TABLE employees
PARTITION (country,state)
SELECT ..., se.cnty, se.st
FROM staged_employees se;
-----单个查询语句中创建表并加载数据(从一个宽表中选取部分需要的数据集，这个不能用于外部表)
CREATE TABLE ca_employees
AS
SELECT name,salary,address
FROM employees
WHERE state ='CA';
-----导出数据
hadoop fs -cp source_path target_path;<----- 数据文件是用户需要的格式，那么只需要copy文件或文件夹就可以了
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name,salary,address
FROM employees
WHERE state ='CA';
在hive CLI中查看导出的目录
hive> ! ls /temp/ca_employees;
hive> ! cat /temp/ca_employees/000000_0;
-----通过如下的方式指定多个输出文件夹
FROM staged_employees se <--------- 从staged_employees表中读取的每条记录都会经过一条SELECT ... WHERE ... 句子进行判断。如果满足某个的话没，就被写入到指定的表和分区中
INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE DIRECTORY '/tmp/il_employees'
SELECT * WHERE se.cnty='US' AND se.st='IL' ;
c) 查询
-----SELECT... FROM 语句
SELECT name,salary FROM employees;<-----SELECT 指定要保存的列以及输出函数需要调用的一个或多个列,FORM标示从哪个表,视图,嵌套查询中选择记录
SELECT e.name,e.salary FROM employees e;<----- 指定了表别名,用于链接操作
SELECT name,subordinates,address,deductions FROM employees;<----- 选择的列是集合数据类型时,Hive会使用JSON语法应用于输出.
SELECT name,subordinates[0],address.city,deductions["State Taxes"] FROM employees;<----- 展示查询数组类型,MAP类型,STRUCT类型字段里面内容的用法
SELECT symbol,`price.*` FROM stocks;<----- 使用正则表达式来指定列
SELECT upper(name),salary,decuctions["Federal Taxes"],round(salary * (1- deductions["Federal Taxes"])) FROM employees;<----- 使用函数调用和算术表达式操作列值
SELECT upper(name),salary FROM employees limmit 2;<-----------limit用于限制返回的行数
SELECT upper(name) AS upper_name FROM employees;<-----------AS ... 定义了列别名
SELECT name,salary,
CASE <-----------CASE ... WHEN ... THEN 语句
WHEN salary < 50000.0 THEN 'low'
WHEN salary >= 50000.0 AND salary < 70000.0 THEN 'middle'
WHEN salary >= 70000.0 AND salary < 100000.0 THEN 'high'
ELSE 'very high'
END As bracket FROM employees;
FROM(
SELECT upper(name),salary,deductions["Federal Taxes"] as fed_taxes,round(salary *(1-deductions["Federal Taxes"])) as salary_minus_fed_taxes
FROM employees <-----------嵌套查询
) e <------------ e 做为嵌套查询的别名
SELECT e.name,e.salary_minus_fed_taxes
WHERE e.salary_minus_fed_taxes > 70000;
-----WHERE 语句 <-----------过滤条件
SELECT name,salary,deductions["Federal Taxes"],
salary * (1-deductions["Federal Taxes"])
FROM employees
WHERE round(salary * (1-deductions["Federal Taxes"])) > 70000 <------- 不能在WHERE语句中使用列别名,但可以使用嵌套的select语句
SELECT e.* FROM
(
SELECT name,salary,deductions["Federal Taxes"],
salary * (1-deductions["Federal Taxes"]) as salary_minus_fed_taxes
FROM employees
) e
WHERE round(e.salary_minus_fed_taxes) > 70000
-----函数
----------数学函数
----------聚合函数 <---------- 对多行进行一些计算,然后得到一个结果值
hive>set hive.map.aggr=true; <--------可以提高聚合的性能
SELECT count(*),avg(salary) FROM employees;
SELECT count( DISTINCT symbol),avg(salary) FROM stocks;<--------- 很多函数都可以接受 DISTINCT...表达式
----------表生成函数 <---------- 将单列扩展成多列或者多行
SELECT explode(subordinates) AS sub FROM employees;<-----------subordinates 字段内容为空的话,那么不产生新的记录,否则,这个数组的每个元素都将产生一行新记录,同时,当使用表生成函数时,要求使用列别名
SELECT parse_url_tuple(url,'HOST','PATH','QUERY') as (host,path,query) FROM url_table;<-----------扩展多列的例子
----------其他内置函数 <----------用于处理字符串,Map,数组,JSON和时间戳
-----什么情况下Hive可以避免进行MapReduce
本地模式
WHERE语句中过滤条件只是分区字段这种情况
属性hive.exec.mode.local.auto的值设置为true的话,Hive会尝试使用本地模式执行其他的操作
-----谓词操作符 可以用于JOIN ... ON和HAVING语句中
操作符 支持的数据类型 描述
A=B 基本数据类型 如果A等于B则返回true,反之返回false
A<=>B 基本数据类型 如果A和B都为NULL则返回TRUE,其他的和等号(=)操作符一致,如果任一为NULL则结果为NULL
A==B 没有 错误的语法SQL使用=,而不是==
A IS [NOT] NULL 所有数据类型 如果A等于[不等于] NULL,则返回TRUE;反之返回FALSE
A [NOT] LIKE B STRING类型 B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE反之返回FALSE,B的表达式说明如下:'x%'表示A必须以字母'x'开头,'%x'表示A必须以字母'x'结尾,而'%x%'表示A包含字母'x'.下划线'_'匹配单个字符.
A RLIKE B,A REGEXP B STRING类型 B是一个正则表达式,如果A与其相匹配,则返回TRUE,反之返回FALSE.匹配使用的是JDK中的正则表达式接口实现的.
-----关于浮点数比较
SELECT name,salary,deductions['Federal Taxes']
FROM employees WHERE deductions['Federal Taxes'] >0.2; <------这个查询会将0.2的记录也查询出来.
造成该问题的原因是 数字0.2是不能够使用FLOAT或DOUBLE进行准确表示的
数字0.2对于FLOAT类型时0.2000001,而对于DOUBLE类型时0.200000000001,因为一个8个字节的DOUBLE值具有更多的小数位,
当表的FLOAT值通过Hive转换为DOUBLE值时,其产生的DOUBLE值时0.200000100000,
这个值实际要比0.200000000001大.这个问题是所有使用IEEE标准进行浮点数编码的系统中存在的一个普遍的问题.
规避该问题的方法有3种
定义表字段的类型的为DOUBLE而不是FLOAT,但会造成查询时所需的内存消耗,同时如果表额存储格式是二进制文件格式的话,也不能简单的进行这样的改变
显示的指出0.2为FLOAT类型的.Hive必须使用cast操作符进行类型转换.修改查询语句将0.2的DOUBLE类型转为FLOAT类型,cast(0.2 AS FLOAT)
和钱相关的都避免使用浮点数
-----LIKE和RLIKE
SELECT name,address.street FROM employees WHERE address.street LIKE '%Ave.';
SELECT name,address.city FROM employees WHERE address.city LIKE 'O%';
SELECT name,address.street FROM employees WHERE address.street LIKE '%Chi%';
SELECT name,address.street FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*';<----java的正则表达式
-----GROUP BY 语句
SELECT year(ymd),avg(price_close) FROM stocks <------- group by 语句通常会和聚合函数一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作
WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd);
-----HAVING 语句
SELECT year(ymd),avg(price_close) FROM stocks
WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd)
HAVING avg(price_close) >50.0 <------- HAVING 语句允许用户通过一个简单的语法完成原本需要通过子查询才能对GROUP BY语句产生的分组进行条件过滤的任务.
----------如果没有HAVING字句,那么需要使用一个嵌套的SELECT子查询
SELECT s2.year,s2.avg FROM
(
SELECT year(ymd) AS year,avg(price_close) AS avg FROM stocks
WHERE exchange='NASDAQ' AND symbol='AAPL'
GROUP BY year(ymd)
) s2
WHERE s2.avg >50.0
-----JOIN 语句
----------INNER JOIN(内连接)
SELECT a.ymd,a.price_close,b.price_close
FROM stocks a INNER JOIN stocks b ON a.ymd = b.ymd <------ 只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来,ON字句指定了两个表间数据进行连接的条件.Hive不支持非等值连接以及在ON字句中的谓词间使用OR
WHERE a.symbol=''AAPL AND b.symbol='IBM'
CREATE EXTENRNAL TABLE IF NOT EXISTS dividends(
ymd STRING,
dividend FLOAT
)
PARTITIONED BY(exchange STRING,symbol STRING)<---------按分区键exchange和symbol进行分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
SELECT s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s INNER JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
WHERE s.symbol='AAPL';
SELECT a.ymd,a.price_close,b.price_close,c.price_close
FROM stocks a INNER JOIN stocks b ON a.ymd = b.ymd <-------- 多表管理,大多数情况下,Hive会对每个JOIN连接对象启动一个MapReduce任务.Hive总是按照从左到右的顺序进行连接操作
INNER JOIN stocks c ON a.ymd = c.ymd
WHERE a.symbol='AAPL' AND b.symbol='IBM' AND c.symbol='GE'
----------LEFT OUTER JOIN(左外连接)
SELECT s.ymd,s.symbol,s.price_close,d.dividend <------ 返回左边表中所有符合WHERE语句的记录,右边表中匹配不上的字段值用NULL代替
FROM stocks s LEFT OUTER JOIN dividends
ON s.ymd=d.ymd AND s.symbol=d.symbol <------ 先做两个表的连接操作,然后在做WHERE过滤,ON语句中的 分区过滤条件 在外链接中无效,在内连接中是有效的
WHERE s.symbol='AAPL';<------WHERE语句在连接操作执行完后才会执行.
----------RIGHT OUTER JOIN(右外连接)
SELECT s.ymd,s.symbol,s.price_close,d.dividend <------ 返回右边表中所有符合WHERE语句的记录,左边表中匹配不上的字段值用NULL代替
FROM dividends d RIGHT OUTER JOIN stocks s
ON d.ymd=s.ymd AND d.symbol=s.symbol
WHERE s.symbol='AAPL';
----------FULL OUTER JOIN(完全外连接)
SELECT s.ymd,s.symbol,s.price_close,d.dividend <------ 返回所有表中所有符合WHERE语句的记录,如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代
FROM dividends d FULL OUTER JOIN stocks s
ON d.ymd=s.ymd AND d.symbol=s.symbol
WHERE s.symbol='AAPL';
----------LEFT SEMI-JOIN(左半开连接) <------ 通常比INNER JOIN要更高效,原因如下:对于左表中一条指定的记录,在右边表中一旦找到匹配的记录,Hive就会立即停止扫描,Hive不支持RIGHT SEMI-JOIN(右半开连接)
SELECT s.ymd,s.symbol,s.price_close <------ 返回所有表中所有符合WHERE语句的记录,如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代
FROM stocks s
WHERE s.ymd,s.symbol IN <------IN 在hive中不支持
(
SELECT d.ymd,d.symbol FROM dividends d;
)
SELECT s.ymd,s.symbol,s.price_close <------ 返回左边表中所有符合WHERE语句的记录,前提是其记录对于右边表满足ON语句中的判定条件,注意SELECT和WHERE语句中不能引用到右边表中的字段.
FROM stocks s LEFT SEMI JOIN dividends d
ON s.ymd=d.ymd AND s.symbol=d.symbol
----------笛卡尔积JOIN <------ 设置hive.mapred.mode值为strict的话,Hive会阻止用户执行笛卡儿积查询
SELECT * FROM stocks JOIN dividends;<------ 表示左边表的行数 * 右边表的行数等于笛卡尔结果集的大小,笛卡尔积不是并行执行的,而且使用了MapReduce计算架构的话,任何方式都无法进行优化
SELECT * FROM stocks JOIN idvidends
WHERE stock.symbol=dividends.symbol AND stock.symbol='AAPL';<------ 在其他的数据库中会被优化成内连接,但是在Hive中没有此优化;WHERE语句中的谓词条件前会先进行完全笛卡儿积计算
----------map-side JOIN <------ 如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中，Hive可以在map端执行连接过程(称为map-side JOIN)，这是因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需的reduce过程
SELECT /*+ MAPJOIN(d) */ s.ymd,s.symbol,s.price_close,d.dividend <------ 通过该标记，可以启动这个优化
FROM stocks s INNER JOIN dividends d ON s.ymd =d.ymd AND s.symbol=d.symbol
WHERE s.symbol='AAPL';
set hive.auto.convert.join = true;<------ 设置hive.auto.convert.JOIN值为true，这样Hive才会在必要的时候启动这个优化，默认该属性的值为false;
set hive.mapjoin.smalltable.filesize = 25000000;<------ 用户也可以配置能够使用这个优化的小表的大小(单位是字节)
如果用户期望hive在必要的时候自动启动这个优化的话，那么可以将这两个属性设置放在$HOME/.hiverc文件中，对于RIGHT和FULL OUTER JOIN不支持这个优化
SELECT s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s INNER JOIN dividends d ON s.ymd =d.ymd AND s.symbol=d.symbol
WHERE s.symbol='AAPL';
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin=true;<------ 如果所有表的数据是分桶的，当表中的数据必须是按照ON语句中的键进行分桶的,而且其中的一张表的分桶的个数必须是另一张表分桶个数的若干倍,当满足这些条件时，那么Hive可以在map阶段按照分桶数据进行连接
set hive.optimize.bucketmapjoin.sortedmerge=true;<------ 如果所涉及的分桶表都具有相同的分桶数，而且数据是按照连接键进行排序的,那么Hive可以执行一个更快的分类-合并连接(sort-merge JOIN)
-----JOIN 优化
1)当对3个或更多表进行JOIN连接时,如果每个ON字句都使用相同的连接键的话,那么只会产生一个MapReduce Job
2)Hive同时假定查询中最后一个表示最大的那个表,因此用户需要保证连接查询中的表的大小是从左到右是一次增加的.
3)Hive提供了一个 标记 机制来显示的告知查询优化器哪张表是大表
SELECT /*+STREAMTABLE(s) */ s.ymd,s.symbol,s.price_close,d.dividend
FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
WHERE s.symbol='AAPL';
4) map_side JOIN
----------ORDER BY和SORT BY
ORDER BY会对查询结果集执行一个全局排序。也就是说会有一个所有的数据都通过一个reducer进行处理的过程。对于大数据集，这个过程会消耗太过漫长的时间来执行
SORT BY 会在每个reducer中对数据进行排序，也就是执行一个局部排序过程，保证每个reducer的输出数据都是有序的（但并非全局有序）
SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
ORDER BY s.ymd ASC,s.symbol DESC;<---------- 如果设置hive.mapred.mode的值是strict的话，那么Hive要求必须加有LIMIT语句进行限制
SORT BY s.ymd ASC,s.symbol DESC;<---------- 如果使用的reducer的个数大于1的话。那么两个查询输出结果的排序就不一样了
----------含有SORT BY的DISTRIBUTE BY <---------- DISTRIBUTE BY控制map的输出在reducer中是如何划分的。
SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
DISTRIBUTE BY s.symbol <---------- 使用DISTRIBUTE BY来保证具有相同股票交易码的记录会分发到同一个reducer中进行处理，然后使用sort by对数据进行排序；DISTRIBUTE BY语句要写在SORT BY语句之前
SORT BY s.symbol ASC,s.ymd ASC;
----------ClUSTER BY <----------在DISTRIBUTE BY语句中和SORT BY语句中涉及到列完全相同的话，而且采用的是升序排序方式，那么CLUSTER BY就等价于前面的2个语句
SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
CLUSTER BY s.symbol;<----------DiSTRIBUTE BY ... SORT BY语句或其简化版的CLUSTER BY语句会剥夺SORT BY的并行性，然而可以实现输出文件的数据是全局排序的
----------Cast()函数<----------对指定的值进行显示的类型转换
SELECT name,salary FROM employees
WHERE cast(salary AS FLOAT) < 100000.0; <----------类型转换函数的语法是cast(value AS TYPE)
将浮点数转换成整数的推荐方式是使用round()或者floor()函数，而不是使用类型转换操作符cast
对于BINARY类型，目前只支持将BINARY类型转换为STRING类型，如果用户知道其值是数值的话，那么可以通过嵌套cast()的方式对其进行类型转换
SELECT (2.0 * cast(cast(b as STRING) as DOUBLE)) FROM SRC;
----------抽样查询 <----------对于一个非常大的数据集，用户需要使用的是一个具有代表性的查询结果而不是全部结果，Hive通过对表进行分桶抽样来满足这个需求
CREATE TABLE numbers(number int);
insert into numbers values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT * FROM numbers <----------number表只有number字段，其值是1到10
TABLESAMPLE(BUCKET 3 OUT OF 10 ON rand()) s;<---------- 使用rand()函数进行抽样，这个函数会返回一个随机值
SELECT * FROM numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;<---------- 按照指定的列而非rand()函数进行分桶的话，那么同一语句多次执行的返回值是相同的
SELECT * FROM numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON number) s;
SELECT * FROM numbers TABLESAMPLE(BUCKET 5 OUT OF 10 ON number) s;
SELECT * FROM numbers TABLESAMPLE(BUCKET 5 OUT OF 10 ON number) s;
SELECT * FROM numbers TABLESAMPLE(BUCKET 1 OUT OF 2 ON number) s;<---------- 分桶语句中的分母表示的是数据将会被散列的桶的个数，而分子表示将会选择的哪个桶
SELECT * FROM numbers TABLESAMPLE(BUCKET 2 OUT OF 2 ON number) s;
----------数据块抽样 <---------- Hive提供了按照抽样百分比进行抽样的方式，这种事基于行数的，按照输入路经下的数据块百分比进行的抽样
SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;<---------- 这种抽样方式不一定适合所有的文件格式，另外，这种抽样的最小抽样单元是一个HDFS数据块，如果表的大小小于普通的块大小128M的话，那么就会返回所有行
set hive.sample.seednumber=0.2;<---------- 基于百分比的抽样方式提供了一个变量，用于控制基于数据块的调优的种子信息
SELECT * FROM numbersflat WHERE number % 2 =0;<---------- 抽样会扫描表中所有的数据，然后在每N行中抽取一行数据。
CREATE TABLE numbers_bucketed(number int)
CLUSTERED BY (number) INTO 3 BUCKETS;<---------- 根据字段number创建分桶表，将数据放入3个分桶中
set hive.enforce.bucketing = true; <---------- 强制分桶处理
INSERT OVERWRITE TABLE numbers_bucketed SELECT number FROM numbers;<---------- 将表numbers的数据insert到分桶表
DESCRIBE EXTENDED numbers_bucketed;
dfs -ls hdfs://Master.hadoop:9000/data/hive/warehouse/guoyongrong.db/numbers_bucketed;<---------- 查看表数据所在的目录
dfs -cat hdfs://Master.hadoop:9000/data/hive/warehouse/guoyongrong.db/numbers_bucketed/000001_0;<---------- 查看表数据所在的目录下的文件信息
SELECT * FROM numbers_bucketed TABLESAMPLE ( BUCKET 2 OUT OF 3 ON number) s;<---------- 高效的仅仅对其中一个数据桶进行抽样
----------UNION ALL
SELECT log.ymd,log.level,log.message
FROM
(
SELECT l1.ymd,l1.level,l1.message,'log1' AS source
FROM log1 l1
UNION ALL <---------- 将2个以上的表进行合并，每个union字查询都必须具有相同的列而且对应的每个字段类型必须是一致的。
SELECT l2.ymd,l2.level,l2.message,'log2' AS source
FROM log2 l2
) log
SORT BY log.ymd ASC;
FROM(
FROM src
SELECT src.key,src.value WHERE src.key < 100
UNION ALL <---------- 这个查询将会对同一分源数据进行多次拷贝分发
FROM src
SELECT src.key,src.value WHERE src.key > 110
) unioninput
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*;