mysql(49) : 大数据按分区导出数据

代码

import com.alibaba.gts.flm.base.util.Mysql8Instance;import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;public class 数据导出_按分区 {// 分区字段private static List<Integer> partitions = new ArrayList<Integer>() {{add(765);add(769);add(782);add(787);add(796);}};private static Mysql8Instance m;private static Integer onCount = 10000;private static String fields;private static String table;public static void main(String[] args) throws Exception {m = new Mysql8Instance("127.0.0.1", 3306, "test", "root", "123456");m.setReturnColumnName(true);fields = "*";table = "test1";handle("test1.csv");}public static String getSql(Integer start, Integer date) {return "SELECT \n" + fields + " FROM    " + table + " where  sdate=" + date + "  limit " + start + "," + onCount;}public static void handle(String filePath) throws Exception {long currentTimeMillis = System.currentTimeMillis();File file = new File(filePath);// TODO 先删除再创建文件，避免文件有其他内容file.delete();file.createNewFile();BufferedWriter output = new BufferedWriter(new FileWriter(file, true));// true,则追加写入text文本long total = 0;boolean addTitle = false;for (Integer partition : partitions) {int start = 0;List<List<String>> rs;while ((rs = getData(m, getSql(start, partition))).size() > 1) {if (!addTitle) {addTitle = true;} else {rs.remove(0);}System.out.println("导出分区:[" + partition + "] ,limit:[" + start + "," + onCount + "]");for (List<String> r : rs) {total += r.size();String str = join(r, ",");output.write(str);output.write("\r\n");// 换行}start += onCount;}output.flush();}output.close();m.close();System.out.println("数据导出完成,总数量:" + total + ", 耗时:" + getHaoShi(System.currentTimeMillis() - currentTimeMillis));}public static List<List<String>> getData(Mysql8Instance m, String sql) {return m.query(sql);}/*** 计算耗时** @param t 毫秒* @return*/public static String getHaoShi(double t) {double d7 = t / 1000.0 / 60 / 60 / 24 / 30 / 12 / 100;if (d7 > 1) return round(d7, 1) + "纪元";double d6 = t / 1000.0 / 60 / 60 / 24 / 30 / 12;if (d6 > 1) return round(d6, 1) + "年";double d5 = t / 1000.0 / 60 / 60 / 24 / 30;if (d5 > 1) return round(d5, 1) + "月";double d4 = t / 1000.0 / 60 / 60 / 24;if (d4 > 1) return round(d4, 1) + "天";double d3 = t / 1000.0 / 60 / 60;if (d3 > 1) return round(d3, 1) + "小时";double d2 = t / 1000.0 / 60;if (d2 > 1) return round(d2, 1) + "分钟";double d1 = t / 1000.0;if (d1 > 1) return round(d1, 1) + "秒";return t + "毫秒";}public static String join(List<String> list, String separator) {Iterator<String> iterator = list.iterator();if (iterator == null) {return null;} else if (!iterator.hasNext()) {return "";} else {Object first = iterator.next();if (!iterator.hasNext()) {return Objects.toString(first, "");} else {StringBuilder buf = new StringBuilder(256);if (first != null) {buf.append(first);}while (iterator.hasNext()) {if (separator != null) {buf.append(separator);}Object obj = iterator.next();if (obj != null) {buf.append(obj);}}return buf.toString();}}}public static Double round(Double data, int amount) {if (data == null)return null;//利用BigDecimal来实现四舍五入.保留一位小数double result = new BigDecimal(data).setScale(amount, BigDecimal.ROUND_HALF_UP).doubleValue();//1代表保留1位小数,保留两位小数就是2,依此累推//BigDecimal.ROUND_HALF_UP 代表使用四舍五入的方式return result;}/**CREATE TABLE test.`test1` (`pkid` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',`sys_create_time` datetime DEFAULT NULL COMMENT '创建时间',`sys_modify_time` datetime DEFAULT NULL COMMENT '修改时间',`lng` double DEFAULT NULL COMMENT '经度',`name` varchar(100) DEFAULT NULL COMMENT '名称',`time` bigint DEFAULT NULL COMMENT '时间',`age` varchar(100) DEFAULT NULL COMMENT '年龄',`speed` double DEFAULT NULL COMMENT '速度',`lat` double DEFAULT NULL COMMENT '维度',`sdate` int(11) NOT NULL COMMENT '小时(分区键)',PRIMARY KEY (`pkid`,`sdate`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表1';*/
}