攻城狮Kevin

由于hivesql中查询语句时，常用查询条件比较多，所以专门写一篇博文对HiveQL的查询语句进行总结，理清联系和区别。目录1. 排序子句（1）单列全局排序order by（2）多列排序（3）每个MapReduce内部排序（sort by）（4）分区排序（distribute by 字段1 sort by 字段2）（5）分区排序（cluster by）2. 分桶查询...

在hive和mysql中都可以通过explain+sql语句，来查看执行顺序。对于一条标准sql语句，它的书写顺序是这样的：select … from … where … group by … having … order by … limit …（1）mysql语句执行顺序：from... where...group by... having.... select ... ...

由于hivesql中查询语句时，常用查询条件比较多，所以专门写一篇博文对HiveQL的查询语句进行总结，理清联系和区别。目录1. where子句（不能跟别名）（1）比较运算符（2）like和rlike（正则匹配）（3）逻辑运算符and or not2. group by子句（每...）3. having子句（只用于group by之后）4. join子句（A joi...

目录1. nvl（value，default_value）2. case wheneg1：对表emp_sex，求每个部门男女人数eg2：统计每个国家隶属洲的人口数（已知字段数据按照另一种条件分组）eg3：统计不同国家男女个数（完成不同条件的分组）3. concat、concat_ws、collect_set（列转行）4. explode & lateral vi...

本篇整理向hive表中导入数据的各种操作，数据操纵语言（Data Manipulation Language,DML）。目录1. load2. Insert3. As select4. Location5. Import1. load加载linux本地文件或HDFS文件到hive表中，其中HDFS文件load方式是剪切模式，覆盖hive表已有数据关键词ov...

hive

由于where和group by的执行顺序都在select之前，所以select的别名不可以跟在其之后，order by的执行顺序在select之后，所以order by后可以跟select的别名。两者的区别在于：hivesql中group by后不可跟别名，而mysql中group by后可以跟别名；2. group by和partition by后不可跟别名。1. order by后可以跟别名。1. order by后可以跟别名。2. group by后可以跟别名。3. where后不可跟别名。

select max(id) from table时，如果字段id中存在空值null，或者字段中全部为空值null，sql语法不会报错，前者会忽略null取个最大值，后者直接max出来的结果就是null

select 1 AS a, 2 as B, 3 As C from table limit 1；得出结果：a b c1 2 3可以发现，无论是关键词as/AS/As，还是字段B，最后一律都处理成小写了，hive底层不区分大小写，除非用lower uper这种函数强制变大小写...

A left join B on A.a = B.b当a与b是一对多时，也就是一条A=a关联多条B=b，关联时候顺序是随机的， a=1 B=1 xxx1 a=1 B=1 xxxx2 a=1 B=1 xxxxx3也可能变成 a=1 B=1 xxxx2 a=1 B=1 xxx1 a=1 B=1 xxxxx3每次重跑其顺序不固定，可见left join的时候出现一对多时，关联顺序是随机的。...

1. udw上的数据托管，删除元数据同时会删除表数据，如果表数据是关联的hdfs路径，那会直接把hdfs路径删了。所以ods层关联hdfs，ods层表不能勾选数据托管。2.udw上的表修改元数据，如果表的字段顺序变化（中间插入或者删除字段），select的字段映射的字段列也会出错，所以如果udw表字段的发生顺序变化，需要重跑数据，除非在表最后添加字段，这样不影响历史数据。...

hive对字段去空格的函数一共3个：1. ltrim(string)：去掉字段的前置空格2. rtrim(string)：去掉字段的后置空格3. trim(string)：去掉字段首尾空格ps：以上三个函数不会去除字段中间的空格！对null也可以使用，trim（空字段）最终返回还是nullps：nvl（trim(A), 1）可以共用，当字段A为空时，最终输出还是1...

一般增加自增列可以考虑row_number() over(partition by XX order by XX) as rank但是需要给hive表全局加上自增列，并且没有字段可供排序时，partition by和order by后无表字段可用。这种情况只能考虑用row_number() over(order by 1)，即每一行记录多出个1，然后对1进行排序，取排序号作为自增id列，但是这样结果会出现一个问题：记录结果会被打乱，因为无论spark还是mr本质是分布式进行的，运行多个task，

