lee的个人空间

hive特性：

数据存储在hdfs上，依托hadoop集群实现并行计算

采用hiveQL作为查询语言，与SQL极其相似

Hive中存储的数据无固定格式要求，可随用户自定义

可追加数据，但不支持更改

可扩展性强，支持大规模并行计算

hive安装
请确保 hadoop集群处在运行状态
当前用户环境变量中有HADOOP_HOME，如果不设置，hive没法运行

$cd /opt/soft
$wget  http://apache.etoak.com/hive/hive-0.7.0/hive-0.7.0-bin.tar.gz
$tar -zxvf  hive-0.7.0-bin.tar.gz
$mv hive-0.7.0 hive
$cd hive/conf

$cp hive-default.xml hive-site.xml
$cd ../bin
$./hive   启动hive进入命令行

操作指南
可见官网文档：https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations

Hive提供了很多的函数，可以在命令行下show functions罗列所有的函数
你会发现这些函数名与mysql的很相近，绝大多数相同的，
可通过describe function functionName 查看函数使用方法

hive支持的数据类型很简单就int,string等原子类型， 连日期时间类型也不支持，

但通过to_date unix_timestamp date_diff date_add date_sub等函数就能完成mysql同样的时间日期复杂操作

分区
hive与mysql分区有些区别，mysql分区是用表结构中的字段来分区(range,list,hash等)，而hive不同，他需要手工指定分区列，这个列是独立于表结构，但属于表中一列，在加载数据时手动指定分区

试用：
create table mytest(id int,name string ,regtime string) PARTITIONED by (ds string) row format delimited fields terminated by ‘\t’ stored as textfile ;

创建一个数据文件
1       sawenlee        2011-07-28 22:23:12
2       tonylee 2011-08-03 22:24:21
3       nick    2011-08-02 6:2:43

执行
LOAD DATA LOCAL INPATH ‘../data/test.txt’ OVERWRITE INTO TABLE mytest  PARTITION (ds=’2011-08-02′);

LOAD DATA LOCAL INPATH ‘../data/test.txt’ OVERWRITE INTO TABLE mytest  PARTITION (ds=’2011-08-03′);

再看hive文件结构

如果执行一个查询，hive会将其转换成map reduce在hadoop上执行(select * from mytest除外)

select * from mytest where to_date(regtime) >to_date(’2011-8-1′);

今天无意间发现开发服务器磁盘空间利用近100%，结合df & du查找出了罪魁祸首是mysql的数据文件和日志文件占用过多磁盘如图

ibdata1占用了35G，mysql-bin.****占用近20G；而开发服务器是非主从架构，不存在日志复制，数据同步机制的。

所以第一步可以把二进制文件清理掉

以管理员身份登录mysql

执行RESET MASTER清除所有的二进制日志

另外还可以有针对性的删除
PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO ‘mysql-bin.******’命令，是单独删除日志文件
执行PURGE MASTER LOGS BEFORE ‘yyyy-mm-dd hh:mm:ss’命令，是将在’yyyy-mm-dd hh:mm:ss’时间之前的所有日志进行删除

如果存在主从同步，设置二进制日志文件失效时间可有效防止磁盘空间无止境的被占用

expire_logs_days=3 这样日志文件只保留3天，如果设置0则代表日志文件永不失效

ibdata1数据文件瘦身

在网上查找了相关资料，ibdata1是存放innodb引擎数据和索引的文件，因开发服务器参数设置不当，所有数据库数据及索引都存放在一个文件，而且这个文件有个特点是：不能通过mysql命令缩小，删除或者drop数据（表）同样不会变小，但新增数据可重复利用空间；

如果给ibdata1瘦身就只能先将库结构及数据dump备份成文件，然后drop掉所有的库，并删除掉ibdata1,利用mysql将之前备份的文件重建数据库

如果采用独立表空间则可以避免这个问题,设置方式；此法有风险，请在备用机上测试OK后再尝试

