专栏首页光城(guangcity)1.8亿条海量Txt数据存储MySQL实践

1.8亿条海量Txt数据存储MySQL实践

0.导语

最近出去旅游了,嗨皮了嗨皮,明天上班,开始做作业,今日将1.8亿数据存储的方式进行总结,欢迎大家拍砖!

预告:后面推送大数据伪分布式从零搭建到1.8亿海量数据从Mysql至HBase数据转存技术分析与应用!

1.搭建MySQL数据库

电脑环境为Ubuntu16.04系统。

1.1 MySQL安装

sudo apt-get install mysql-server

出现依赖问题:

sudo apt-get install -f

最后再次运行上述命令即可。

1.2 配置目录

安装成功后,相关配置文件如下:

  • 数据库目录:/var/lib/mysql/
  • 配置文件:/usr/share/mysql(命令及配置文件) ,/etc/mysql(如:my.cnf)
  • 相关命令:/usr/bin(mysqladmin mysqldump等命令) 和/usr/sbin
  • 启动脚本:/etc/init.d/mysql(启动脚本文件mysql的目录)

可以通过进入上述相关目录查看,例如:查看数据库:

查看配置文件:

1.3 服务管理

启动MySQL:sudo service mysql start

进入MySQL:mysql -uroot -p

#启动
sudo service mysql start
#停止
sudo service mysql stop
#服务状态
sudo service mysql status

2.导入海量GPS数据

选择导入方式为:load data命令行方式与程序插入方式

导入数据之前,根据字段描述编写SQL语句进行创建数据库与表操作。

字段描述:

数据以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=有效

结束串:回车符+换行符

  • 创建数据库
create database loaddb;
  • 创建数据库表

创建load data方式表

CREATE TABLE loadTable(id int primary key not null auto_increment,
carflag VARCHAR(6),touchevent CHAR(1),opstatus CHAR(1),gpstime DATETIME,
gpslongitude DECIMAL(10,7),gpslatitude DECIMAL(9,7),gpsspeed TINYINT,
gpsorientation SMALLINT,gpsstatus CHAR(1));

创建程序插入法表

CREATE TABLE loadTable1(id int primary key not null auto_increment,
carflag VARCHAR(6),touchevent CHAR(1),opstatus CHAR(1),gpstime DATETIME,
gpslongitude DECIMAL(10,7),gpslatitude DECIMAL(9,7),gpsspeed TINYINT,
gpsorientation SMALLINT,gpsstatus CHAR(1));

2.1 load data命令行方式

导入数据

load data local infile "/home/light/mysql/gps1.txt" into table loadTable fields terminated by ',' lines terminated by "\n"  (carflag, touchevent, opstatus,gpstime,gpslongitude,gpslatitude,gpsspeed,gpsorientation,gpsstatus);

完成耗时:

2.2 程序插入法

这里使用Python操纵MySQL数据库,进行SQL的插入。

基本思路是使用Python的pymysql对MySQL进行连接,使用executemany进行批量提交,每隔7万提交1次。

Python代码:

import pymysql
import time

