高级数据库之MySQL导入海量数据总结

使用MySQL来存储海量数据

1.背景：

将10个G的数据导入MySQL中，数据来自于北京出租车公司的GPS数据，字段描述：

数据以ASCII文本表示，以逗号为分隔符，以回车换行符(0x0D 0x0A)结尾。数据项及顺序：车辆标识、触发事件、运营状态、GPS时间、GPS经度、GPS纬度,、GPS速度、GPS方向、GPS状态
车辆标识：6个字符
触发事件：0=变空车，1=变载客，2=设防，3=撤防，4=其它
运营状态：0=空车，1=载客，2=驻车，3=停运，4=其它
GPS时间：格式yyyymmddhhnnss，北京时间
GPS经度：格式ddd.ddddddd，以度为单位。
GPS纬度：格式dd.ddddddd，以度为单位。
GPS速度：格式ddd，取值000-255内整数，以公里/小时为单位。
GPS方位：格式ddd，取值000-360内整数，以度为单位。
GPS状态：0=无效，1=有效
结束串:回车符+换行符

任务一：将10G的数据导入到MySQL中，并进行一些数据分析。

• 导入至少选择两种方式：

  load data 命令
  GUI操作方式
  程序插入方式基于

• 对比不同插入方式的异同和效率

• 对出租车轨迹数据分析

任务二：基于Hbase/Phoenix的数据分析

自行安装Hbase/Phoenix环境
•http://phoenix.apache.org/
•https://hbase.apache.org/
实现数据从MySQL数据库导入Hbase数据库
对比SQL语句与Hbase查询语句的区别
使用Hbase查询统计记录数和出租车数
对比使用Phoenix组件和原生Hbase查询的时间性能
基于Phoenix组件实现对Hbase数据库的查询分析
尝试编程实现对Hbase数据库的访问

2.任务一的完成

数据库的创建

根据数据段的描述创建一张数据表：

CREATE DATABASE `taxi` DEFAULT CHARACTER SET utf8;  
 
create table taxi_infor(  
infor_id bigint unsigned not null auto_increment comment '信息的id' primary key,  
taxi_id char(6) not null comment '车辆标示',  
taxi_event tinyint not null DEFAULT '0' comment '0=变空车，1=变载客，2=设防，3=撤防，4=其他',  
taxi_status tinyint not null DEFAULT '0' comment '0=空车，1=载客，2=驻车，3=停运，4=其他',  
taxi_gps_time datetime not null DEFAULT current_timestamp comment 'Gps时间' ,  
taxi_longitude double comment 'Gps经度信息',  
taxi_dimension double comment 'Gps维度信息',  
taxi_speed int not null DEFAULT 0 comment 'Gps速度',  
taxi_orient int not null DEFAULT 0 comment 'Gps方位',  
taxi_gps_status tinyint not null DEFAULT 0 comment 'Gps状态'  
)engine=InnoDB;

首先使用python将数据进行导入：
因为数据有10个G大，无法将数据文件一次全部读入，考虑将文件分块读取，使用MySQLdb模块将数据插入到数据库中。
关键代码：

  f = pd.read_csv(path,iterator=True,header=None,infer_datetime_format=True,parse_dates=[3])  # infer_datetime_format和parse_dates来直接将格式转化为日期格式  
      print isinstance(f,Iterable)  
      print f.get_chunk(2)  
      try:  
          while True:  
              temp = f.get_chunk(100000)  
              data2mysql(temp)  
    
      except Exception, e:  
         print 'stop!'  

sql_insert = "INSERT INTO taxi_infor(taxi_id,taxi_event,taxi_status,taxi_gps_time,taxi_longitude,taxi_dimension,taxi_speed,taxi_orient,taxi_gps_status) " \  
                      "VALUES ('%s','%d','%d','%s','%f','%f','%d','%d','%d')" % (str(u[0]),u[1],u[2],str(u[3]),u[4],u[5],u[6],u[7],u[8])  
         print "check_insert_available:"+ sql_insert  
         cursor.execute(sql_insert)  #执行SQL语句  

当引擎是InnoDB时使用select count(*) from taxi_infor;效率很低，消耗很长时间。在MySQL中创建表和数据库时默认是使用的InnoDB存储引擎，InnoDB具有较好的事务支持。
使用load data命令进行数据的导入：
这次将表命名为了taxi_infor_test，考虑到对于本表后期涉及的选择操作比较多，存储引擎选择使用的是MyISAM。
命令：

load data local infile "F:/workFile/2012.csv" into table taxi_infor_test fields terminated by ',' (taxi_id,taxi_event,taxi_status,taxi_gps_time,taxi_longitude,taxi_dimension,taxi_speed,taxi_orient,taxi_gps_status);  

插入的结果：

效率提升了不是一点点，仅仅用了24分钟就将所有数据都导入了数据库。
对比：不同方法导入异同的分析
不同点：
使用python导入数据时对数据的可操作性比较强，可以对插入的数据进行一些中间操作，格式的转换等等。但是效率比较低。
使用load data命令时效率很高，但是无法对数据进行中间操作，只能将数据安装原来的大小和格式进行导入。
相同点：
都可以将数据完整的插入数据库。

轨迹数据的分析

① 统计记录的总数，统计出租车数量
记录的总数：

1.  select count(*) from taxi_infor_test;  

结果：

当存储引擎是InnoDB时执行此命令要两分分钟，因为没有存储表的总数信息，要对表进行一次全表的扫描。当使用MyISAM时效率较高。
统计出租车总数：

结果：

可以看到，出租车的数量是12522辆，但是第一次查询所花费的时间为28.3秒，第二次仅仅用了0.38秒，数据库像是保存了上次的查询结果。

② 编写SQL语句进行条件查询。
查询某时刻有多少量车是空车：

1.  select count(distinct taxi_id) as taxi_num from taxi_infor_test where taxi_status=0 and taxi_gps_time like "2012-11-07 00:15%";  

查询某时段有多少量车是载客：

1.  select count(distinct taxi_id) as taxi_num from taxi_infor_test where taxi_status=1 and taxi_gps_time between "2012-11-07 00:15:00" and "2012-11-07 00:16:00";  

查询有多少辆车载客路过了某个地区：

1.  select count(distinct taxi_id) from taxi_infor_test where  taxi_status=1  and taxi_longitude like "116.4508%" and taxi_dimension like "39.9594%";  

③ 编程实现数据库连接，获取数据和简单数据分析
使用Python在程序中导入MySQLdb库，就可以建立数据库交互对象，建立数据库连接对象，就可以进行数据库的连接和操作。
关键步骤：

  conn = MySQLdb.Connect(host='127.0.0.1',  
                             port=3306,  
                             user='root',  
                             passwd='',  
                             db='taxi',  
                            charset='utf8')  # 建立一个数据库连接对象  
     cursor = conn.cursor()  # 建立一个数据库交互对象cursor  
     queryInforCount = "select count(*) from taxi_infor_test"  
     queryTaxiCount = "select count(distinct taxi_id) as taxi_num from taxi_infor_test"  
     cursor.execute(queryInforCount)  
     print cursor.fetchone()  
     cursor.execute(queryTaxiCount)  
     print cursor.fetchone()  
     cursor.close()  
     conn.close()