hive SQL的常用设置

遇到个情况,跑hive级联insert数据报错,可以尝试换个hive计算引擎

hive遇到FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask错误

1、配置mapreduce计算引擎
set hive.execution.engine=mr;

2、配置spark计算引擎
set hive.execution.engine=spark;

3、配置tez 计算引擎
set hive.execution.engine=tez;

hive的sql操作与musql些许区别,比如hive没有group_concat,取而代之的是collect_set;hive不能使用子查询等,以下是常用操作:

 添加分区

ALTER TABLE table_name ADD PARTITION (partCol = ‘value1‘) location ‘loc1‘; //示例
ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (dt=‘20130101‘) LOCATION ‘/user/hadoop/warehouse/table_name/dt=20130101‘; //一次添加一个分区

例:
ALTER TABLE $abc$ ADD IF NOT EXISTS PARTITION(p_provincecode=$abc.p_provincecode$,p_year=$abc.p_year$,p_month=$abc.p_month$) LOCATION ‘/hadoop路径/p_provincecode=$abc.p_provincecode$/p_year=$abc.p_year$/p_month=$abc.p_month$/‘

ALTER TABLE page_view ADD PARTITION (dt=‘2008-08-08‘, country=‘us‘) location ‘/path/to/us/part080808‘ PARTITION (dt=‘2008-08-09‘, country=‘us‘) location ‘/path/to/us/part080809‘;  //一次添加多个分区

删除分区
ALTER TABLE login DROP IF EXISTS PARTITION (dt=‘2008-08-08‘);
ALTER TABLE page_view DROP IF EXISTS PARTITION (dt=‘2008-08-08‘, country=‘us‘);

修改分区
ALTER TABLE table_name PARTITION (dt=‘2008-08-08‘) SET LOCATION "new location";
ALTER TABLE table_name PARTITION (dt=‘2008-08-08‘) RENAME TO PARTITION (dt=‘20080808‘);

添加列(字段)
ALTER TABLE table_name ADD COLUMNS (col_name STRING);  //在所有存在的列后面,但是在分区列之前添加一列

修改列
CREATE TABLE test_change (a int, b int, c int);
/* will change column a‘s name to a1*/
ALTER TABLE test_change CHANGE a a1 INT;

修改列的顺序
ALTER TABLE table_name CHANGE col_old_name col_new_name column_type AFTER column_name;
 
/* will change column a‘s name to a1, a‘s data type to string, and put it after column b. The new table‘s structure is: b int, a1 string, c int*/
ALTER TABLE test_change CHANGE a a1 STRING AFTER b; 

/* will change column b‘s name to b1, and put it as the first column. The new table‘s structure is: b1 int, a int, c int*/
ALTER TABLE test_change CHANGE b b1 INT FIRST;

hive删除字段
ALTER TABLE wireless_union_data_export_part1 REPLACE COLUMNS();

修改表属性
alter table table_name set TBLPROPERTIES (‘EXTERNAL‘=‘TRUE‘);  //内部表转外部表 
alter table table_name set TBLPROPERTIES (‘EXTERNAL‘=‘FALSE‘);  //外部表转内部表

表的重命名
ALTER TABLE table_name RENAME TO new_table_name

gbase修改字段只能先删掉字段,然后再添加
gbase删除字段
alter table table_name drop 字段名

gbase添加字段
alter table table_name add COLUMN col_name STRING;

gbase清除表数据不删除表
delete 表名

常用函数
1.isnull(string a) --如果a是null,返回flase,如果啊不为空,返回true
2.ROUND(double a)四舍五入bigint类型
  CEILING       --舍入到最大整数   3.33舍入到4    2.98舍入到3    -3.61舍入到-3
  FLOOR()       --舍入到最小整数   3.33舍入到3    2.98舍入到2    -3.61舍入到-4
3.map(key1, value1, key2, value2, …)通过指定的键/值对,创建一个map