一、hive关联外部表ALTER TABLE XXX DROP IF EXISTS PARTITION (event_day='{YESTERDAY}');ALTER TABLE XXX ADD IF NOT EXISTS PARTITION(event_day='{YESTERDAY}') LOCATION 'XXX';1. hdfs文件和hive表的存储类型、分隔符必须一致，不然报错。2. hdfs上按照分隔符分割后，比如有20个字段，hive表有16个字段，那么映射会成功，但是只映射前面

采用spark引擎跑sql，insert overwrite select方式覆盖写数据表时，spark某些版本覆盖写不会重置表的schema；所以建议如果数据表中间有插入新字段，schema改变的话，覆盖写数据之前先删除分区ALTER TABLE udw_ns.default.XXX DROP IF EXISTS PARTITION (event_day='{YESTERDAY}');insert overwrite XXX select ......

对于hive中的聚合函数，以及大小比较，例如max、min、count、sum、>、<、=等与java一致，如果max（字符串），会按照字典序进行比较，所以如果要对字段进行数值比较，该字段必须设置为int、float、bigint、double等数值类型，不要设置成string或者cast（XX as int）转一下也可以...

数值大小的比较，字段类型必须是int或者bigint，不能是stringstring可以比较 =，是否为某个值string比较大小时是按照字典序排的，例如select 9 < 15trueselect '9' < '15'false因为字典序比较大小时，9大于1，所以'9' > '15'...

hive支持in的查询用法有两种：1. in （1,2,3 ...）2. in（select from ...）

hive的语句执行顺序是group by ... select ... order byorder by是对前面查询到的结果进行排序，两者连用的语法顺序即 select ... group by ... order by注意事项有两点：1. order by的字段必须是在group by中，或者是聚合函数1. select file1,file2 from tableName group by file1,file2 order by file1;2. select file1, cou

语法: rand()，rand(int seed)函数返回值: double随机数说明：返回一个0到1范围内的随机数。若是指定种子seed，则会等到一个稳定的随机数序列。> select rand(); 0.9629742951434543> select rand(0); 0.8446490682263027> select rand(null); 0.8446490682263027ps：如果想要取的0-9或者1-10之间的随机数，x10后向下向上取整即可

hdfs文件映射生成hive外部表时，本质是文件的序列化和反序列化ALTER TABLE XXX ADD IF NOT EXISTS PARTITION(event_day='{YESTERDAY}') LOCATION 'HDFS路径';需要注意两点：1. hdfs文件和hive表的存储格式必须一致，如果hdfs是text格式，hive表是orc格式，数据关联不上，映射不过来2. hdfs文件和hive表的分隔符必须一致，否则解析错误，hive的分隔符需要注意\t和\001（对应代码

group by A，B，CABC的先后，对查询的最终结果值没有影响；但是分组排序的逻辑是：先按照A进行字典排序，默认升序，然后当A相同时，对B进行字典排序，默认升序，最后当B相同时，对C进行字典排序，默认升序。所以ABC的分组字段先后顺序，对排序结果有影响，但是对最终查询的结果数值没有影响...

Hql语句：SELECT *FROM Topic t WHERE t.id >2 ORDER BY t.type,t.number DESC依据sql的执行顺序，from---where---select---order by假设where t.id>2之后的结果如下则通过对t.type进行降序排序后结果为:再通过对t.number进行降序排序后结果为：很明显，order by type, number在保持满足对type排序的前提下，即当type字段...

Hive支持的数据表的存储数据格式，主要有四种：行式存储：textFile、SequenceFile列式存储：orc、parquet一、测试4种存储格式的压缩比测试数据log.data，查看属性为18.1MB1. textFile格式textfile格式是hive表存储的默认格式，数据不做压缩，磁盘开销大，数据解析开销大。（1）创建表，存储格式为textfilecr...

（1）hive并不是所有的查询都需要走MRhive的优化中fetch抓取，就是修改配置文件参数hive.fetch.task.conversion为more这样全局查找、字段查找、过滤查询、limit查询，都不会走MR，直接fetch抓取，提高查询效率（其余的还是走MR）ps：该参数设置为none时，所有任务转化为MR，一般默认是more（2）hive中需要走MR的sql，通常涉及key的shuffle，比如join、group by、distinct等以下参照链接：HiveSQL转化

一、解决数据倾斜1. 过滤掉null部分key值存在大量空字段，会hash到同一reduce，造成reduce长尾，将null 值过滤掉举例： select user_id from logs where statdate = '20170815' where user_id is not null;2. hive join倾斜：设置join skew参数解决set hive.optimize.skewjoin = true;set hive.skewjoin.key = sk