在my.cnf中的innodb块新增一行配置

innodb_file_per_table

重启server后，今后新建的innodb引擎表都会独立一个文件以table_name.ibd文件存在每个库数据文件夹下。

这次完全是奔着活动主题去的——当下火热的nosql,云计算平台的解决方案

活动地点是在中关村某咖啡店，首先是签到领取t-shirt，进入会场演讲者steven早已就位，看到首页PPT投影在屏幕上时就已明白,T-shirt不是白送的，饮料也不是白喝的，演讲者Steven是来推广他家产品的。

废话少说，直奔主题！

couchbase有多款产品： couchbase server  &  cousebase single server & couchbase mobile

couchbase server是这次分享的主角，毕竟是云计算的解决方案定位的主题

couchbase server有以下特点：

1，A distributed key-value NoSQL database——schema-less, auto-sharding, high-performance

2，Horizontally scalable——可动态增删节点（Zero downtime topology change），通过界面操作即可，系统会自动rebalance节点数据

3，Support hadoop integration——要做大量数据统计分析时可通过flume&sqoop将数据从couchbase迁移到hdfs

4，Easy management and monitoring——完善友好的监控系统（可通过后台监测ops、内存、硬盘、网络等指标）

couchbase架构

moxi为分布式代理，通过key查找它的value位置完全由它来决定，相当于memcache客户端的分布式hash算法，couchbase 集成了memcachd充当缓存，它完全可以memcached模式运行，数据就完全存储在内存中，不会持久化到磁盘上。

数据写入流程

rebalance机制——如何能zero downtime topology change，仅仅是在节点扩展时，迁移需要迁移的数据，如下图node1&node2中仅仅迁移了多余的两组数据，所以速度还是非常快的

这仅仅是本次会议总结，今后有机会再测试下它的性能；附上一张现场讨论PP

couchbase官方网站：http://www.couchbase.com/products-and-services/overview

培训文档点这里下载

当数据表的量达到一定级别时，就需要考虑横向切分，这时分区(Partition) 是一种惯用手段

项目中核心表数据量递增量不是线性的，按接受时间字段来分区，并且根据数据量级来动态调整分区天数

比如目前数据不算太大，一个月一分区足矣，但今后数据会骤增，则需要动态调整成10天一分区，最重要的是能通过程序自动扩展分区

以下就是根据这些需求整理的方案

先创建分区表，一月一分区，预先分配3个月的