class MyPyMysql:
    def __init__(self, host, port, username, password, db, charset='utf8'):
        self.host = host  # mysql主机地址
        self.port = port  # mysql端口
        self.username = username  # mysql远程连接用户名
        self.password = password  # mysql远程连接密码
        self.db = db  # mysql使用的数据库名
        self.charset = charset  # mysql使用的字符编码,默认为utf8
        self.pymysql_connect()  # __init__初始化之后,执行的函数

    def pymysql_connect(self):
        # pymysql连接mysql数据库
        # 需要的参数host,port,user,password,db,charset
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.username,
                                    password=self.password,
                                    db=self.db,
                                    charset=self.charset
                                    )
        # 连接mysql后执行的函数
        self.run()

    def run(self):
        # 创建游标
        self.cur = self.conn.cursor()

        # 定义sql语句,插入数据id,name,gender,email
        sql = "insert into loadTable1(carflag, touchevent, opstatus,gpstime,gpslongitude,gpslatitude,gpsspeed,gpsorientation,gpsstatus) values(%s,%s,%s,str_to_date(%s,'%%Y-%%m-%%d %%H:%%i:%%s'),%s,%s,%s,%s,%s)"

        # 定义总插入行数为一个空列表
        data_list = []
        count = 0
        with open('/home/light/mysql/gps1.txt', 'r') as fp:
            for line in fp:
                line = line.split(',')
                # print(line)
                carflag, touchevent, opstatus, gpstime, gpslongitude, gpslatitude, gpsspeed, gpsorientation, gpsstatu = line[0],line[1],line[2],line[3],line[4],line[5],line[6],line[7],line[8].strip()

                gpstime = gpstime[:4]+'-'+gpstime[4:6]+'-'+gpstime[6:8]+' '+gpstime[8:10]+':'+gpstime[10:12]+':'+gpstime[12:14]
                tup = (carflag, touchevent, opstatus, gpstime, gpslongitude, gpslatitude, gpsspeed, gpsorientation, gpsstatu)
                data_list.append(tup)
                count += 1
                if count and count%70000==0:
                    # 执行多行插入,executemany(sql语句,数据(需一个元组类型))
                    self.cur.executemany(sql, data_list)
                    # 提交数据,必须提交,不然数据不会保存
                    self.conn.commit()
                    data_list = []
                    print("提交了:" + str(count) + "条数据")

        if data_list:
            # 执行多行插入,executemany(sql语句,数据(需一个元组类型))
            self.cur.executemany(sql, data_list)
            # 提交数据,必须提交,不然数据不会保存
            self.conn.commit()
            print("提交了:" + str(count) + "条数据")
        self.cur.close()  # 关闭游标
        self.conn.close()  # 关闭pymysql连接

if __name__ == '__main__':
    start_time = time.time()  # 计算程序开始时间
    st = MyPyMysql('127.0.0.1', 3306, 'root', 'xxxx', 'loaddb')  # 实例化类,传入必要参数
    print('程序耗时{:.2f}'.format(time.time() - start_time))  # 计算程序总耗时

完成耗时:

3.分析两种插入方式

两者异同比较

相同点

不同点

两者都是通过读取本地txt文件,按照相同的分隔来读取进行插入。

程序插入法实质为insert语句间接执行。load data设计用于在单个操作中大量加载表格数据。

两者效率比较

两者耗时如下:

第一种:load data

用时1h11分。

第二种:程序插入法

用时:27322.45/36=7.58h

上述对比可知,load data效率非常高,原因在于使用的是load data infile方式,而第二种则为传统的insert方式。

究其根源主要是MySQL内部对于load 和 insert的处理机制不同。

Load的处理机制是:在执行load之前,会关掉索引,当load全部执行完成后,再重新创建索引.

Insert的处理机制是:每插入一条则更新一次数据库,更新一次索引.

另外,load与insert的不同还体现在load省去了sql语句解析,sql引擎处理,而是直接生成文件数据块,所以会比Insert快很多.

4.出租车轨迹数据的分析

4.1 统计总记录数、统计出租车数量

统计:

select count(*) AS '总记录数', count(distinct carflag) AS '出租车数量' from loadTable;

4.2 编写3个SQL语句

  • 查询前200万数据中各个触发事件的总数并降序排序。
select touchevent,count(id) as number from (select * from loadTable as lt  limit 2000000) as t group by touchevent order by number desc;
  • 查询前200万数据中GPS速度不小于30时公里/小时中各个运营状态的总数并按照升序排序。
select t.opstatus,count(t.id) as number from (select * from loadTable as lt  limit 2000000) as t where t.gpsspeed>=30  group by t.opstatus order by number;
  • 查询前200万数据GPS时间2012-11-01 00:35触发事件为变其他且运营状态为载客状态下的总信息数。
select count(*) from (select * from loadTable as lt  limit 2000000) as t where t.touchevent=4 and t.opstatus=1 and t.gpstime like '2012-11-01 00:35:%';

4.3 数据库连接

这里使用Python完成本题。

连接数据库