一般查询hive表中某个字段是否存在某个值，会使用如下查询语句select * from 表名 where dt=XXXX and 字段 like '%某个值%' limit 10；这种比较适合hive表数据量较小的情况，出现limit10，hive会默认判断此表数据量不大，只开启一个map进行查询，如果对于大表，比如好几百G上T的数据量，这么大的数据量只开一个map一方面查询慢，另一方面，因为数据量太大容易把机器跑崩。采用以下方式即可，加上group by，然后就会依据hive表的文件

get_json_object(string json_string, string path)说明：第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。每次只能返回一个数据项。举例：data 为 test表中的字段，数据结构如下：data ={"store": { "fruit":[{"weight":8,"type":"apple"},...

比如处理80TB的数据，partition数量为15000，理论上平均每个节点是5-6G的数据，但是实际上根据key-value在存储时，很有可能因为某个key的数量特别多，导致数据倾斜。这样就会出现超过物理内存限制的报错。偶尔重试可能会通过，但是会比较不稳定；目前我们这边的两种解决办法是：1. 如果倾斜的key数量比较少，那么过滤出来，对其进行单独处理；2. 如果倾斜的key数量比较多...

表中一共两个字段，tag_name和tag_value，查询出指定tag_name下，tag_value值最大的记录通过以下sql语句，查询出结果为999，不正常select tag_name, max(a.tag_value) from (select tag_name, tag_value_num fromhdp_teu_dpd_feature_db.da_wanxiang_dr...

在hiveSql中指定生成的文件个数用到的参数如下：SET mapred.reduce.tasks=1001;但是单独加此参数，文件数并不会变成1001，为什么呢？其实是hive默认在mapReduce结束时合并小文件所致，因此需要加入以下两个参数：set hive.merge.mapfiles=false;（默认为true）set hive.merge.mapredfil...

往HDFS落盘数据后，select *不显示数据，需要修复一下hive表的分区，修复语句如下：MSCK REPAIR TABLE tableName;

如果HDFS配置好回收站的话，drop删除表后可以通过命令恢复表数据，例如回收站HDFS路径如下：/user/hdp_teu_dpd/.Trash/Current/表的HDFS路径如下：/home/hdp_teu_dpd/warehouse/wbdb.db/dw_smart_merge_idmapping/当drop tabledw_smart_merge_idmappi...

场景：需要对mysql数据库中的表A数据去重，一共3个字段：id，tag_en，mapping_id其中id为自增主键，重复数据的tag_en，mapping_id字段一致，如下，需要去除重复数据并且只保留一行记录。因为数据量较大，在navicat中用group by的方式难以实现。实现方法：先建一张结构一致的表create table B like A;然后先把...

第一种用法：substr（string A，int start）和 substring（string A，int start），用法一样功效：返回字符串A从下标start位置到结尾的字符串第二种用法：substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样功效：返回字符串A...

目录一、Hive到底是什么1. 如何理解Hive2. Hive中SQL-MapReduce原理图解3. 为什么说Hive是基于Hadoop的呢？二、Hive的优缺点1. 优点2. 缺点三、Hive的架构原理1. 两种客户端2. 四种驱动Driver3. 元数据库Meta store四、Hive和数据库的区别（1）数据存储位置不同（2）数据更新...

适用于HDFS中存放结构化数据的场景，先建一张外部表CREATE EXTERNAL TABLE IF NOT EXISTS `ds_smart_log_idmapping`( `id` string COMMENT '包含wimei、wuser、telep、dimei和idfa', `ffaid` string COMMENT 'ffaidXXX', `timeStamp`...

Mysql中可以用limit m-1, n的限制语法；表中下标从0开始，从第m条记录开始取，一共取n条记录Hive中这种语法不支持，可以用row_number() over(distribute by ... sort by ... ) rank where rank< (m+n+1) and rank > (m-1)实现...

目录一、Java数据类型1. 八种基本数据类型2. 引用数据类型二、Hive数据类型1. 基本数据类型（对应Java中的数据类型）2. 集合数据类型Hive的数据类型比较特殊，与传统Java数据类型不一样，先回顾一下Java的数据类型一、Java数据类型1. 八种基本数据类型（1）数值型-整数：byte1B、short2B、int4B、long8B（2）...

聚合：count、sum、min、max、avg、first_value、last_value、lag、lead、cume_dist、排序：rank、dense_rank、ntile、row_numer/percent_rank、