LittleQ

最近在给PM做报表时，要统计登陆订单的下单比例，也就是说哪些订单是登陆用户的订单，那些没有用户名，即用户名为null的用户不需要统计到最后的登陆订单中。
一开始我想这直接在count函数内写一个条件判断:

1
2

select count(case when user_name is not null then 1 else null end) as login_order
from table_name

但是后来我在查询Hive里count函数的用法时，发现了网上有人说，其实count在统计行数的时候，会根据使用的用法进行不同的逻辑运算，具体看Hive参考文档：

1
2
3

count(*) - Returns the total number of retrieved rows, including rows containing NULL values;
count(expr) - Returns the number of rows for which the supplied expression is non-NULL;
count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL.

上面的意思简单明了，除了count(*)之外，其他的计算的是非空值的条数，并且加上distinct还会合并重复记录到一类里面。
看到这里，我才发现，上面的写法有些多余，可以直接就count(user_name)，会自动略过那些user_name为空的记录。但是后面我还是采用了这个多余的写法，为什么？其实还是出于可读性和可维护性，这样写虽然复杂，但是后来维护的人起码知道你没有统计null用户。

Hive count高级用法

count不是简单只有count(*)用法，下面还有一些更为高级的用法，加上条件语句:

1
2
3
4
5
6
7
8
9
10
11

select type,
	count(*),
	count(distinct u),
	count(case when plat=1 then u else null end),
	count(distinct case when plat=1 then u else null end),
	count(case when (type=2 or type=6) then u else null end),
	count(distinct case when (type=2 or type=6) then u else null end)
