1 limit语句优化
eg.select *from table_name > limit 100
在 Hive 中, 由于表的数据量往往较大, 以上语句都会被优化 (set hive.fetch.task.conversion = none 会被关闭这项优化, 强制起 MR 作业; 默认配置值为 more); 这些语句的运行,都会通过过滤文件的处理方式查询结果并返回, 而不是起 MR 任务提交到 Yarn 上执行返回, 这一点在运行日志中可以看出; 在表或分区数据量较小, 而且查询过滤条件命中的条目较多是会较快返回; 但是当由于表或分区数据量巨大, 或者命中条数很少而达不到 limit 的条数时, 过滤文件的操作就会一直进行到满足 limit 或者过滤 完文件所有数据才返回, 反而变得更慢。
表或者分区数据量多情况下,如果满足的数据量多,那么过滤某文件很快能返回值;如果数据量很少,是不加limit限制,那么MR查询所有,也可以很快返回。
如果查询的 where 子句中的字段过滤条件命中率不高, 建议不要带 limit 子句 (但强烈建议分区表查询时带上分区!!!)
如果非要带limit,加上:set hive.fetch.task.conversion = none;
Fetch Task功能:
一个简单的查询语句,是指一个没有函数、排序等功能的语句,当开启一个Fetch Task功能,就执行一个简单的查询语句不会生成MapRreduce作业,而是直接使用FetchTask,从hdfs文件系统中进行查询输出数据,从而提高效率。
2 case when语句报错
when 后面的表达式应该类型保持一致
3 修正ctas的表数据错行问题
直接select可能没问题,创建新表时:
CREATE TABLE table_name_new asselect ,,, from table_nameWHERE dt='';
这时候查询table_name_new可能会出现错位,解决办法:
CREATE TABLE table_name_new storeas parquetas select ,,, from table_nameWHERE dt='';
原因:table_name_new数据格式是 Textfile 格式, 默认的行分隔符为 \n,列分隔符为 \001; 所以当数据中有换行符时解析时会被换行,为了正确解析带有特殊字符的数据, 建议将表存储为 PARQUET 或者其他 Hive 支持的数据格式; 这样带特殊字符的字段将不会再错行或者错列。
4 查询表时文件格式出错
报错信息:
org.apache.hadoop.hive.serde2.columnar.BytesRefArrayWritable cannot be cast to org.apache.hadoop.io.BinaryComparable
或
org.apache.hadoop.hive.ql.io.orc.OrcStruct cannot be cast to org.apache.hadoop.io.BinaryComparable
这是因为表存储为 RCFILE 或 ORCFILE, 而表的 SERDE 设置的序列化反序列化类不适配, 使用以下语句修改, 如果是分区表, 分区也需要使用 alter 或 msck 重建 (Hive 的元数据表 SDS 中存储了表和分区的序列化反序列化等属性)
--只 serde 不同时示例
alter table ${database}.${table}set serde 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';--field.delim 等也不同时示例
alter table ${database}.${table}set serde 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
WITH SERDEPROPERTIES ('field.delim' = '\001', 'serialization.format' = '\001');
5 表查询子目录递归问题
当 (非分区) 数据表目录下有子目录时, 使用 MR 引擎可能查不出数据或查不到部分数据, 因为默认 MR 是不会读取表目录下的子目录数据的, 例子:
hive> dfs -ls hdfs://XX/detail_tmp1;
Found 5items
drwxr-xr-x - hive hadoop 0 2018-08-07 14:46 hdfs://XX/detail_tmp1/1
drwxr-xr-x - hive hadoop 0 2018-08-02 15:05 hdfs://XX/detail_tmp1/2
drwxr-xr-x - hive hadoop 0 2018-08-02 15:06 hdfs://XX/detail_tmp13
drwxr-xr-x - hive hadoop 0 2018-08-02 15:05 hdfs://XX/detail_tmp1/4
drwxr-xr-x - hive hadoop 0 2018-08-02 15:06 hdfs://XX/detail_tmp1/5
表目录下无文件但有子目录, MR 引擎默认不会递归到子目录所以查不出数据;
使用 set hive.execution.engine = tez 将引擎切换到 TEZ, 查询即可有数, 这是因为在 切换为TEZ 引擎下时, Hive 会将 mapred.input.dir.recursive 和 mapreduce.input.fileinputformat.input.dir.recursive 设置为 true, 查询时会递归目录;
如果在同一个会话下, 再将引擎切回至 MR, 则会发现也能查出数据了, 因为之前在 TEZ 引擎下执行了语句, 已将 mapred.input.dir.recursive 和 mapreduce.input.fileinputformat.input.dir.recursive 设置为 true 了。
部分引擎会默认将 mapred.input.dir.recursive 和 mapreduce.input.fileinputformat.input.dir.recursive 这两个属性设置为 true