大数据入门与实战-Hive 常见SQL、技巧与问题
1 常见SQL
- 取数据库前100条数据
select * from table order limit 100;
-随机取100条数据
select * from table order by rand() limit 100;
select * from table limit 100;
2 常见方法
- 宏的使用
宏可以看做是一个简短的函数，或者是对一个表达式取别名，同时可以将这个表达式中的一些值做成变量调用时传入，比较适合于做分析时为一些临时需要用到很多次的表达式操作封装一下取个简短点的别名来调用。
创建宏的语法：
CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;
- TRIM函数
SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白。
各种 trim 函数的语法如下：
TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。 这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。
LTRIM(字串): 将所有字串起头的空白移除。
RTRIM(字串): 将所有字串结尾的空白移除。
- LCASE() 函数
LCASE() 函数把字段的值转换为小写。
- regexp_replace:正则替换
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
实例如下：
select regexp_replace(poi_name,'\\\\n','')
-
size：统计大小
-
split: 分割文本，和Python差不多
-
reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。
使用案例3：使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。
使用方式如下：
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123") from dual;
结果：
true
- length：字符串长度
- concat_ws
concat_ws()表示连接之用分隔符，它是CONCAT()的一种特殊形式。 第一个参数是分隔的参数的其余部分。分隔符是要连接的串之间加入。分隔符可以是一个字符串，如可以是参数的其余部分。如果分隔符是NULL，则结果为NULL。
- collect_list/collect_set
将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重
Hive笔记之collect_list/collect_set（列转行）
3 常见问题
- hive严格模式：No partition predicate found for Alias
在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说，就是用户不允许扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。如果没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
hive严格模式：No partition predicate found for Alias
