Hive最新数据操作详解(超级详细)
数据操作能力是大数据分析至关重要的能力。数据操作主要包括:更改(exchange),移动(moving),排序(sorting),转换(transforming)。Hive提供了诸多查询语句,关键字,操作和方法来进行数据操作。 一、 数据更改 数据更改主要包括:LOAD, INSERT, IMPORT, and EXPORT 1. LOAD DATA load关键字的作用是将数据移动到hive中。如果是从HDFS加载数据,则加载成功后会删除源数据;如果是从本地加载,则加载成功后不会删除源数据。数据:employee_hr.txt http://pan.baidu.com/s/1c0D9TpI
例:
hive>(不用输入,在此表示在Hive的shell输入以下命令,下同) CREATE TABLE IF NOT EXISTS employee_hr( name string, employee_id int, sin_number string, start_date timestamp ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS TEXTFILE;
例: hive> LOAD DATA LOCAL INPATH '/apps/ca/yanh/employee_hr.txt' OVERWRITE INTO TABLE employee_hr; 注1:在指令中LOCAL关键字用于指定数据从本地加载,如果去掉该关键字,默认从HDFS进行加载! OVERWRITE关键字指定使用覆盖方式进行加载数据,否则使用附加方式进行加载。
注2:如果数据加载到分区表,则必须指定分区列。
2. INSERT 同RDBMS一样,Hive也支持从其他hive表提取数据插入到指定表,使用INSERT关键字。INSERT操作是Hive数据处理中最常用的将已有数据填充进指定表操作。在Hive中,INSERT可以和OVERWRITE一起使用实现覆盖插入,可以进行多表插入,动态分区插入以及提取数据至HDFS或本地。
例: hive> CREATE TABLE ctas_employee AS SELECT * FROM employee; TRUNCATE TABLE employee; //删除employee中的数据,保留表结构
例:
hive> INSERT INTO TABLE employee SELECT * FROM ctas_employee; 注:这里使用Hive提供的beeline工具进行连接,以便清晰的显示数据表。
例:从CTE插入数据 hive> WITH a AS (SELECT * FROM ctas_employee) FROM a INSERT OVERWRITE TABLE employee SELECT *; //效果和上例相同 注:Hive从0.13.0版本开始支持CTE
例:多表插入 hive> CREATE TABLE employee_internal LIKE employee; FROM ctas_employee INSERT OVERWRITE TABLE employee SELECT * INSERT OVERWRITE TABLE employee_internal SELECT *; SELECT * FROM employee_internal;
Hive除了支持向静态分区插入静态数据,还支持插入动态数据,如日期
例:动态分区插入 动态分区默认是关闭的,可通过以下设置开启:SET hive.exec.dynamic.partition=true; Hive默认至少需要一个partition列是静态的,可以通过以下设置关闭:SET hive.exec.dynamic.partition.mode=nonstrict; hive> INSERT INTO TABLE employee_partitioned PARTITION(year,month) SELECT name,array('Toronto') AS work_place, named_struct("sex","Male","age",30) AS sex_age, map("Python",90) AS skills_score, map("R&D",array('Developer')) AS depart_title, year(start_date) AS year, month(start_date) AS month FROM employee_hr eh WHERE eh.employee_id = 102;
例:
hive> SELECT * FROM employee_partitioned;
例:提取数据至本地(默认使用^A分离列,换行符分离行) 注:Hive提取数据只能使用OVERWRITE, 不能使用INTO。 注:在一些Hadoop版本中目录深度只支持到2层,可以使用以下设置修复:SET hive.insert.into.multilevel.dirs=true; hive> INSERT OVERWRITE LOCAL DIRECTORY '/apps/ca' SELECT * FROM employee; 注:默认下Hive会将数据按reducer数量生成多个输出文件,可以使用以下命令进行合并: hdfs dfs -getmerge hdfs://
汇总函数:这些方法主要用于对复杂类型进行size、key、value等的查询,如SIZE(Array
类型转换函数:这些方法主要用于对数据类型进行转换,如CAST和BINARY.
日期函数:用于对日期相关进行操作,如YEAR(string date)和MONTH(string date).
条件函数:用于返回特定条件过滤后的函数,如COALESCE、IF、和CASE WHEN.
字符串函数:此类函数主要用于字符串相关操作,如UPPER(string A) 和TRIM(string A).
聚合函数:此类函数主要用于数据聚合,如SUM(),COUNT(*).
列表生成函数:此类函数主要用于将单行输入转换为多行输出,如EXPLODE(MAP)和JSON_TUPLE(jsonString, k1, k2, ...).
自定义函数:此类由Java生成的函数作为Hive的扩展函数对Hive功能进行扩展.
可以在Hive CLI使用以下语句进行Hive内建函数查询: SHOW FUNCTIONS; //列出Hive所有函数 DESCRIBE FUNCTION
详细样例:
1. 复杂数据类型函数提示:SIZE函数用于计算MAP、ARRAY或嵌套MAP/ARRAY。如果size未知则返回-1.
例: hive> SELECT work_place, skills_socre, depart_title FROM employee;
例: hive> SELECT SIZE(work_place) AS array_size, SIZE(skills_score) AS map_size, SIZE(depart_title) AS complex_size, SIZE(depart_title["Product"]) AS nest_size FROM employee;
ARRAY_CONTAINS声明用于使用TRUE或FALSE返回值检验指定列是否包含指定值。SORT_ARRAY声明用于对数组进行升序排序。
例: hive> SELECT ARRAY_CONTAINS(work_place, 'Toronto') AS is_Toronto, SORT_ARRAY(work_place) AS sorted_array FROM employee;
2. 日期函数提示:FROM_UNIXTIME(UNIX_TIMESTAMP())声明与Oracle中的SYSDATE函数相同,动态返回Hive服务器的当前时间。
例: hive> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()) AS current_time FROM employee limit 1;
TO_DATE用于将获取的系统时间截取日期
例: hive> SELECT TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) AS current_date FROM employee limit 1;
3. 多种不同数据类型的CASE:在Hive0.13.0版本之前THEN或者ELSE后面的数据类型必须相同。否则可能会产生异常值,如ELSE后的数据类型必须同THEN。此问题在0.13.0之后得到修复。
例:由于数据类型不同造成异常返回 hive> SELECT CASE WHEN 1 IS NULL THEN 'TRUE' ELSE 0 END AS case_result FROM employee LIMIT 1;
4. 解析和查找:LATERAL VIEW是用来生成用户自定义表以展开的形式显示map或array的值,如同EXPLODE(),但其会忽略值为NULL的列,如果要显示这些列,可以使用LATERAL VIEW OUTER(Hive0.12.0之后版本)
例: hive> INSERT INTO TABLE employee SELECT 'Steven' AS name, array(null) AS work_place, named_struct("sex","Male","age",30) AS sex_age, map("Python", 90) AS skills_score, map("R&D",array('Developer')) AS depart_title FROM employee LIMIT 1; SELECT name, work_place, skills_score FROM employee;
例: hive> SELECT name, workplace, skills, score FROM employee LATERAL VIEW explode(work_place) wp AS workplace LATERAL VIEW explode(skills_score) ss AS skills, score;
例: hive> SELECT name, workplace, skills, score FROM employee LATERAL VIEW OUTER explode(work_place) wp AS workplace LATERAL VIEW explode(skills_score) ss AS skills, score;
REVERSE用于将指定字符串进行反转,SPLIT用于将字符串按指定分隔符进行分隔。
例: hive> SELECT reverse(split(reverse('/apps/ca/yanh/employee.txt'),'/')[0]) AS linux_file_name FROM employee LIMIT 1;
REVERSE将输出转换为单独元素,而COLLECT_SET和COLLECT_LIST则是反过来将元素组合成集合进行输出。COLLECT_SET和COLLECT_LIST的不同在COLLECT_SET返回的集合不含重复元素,而COLLECT_LIST则可以包含重复元素。
例: hive> SELECT collect_set(work_place[0]) AS flat_wprkplace FROM employee;
例:
hive> SELECT collect_list(work_place[0]) AS flat_wprkplace FROM employee; 注:Hive0.11.0及以前不支持collect_list
5. 虚拟列:虚拟列是Hive中特殊的列的特殊函数类型。目前为止Hive仅支持2个虚拟列:INPUT_FILE_NAME和BLOCK_OFFSET_INSIDE_FILE。INPUT_FILE_NAME列是mapper的输入文件名,BLOCK_OFFSET_INSIDE_FILE是当前全部文件位置或当前压缩文件的块偏移量。
例: hive> SELECT INPUT_FILE_NAME, BLOCK_OFFSET_INSIDE_FILE AS OFFSIDE FROM employee_id; 注:在Hive0.13.0上测试失败,没有该函数。
6. wiki未提到的函数:
例:isnull,用于检验值是否为空 hive> SELECT work_place, isnull(work_place) is_null, isnotnull(work_place) is_not_null FROM employee;
例:assert_true,如果条件为false时抛出异常 hive> SELECT assert_true(work_place IS NULL) FROM employee;
例:elt,返回第n个字符串 hive> SELECT elt(2, 'New York', 'Beijing', 'Toronto') FROM employee LIMIT 1;
例:current_database,返回当前数据库名 hive> SELECT current_database(); 注:Hive0.11.0及以前没有此函数
四、 数据转换 在Hive0.13.0以前不支持行级的数据转换。因此,数据行的更新、插入、删除都不能实现。因此数据重写只能发生在表或者分区,这使得Hive很难处理并发读写和数据清洗的情况。但是从0.13.0开始,Hive提供了原子性、一致性、隔离性和持久性(ACID)的行级数据处理功能。如今所有的转换操作支持ORC(优化排柱状,从Hive0.11.0开始支持)文件和桶列表中的数据。
以下配置参数需要适当的配置以开启Hive的转换功能: SET hive.support.concurrency = true; SET hive.enforce.bucketing = true; SET hive.exec.dynamic.partition.mode = nonstrict; SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; SET hive.compactor.initiator.on = true; SET hive.compactor.worker.threads = 1;
SHOW TRANSACTIONS可以对当前已开启的转换操作进行查询: hive> SHOW TRANSACTIONS;
从Hive0.14.0开始,行级插入数值、更新和删除可以使用以下语法规则进行实现: INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row …]; UPDATE tablename SET column = value [, column = value…] [WHERE expression]; DELETE FROM tablename [WHERE expression];
结语 以上便是全部Hive的关于数据的具体操作,相信到此为止应该能对Hive的常规数据操作进行较为得心应手的使用了吧。以上有截图的用例均由本人亲测可行,测试环境为Hive0.11.0,部分Hive0.13.0的特性是在0.13.0下进行测试,在截图下均有说明。
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。