导hive表项目总结（未完待续）

shell里面对日期的操作

#!/bin/bash
THIS_FROM=$(date +%Y%m%d -d "-7 day")
THIS_TO=$(date +%Y-%m-%d -d "-1 day")
LAST_FROM=$(date +%Y-%m-%d -d "$THIS_FROM -1 year")
LAST_TO=$(date +%Y-%m-%d -d "$THIS_TO -1 year")

echo $THIS_FROM
echo $THIS_TO
echo $LAST_FROM
echo $LAST_TO

; i<; i++))
do
exp="-"$i" day"
DATE=$(date +%Y%m%d -d "${exp}")
MYDATE[i]=$DATE
done

 ; i<; i++))
do
echo ${MYDATE[i]}
done

python 日期的一些问题

#!/usr/bin/python
import datetime
import time

def formatdate_day(d, i):
   return (datetime.datetime.strptime(d, '%Y%m%d')
            + datetime.timedelta(days=i)).strftime('%Y%m%d')

DATE=time.strftime('%Y%m%d',time.localtime(time.time()))
print formatdate_day(DATE, 1)

python里面用shell的代码

#!/usr/bin/python
import os
os.system('date +%Y%m%d -d "-1 year -8 day"')

python 执行shell命令

#!/usr/bin/python
import os
import commands
os.system('ls')

os.system('cat ./config.js')
print "--------------------------------------------"

output = os.popen('cat ./config.js')
print output.read()
print "--------------------------------------------"

(status, output) = commands.getstatusoutput('cat ./config.js')
print status, output
print "--------------------------------------------"

res = commands.getstatusoutput('cat ./config.js')
print res

python里使用shell

#!/usr/bin/python
# -*- encoding: utf-8 -*-
import os
import commands
from subprocess import call

# os 模块的system方法
# system方法会创建子进程运行外部程序，方法只返回程序的运行结果。这个方法比较适用于外部程序没有输出结果的情况
os.system("echo \"Hello World\"")
print "----------------------------------------"

val = os.system("ls -al | grep \"log\" ")
print val
print "----------------------------------------"

val2 = os.system("ls -al | grep \"log1\"")
print val2
print "----------------------------------------"

#os 模块的popen方法
# 当需要得到外部程序的输出结果时（system只是执行了没有保存输出结果），本方法非常有用
# 它返回了一个类文件对象，调用该对象的read()或readline()方法可以读取输出内容
# os.popen(cmd) 要得到命令的输出内容，只需要再调用下read()或readline()

val3 = os.popen("ls -al")

val4=val3.read()

if "log" in val4:
    print "log exists"
else:
    print "log not exists"

print "-----------------------------------------"

#commands 模块
# 使用commands模块的getoutput方法，这种方法同popen的区别在与popen返回的是一个类文件对象，而本方法将外部程序的输出结果当作字符串返回，很多情况用起来更方便
# 主要方法  commands.getstatusoutput(cmd)  返回(status, output)   直接print转义字符不转化..
#   commands.getoutput(cmd) 只返回输出结果

(status, output) = commands.getstatusoutput('ls')
print output
print commands.getoutput('ls')
print "-----------------------------------------"

# subprocess模块
# 根据Python官方文档说明，subprocess模块用于取代上面的模块，与system相比更灵活
# 可以得到标准输出，标准错误，“真正”的状态代码，更好地错误处理等
print call(["ls","-al"])

对于日期的一些处理

#!/usr/bin/python
# -*- encoding: utf-8 -*-
import datetime
import time

#python array
a = ['cat', 'window', 'd']
print a[1]
print a[2]

print "---------------------------"

# 加减任意天
def format_date(d, i):
   return (datetime.datetime.strptime(d, '%Y%m%d')
            + datetime.timedelta(days=i)).strftime('%Y%m%d')

DATE=time.strftime('%Y%m%d',time.localtime(time.time()))
YESTERDAY=format_date(DATE, 1)
#格式化
YESTERDAY=time.strftime("%Y-%m-%d",time.strptime(YESTERDAY,"%Y%m%d"))
print YESTERDAY
print "--------------------------"

#上面格式化的拆分成两步，先按指定格式拆成时间数组，再转化成指定格式的时间
timeArray=time.strptime("2015-10-10","%Y-%m-%d")
print timeArray
mytime=time.strftime("%Y:%m:%d",timeArray)
print mytime

#timedelata使用
#>>> datetime.datetime.now()
#datetime.datetime(2015, 10, 20, 19, 49, 26, 167069)
#>>> datetime.datetime.now()+datetime.timedelta(1)
#datetime.datetime(2015, 10, 21, 19, 49, 33, 207531)