CREATE TABLE sky_monitor_data(

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘唯一id,自然主键’,

`rcvtime` datetime NOT NULL COMMENT ‘数据接收时间’,

`partnerid` int(11) NOT NULL COMMENT ‘合作伙伴标识’,

`version` tinyint(4) NOT NULL COMMENT ‘结构版本号’,

PRIMARY KEY (id,rcvtime)                                                  #分区字段必须是主键值，所以与id自然主键并入一起充当PK

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (to_days(rcvtime))

(PARTITION p20110401 VALUES LESS THAN (734593) ENGINE = InnoDB,

PARTITION p20110501 VALUES LESS THAN (734623) ENGINE = InnoDB,

PARTITION p20110601 VALUES LESS THAN (734654) ENGINE = InnoDB);   #预先分配3个月的分区

这样分区表就建好了，但仅仅预分配了3个分区，如果当rcvtime到了7月份，则入库就会出问题了，咱们就得考虑能自动的扩展分区，

之所以考虑to_days(rcvtime) ,主要是考虑能根据天数来划分分区，以满足动态调整数据分区大小

create index I_sky_rcvtime on sky_monitor_data(rcvtime);

创建维护分区procedure，设置在crontab中，每月20日分下月分区

delimiter $$

create procedure set_partition()

begin

declare v_partname varchar(64);

declare stopFlag int;

/*————————————以下为增加分区部分————————*/

start TRANSACTION;

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，比如P20110201，方便后面维护 */

select REPLACE(partition_name,’p',”) into @Pmax_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=’monitor’

and table_name=’part_sky_monitor_data’ order by partition_ordinal_position DESC limit 1;

/* 判断需要加的分区时间段,+0 是为了把日期都格式化成YYYYMMDD这样的格式*/

select date(last_day(date_add(now()+0,interval 1 month)))+0 into @Max_date;

select @pmax_name;

select @max_date;

/* 判断已有的max partition的时间是否 小于 想增加的分区时间范围 */

if (@pmax_name < @Max_date) then

SET @s1=concat(‘ALTER TABLE monitor.part_sky_monitor_data ADD PARTITION (PARTITION p’,@Max_date,’ VALUES LESS THAN (TO_DAYS (”’,date(@Max_date),”’)))’);

PREPARE stmt2 FROM @s1;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt2;

commit;

end if;

end

$$

30 10 20 * * /home/mysql/dbadmin/scripts/add_partition.sh > /dev/null 2>&1

more add_partition.sh

#!/bin/bash

mysql monitor -e “call set_partition();”

因协议版本升级，对现有数据库表结构需要做调整

核心表 2亿多数据量，索引+数据大小超80G，需要对该表结构作调整——drop两字段，然后新增5字段
在测试服务器上测试drop 两个字段，花费了30多分钟，并且内存占用率持续99.9%，其中3个cpu core占用80%以上（硬件16核，50G内存)；虽然两列是drop掉了，但这么长的时间难以接受，基于此查看了官方文档中表结构修改处理流程以查找更佳解决办法：

http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table

以下为原文引用：

“ALTER TABLE运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行ALTER TABLE时，其它用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。

注意，如果您在执行ALTER TABLE时使用除了RENAME以外的选项，则MySQL会创建一个临时表。即使数据并不需要进行复制（例如当您更改列的名称时），MySQL也会这么操作。对于MyISAM表，您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值，来加快重新创建索引（该操作是变更过程中速度最慢的一部分）的速度。

如果您使用ALTER TABLE tbl_name RENAME TO new_tbl_name并且没有其它选项，则MySQL只对与table tbl_name相对应的文件进行重命名。不需要创建一个临时表。（您也可以使用RENAME TABLE语句对表进行重命名。”

##############引用结束

这就找到原因了，虽然是该动的一列，但把整个表都拷贝了一次（如果是两列，需要操作两次），80多G的数据及逻辑操作花费这么长时间就能理解了。接着新增5个字段就不用试了，理论上需要复制表5次，时间更没法接受！

最后与dba商量改进方案，经测试只需要做一次表数据复制即可，流程是这样的：
1,create table_new ，并且包含去掉了2个多余字段+新增5字段
2,insert into table_new select * from table 复制所有的数据到新的表结构里
3,drop table ;  rename table_new to table;   删掉就表，并将新表名字改成原表名

这样总共花费也就15分钟左右，因为整个流程只做了一次表复制；当然这过程系统资源占用率同样很高，处理完后即恢复正常！

最近项目线上数据出了故障，停了将近6小时，后来基础数据虽然恢复了，按小时按天做的初步统计数据要重新计算，如果通过一个个sql语句查询恢复的话，server没崩溃人先崩溃了！
找到了一个相对自动化的统计方式，感觉还不错，至少节省了大量人工，我拿其中按天统计用户登录次数的示例脚本说明解决思路

DROP PROCEDURE IF EXISTS `proc_deal_loginTimes`$$

CREATE DEFINER=`USER`@`CLIENT_IP` PROCEDURE

`proc_deal_loginTimes`(v_start_time DATETIME,v_end_time DATETIME)    #定义一个存储过程，带上两个时间参数
BEGIN

WHILE v_start_time < v_end_time DO

INSERT INTO stat_logintimes(logintimesDate,logintimes)
SELECT DATE_FORMAT(v_start_time, ‘%Y-%m-%d’) AS statDate,COUNT(1) AS loginTimes,adpositionid
FROM sky_monitor_data
WHERE adAction = 1 AND  ADDTIME BETWEEN DATE_FORMAT(v_start_time, ‘%Y-%m-%d’) AND DATE_FORMAT(DATE_ADD(v_start_time,INTERVAL 1 DAY), ‘%Y-%m-%d’)
AND adPositionID BETWEEN 1001 AND 2000
GROUP BY adpositionid;

SET v_start_time=DATE_ADD(v_start_time,INTERVAL 1 DAY);
END WHILE;
END$$

进入mysql client命令行，直接调用call proc_deal_loginTimes(’2011-06-01′,’2011-06-11′) 就能恢复6月1号到10号的数据了！

血的教训，今后执行update操作，我猜我的手都要颤抖了
这次将测试环境的一条update语句放线上环境执行了，结果线上2亿多条记录慢慢的慢慢的都成为了废数据。害惨dba同学了，经过彻夜奋战总算恢复了发生问题之前的数据，剩下的我得将故障后的数据重新导入了。

事后总结了数据更新操作经验：

1，测试环境与正是环境要区分开，不然容易混淆
2，更新数据一定要带where条件，更新前按照条件select确认一遍，条件不要用‘<>’,用in,between等替代
3，在事务中执行数据更新操作，发生问题及时回滚！
4，备份是王道！
5，杜绝直接人为操作DB

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。官方文档上说，load data装载一个文件，比普通的insert方式至少要快20倍，相对于批量insert，效率上也有很大的优势

使用方法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name.txt’

[REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY ‘char’]

[ESCAPED BY 'char' ]

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name = expr,...)]

