hive中支持ip转化的函数吗 hive rpad

转载
数据探索先锋 2024-06-25 11:37:59
文章标签 hive中支持ip转化的函数吗 hive hiveql 大数据数据 文章分类 Hive 大数据
-----2018.7.25
--增加列，在分区字段之前
ALTER TABLE log_message ADD COLUMNS(
app_name STRING COMMENT 'application name',
session_id STRING COMMENT 'the current session id');
--删除或替换列,只将需要的列写出即可，未写出的列即等于删除
ALTER TABLE log_message REPLACE COLUMNS (
hours_mins_secs INT COMMENT 'hoour,minute,seconds from timestamp',
severity STRING COMMENT 'the message severity',
message STRING COMMENT 'the rest of the message');
--修改表属性
ALTER TABLE log_message SET TBLPROPERTIES(
'notes'='the process id is no longer captured;this column is always null');
--修改存储属性,如果表是分区表，那么需要使用PARTITION子句
ALTER TABLE log_message 
PARTITION(year=2012,month=1,day=1)
SET FILEFORMAT SEQUENCEFILE;--将一个分区的存储格式修改为SEQUENCEFILE
--用户可以指定一个新的SerDe，并为其指定SerDe属性，或者修改已经存在的SerDe的属性。
ALTER TABLE table_using_JSON_storage
SET SERDE 'com.example.JSONSerDe'
WITH SERDEPROPERTIES(
'prop1'='value1',
'prop2'='values2');
--向一个已经存在的SerDe增加新的SERDEPROPERTIES属性
ALTER TABLE table_using_JSON_storage
SET SERDEPROPERTIES (
'prop3'='value3',
'prop4'='value4');
--其他修改表语句
--1.为各种操作增加执行钩子的技巧。典型应用场景是，当表中存储的文件在hive之外被修改了，就会触发钩子的执行。例如某个脚本往分区中添加了新的日志信息文件
ALTER TABLE log_message TOUCH
PARTITION(year=2012,month=1,day=1);
--2.将分区内的文件打包成hadoop压缩包HAR文件。但是这样不会减少任何的存储空间，仅仅降低文件系统的文件数以及减轻NameNode的压力
ALTER TABLE log_message ARCHIVE
PARTITION(year=2012,month=1,day=1);--使用UNARCHIVE代替ARCHIVE既可以反向操作。该功能只能用于分区表中独立的分区
--3.hive提供各种保护，
ALTER TABLE logmessage
PARTITION(year=2012,month=1,day=1) ENABLE NO_DROP;--防止分区被删除，使用DISABLE可以达到反向操作的目的
ALTER TABLE log_message
PARTITION(year=2012,month=1,day=1) ENABLE OFFLINE;--防止分区被查询，使用DISABLE可以达到反向操作的目的
--向管理表中装载数据
LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
OVERWRITE INTO TABLE employees--如果用户指定了OVERWRITE关键字，那么目标文件夹中之前存在的数据将会被先删除。如果没有，仅仅会把新增的文件增加到目标文件夹中而不会删除之前的数据
PARTITION(COUNTRY='US',state='CA');
/*注：如果分区目录不存在的话，上个命令会先创建分区目录，然后再将数据拷贝到该目录下。如果目标表是非分区表，那么语句中应该省略PARTITION子句。如果使用了LOCAL这个
关键字，那么这个路径应该为本地文件系统路径。数据将会被拷贝到目标位置。如果省略掉LOCAL关键字，那么这个路径应该是分布式文件系统中的路径。这种情况下，数据是从这个
路径转移到目标位置的。即LOAD DATA LOCAL...拷贝本地数据到位于分布式文件系统上的目标位置，而LOAD DATA...转移数据到目标位置。其次，如果目标表为分区表，还需使用
PARTITION子句，而且用户还必须为每个分区的键指定一个值。再次，对于INPATH子句使用的文件路径还有一个限制，即这个路径下不可以包含任何文件夹*/
--通过查询语句向表中插入数据
INSERT OVERWRITE TBALE employees--使用OVERWRITE关键字，因此之前分区中的内容将会被覆盖掉，如想要追加的形式，可使用INTO替换OVERWRITE
PARTITION(country='US',state='OR')
SELECT * FROM staged_employees se
WHERE se.cnty='US' AND se.st='OR';
/*注：如果表staged_employees非常大，而且用户需要对65个州都执行这些语句，那么也就意味这需要扫描staged_employees表65次！hive提供了另一种INSERT语法，可以值扫描一次
输入数据，然后按多种形式划分，如下：*/
FROM staged_employees se
INSERT OVERWRITE TABLE employees
PARTITION(country='US',state='OR')
SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE TABLE employees
PARTITION(country='US',state='CA')
SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE TABLE employees
PARTITION(country='US',state='IL')
SELECT * WHERE se.cnty='US' AND se.st='IL';
--动态分区插入，基于查询参数推断出需要创建的分区名称
INSERT OVERWRITE TABLE employees
PARTITION(country,state)
SELECT ...,se.cnty,se.st
FROM staged_employees se;--hive根据select的最后两列来确定分区字段country和state的值
--混合动态和静态插入
INSERT OVERWRITE TABLE employees
PARTITION(country='US'，state)--静态分区键必须出现在动态分区键之前
SELECT ...,se.cnty,se.st
FROM staged_employees se
WHERE se.cnty='US';
/*动态分区属性
属性名称                               缺省值            描述
hive.exec.dynamic.partition             false          设置成true，表示开启动态分区功能
hive.exec.dynamic.partition.mode         strict         设置成nonstrict，表示允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode      100            每个mapper或reducer可以创建的最大动态分区个数。如果某个mapper或reducer尝试创建大于这个值的分区的话则会抛出一个致命错误
hive.exec.max.dynamic.partitions             +1000          一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息
hive.exec.max.created.files            100000          全局可以创建的最大文件个数。有一个hadoop计数器会跟踪记录创建了多少个文件，如果超过这个值则会抛出一个致命错误信息
*/
hive> set hive.exe.dunamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
hive> set hive.exec.max.dynamic.partition.pernode=1000;
hive> INSERT OVERWRITE TABLE employees
       PARTITION(country,state)
	   SELECT ...,se.cnct,se.st
	   FROM staged_employees se;