shell EOF：

在shell编程中，”EOF“通常与”<<“结合使用，“<<EOF“表示后续的输入作为子命令或子shell的输入，直到遇到”EOF“，再次返回到主调shell，可将其理解为分界符（delimiter）。既然是分界符，那么形式自然不是固定的，这里可以将”EOF“可以进行自定义，但是前后的”EOF“必须成对出现且不能和shell命令冲突。其使用形式如下：

交互式程序(命令)<<EOF
command1
command2
...
EOF

（我们这里用的是..  $HIVE << EOF         EOF）

”EOF“中间的内容将以标准输入的形式输入到”交互式程序“，当shell看到”<<“知道其后面输入的分界符，当shell再次看到分界符时，两个分界符中间的部分将作为标准输入。
"EOF"一般常和cat命令连用。
注意，最后的”EOF“必须单独占一行。

下面以cat命令为例讲解”EOF“使用。一般有以下两种形式
1.cat << EOF
2.cat << EOF > filename或者cat << EOF >> filename
其实，第一种形式和第二种形式没有什么本质的区别，第一种形式将内容直接输出到标准输出（屏幕），而第二种形式将标准输出进行重定向，将本应输出到屏幕的内容重定向到文件而已。

例1：
#cat<<EOF
>12
>34D
>EOF
12
34D

例2：
#cat>>filename<<EOF
>DHA
>23
>EOF
#cat filename
DHA
23

hive 基本插入语法

insert overwrite table tablename [partition(partcol1=val1,partclo2=val2)] select_statement;

insert into table tablename [partition(partcol1=val1,partclo2=val2)] select_statement;

eg：
insert overwrite table test_insert select * from test_table;
insert into table test_insert select * from test_table;
insert overwrite table xx_xx_xx partition (dt = '$DATE')select xxx;

注：
overwrite重写，into追加。

ps： hive 的注释是两个横杠 “--”

hive导出文件

$HIVE/bin/hive -e "

　　select xxx

“

desc tablename  查看hive表结构

# -*- encoding: utf-8 -*-   这个是指定编码的，否则python里面不能写中文

hive 中concat连接统计的百分之多少时，一定要用双百分号 %%

如select t.dt, round(t.room_night,2), concat(cast(round((t.room_night/l.room_night -1),2) * 100 as int), '%%') ... 这个处理貌似绕弯了，因为直接round得到的事类似129.0这样的double数字，可以直接转int的

不用取小数点后两位再成100 没试，应该是这样

部分内置函数
返回类型 函数 说明
BIGINT round(double a) 四舍五入
DOUBLE round(double a, int d) 小数部分d位之后数字四舍五入，例如round(21.263,2),返回21.26
BIGINT floor(double a) 对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21。
BIGINT ceil(double a), ceiling(double a) 将参数向上舍入为最接近的整数。例如ceil(21.2),返回23.
double rand(), rand(int seed) 返回大于或等于0且小于1的平均分布随机数（依重新计算而变）
double exp(double a) 返回e的n次方

hive 分区：

当用户不加限制条件对一个非常大的分区表进行全表扫描时，这样触发一个巨大的MapReduce Job，会给硬盘带来很大的压力。所以Hive强烈建议使用“strict”，即当用户的查询语句不加where条件时，是禁止对分区表进行查询的。你能改成“nonstrict”模式（默认的模式）取消这种限制。

hive (mydb)>set hive.mapred.mode=strict;

hive (mydb)>select * from employees;

FAILED: SemanticException[Error 10041]: No partition predicate found for Alias "employees"Table "employees"

hive 格式化

Hive的SELECT时，打印列名，以及key value显示
在MySQL中，支持\G，按照key = value的方式显示。 Hive也是支持的，只不过通过参数配置产生。

1、打印列名：
set hive.cli.print.header=true;

2、每行显示一个key、value，即\G类似的方式

set hive.cli.print.header=true;
set hive.cli.print.row.to.vertical=true;
set hive.cli.print.row.to.vertical.num=1;

CASE  WHEN  THEN  ELSE   END：
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

--比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
         WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END

Sqoop(发音：skup)是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql...)间进行数据的传递，可以将一个关系型数据库（例如 ： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。

关于hive的partition的使用详解（转载的，用了其中几个，很好用）
一、背景

1、在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。
2、分区表指的是在创建表时指定的partition的分区空间。
3、如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by，详见表创建的语法结构。

二、技术细节

1、一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。

2、表和列名不区分大小写。

3、分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