须指定数据文件(暂不支持ucs2编码文件)位置、字段分隔符、行分隔符等。

load data在linux下读取数据文件默认目录是/tmp ，在其他目录下即使设置成了777权限，也可能会报mysql–Can’t get stat of ” (Errcode: 13);所以建议将要导入的数据文件放置在/tmp下

我们通过实践来测试下load的容错性

Step1. 创建一个表test.load_data_test:

字段 类型 空 默认 注释

id int(11) 否

name varchar(30) 否

age int(11) 否

text  int(11) 否 #这个字段因手误弄错了类型，咱就将错就错好了！:)

addtime datetime 否

Step2. 创建数据文件

在tmp目录下创建数据文件load.txt，内容如下:

|Tony|23|23|2011-1-1 22:23:21|

|||||

|Tony hey I’m Tony Yes I’m Tony Tony hey I’m Tony Yes I’m TonyTony hey I’m Tony Yes I’m TonyTony |23|23ii|1/1/2011 12:33:44|

Sawen|21|||

执行LOAD DATA INFILE ‘/tmp/load.txt’ INTO TABLE test.load_data_test character set utf8 FIELDS TERMINATED BY ‘\|’ lines terminated by ‘\n’

如果是Linux shell下请执行：

mysql -h$host -u$user -p$passwd -f -v -e\

“LOAD DATA INFILE ‘$base_path’ INTO TABLE test.load_data_test \

character set utf8 FIELDS TERMINATED BY ‘\|’ lines terminated by ‘\n’”;

-f： 如果执行出错，忽略错误，继续执行

-v:  输出load执行明细

-e:  后接mysql 命令，比如show tables等，这里要执行的是Load Data

执行完后我们查看数据表里的数据：

分析：

第一行无异常数据，insert后都正常，符合要求

第二行只有字段分隔符，数值都为空，可以看出这条记录也被处理了，不过name字段非空，所以用一个空格(截图蓝色)代替值插入了,int、datetime类型都用默认值替换insert

第三行name字段超长，load会自动截断插入， text字段含有非数字字符，load同样截取了字段有效数值部分insert， datetime类型因格式不合法忽略掉，mysql load对于datetime只能自动解析yyyy-mm-dd HH:mi:ss格式，如第一行，其他格式不能自动识别。

