java 鏈接mysql 產生500W數據模擬生成環境

java 插入數據到mysql 通過sqoop 導入到hive 中，kylin模擬見cube 時間和 數據膨脹率 kylin 數據插入到 HBase

Kylin

HBase 1.1.3

Hive 1.2.1

Hadoop 2.5.1

create table infoagetime(
prod_name char(10),
prod_id SMALLINT,
ods_date DATE
)

數據格式

oPmgBZxldW?? ?75?? ?2016-09-04
WSSAnnZrNy?? ?57?? ?2016-09-09

本人Java水平有限，大家自行改善，尤其是批量插入mysql語句

另工程中需要導入 mysql jar ：?? mysql-connector-java-5.1.6.jar

V2

package com.wubaiwan.instmysql;import java.io.File;
import java.io.FileOutputStream;
import java.sql.DriverManager;
import java.util.Random;public class YiBaiWan {public static String getRandomString(int length) { // length表示生成字符串的長度String base = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";Random random = new Random();StringBuffer sb = new StringBuffer();for (int i = 0; i < length; i++) {int number = random.nextInt(base.length());sb.append(base.charAt(number));}return sb.toString();}public static final String url = "jdbc:mysql://192.168.184.168/hive2";public static final String name = "com.mysql.jdbc.Driver";public static final String user = "root";public static final String password = "123";public static java.sql.Connection conn = null;public static java.sql.PreparedStatement pst = null;static FileOutputStream out = null;public static void main(String args[]) {try {Class.forName(name);conn = DriverManager.getConnection(url, user, password);// 獲取連接conn.setAutoCommit(false);} catch (Exception e1) {// TODO Auto-generated catch blocke1.printStackTrace();} // 指定連接類型Random random = new Random();// int k = random.nextInt();// System.out.println(k);int x = 0;try {out = new FileOutputStream(new File("D:/mysql.txt"));String sql = "insert into infoagetime(prod_name,prod_id,ods_date) values(?,?,?)";// System.out.println(sql);pst = conn.prepareStatement(sql);while (x < 5000000) {// System.out.println((int)(Math.random()*100));// System.out.println(getRandomString(10) + ',' +// (int)(Math.random()*100));// String k = getRandomString(10) + ',' +// (int)(Math.random()*100 )+ ','+ "2016-09-0" +// (int)(random.nextInt(9)%9 + 1)+"\r\n" ;// System.out.println(k);// out.write(k.getBytes());pst.setString(1, getRandomString(10));pst.setInt(2, (int) (Math.random() * 100));pst.setString(3, "2016-09-0" + (int) (random.nextInt(9) % 9 + 1));pst.addBatch();if (x % 1000 == 0) {pst.executeBatch();//pst.executeUpdate(arg0)}x++;}System.out.println("Commit");pst.executeBatch();conn.commit();out.close();conn.close();pst.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{}}}

======================================

V1? 這個版本會有java memory 異常

package com.wubaiwan.instmysql;import java.io.File;  
import java.io.FileOutputStream;
import java.sql.DriverManager;
import java.util.Random;public class YiBaiWan {  public static String getRandomString(int length) { //length表示生成字符串的長度      String base = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";         Random random = new Random();         StringBuffer sb = new StringBuffer();         for (int i = 0; i < length; i++) {         int number = random.nextInt(base.length());         sb.append(base.charAt(number));         }         return sb.toString();         }    public static final String url = "jdbc:mysql://192.168.184.168/hive2";    public static final String name = "com.mysql.jdbc.Driver";    public static final String user = "root";    public static final String password = "123";    public static java.sql.Connection conn = null;    public static java.sql.PreparedStatement pst = null;    public static void main(String args[]){  try {Class.forName(name);conn = DriverManager.getConnection(url, user, password);//獲取連接 } catch (Exception e1) {// TODO Auto-generated catch blocke1.printStackTrace();}//指定連接類型   FileOutputStream out = null;     Random random = new Random();  //int k = random.nextInt();  //System.out.println(k);  int x = 0;  try {  out = new FileOutputStream(new File("D:/mysql.txt"));  while(x <5000000){  //System.out.println((int)(Math.random()*100));  //System.out.println(getRandomString(10) + ',' + (int)(Math.random()*100));  //String k = getRandomString(10) + ',' + (int)(Math.random()*100 )+ ','+ "2016-09-0" + (int)(random.nextInt(9)%9 + 1)+"\r\n" ;  //System.out.println(k);  //out.write(k.getBytes());  String sql = "insert into infoagetime(prod_name,prod_id,ods_date) values('"+getRandomString(10)+ "',"+(int)(Math.random()*100 )+","+ "'2016-09-0" + (int)(random.nextInt(9)%9 + 1) + "')"   ;//System.out.println(sql);pst = conn.prepareStatement(sql);  pst.executeUpdate();  if(x%10000 == 0 ) {System.out.println("Commit");conn.commit();}x++;  }   out.close();    conn.close();   pst.close();  }catch (Exception e) {  // TODO Auto-generated catch block  e.printStackTrace();  }  }  }