精华内容
下载资源
问答
  • 当用户因特殊情况清除缓存 导致app 发送json串 入库并发高 导致CPU 暴增到88% 并且居高不下 优化思路: 1、异步队列处理 2、redis 过滤(就是只处理当天第一次请求) 3、redis 辅助存储app名称(验证过后批量插入...
  • Educoder题目:MySQL开发技巧 - 批量数据入库及检索答案解析.md
  • StampGIS管线二维数据入库流程,
  • 城市基础测绘成果(DLG)是建设空间地理信息数据库的重要数据源。基于甘肃省天水市麦积区1:500地形图建立数据库项目,对DWG数据转换入库过程中涉及的数据转换与数据处理等方面进行研究。
  • 说到采集,无非就是远程获取...整理下基本信息,采集入库: <?php include_once("conn.php"); if($_GET['id']<=8&&$_GET['id']){ $id=$_GET['id']; $conn=file_get_contents("http://www.93moli.com/news_
  • java数据入库

    2019-04-12 01:32:48
    NULL 博文链接:https://jaybin.iteye.com/blog/1704825
  • 海量数据入库

    2013-08-22 19:42:22
    本程序经过测试,可以将批量超大型txt文件经过处理后存到数据库中,并且不会出现卡死现象。
  • 电力GIS数据入库

    2020-05-06 03:33:36
    Wealthgis是以GIS为基础平台,建立全数字化电网...文中以唐山市某县电力局采集数据录入为例,具体介绍电力GIS数据入库时wealthgis系统对数据的要求、入库的方法、检查的手段等内容,为wealthgis电力用户提供一个参考平台。
  • java高并发下数据入库

    千次阅读 2021-08-24 16:03:54
    该服务利用线程池并结合缓存类来处理高并发下数据入库问题,做到实时数据存入redis和数据批量入库,使用的时候需要修改为自己的业务数据,该服务暂时适合下面两种情况: 1、达到设置的超时时间。 2、达到最大批次。 ...

    java高并发下数据入库

    该服务利用线程池并结合缓存类来处理高并发下数据入库问题,做到实时数据存入redis和数据批量入库,使用的时候需要修改为自己的业务数据,该模块是根据下面的设置进行高并发处理。
    1、达到设置的超时时间。
    2、达到最大批次。

    package io.jack.service.impl;
    
    import com.alibaba.fastjson.JSON;
    import com.alibaba.fastjson.JSONArray;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.InitializingBean;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.Resource;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.concurrent.ExecutorService;
    import java.util.concurrent.Executors;
    
    /**
     * <pre>
     *   数据批量入库服务
     * </pre>
     * Created by RuiXing Hou on 2021-08-05.
     *
     * @since 1.0
     */
    @Component
    @Slf4j
    public class BatchDataStorageService implements InitializingBean
    {
    	/**
    	 * 最大批次数量
    	 */
    	@Value("${app.db.maxBatchCount:800}")
        private int maxBatchCount;
    
    	/**
    	 * 最大线程数
    	 */
        @Value("${app.db.maxBatchThreads:100}")
        private int maxBatchThreads;
    
    	/**
    	 * 超时时间
    	 */
    	@Value("${app.db.batchTimeout:3000}")
        private int batchTimeout;
    
    	/**
    	 * 批次数量
    	 */
        private int batchCount = 0;
    
    	/**
    	 * 批次号
    	 */
    	private static long batchNo = 0;
    
    	/**
    	 * 线程池定义接口
    	 */
        private ExecutorService executorService = null;
    
    	/**
    	 * 服务器缓存工具类,下面提供源码
    	 */
    	@Resource
    	private CacheService cacheService;
    
    	/**
    	 * 业务接口
    	 */
    	@Resource
    	private DeviceRealTimeService deviceRealTimeService;
    
    	/**
    	 * redis工具类
    	 */
    	@Resource
    	private RedisUtils redisUtils;
    
    	@Override
    	public void afterPropertiesSet() {
    		this.executorService = Executors.newFixedThreadPool(this.maxBatchThreads, r -> {
    			Thread thread = new Thread(r);
    			if (r instanceof BatchWorker) {
    				thread.setName("batch-worker-" + ((BatchWorker) r).batchKey);
    			}
    			return thread;
    		});
    	}
    
    	/**
    	 * 需要做高并发处理的类只需要调用该方法
    	 *
    	 * @param deviceRealTimeDTO
    	 */
    	public void saveRealTimeData(DeviceRealTimeDTO deviceRealTimeDTO) {
    		final String failedCacheKey = "device:real_time:failed_records";
    
    		try {
    
    			String durationKey = "device:real_time:batchDuration" + batchNo;
    			String batchKey = "device:real_time:batch" + batchNo;
    
    			if (!cacheService.exists(durationKey)) {
    				cacheService.put(durationKey, System.currentTimeMillis());
    				new BatchTimeoutCommitThread(batchKey, durationKey, failedCacheKey).start();
    			}
    
    			cacheService.lPush(batchKey, deviceRealTimeDTO);
    			if (++batchCount >= maxBatchCount) {
    				// 达到最大批次,执行入库逻辑
    				dataStorage(durationKey, batchKey, failedCacheKey);
    			}
    
    		} catch (Exception ex) {
    			log.warn("[DB:FAILED] 设备上报记录入批处理集合异常: " + ex.getMessage() + ", DeviceRealTimeDTO: " + JSON.toJSONString(deviceRealTimeDTO), ex);
    			cacheService.lPush(failedCacheKey, deviceRealTimeDTO);
    		} finally {
    			updateRealTimeData(deviceRealTimeDTO);
    		}
    	}
    
    	/**
    	 * 更新实时数据
    	 * @param deviceRealTimeDTO 业务POJO
    	 */
    	private void updateRealTimeData(DeviceRealTimeDTO deviceRealTimeDTO) {
    		redisUtils.set("real_time:"+deviceRealTimeDTO.getDeviceId(), JSONArray.toJSONString(deviceRealTimeDTO));
    	}
    
    	/**
    	 *
    	 * @param durationKey 		持续时间标识
    	 * @param batchKey			批次标识
    	 * @param failedCacheKey	错误标识
    	 */
    	private void dataStorage(String durationKey, String batchKey, String failedCacheKey) {
    		batchNo++;
    		batchCount = 0;
    		cacheService.del(durationKey);
    		if (batchNo >= Long.MAX_VALUE) {
    			batchNo = 0;
    		}
    		executorService.execute(new BatchWorker(batchKey, failedCacheKey));
    	}
    
    	private class BatchWorker implements Runnable
    	{
    
    		private final String failedCacheKey;
    		private final String batchKey;
    
    		public BatchWorker(String batchKey, String failedCacheKey) {
    			this.batchKey = batchKey;
    			this.failedCacheKey = failedCacheKey;
    		}
    		
    		@Override
    		public void run() {
    			final List<DeviceRealTimeDTO> deviceRealTimeDTOList = new ArrayList<>();
    			try {
    				DeviceRealTimeDTO deviceRealTimeDTO = cacheService.lPop(batchKey);
    				while(deviceRealTimeDTO != null) {
    					deviceRealTimeDTOList.add(deviceRealTimeDTO);
    					deviceRealTimeDTO = cacheService.lPop(batchKey);
    				}
    
    				long timeMillis = System.currentTimeMillis();
    
    				try {
    					List<DeviceRealTimeEntity> deviceRealTimeEntityList = ConvertUtils.sourceToTarget(deviceRealTimeDTOList, DeviceRealTimeEntity.class);
    					deviceRealTimeService.insertBatch(deviceRealTimeEntityList);
    				} finally {
    					cacheService.del(batchKey);
    					log.info("[DB:BATCH_WORKER] 批次:" + batchKey + ",保存设备上报记录数:" + deviceRealTimeDTOList.size() + ", 耗时:" + (System.currentTimeMillis() - timeMillis) + "ms");
    				}
    			} catch (Exception e) {
    				log.warn("[DB:FAILED] 设备上报记录批量入库失败:" + e.getMessage() + ", DeviceRealTimeDTO: " + deviceRealTimeDTOList.size(), e);
    				for (DeviceRealTimeDTO deviceRealTimeDTO : deviceRealTimeDTOList) {
    					cacheService.lPush(failedCacheKey, deviceRealTimeDTO);
    				}
    			}
    		}
        }
    
    	class BatchTimeoutCommitThread extends Thread {
    
    		private final String batchKey;
    		private final String durationKey;
    		private final String failedCacheKey;
    
    		public BatchTimeoutCommitThread(String batchKey, String durationKey, String failedCacheKey) {
    			this.batchKey = batchKey;
    			this.durationKey = durationKey;
    			this.failedCacheKey = failedCacheKey;
    			this.setName("batch-thread-" + batchKey);
    		}
    
    		public void run() {
    			try {
    				Thread.sleep(batchTimeout);
    			} catch (InterruptedException e) {
    				log.error("[DB] 内部错误,直接提交:" + e.getMessage());
    			}
    
    			if (cacheService.exists(durationKey)) {
    				// 达到最大批次的超时间,执行入库逻辑
    				dataStorage(durationKey, batchKey, failedCacheKey);
    			}
    		}
    
    	}
    
    }
    
    
    package io.jack.service;
    
    import org.springframework.beans.factory.InitializingBean;
    import org.springframework.context.annotation.Scope;
    import org.springframework.stereotype.Component;
    
    import java.util.HashMap;
    import java.util.LinkedList;
    import java.util.Map;
    import java.util.concurrent.ConcurrentHashMap;
    import java.util.concurrent.atomic.AtomicLong;
    
    @Component
    @Scope("singleton")
    public class CacheService implements InitializingBean {
    
        private Map<String, Object> objectCache = new ConcurrentHashMap<>();
    
        private Map<String, AtomicLong> statCache = new ConcurrentHashMap<>();
    
        @Override
        public void afterPropertiesSet() {
            statCache.put("terminals", new AtomicLong(0));
            statCache.put("connections", new AtomicLong(0));
        }
    
        public long incr(String statName) {
            if (!statCache.containsKey(statName))
                statCache.put(statName, new AtomicLong(0));
            return statCache.get(statName).incrementAndGet();
        }
    
        public long decr(String statName) {
            if (!statCache.containsKey(statName))
                statCache.put(statName, new AtomicLong(0));
            return statCache.get(statName).decrementAndGet();
        }
    
        public long stat(String statName) {
            if (!statCache.containsKey(statName))
                statCache.put(statName, new AtomicLong(0));
            return statCache.get(statName).get();
        }
    
        public <T> void put(String key, T object) {
            objectCache.put(key, object);
        }
    
        public <T> T get(String key) {
            return (T) objectCache.get(key);
        }
    
        public void remove(String key) {
            objectCache.remove(key);
        }
    
        public void hSet(String key, String subkey, Object value) {
            synchronized (objectCache) {
                HashMap<String, Object> submap = (HashMap<String, Object>) objectCache.get(key);
                if (submap == null) {
                    submap = new HashMap<>();
                    objectCache.put(key, submap);
                }
                submap.put(subkey, value);
            }
        }
    
        public <T> T hGet(String key, String subkey) {
            synchronized (objectCache) {
                HashMap<String, Object> submap = (HashMap<String, Object>) objectCache.get(key);
                if (submap != null) {
                    return (T) submap.get(subkey);
                }
                return null;
            }
        }
    
        public boolean hExists(String key, String subkey) {
            synchronized (objectCache) {
                HashMap<String, Object> submap = (HashMap<String, Object>) objectCache.get(key);
                if (submap != null) {
                    return submap.containsKey(subkey);
                }
                return false;
            }
        }
    
        public void lPush(String key, Object value) {
            synchronized (objectCache) {
                LinkedList queue = (LinkedList) objectCache.get (key);
                if (queue == null) {
                    queue = new LinkedList();
                    objectCache.put(key, queue);
                }
                queue.addLast(value);
            }
        }
    
        public <T> T lPop(String key) {
            synchronized (objectCache) {
                LinkedList queue = (LinkedList) objectCache.get (key);
                if (queue != null) {
                    if (!queue.isEmpty()) {
                        return (T)queue.removeLast();
                    }
                    objectCache.remove(key);
                }
                return null;
            }
        }
    
        public void del(String key) {
            objectCache.remove(key);
        }
    
        public boolean exists(String key) {
            return objectCache.containsKey(key);
        }
    
        public void dump() {
    
        }
    }
    
    
    展开全文
  • 在一个字符串中,如果某个序列具有特殊的含义,每个序列以反斜线符号 (“”)开头,称为转义字符。 MySQL 识别下列转义字符: 0 一个 ASCII 0 (NUL) 字符。 ‘ 一个 ASCII 39 单引号 (“’”) 字符。...
  • 对使用世纪国源软件进行数据建库、入库前的数据处理做了详细的说明与指导。
  • 实验3使用kettle进行数据入库 PAGE 2 [文档标题 实验3使用kettle进行数据入库 本次课程介绍实验3使用kettle进行数据入库 一kettle进行数据入库 一步骤 1. 由于是绿色版本,解压出来后直接双击data-integration目录下...
  • select to_char(scn_to_timestamp(ORA_ROWSCN),‘yyyy-mm-dd hh24:mi:ss’) insert_time from tablename; tablename 数据对应的表名

    select to_char(scn_to_timestamp(ORA_ROWSCN),‘yyyy-mm-dd hh24:mi:ss’) insert_time from tablename;
    tablename 数据对应的表名

    展开全文
  • 基于kafka的数据入库程序,将数据推送到es中
  • HBase数据入库

    2019-08-03 01:06:28
    NULL 博文链接:https://57832638.iteye.com/blog/1983143
  • python处理excel或csv数据入库;处理csv需要引入相应的包import csv;处理excel则引入import xlrd;from Sqlhelper_excel(python文件名) import SqlHelper(类)
  • 基于FME语义转换的CAD格式地下管线数据入库预处理.pdf
  • title= “MySQL开发技巧 - 批量数据入库及检索” categories= [“educoder”] date= 2020-07-15 +++ MySQL数据库连接 src/step1/connect.py # coding=utf-8 import pymysql def connect(): # 请在下面添加连接数据库...

    +++
    title= “MySQL开发技巧 - 批量数据入库及检索”
    categories= [“educoder”]
    date= 2020-07-15
    +++

    MySQL数据库连接

    src/step1/connect.py

    # coding=utf-8
    import pymysql
    def connect():
    # 请在下面添加连接数据库的代码,完成相应功能
    # ###### Begin ######
        conn = pymysql.connect(host='localhost',
    展开全文
  • 数据入库:略 数据可视化: #!/usr/bin/python3 import pymysql type_list = ["userInfoSync","alertReport","changeNetwork","closeDoor","dataSync",&...

    数据从同事那里拿来,大概60万条,几百MB ,是某市面上保险柜子的数据,现在要分析这批数据。

    数据清洗:略

    数据入库:略

    数据可视化:

    #!/usr/bin/python3
     
    import pymysql
    
    
    type_list = ["userInfoSync","alertReport","changeNetwork","closeDoor","dataSync","deleteFP","dynPwd","dynPwdSecond",
                 "formatDevice","heartbeat","lock_activation","network","openDoor","readStatus","regFP","resetting",
                 "setCtlPwd","updateFirmware"]
    
    
    def get_type_counts(): 
    
        config = {
            "mysql_config": {
                "host": "***",
                "user": "***",
                "password": "***",
                "database": "***"
                    }
        }
        
        type_counts_dict={}
        
        user = config["mysql_config"]["user"]
        host = config["mysql_config"]["host"]
        password = config["mysql_config"]["password"]
        database = config["mysql_config"]["database"]
        # 打开数据库连接
        db = pymysql.connect(host,user ,password ,database , charset='utf8' )
    
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 查询语句
        sql = "SELECT type,count(*) as freq  FROM dictionary WHERE type != 'NULL' and type != 'networkStatus' group by type ;"
    
        try:
           # 执行SQL语句
           cursor.execute(sql)
           # 获取所有记录列表
           results = cursor.fetchall()
           #print(results)
           for row in results:
              type = row[0]
              freq = row[1]
              type_counts_dict[type]=freq
            
            
    #            # 打印结果
    #           print ("type=%s,freq=%s" % \
    #                  (type, freq ))
                
                
        except:
           print ("Error: unable to fetch data")
    
        # 关闭数据库连接
        db.close()
        return type_counts_dict
    
    def fill_null_type(type_counts_dict,type_list):
        
        key_list = [ i for i in type_counts_dict]
        len_key_list = len(key_list)
        len_type_list = len(type_list)
        #查出的数据类型是否和默认业务类型作对比
        if len_key_list < len_type_list :
            null_type = list(set(type_list).difference(set(key_list)))
            print(null_type)
            for i in null_type:
                type_counts_dict[i] = 0
            return type_counts_dict
        elif len_key_list == type_list :
            print("Info: Data type is equals  business type!!!")
            return type_counts_dict
        else:
            print("Error: Data type is larger than business type!!!")
        return type_counts_dict
    
    def data_visualization(type_counts_dict):
        import matplotlib.pyplot as plt
        import matplotlib
        
        matplotlib.rcParams['font.sans-serif'] = ['SimHei']
        matplotlib.rcParams['axes.unicode_minus'] = False
        #对字典进行排序
        type_counts_dict_sorted = sorted(zip(type_counts_dict.values(), type_counts_dict.keys()),reverse=True)
        datas = []
        type_name = []
        for x in type_counts_dict_sorted:
            datas.append(x[0])
            type_name.append(x[1])
    
        """
        绘制水平条形图方法barh
        参数一:y轴
        参数二:x轴
        """
        plt.barh(range(len(datas)), datas, height=0.5, color='steelblue', alpha=0.8)      # 从下往上画
        plt.yticks(range(len(type_name)), type_name)
        max_datas = max(datas)
        plt.xlim(0,max_datas+1000)
        plt.xlabel("Data Proportion")
        plt.title("Different types of data volume")
        for x, y in enumerate(datas):
            plt.text(y + 1/2, x - 0.1, '%s' % y)
        plt.show()
    
    
    #获取数据
    type_counts_dict = get_type_counts()
    #填充业务上要求,数据中没有的类型
    type_counts_dict = fill_null_type(type_counts_dict,type_list)   
    #结果展示
    data_visualization(type_counts_dict)
    

    横条形图

    #!/usr/bin/python3
     
    import pymysql
    import json
    
      
    
    #获取数据
    def get_type_counts(): 
    
        config = {
            "mysql_config": {
                "host": "****",
                "user": "***",
                "password": "***.***",
                "database": "****"
                    }
        }
        
        user = config["mysql_config"]["user"]
        host = config["mysql_config"]["host"]
        password = config["mysql_config"]["password"]
        database = config["mysql_config"]["database"]
        
        open_Doortype_counts_dict={}
        
        # 打开数据库连接
        db = pymysql.connect(host,user ,password ,database , charset='utf8' )
    
        # 使用cursor()方法获取操作游标 
        cursor = db.cursor()
    
        # SQL 查询语句
        sql = "SELECT  msg  FROM dictionary WHERE type = 'openDoor';"
        
        try:
           # 执行SQL语句
           cursor.execute(sql)
           # 获取所有记录列表
           results = cursor.fetchall()
           #print(results)
           for row in results:
              line = str(row)[2:-3].strip("\\n")
              #print(line)
              open_Doortype = json.loads(line)["data"]["openDoorType"]
              if open_Doortype in open_Doortype_counts_dict.keys():
                    open_Doortype_counts_dict[open_Doortype] += 1
              else:
                    open_Doortype_counts_dict[open_Doortype] = 1
              
            
    #            # 打印结果
    #           print ("type=%s,freq=%s" % \
    #                  (type, freq ))
                
                
        except:
           print ("Error: unable to fetch data")
    
        # 关闭数据库连接
        db.close()
        return open_Doortype_counts_dict
    
    
    #获取数据
    open_Doortype_counts_dict = get_type_counts()
    
    #print(open_Doortype_counts_dict)
    #{'3': 2191, '1': 1275}
    
    
    
    
    
    #填充数据
    def fill_null_type(open_Doortype_counts_dict):
        type_list = ["0","1","2","3","4"]
        key_list = [ i for i in open_Doortype_counts_dict]
        len_key_list = len(key_list)
        len_type_list = len(type_list)
        #查出的数据类型是否和默认业务类型作对比
        if len_key_list < len_type_list :
            null_type = list(set(type_list).difference(set(key_list)))
            print(null_type)
            for i in null_type:
                open_Doortype_counts_dict[i] = 0
            return open_Doortype_counts_dict
        elif len_key_list == type_list :
            print("Info: Data type is equals  business type!!!")
            return type_counts_dict
        else:
            print("Error: Data type is larger than business type!!!")
        return type_counts_dict
    
    #  填充空值
    open_Doortype_counts_dict = fill_null_type(open_Doortype_counts_dict)
    
    #数据可视化
    def data_visualization(open_Doortype_counts_dict):
        import numpy as np
        import matplotlib.pyplot as plt
    
        fig, ax = plt.subplots(figsize=(9, 20), subplot_kw=dict(aspect="equal"))
        datas = []
        type_name = []
        open_Doortype_name_dict={'0':"Bluetooth opening",'1':"Open the door remotely",'2':"Password open",'3':"Fingerprint opening",'4':"Dynamic cipher"}
        type_name_cn = {}
        #名称转换 0 -> 蓝牙开启
        for name in open_Doortype_counts_dict:
            if name in open_Doortype_name_dict.keys():
                type_name_cn[open_Doortype_name_dict[name]] = open_Doortype_counts_dict[name]
                
        for x in type_name_cn:
            datas.append(type_name_cn[x])
            type_name.append(x)
    
    
        def func(pct, allvals):
            absolute = int(pct/100.*np.sum(allvals))
            return "{:.1f}%\n({:d} )".format(pct, absolute)
    
    
        wedges, texts, autotexts = ax.pie(datas, autopct=lambda pct: func(pct, datas),
                                          textprops=dict(color="w"))
    
        # 标签距离
        ax.legend(wedges, type_name,
                  title="Ingredients",
                  loc="center left",
                  bbox_to_anchor=(1, 0, 0.5, 0.5))
        #图上的字
        plt.setp(autotexts, size=20, weight="bold")
        # title
        ax.set_title("Open Door Type Proportion",size = 20)
    
        plt.show()  
        
    data_visualization(open_Doortype_counts_dict)
    
    

     

     

     

    就先这样吧。

     

     

     

     

    展开全文
  • Java jdbc多线程写数据入库

    千次阅读 2020-01-18 20:15:51
    今天下午在做模拟数据时发现200万条数据写入数据库,时间很长,思考通过使用多线程进行数据批量写入操作。 二、实现代码: 1、数据库工具类 package com.general.data.utils; import java.sql.Connection; import ...
  • Hive数据入库

    千次阅读 2019-05-06 11:49:40
    hive中建立数据表 create table nm_use ( month string comment '账期' , province string comment '省份' , business_type string comment '业务类型' , product_type string ...
  • 读取文件入库,两种方式 资源中内容包括两种功能:1.把数据库的数据,通过sql查询生成txt文件,存放到指定路径;(CreateFileToPathMain类,java调用shell脚本方式) 2.读取指定路径下的文件数据,插入到指定表中。...
  • 基于ArcGIS的CAD数据入库探究.pdf
  • mayfish 可以灵活的自定义将要执行写入的数据内容的校验规则,以减少开发人员手动对每一个字段的数据进行校验的麻烦。
  • php保存数据到mysql 打算在dao层进行数据入库前的清理,比如varchar进行trim,int进行intval。 有一天突然想起,php intval的取值范围与mysql的int类型一样吗? 查了一下,不一样…… ...
  • 作为技术负责人和项目负责人,我负责了河南省新蔡县和永城市农村土地确权项目,在图解地块、调查公示、数据入库等环节形成了一系列原创性成果,现以博客形式共享,希望对从事相关工作的同仁有所帮助。 本文是数据...
  • iData数据工厂是一款GIS数据处理平台,软件的界面和操作习惯与AutoCAD平台一致,这样就使iData在拥有强大的数据处理功能的同时具有良好的图形...文中主要介绍利用iData处理大比例尺基础地形数据入库与检查修改等工作。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 90,130
精华内容 36,052
关键字:

数据入库