--单个查询语句中创建表并加载数据
CREATE TABLE ca_employees
AS SELECT name,salary,address
FROM employees se
WHERE se.state='CA';--新表的模式是根据select语句来生成的。新表中只含有employees表中来自加尼福利亚的雇员的name,salary,address字段信息。该功能不适用与外部表
--导出数据
hadoop fs -cp source_path target_path
---否则，用户可以使用INSERT...DIRECTORY...,如下所示
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name,salary,address
FROM employees se
WHERE se.state='CA';
---和向表中插入数据一样，用户也可以通过如下方式指定多个输出文件夹目录
FROM staged_employees se
INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
SELECT * WHERE se.cnty='US' AND se.st='OR'
INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
SELECT * WHERE se.cnty='US' AND se.st='CA'
INSERT OVERWRITE DIRECTORY '/tmp/il_employees'
SELECT * WHERE se.cnty='US' AND se.st='IL';
--建表
CREATE TABLE employees(
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING,FLOAT>,
address STRUCT<street:STRING,city:STRING,state:STRING,zip:IND>)
PARTITION BY (country STRING,state STRING);
--SELECT...FROM...
hive> SELECT name,salary FROM employees;
hive> SELECT e.name,e.salary FROM employees e;--多表连接查询时有用
--引用集合数据类型中的元素，WHERE子句中同样可以引用
hive> SELECT name,subordinates[0] FROM employees;--引用ARRAY元素，索引下标从0开始
hive> SELECT name,deductions['State Taxes'] FROM employees;--引用MAP元素
hive> SELECT name,address.street FROM employees;--引用STRUCT元素
--使用正则表达式来指定列
hive> SELECT symbol,'price.*' FROM stocks;--挑选symbol列和所有列名以price作为前缀的列
--使用列值进行计算
hive> SELECT upper(name),salary,deductions['Federal Taxes'],round(salary*(1-deductions['Federal Taxes'])) FROM employees;
/*算数运算符*
a+b   数值     a和b相加
a-b   数值     a减去b
a*b   数值      a和b相乘
a/b   数值      a除以b。如果能整除，那么返回商数
a%b   数值      a除以b的余数
a&b   数值      a和b按位取与
a|b   数值     a和b按位取或 
a^b   数值     a和b按位取亦或
~a    数值       a按位取反*/
--设置属性hive.map.aggr的值为true，来提高聚合的性能
hive> set hive.map.aggr=true;
--表生成函数
---将ARRAY类型内容转换成0个或多个新的记录，如果字段为空，那么将不会产生新的记录，如果不为空，那么数据的每个元素都将产生一行新记录
hive> select explode(subordinates) as sub from employees;--当使用表生成函数时，hive要求使用列别名
--limit语句,限制返回的行数
hive> select upper(name),salary,deductions['federal taxes'],round(salary*(1-deductions['federal taxes'])) from employees limit 2;
--列别名，为新产生的列取名
hive> select upper(name)
             ,salary
			 ,deductions['federal taxes'] as fed_taxes
			 ,round(salary*(1-deductions['federal taxes'])) as salary_minus_fed_taxes
			 from employees limit 2;