第四行空白行： 同第二行

第五行 没有开始的字段分隔符，name值(“sawen”)当作主键值忽略掉，以后每个字段都向前挪一个insert入表。

从以上测试来看，load data infile 方式能处理绝大部分错误，在能保证数据质量，不人为破坏数据的情况下，load data可以非常稳定的工作。

环境： OS : CentOS4  DBServer: Oracle10g  Client: pl/sql Developer + SecureCRT

这里只是我在工作中用到了一些简单的用法，也是最常用到的，在这里总结一下

Oracle自带了几个数据备份和迁移的工具，很强大，也很好用: exp, imp expdp

一般包括一下步骤：

1，登录oracle

Su –oracle

Sqlplus /nolog

Conn /as sysdba

2，创建表空间/用户

CREATE TABLESPACE TS_SHSJB

DATAFILE

‘/u01/app/oracle/oradata/ora10g/TS_SHSJB_01.DBF’ SIZE 100M AUTOEXTEND ON NEXT50M MAXSIZE 2048M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

需要保证 datafile目录存在，并且oracle用户有权限

然后利用exp命令导出: Attention: exp是shell命令不是在sqlplus模式下的工具

语法如下：

导出数据库、

exp的三种导出方式：  用户模式，表模式，整个数据库。整库的不常用；

exp使用方法： exp [option]

option语法 keyword=value，exp help=y 显示该命令的帮助信息

用户模式导出：exp userid=scott/tiger@wdb file=scott.dmp log= scott.log owner=scott

表模式导出：       exp userid=scott/tiger@wdb file=scott.dmp log= scott.log tables=(emp,dept)

整个数据库导出：exp userid=scott/tiger@wdb file=scott.dmp log= scott.log full=y

这里的wdb格式为 ip:port/sid

3，创建用户

–删除用户

drop user SHSJB cascade;

–建用户

CREATE USER SHSJB IDENTIFIED BY SHSJB DEFAULT TABLESPACE TS_SHSJB;

GRANT RESOURCE,CONNECT TO SHSJB;

REVOKE UNLIMITED TABLESPACE FROM SHSJB;

ALTER USER SHSJB QUOTA UNLIMITED ON TS_SHSJB;

4，导入数据

imp与exp语法相似，与exp的三种导出方式相对应也有三种导入方式，意义基本一样。

用户模式导入：imp userid=scott/tiger@wdb file=scott.dmp log=scott-imp.log fromuser=scott touser=scott

表模式导入 ：imp userid=scott/tiger@wdb file=scott.dmp log=scott-imp.log fromuser=scott touser=scott tables=(emp,dept)

完全导入：imp userid=scott/tiger@wdb file=scott.dmp log=scott-imp.log full=y

5，Expdp工具使用

两种方式： 按照用户 或者表两种模式导出

Expdp使用中dumpfile 和logfile都不能带路径，像/home/oracle/user.log，这点需要注意，否则会报如下错误：

ORA-39088: file name cannot contain a path specification

可以直接写名字即可logfile,之后他会自动保存在$ORACLE_HOME目录下，如果一定要保存到指定的目录，需要实现做以下操作：、

Su – oracle

Sqlplus / as sysdba

create directory dump_dir as ‘/home/oracle/dump/’;

grant read on directory sys.dump_dir to username;

grant write on directory sys.dump_dir to username;

expdp userid=user/passwd dumpfile=dump_dir:20090716.dmp logfile=dump_dir:20090716-expdp.log schemas=导出的用户

这样就OK了

表模式导出

expdp userid=user/passwd dumpfile=dump_dir:20090716.dmp logfile=dump_dir:20090716-expdp.log tables=user_info,user_order

这样就能导出指定的两张表了。

如果要导出表中某些条件的数据则加上query=user_info:’”where phonenumber is null”’,user_order:’”cellphonenumber is null”’

条件是先单引号，里面是双引号！