4.cast(expr as <type>)类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint),如果转换失败返回NULL。
  例:CAST(num*100 AS decimal(8,0)) as num

5.unix_timestamp()  --如果不带参数的调用,返回一个Unix时间戳(从’1970- 01 – 0100:00:00′到现在的UTC秒数)
   --指定日期参数调用UNIX_TIMESTAMP(),它返回参数值’1970- 01 – 0100:00:00′到指定日期的秒数
  unix_timestamp(string date, string pattern)   --指定时间输入格式,返回到1970年秒数:unix_timestamp(’2009-03-20′, ‘yyyy-MM-dd’) = 1237532400
  from_unixtime(bigint unixtime[, string format]) 
    例:select from_unixtime(1505750400,‘%Y-%m-%d %H-%i-%S‘);
  --UNIX_TIMESTAMP参数表示返回一个值’YYYY- MM – DD HH:MM:SS’或YYYYMMDDHHMMSS.uuuuuu格式,这取决于是否是在一个字符串或数字语境中
  to_date(string timestamp)    --返回时间中的年月日: to_date(“1970-01-01 00:00:00″) = “1970-01-01″
  to_dates(string date)     --给定一个日期date,返回一个天数(0年以来的天数)
  year、month、day、hour、minute、second、weekofyear    --日期转年、转月…………
  
  hour  --返回指定时间的小时,范围为0-23  
  weekofyear(string date)   --返回指定日期所在一年中的星期号,范围0到53
  datediff(‘string enddate‘,‘string startdate‘)     --两个时间参数的日期之差
  DATEDIFF ( date-part, date-expression-1, date-expression-2 )      --返回两个日期之间的间隔
  date-part : year | quarter | month | week | day | hour | minute | second | millisecond
  sec_to_time(秒)    --将秒转化成时间
  time_to_sec(时间)   --将时间转化为秒
  date_add(string startdate,int days)       --给定时间,在此基础上加上指定的时间段
  date_sub(string startdate,int days)       --给定时间,在此基础上减去指定的时间段
  
6.CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END    --当a=b时,返回c;当a=d时,返回e,否则返回f
  CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END      --当值为a时返回b,当值为c时返回d。否则返回e
  
7.concat(string A, string B…)   --连接多个字符串,合并为一个字符串,可以接受任意数量的输入字符串
                                --任何字符与null连接都是NULL
  concat_ws(string SEP, string A, string B…)    --带分隔符字符串连接函数
  例如:select concat_ws(‘_‘,‘abc‘,‘def‘,‘gh‘) from lxw_dual;返回 abc_def_gh

  行转列,e.g. select concat_ws(‘_‘,collect_set(visitor_name))


8.upper(string A) 或者 ucase(string A)   --将文本字符串转换成字母全部大写形式
9.lower(string A) 或者 lcase(string A)   --将文本字符串转换成字母全部小写形式
10.trim(string A)   --删除字符串两端的空格,字符之间的空格保留
   ltrim   --左边去空格
   rtrim   --右边去空格

11.like     --例如:a like b --如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B  的正则语法,则为TRUE;否则为FALSE。
            --B中字符”_”表示任意单个字符,而字符”%”表示任意数量的字符。
   例如:select aaaaaa like ‘%aaa%‘

12.split(string str, string pat)    --将字符串转换为数组,以pat为分隔

13.substr(string A, int start) 或者 substring(string A, int start) --字符串截取函数,
   例:select substr(‘abcde’,3);  --返回cde
   例:select substring(‘ancdefghijklmn‘,4,8);    --返回 defghijk     
   
    该子字符串从指定的 beginIndex 处开始, endIndex:到指定的 endIndex-1处结束。   