--嵌套select语句
hive> from(
           select upper(name)
		   ,salary
		   ,deductions['federal taxes'] as fed_taxes
		   ,round(salary*(1-deductions['federal taxes'])) as salary_minus_fed_taxes
		   from employees
		   )e
		   select e.name,e.salary,e.fed_taxes,e.salary_minus_fed_taxes
		   where e.salary_minus_fed_taxes>70000;
--case...when...then句式
hive> select name,salary
      case when salary<50000.0 then 'low'
	       when salary>=50000.0 and salary<70000.0 then 'midden'
		   when salary>=70000.0 and salary<100000.0 then 'high'
		   else 'very high' 
		   end as bracket 
		   from employees;
--hive避免进行mapreduce情况
select * from employees;
select * from employees 
where country='us' and state='ca'
limit 100;--过滤条件为分区字段
--where语句，谓词表达式用or或and连接，where语句中不能使用列别名，如需使用，需改为如下形式的嵌套查询
hive> select e.* 
      from (select name,salary,deductions['federal taxes'] as ded,salary*(1-deductions['federal taxes']) as salary_minus_fed_taxes) e
	  where round(e.salary_minus_fed_taxes)>70000.0;
--浮点数比较
hive> select name,salary,deductions['federal taxes'] from employees where deductions['federal taxes']>0.2;
john doe    10000.0       0.2--因为deductions map<string,float>,而用户写的0.2会被hive默认为double型，即表中的0.2（float型）=0.200000100000，而用户写的0.2（double型）=0.2000000001，所以该条记录将被查出
mary smith  80000.0       0.3
/*浮点数比较问题解决方法
将deductions字段修改为map<string,double>;
使用cast操作符，将0.2强制转换为float,即如下：
*/
hive> select name,salary,deductions['federal taxes'] from employees where deductions['federal taxes']>cast(0.2 as float);
--like %
hive> select name
            ,address.street
			from employees
			where address.street 
			like '%o';--挑选出所有以o结尾的街道名
hive> select name
            ,address.street
			from employees
			where address.street 
			like 'o%';--挑选出所有以o开头的街道名
hive> select name
            ,address.street
			from employees
			where address.street 
			like '%o%';--挑选出所有包含o的街道名
--rlike:like的扩展应用，'.'表示任意的字符匹配，'*'表示重复左边的字符0到无数次，'|'表示或
hive> select name
            ,address.street
			from employees
			where address.street 
			rlike '.*(chicago|ontario).*';--查找出所有住址的街道名称中含有单词chicago或ontario的雇员名称和街道信息;等同于下列语句（使用like转换）
hive> select name
            ,address.street
			from employees
			where address.street like '%chicago%' 
			or address.street like '%ontario%';
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。