4、建表的语法（建分区可参见PARTITIONED BY参数）：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

5、分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
a、单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。
b、双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。

6、添加分区表语法（表已创建，在此基础上添加分区）：
ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。例：
ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';

7、删除分区语法：
ALTER TABLE table_name DROP partition_spec, partition_spec,...
用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例：
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

8、数据加载进分区表中语法：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
例：
LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour='08'); LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');
当数据被加载至表中时，不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录，文件存放在该分区下。

9、基于分区的查询的语句：
SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

10、查看分区语句：

hive> show partitions day_hour_table;
OK
dt=2008-08-08/hour=08
dt=2008-08-08/hour=09
dt=2008-08-09/hour=09

三、总结

1、在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在最字集的目录中。
2、总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。

Group by与having理解

注意：select 后的字段，必须要么包含在group by中，要么包含在having 后的聚合函数里。

1. GROUP BY 是分组查询, 一般 GROUP BY 是和聚合函数配合使用

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面（重要）

例如,有如下数据库表：

A    B
1    abc
1    bcd

1    asdfg

如果有如下查询语句（该语句是错误的，原因见前面的原则）

select A,B from table group by A

该查询语句的意图是想得到如下结果(当然只是一相情愿)

A     B
       abc
1     bcd

asdfg

右边3条如何变成一条,所以需要用到聚合函数，如下(下面是正确的写法):

select A,count(B) as 数量 from table group by A
这样的结果就是
A    数量
1    3

2. Having

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。例如：

SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

有一个需求是要实现把group by中的某些值作为其它统计出来，例如

type  num
1     1
2     2
3     3
4     4
5     5
6     6
7     7

想统计成type 为1,2和其他时候的三中情况，那么想了很久用case when  then else end 可以解决

select  
(case type
    when 1 then 1
    when 2 then 2
    else 0 end
),
count(num)
from data4
group by type;

结果如下：

+----------------------------------------------------+------------+
|                                                  1 |          1 |
|                                                  2 |          1 |
|                                                  0 |          1 |
|                                                  0 |          1 |
|                                                  0 |          1 |
|                                                  0 |          1 |
|                                                  0 |          2 |
+----------------------------------------------------+------------+

select  
(case type
    when 1 then 1
    when 2 then 2
    else 0 end
),
count(num)
from data4
group by (case type
    when 1 then 1
    when 2 then 2
    else 0 end
);

结果如下：

+-------------------------------------------------------+------------+
|                                                     0 |          6 |
|                                                     1 |          1 |
|                                                     2 |          1 |
+-------------------------------------------------------+------------+

hive 的order by

原文地址：http://www.oratea.net/?p=622

hive中的order by也是对一个结果集合进行排序，但是和关系型数据库又所有不同。
这不同的地方也是两者在底层架构区别的体现。

hive的参数hive.mapred.mode是控制hive执行mapred的方式的，有两个选项：strict和nonstrict，默认值是nonstrict。
这个两个值对order by的执行有着很大的影响。

测试用例
hive> select * from test09;
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 0.061 seconds

我们先来看看nonstrict的情况。

hive> set hive.mapred.mode=nonstrict;
hive> select * from test09 order by id;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201105020924_0065, Tracking URL = http://hadoop00:50030/jobdetails.jsp?jobid=job_201105020924_0065
Kill Command = /home/hjl/hadoop/bin/../bin/hadoop job -Dmapred.job.tracker=hadoop00:9001 -kill job_201105020924_0065
2011-05-03 03:37:41,270 Stage-1 map = 0%, reduce = 0%
2011-05-03 03:37:43,292 Stage-1 map = 50%, reduce = 0%
2011-05-03 03:37:45,314 Stage-1 map = 100%, reduce = 0%
2011-05-03 03:37:50,360 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201105020924_0065
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 15.049 seconds

这个时候order by可以正常的执行，hive启动了一个reduce进行处理，事实上也只能启动一个reduce。

在来看看strict的情况
hive> set hive.mapred.mode=strict;
hive> select * from test09 order by id;
FAILED: Error in semantic analysis: line 1:30 In strict mode, limit must be specified if ORDER BY is present id

这个时候提示你，在strict模式下如果执行order by的操作必须要指定limit。
因为执行order by的时候只能启动单个reduce执行，如果排序的结果集过大，那么执行时间会非常漫长。