from t
where dt in ('2015-11-01', '2015-11-01)
group by type
order by type;

执行脚本报错:

Syntax error: “(“ unexpected

与实际使用的Shell版本有关，判断方法可以使用：

1
2
3
4
5
6
7
8

⚡ ⇒ ls -l /bin/*sh
-rwxr-xr-x 1 root root 1021112 10月  8  2014 /bin/bash
-rwxr-xr-x 1 root root  121272  2月 19  2014 /bin/dash
lrwxrwxrwx 1 root root       4 10月  8  2014 /bin/rbash -> bash
lrwxrwxrwx 1 root root      22 10月 24 18:08 /bin/rzsh -> /etc/alternatives/rzsh
lrwxrwxrwx 1 root root       4 10月 24 14:21 /bin/sh -> dash
lrwxrwxrwx 1 root root       7 10月 24 14:21 /bin/static-sh -> busybox
lrwxrwxrwx 1 root root      21 10月 24 18:08 /bin/zsh -> /etc/alternatives/zsh

可以看到，sh果然被重定向到dash。因此，如果执行./scirpt.sh，使用的是dash。
避免报错方法很多，可以手动指定用：

1

bash script.sh

或者，在脚本第一行制定用什么Shell来执行：

1

#! /bin/bash

替换[substitute]

1

:[range]s/pattern/string/[c,e,g,i]

参数

注意:g一般都要加上,否则只会替换每一行的第一个符合pattern的字符串.当然后面的四个参数可以一起用,不用逗号隔开,例如cgi表示:每次都询问,整行替换,不分大小写.

用法举例

替换就这几种开关,用法千变万化,以下命令都是在Vim里操作的.

在操作数据库的时候,往往是保留想要修改的库,先建一个一样的来操作,例如我想给一个库加个字段,然后从另一个很复杂的sql语句导入数据,看字段添加是否正确,最好的做法就是自己建一张临时表,把查询结果插到临时表,这样一旦出错,也不会影响到正常数据.下面是一些涉及到表复制和修改的常用命令.

MySql表复制

这个分为表是存在和不存在两种,具体使用不同的语句.

新表不存在

• 复制表结构即数据到新表

1
2

create table new_table
select * from old_talbe;

这种方法会将old_table中所有的内容都拷贝过来,用这种方法需要注意,new_table中没有了old_table中的primary key,Extra,auto_increment等属性,需要自己手动加,具体参看后面的修改表即字段属性.

• 只复制表结构到新表

1
2
3
4
5
6

# 第一种方法,和上面类似,只是数据记录为空,即给一个false条件
create table new_table
select * from old_table where 1=2;

# 第二种方法
create table new_table like old_table;

新表存在

• 复制旧表数据到新表(假设两个表结构一样)

1
2

insert into new_table
select * from old_table;

• 复制旧表数据到新表(假设两个表结构不一样)

1
2

insert into new_table(field1,field2,.....)
select field1,field2,field3 from old_table;

• 复制全部数据

1

select * into new_table from old_table;

• 只复制表结构到新表

1

select * into new_talble from old_table where 1=2;

MySql修改命令

• 增加字段

1

alter table table_name add column column_type [other];

例如:

1

alter table user_info add mobile_phone varchar(30) default '0';

• 增加索引

1

alter table table_name add index index_name (field1[，field2 …]);

用法样例:

1

alter table user_info add index idx_user_no(user_no);

• 加主关键索引

1

alter table table_name add primary key(field);

用法举例:

1

alter table table_name add primary key(id,user_no);

• 加唯一限制条件索引

1

alter table table_name add unique idx_name(field);

用法举例:

1

alter table user_info add unique idx_user_name(user_name);

• 删除索引

1

alter table table_name drop index idx_name;

• 修改字段名称或类型

1

alter table table_name change old_field_name new_field_name field_type;

• 删除字段

1

alter table table_name drop field_name;

来一个简单的例子，看Python如何操作数据库，相比Java的JDBC来说，确实非常简单，省去了很多复杂的重复工作，只关心数据的获取与操作。

准备工作

需要有相应的环境和模块：

Ubuntu 14.04 64bit
Python 2.7.6
MySQLdb

**注意:**Ubuntu 自带安装了Python，但是要使用Python连接数据库，还需要安装MySQLdb模块，安装方法也很简单：

1

sudo apt-get install MySQLdb

然后进入Python环境，import这个包，如果没有报错，则安装成功了：

1
2
3
4
5
6

~|⇒ python
Python 2.7.6 (default, Jun 22 2015, 17:58:13) 
[GCC 4.8.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> 

Python标准的数据库接口的Python DB-API（包括Python操作MySQL）。大多数Python数据库接口坚持这个标准。不同的数据库也就需要不同额模块，由于我本机装的是MySQL，所以使用了MySQLdb模块，对不同的数据库而言，只需要更改底层实现了接口的模块，代码不需要改，这就是模块的作用。

Python数据库操作

首先我们需要一个测试表

建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE DATABASE study;
use study;
DROP TABLE IF EXISTS python_demo;
CREATE TABLE python_demo (
  id int NOT NULL AUTO_INCREMENT COMMENT '主键，自增',
  user_no int NOT NULL COMMENT '用户编号',
  user_name VARBINARY(50) NOT NULL COMMENT '用户名',
  password VARBINARY(50) NOT NULL COMMENT '用户密码',
  remark VARBINARY(255) NOT NULL COMMENT '用户备注',
  PRIMARY KEY (id,user_no)
)ENGINE =innodb DEFAULT CHARSET = utf8 COMMENT '用户测试表';

INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1001,'张三01','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1002,'张三02','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1003,'张三03','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1004,'张三04','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1005,'张三05','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1006,'张三06','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1007,'张三07','admin','我是张三');
INSERT INTO python_demo(user_no, user_name, password, remark) VALUES
  (1008,'张三08','admin','我是张三');

Python代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# --coding=utf8--
import ConfigParser

import sys
import MySQLdb

def init_db():
    try:
        conn = MySQLdb.connect(host=conf.get('Database', 'host'),
                               user=conf.get('Database', 'user'),
                               passwd=conf.get('Database', 'passwd'),
                               db=conf.get('Database', 'db'),
                               charset='utf8')
        return conn
    except:
        print "Error:数据库连接错误"
        return None

def select_demo(conn, sql):
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        return cursor.fetchall()
    except:
        print "Error:数据库连接错误"
        return None

def update_demo():
    pass

def delete_demo():
    pass

def insert_demo():
    pass

if __name__ == '__main__':
    conf = ConfigParser.ConfigParser()
    conf.read('mysql.conf')
    conn = init_db()
    sql = "select * from %s" % conf.get('Database', 'table')
    data = select_demo(conn, sql)
    pass

用PyCharm 4.5.4写python代码的时候,改了文件夹的名字,再运行脚本,始终运行不起来,一直报错:

1

Error running xx: Cannot start process, the working directory /home/xx does not exist

看一下项目的配置,具体是工具栏:Run-->Edit Configurations

看了一下Working directory:以及Script:的内容果然不对,改为对应的脚本以及脚本目录就好了.

最近碰到个坑,在写shell脚本查询Hive数据的时候,有一个脚本在Shell里怎么都执行不出结果,但是拷贝到Hive界面里却能正常执行出结果,因为Hive表是按日期分区的,有两种格式:
一种是%Y%m%d,另一种是%Y-%m-%d.dw_source的表是第一种格式,而dw_transform层的表是第二种格式:

错误样例

1
2
3
4
5
6
7

# sql用单引号 执行成功,可以返回正常结果
sql1='select uid,first_ip,first_date from dw_source.user_ext where dt='20151104';'
sudo -uhive_user hive -e "$sql1" > source.txt

# sql用单引号 执行成功,但是没有返回值
sql2='select uid,first_ip,first_date from dw_transform.user_ext where dt='2015-11-04';'
sudo -uhive_user hive -e "$sql2" > transform.txt

我们可以用echo命令来看看具体执行的命令:

1
2
3
4
5

sql1='select uid,first_ip,first_date from dw_source.user_ext where dt='20151104';'
sql2='select uid,first_ip,first_date from dw_transform.user_ext where dt='2015-11-04';'

echo sudo -uhive_user hive -e "$sql1"
echo sudo -uhive_user hive -e "$sql2"

看看返回结果:

1
2

sudo -uhive_user hive -e select uid,first_ip,first_date from dw_source.user_ext where dt=20151104;
sudo -uhive_user hive -e select uid,first_ip,first_date from dw_transform.user_ext where dt=2015-11-04;

看到这里发现了一个问题,本来在日期上应该有的单引号消失了,因为第一种时间格式没有分隔符,可以被正常的识别,但是第二种就比较悲催了,应该是被-分隔了,导致日期不对,解决办法就是最外面用双引号,即改成如下内容:

1
2

sql1="select uid,first_ip,first_date from dw_source.user_ext where dt='20151104';"
sql2="select uid,first_ip,first_date from dw_transform.user_ext where dt='2015-11-04';"

反引号 双引号 单引号用法

这几个符号长得比较像，但是功能还是有很大区别的。

反引号(`)

起着命令替换的作用。命令替换是指shell能够将一个命令的标准输出插在一个命令行中任何位置。,举个简单的例子:

1
2

➜  ~  echo The date is `date`
The date is 2015年 11月 06日 星期五 16:27:46 CST

双引号(“)

双引号是字符串的界定符,而不是字符的界定符,取消除[`,$,”,]以外,其他的都变成字符串的内容了.双引号是弱引用，引号里的值若再包含变量，那在赋值的时候，所有这些变量就被立即替换了。
用双引号的时候:

1
2
3
4
5
6

$加变量名可以取变量的值
反引号仍表示命令替换
\$表示$的字面值
\`表示`的字面值
\"表示"的字面值
\\表示\的字面值

看个简单的例子:

1
2
3
4
5
6

➜  ~  name=World
➜  ~  echo $name
World
➜  ~  sayHello="Hello $name"
➜  ~  echo $sayHello
Hello World

单引号(‘)

单引号告诉shell忽略所有特殊字符,保持引号内所有字符的字面值，即使引号内的\和回车也不例外,单引号是强引用,但是字符串中不能出现单引号。之前的问题就是犯了这个错,两个单引号嵌套了.

1
2
3
4
5
6
7

➜  ~  echo "`date`"
2015年 11月 06日 星期五 16:42:29 CST
➜  ~  echo '`date`'
`date`
➜  ~  echo "'`date`'"
'2015年 11月 06日 星期五 16:42:29 CST'
➜  ~  

**注意:**如果'出现在"里面，'就退化为一个单纯的字符了，而不再有强引用一说。换句话说，只有最外层的'才具有强引用效果。

应用

这个对我们有啥影响呢？基本在我们使用Shell的时候都会碰到这个坑，尤其是正则表达式，有时候匹配数字用\d就可以了，但是像Hive这种用Java写的，内部会有转义，所以需要传入两个\\的时候，就容易踩坑，以Hive中的正则为例,看下面:

1
2

reg_str1='.*id=(\\d).*'
reg_str2=".*id=(\\\\d).*"

这两种写法都是正确的写法，效果是一样的，其他的写法都是不对的

Vim虽然很好用,但是平时有时候还不够好,需要自己配置一下,比如说,每次粘贴到vim里一大片内容,可能就会被弄乱,常用的vim配置设置都在~/.vimrc里.

基本配置

基本配置就是平时的一些简单的配置,会经常用到的,下面都是我从平时工作中需要用到的配置中慢慢总结出来的,下面讲的如果没有特殊说明,都是默认配置在~/.vimrc文件中.

设置table的宽度

写shell的时候,经常会在if的下一行给我空出两个制表符,每个制表符都是8个字符宽度,看着很别扭,ts是一个制表符显示宽度,sw是制表符实际占宽度.

1
2

set ts=4
set sw=4

设置粘贴不自动缩进

粘贴之前在vim编辑器里先:

1

:set paste

然后再insert,把内容粘贴到vim里即可,粘贴完还需要关闭:

1

:set nopaste

相信你也觉得好麻烦,其实这个有个开关模式,只要选择打开开关,关闭开关就能达到我们要的效果,所以利用键盘映射到这个开关,在配置文件里加上:
前两行是为了提示的,这样你在粘贴的时候就可以区分paste模式和nopaste模式.

1
2
3

nnoremap <F2> :set invpaste paste?<CR>
imap <F2> <C-O>:set invpaste paste?<CR>
set pastetoggle=<F2>

更改Esc映射

Vim里使用Esc的频率很高,但是一般的键盘设计使得按Esc很不方便,而且在Vim里,Caps Lock就是多余的,为什么这么说?因为有快捷键可以直接将小写转大写,单个的大写就使用Shift即可.
在终端里面执行下面命令:

1

xmodmap -e 'clear Lock' -e 'keycode 0x42 = Escape'

这样你的建就被映射了,但是每次你重启之后,还得重新做映射,而且我们只想这个功能在Vim里是这样的,当我们退出Vim,键盘的按键恢复正常,可以在.vimrc文件里加上下面的内容:

1
2
3

" 当进入vim的时候将Esc映射成大写键,退出的时候将大写键映射回来
au VimEnter * !xmodmap -e 'clear Lock' -e 'keycode 0x42 = Escape'
au VimLeave * !xmodmap -e 'clear Lock' -e 'keycode 0x42 = Caps_Lock'

为了使用更加友好,还可以在.vimrc文件里加上下面这句:
在insert模式下,按<Ctrl+u>将光标所在单词变大写,记得要重启vim才会生效.

1

inoremap <C-u> <esc>gUiwea

这个需要安装xorg-xmodmap安装包.

数据库中存放的json串,有时候需要对某个元素判断来查询结果,非常笨的一个方法就是直接把查询结果的字符串做模糊查询,即like '%str%',当一个json串非常长的时候,本来效率就很慢,况且是在hadoop海量数据里查找,其实我们并不需要那些其他的字符串,只是需要某个元素的值而已,这个时候就需要用到Hive的字符函数get_json_object()函数.

get_json_object()函数

函数用法:

1

get_json_object(string json_string, string path)

具体看一个例子,数据库test定义如下:

1
2

id                  	int                 	自增id
content                	string              	内容	

其中content是个json串,内容如下:

1
2
3
4
5
6
7

{
  "status": {
     "person": {
        "name": false
    }
  }
}

查询一下看看结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

select get_json_object(content,'$.status') from test limit 1;
OK
{"person":{"name":false}}
Time taken: 0.066 seconds, Fetched: 1 row(s)

select get_json_object(content,'$.status.person') from test limit 1;
OK
{"name":false}
Time taken: 0.081 seconds, Fetched: 1 row(s)

select get_json_object(content,'$.status.person.name') from test limit 1;
OK
false
Time taken: 0.077 seconds, Fetched: 1 row(s)

**注意:**如果要判断name的值,这个值并不是布尔型,而是一个string,所以需要加上'',像下面这样:

1
2
3
4
5

select id,content from test where get_json_object(content,'$.status.person.name')='false' limit 2;
OK
7	{.status":{"person":{"name":false}}}
31	{.status":{"person":{"name":false}}}
Time taken: 0.085 seco	nds, Fetched: 2 row(s)

Hive自身提供了逻辑运算以及数学上的一些函数,基本和mysql
里差不多.简单的不再多说了，只记录一些常用的

函数

Hive自带的函数非常多,大致分为一下几块介绍,详细信息在Hive Wiki官网

数学函数

日期函数

NOTE:所以想把一个20160302转换成2016-03-02这种时间格式，要么用**strsub()配合concat()**来实现，或者就使用from_unixtime(unix_timestamp(create_time, 'yyyyMMdd'), 'yyyy-MM-dd)

字符函数

内置聚合函数

还有一些是内置的统计函数,可以免去不少计算的步骤