class analysisData:
    def __init__(self, host, port, username, password, db, charset='utf8'):
        self.host = host  # mysql主机地址
        self.port = port  # mysql端口
        self.username = username  # mysql远程连接用户名
        self.password = password  # mysql远程连接密码
        self.db = db  # mysql使用的数据库名
        self.charset = charset  # mysql使用的字符编码,默认为utf8
        self.pymysql_connect()  # __init__初始化之后,执行的函数

    def pymysql_connect(self):
        # pymysql连接mysql数据库
        # 需要的参数host,port,user,password,db,charset
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.username,
                                    password=self.password,
                                    db=self.db,
                                    charset=self.charset
                                    )
        # 连接mysql后执行的函数
        self.run()

获取数据

def run(self):
    # 创建游标
    self.cur = self.conn.cursor()

    touchsql = "select touchevent,count(id) as number from loadTable group by touchevent;"
    self.cur.execute(touchsql)

    # 查询数据库多条数据
    result = self.cur.fetchall()
    event = list(zip(*list(result)))[1]
    print(result)

    opssql = "select opstatus,count(id) as number from loadTable group by opstatus;"

    self.cur.execute(opssql)

    # 查询数据库多条数据
    result = self.cur.fetchall()
    status = list(zip(*list(result)))[1]
    print(result)
    for data in result:
        print(data)

    self.visualCategory(event,status)

    self.cur.close()  # 关闭游标
    self.conn.close()  # 关闭pymysql连接

数据分析可视化

def visualCategory(self,event,status):
    bar = (
        Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis([0, 1, 2, 3, 4])
            .add_yaxis("触发事件", event)
            .add_yaxis("运营状态", status)
            .set_global_opts(title_opts=opts.TitleOpts(title="触发事件与运营状态分类统计图"))
    )
    bar.render()

类实例化与调用

if __name__ == '__main__':
    start_time = time.time()  # 计算程序开始时间
    st = analysisData('127.0.0.1', 3306, 'root', 'xxxx', 'loaddb')  # 实例化类,传入必要参数
    print('程序耗时{:.2f}'.format(time.time() - start_time))  # 计算程序总耗时

数据分析图:

触发事件与运营状态分类统计图,可以发现爱你对于触发事件来说多为其他。而运营状态则多为空车

本文分享自微信公众号 - 光城(guangcity),作者:lightcity

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如醉如痴之最小堆

    一道简单的题,可以让你如醉如痴,更是因为这一道题,你才会学会很多,不要小看简单,简单中蕴含深意。

    公众号guangcity
  • 实时车票查询及登陆CTC

    前两天老表发了个12306软文,忽然想起,自己的公众号也好久没更新爬虫系列了,今天就开始琢磨一下,本次的爬虫主要有两大方面的功能。

    公众号guangcity
  • 全连接神经网络(下)

    0.说在前面1.Batch Normalization1.1 什么是BN?1.2 前向传播1.3 反向传播2.Dropout2.1 什么是Dropout?2.2...

    公众号guangcity
  • python pyqt5 QStatusBar 常用

    import sys from PyQt5.QtCore import * from PyQt5.QtGui import * from PyQt5.Qt...

    用户5760343
  • 隐马尔科夫-维特比算法

    概念介绍:   继上篇贝叶斯(https://cloud.tencent.com/developer/article/1056640)后,一直想完成隐马尔科夫这...

    知然
  • Core ML简介及实时目标检测及Caffe TensorFlow coremltools模型转换

    Core ML简介及实时目标检测,Caffe、Tensorflow与Core ML模型转换、Vision库的使用 转载请注明出处 https://cloud.t...

    WWWWDotPNG
  • python pyqt5 QComboBox下拉列表框 常用

    addItem() addItems() clear() count() currentText() currentIndex()

    用户5760343
  • python面向对象基础

    面向过程的程序设计的核心是过程,过程即解决问题的步骤,面向过程的设计就好比精心设计好一条流水线,考虑周全什么时候处理什么东西。

    菲宇
  • 全面深入理解Python面向对象编程

    面向过程编程最易被初学者接受,其往往用一长段代码来实现指定功能,开发过程中最常见的操作就是粘贴复制,即:将之前实现的代码块复制到现需功能处。

    顶级程序员
  • 如何用vn.py做隔夜交易?

    本文提供了一个每个交易日开盘前不用重连CTP的方法。如果不是特殊需求,强烈建议每天盘前重启程序。感谢viponedream在维恩的派论坛里的分享!

    用Python的交易员

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动