hive> select * from test09 order by id limit 4;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201105020924_0067, Tracking URL = http://hadoop00:50030/jobdetails.jsp?jobid=job_201105020924_0067
Kill Command = /home/hjl/hadoop/bin/../bin/hadoop job -Dmapred.job.tracker=hadoop00:9001 -kill job_201105020924_0067
2011-05-03 04:18:26,828 Stage-1 map = 0%, reduce = 0%
2011-05-03 04:18:27,842 Stage-1 map = 50%, reduce = 0%
2011-05-03 04:18:29,864 Stage-1 map = 100%, reduce = 0%
2011-05-03 04:18:35,916 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201105020924_0067
OK
100 tom
200 mary
300 kate
400 tim
Time taken: 15.706 seconds

加上limit后，SQL成功执行。

order by  sort by

 

// 根据年份和气温对气象数据进行排序，以确保所有具有相同年份的行最终都在一个reducer分区中
// 一个reduce(海量数据,速度很慢)
select year, temperature
order by year asc, temperature desc
limit ; 

// 多个reduce(海量数据,速度很快)
select year, temperature
distribute by year
sort by year asc, temperature desc
limit ;

order by  (全局排序 )
order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）

只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

在hive.mapred.mode=strict模式下，强制必须添加limit限制，这么做的目的是减少reducer数据规模

例如，当限制limit 100时， 如果map的个数为50， 则reducer的输入规模为100*50

sort by   (类似于桶内排序)
sort by不是全局排序，其在数据进入reducer前完成排序。

因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。

 hive 中的部分用到的内置函数

 6.日期转年函数: year语法:   year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive>   select year('2011-12-08 10:03:01') from dual;
2011
hive>   select year('2012-12-08') fromdual;
2012

7.日期转月函数: month语法: month   (string date)
返回值: int
说明: 返回日期中的月份。
举例：
hive>   select month('2011-12-08 10:03:01') from dual;
12
hive>   select month('2011-08-08') fromdual;
8

8.日期转天函数: day语法: day   (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive>   select day('2011-12-08 10:03:01') from dual;
8
hive>   select day('2011-12-24') fromdual;
24

9.日期转小时函数: hour语法: hour   (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive>   select hour('2011-12-08 10:03:01') from dual;
10

 字符串截取函数：substr,substring

语法: substr(string A, int start, int len),substring(string A, intstart, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

举例：

hive> select substr('abcde',3,2) from lxw_dual;

cd

hive> select substring('abcde',3,2) from lxw_dual;

cd

hive>select substring('abcde',-2,2) from lxw_dual;

de

Hive取非Group by字段数据的方法

遇到这么一个需求，输入数据为一个ID对应多个name，要求输出数据为ID是唯一的，name随便取一个就可以。

执行以下hive ql语句：

SELECT
  sid,
  class_id
FROM
  table2
GROUP BY sid ;

会报错：

FAILED: Error in semantic analysis: Line 1:18 Expression not in GROUP BY key 'class_id'

查了一下，HIVE有这么一个函数collect_set，类似于mysql的group_concat函数，把每个分组的其他字段，按照逗号进行拼接，得到一个最终字符串：

collect_set(col)
返回类型：array
解释：返回一个去重后的对象集合

将上述的QL语句改一下：

select sid,collect_set(class_id) from table2 group by sid;

结果是这样的：

 [11,12,13]
 [11,14]
 [12,15]
 [12,13]
 [16,14]
 [13,15]

postgresQL 相关

sudo su postgrespsql postgres\d   所有表

除了前面已经用到的\password命令（设置密码）和\q命令（退出）以外，控制台还提供一系列其他命令

\h：查看SQL命令的解释，比如\h select。\?：查看psql命令列表。\l：列出所有数据库。\c [database_name]：连接其他数据库。\d：列出当前数据库的所有表格。\d [table_name]：列出某一张表格的结构。\du：列出所有用户。\e：打开文本编辑器。\conninfo：列出当前数据库和连接的信息。

数据库基本操作(貌似和mysql没啥区别)：# 创建新表CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 选择记录SELECT * FROM user_tbl;

# 更新数据UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录DELETE FROM user_tbl WHERE name = '李四' ;

# 添加栏位ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更新结构ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 更名栏位ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除栏位ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表格DROP TABLE IF EXISTS backup_tbl;

$HIVE -e "select xxx;" | $QHSTATS_PSQL -c "delete from xxx; copy xxx from stdin;"

postgres=# copy test1 to STDOUT;wangwu    2015-10-23postgres=# copy test1 from STDIN with delimiter ' ';Enter data to be copied followed by a newline.End with a backslash and a period on a line by itself.>> zhangsan 2015-01-01>> lisi 2015-02-02>> \.postgres=# copy test1 to STDOUT;wangwu    2015-10-23zhangsan    2015-01-01lisi    2015-02-02