14.regexp_extract    --正则表达式解析函数  通过下标返回正则表达式指定的部分
regexp_extract(str, regexp[, idx])
其中:
str是被解析的字符串
regexp 是正则表达式
idx是返回结果 取表达式的哪一部分  默认值为1。
0表示把整个正则表达式对应的结果全部返回,1表示返回正则表达式中第一个() 对应的结果 以此类推 
例 from 
     (select distinct id,      name,   val   from text_table
      where p_provincecode=12345    and regexp_extract(lower(id),‘(@id)$|(@num)(\\d*)$‘,0) <>‘‘
     )a
$   匹配行结束符;
*   匹配0或多个正好在它之前的那个字符;重复零次或多次
\   这是引用符,用来将这里列出的这些元字符当作普通的字符来进行匹配;
|   将两个匹配条件进行逻辑“或”(Or)运算;
\d  匹配数字。在hive这种写正则表达式需要用\转义,所以\\d
^   匹配一行的开始
?   匹配0或1个正好在它之前的那个字符;重复零次或一次
.   匹配除换行符号外的任意字符

15.regexp_replace(string A, string B, string C)    --正则表达式替换函数 字符串A中的B字符被C字符替代
regexp_replace(trim(waitbegin), ‘/‘, ‘-‘)

unix_timestamp(concat_ws(‘:‘, regexp_replace(trim(ackbegin), ‘/‘, ‘-‘), ‘00‘))-unix_timestamp(concat_ws(‘:‘, regexp_replace(trim(waitbegin), ‘/‘, ‘-‘), ‘00‘)) as wait_duration,

16.get_json_object(string json_string, string path)  --json解析函数  解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效,那么返回NULL。

17.内置聚合函数  count、sum、avg、min、max

18.left semi join ... on ...--只打印左边的表中的列,规律是如果主键在右边表中存在,则打印,否则过滤掉
--相当于sql的in
--不会产生重复的多条记录

19.collect_set --与group by组合能列转行,相当于group_concat
20.SELECT  COALESCE(NULL, NULL, GETDATE())  --由于两个参数都为null,所以返回getdate()函数的值,也就是当前时间。即返回第一个非空的值。由于这个函数是返回第一个非空的值,所以参数里面必须最少有一个非空的值,如不是,将会报错

sql:注意问题
1.hive中只有union 没有union all
union --联合两张表(会自动去重,两张表取出的字段个数和类型必须一致)    并集操作
union all  --联合两张表的所有数据(不会去重,两张表取出的字段个数和类型必须一致)     并集操作

2.ABS() --求绝对值
  
3.与over函数结合的几个函数介绍
参考网址:http://blog.****.net/winer2008/article/details/4283539
row_number()over()、rank()over()和dense_rank()over()函数的使用
  row_number() 是没有重复值的排序(即使两天记录相等也是不重复的),可以利用它来实现分页
  dense_rank() 是连续排序,两个第二名仍然跟着第三名
  rank()       是跳跃排序,两个第二名下来就是第四名

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 
--表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
例:row_number() over (partition BY stbid,userid ORDER BY timestamp) AS group_idx,

--over(order by salary range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。
sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和

--over(order by salary rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。
sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
4.group by 和 having
SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2
5.SemanticException Column xx Found in more than One Tables/Subqueries报错

Hive中有两张表中拥有同一个字段名称,在这两个表做关联的时候会报这个错误,

select a.c,   b.c,   c.name
from A b
left join (select count(1) as c,code from B group by code )b  on a.code=b.code
left join C c on a.code = c.code;

a.c 和c.name的c,hive下含义辨识不清楚,改为如下

select a.c,   b.c,   cc.name
from A b
left join (select count(1) as c,code from B group by code )b  on a.code=b.code
left join C cc on a.code = cc.code;

5.子查询只能在from后或者where后

hive中的子查询改join操作

 

6.hive基本数据类型记录

 Hive内置数据类型

 

 

转载:https://www.cnblogs.com/xiaoliu66007/p/9436087.html

hive SQL的常用设置

上一篇:mysql:sql create database新建utf8mb4 数据库


下一篇:十六:SQL注入之查询方式及报错盲注