public static OpenIntIntHashMap getIntegerMapUidToPartitionColt(Map<Integer, Connection> cnxMap)
throws SQLException {
Connection conn = null;
// try to connect and load from testmachine so faster
String userName = "******";
String password = "";
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://testmachine:3334/";
String dbName = "twitter3";
try {
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url + dbName, userName, password);
} catch (Exception e) {
System.out.println("could not load from testmachine");
conn = null;
}
System.out.println("starting to load huge lookup table");
if (conn == null) {
conn = cnxMap.values().iterator().next();
}
String partitionTable = DebugUtil.PARTITION_TABLE; // "user3";
String partCol = DebugUtil.PARTITION_COLUMN;
Statement stmt = conn.createStatement();
String query =
"select uid, "
+ partCol
+ " from "
+ partitionTable
+ " where "
+ partCol
+ " is not null"; // im not sure why there are nulls...
System.out.println("loading partitioning from table: " + partitionTable);
ResultSet rs = stmt.executeQuery(query);
System.out.println("got big result set");
OpenIntIntHashMap map = new OpenIntIntHashMap();
int i = 0;
int unknownId = 0;
int[] addTo = new int[5];
addTo[0] = 5;
addTo[1] = 7;
addTo[2] = 9;
addTo[3] = 2;
addTo[4] = 4;
int whaleCnt = 0;
while (rs.next()) {
int part = rs.getInt(2);
if (part >= 0) {
part++;
}
// -1 ids should be assigned in 1 hop scenario
else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(DebugUtil.TwitterRouterType.ONE_HOP)) {
part = (whaleCnt % 10) + 1;
whaleCnt++;
}
map.put(rs.getInt(1), part);
i++;
}
// now get ids with null partition id and add them to other partitions
query = "select uid from " + partitionTable + " where " + partCol + " is null";
rs.close();
rs = stmt.executeQuery(query);
while (rs.next()) {
// map.put(rs.getInt(1), unknownId%9+2);
if (partitionTable.equals("user")) {
map.put(rs.getInt(1), addTo[unknownId % 5]);
} else {
map.put(rs.getInt(1), unknownId % 9 + 2);
}
unknownId++;
}
rs.close();
stmt.close();
System.out.println(
"done loading huge lookup table with "
+ (i + unknownId)
+ " values in the mapping, "
+ unknownId
+ " of which were null and assigned to partitions with fewer tuples");
return map;
}
public static DBRouter loadBasicRouter(
Map<Integer, Connection> cnxMap, String schemaName, Map<String, String> tableToColNames) {
System.out.println("loading router....");
DBRouter dbRouter = new DBRouter(new HashSet<Integer>(cnxMap.keySet()));
// dbRouter.addLookupTable(new BasicLookupTable("persons", "p_id", null));
if (DebugUtil.WIKIPEDIA_DATA_SET) {
try {
// create the range partition lookup on table name
StringRangeRouter srr = loadStringRangeRouter(cnxMap);
// create the dual lookup table for page.page_id and revision.rev_id
// LookupTable lt1 = loadLookupTable(schemaName, "revision", "rev_page", cnxMap);
LookupTable lt1 = loadLookupTable(schemaName, "page", "page_id", cnxMap);
// create the lookup table on text.old_id (can use revision.rev_text_id here)
LookupTable lt2 = loadLookupTable(schemaName, "revision", "rev_text_id", cnxMap);
lt2.setName("text");
dbRouter.addLookupTable(lt1);
dbRouter.addLookupTable(lt2);
dbRouter.addSecondaryLookup("page", srr);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dbRouter;
} else if (DebugUtil.TPCE_DATA_SET) {
if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.LOOKUP_TABLE)) {
try {
// read in the a_id -> partition table
ConcurrentHashMap<Long, Integer> a_id_map = loadConcurrentLongHashMap("a_by_aid", cnxMap);
ConcurrentLookupLong cl_a_id = new ConcurrentLookupLong(a_id_map);
// read in the b_id mapping
ConcurrentHashMap<Long, Integer> b_id_map = loadConcurrentLongHashMap("b_by_bid", cnxMap);
ConcurrentLookupLong cl_b_id = new ConcurrentLookupLong(b_id_map);
// read in the c_id mapping
ConcurrentHashMap<Long, Integer> c_id_map = loadConcurrentLongHashMap("c_by_cid", cnxMap);
ConcurrentLookupLong cl_c_id = new ConcurrentLookupLong(c_id_map);
// read in the c tax_id
ConcurrentHashMap<String, Integer> taxid_map =
loadConcurrentStringHashMap("c_by_taxid", cnxMap);
ConcurrentLookupString cl_taxid = new ConcurrentLookupString(taxid_map);
// read in the tid map
ConcurrentHashMap<Long, Integer> t_id_map = loadConcurrentLongHashMap("t_by_tid", cnxMap);
ConcurrentLookupLong cl_t_id = new ConcurrentLookupLong(t_id_map);
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_id", cl_t_id);
ltTrade.addLookupTable("t_ca_id", cl_a_id);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_a_id);
ltCustAcct.addLookupTable("ca_c_id", cl_c_id);
ltCustAcct.addLookupTable("ca_b_id", cl_b_id);
// create lt for customer
LookupMultiColumn ltCustomer =
new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_c_id);
ltCustomer.addLookupTable("c_tax_id", cl_taxid);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_b_id);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.RANGE)) {
ConcurrentRangeLookup cl_broker = new ConcurrentRangeLookup(4300000001L, 4300000100L);
ConcurrentRangeLookup cl_cust = new ConcurrentRangeLookup(4300000001L, 4300010000L);
ConcurrentRangeLookup cl_account = new ConcurrentRangeLookup(43000000001L, 43000099992L);
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_ca_id", cl_account);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_account);
// create lt for customer
LookupMultiColumn ltCustomer = new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_cust);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_broker);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.HASH)) {
ConcurrentHashLookup cl_broker = new ConcurrentHashLookup();
ConcurrentHashLookup cl_cust = new ConcurrentHashLookup();
ConcurrentHashLookup cl_account = new ConcurrentHashLookup();
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_ca_id", cl_account);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_account);
// create lt for customer
LookupMultiColumn ltCustomer = new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_cust);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_broker);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.SINGLE_BACKEND)) {
OnePartitionTable opt_trade = new OnePartitionTable("trade", "", 1);
OnePartitionTable opt_cust_ac = new OnePartitionTable("customer_account", "", 1);
OnePartitionTable opt_cust = new OnePartitionTable("customer", "", 1);
OnePartitionTable opt_broker = new OnePartitionTable("broker", "", 1);
// add lt to the router
dbRouter.addLookupTable(opt_trade);
dbRouter.addLookupTable(opt_cust_ac);
dbRouter.addLookupTable(opt_cust);
dbRouter.addLookupTable(opt_broker);
return dbRouter;
} else {
throw new RuntimeException("other router types not supported for tpce");
}
} else if (DebugUtil.TWITTER_DATA_SET) {
if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.SINGLE_BACKEND)) {
OnePartitionTable t_user = new OnePartitionTable("user", "uid", 1);
OnePartitionTable t_followers = new OnePartitionTable("followers", "f1", 1);
OnePartitionTable t_follows = new OnePartitionTable("follows", "f1", 1);
OnePartitionTable t_tweets = new OnePartitionTable("tweets", "uid", 1);
OnePartitionTable t_added_tweets = new OnePartitionTable("added_tweets", "uid", 1);
dbRouter.addLookupTable(t_tweets);
dbRouter.addLookupTable(t_user);
dbRouter.addLookupTable(t_followers);
dbRouter.addLookupTable(t_follows);
dbRouter.addLookupTable(t_added_tweets);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.LOOKUP_TABLE)
|| DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.ONE_HOP)) {
try {
OpenIntIntHashMap map = getIntegerMapUidToPartitionColt(cnxMap);
IntLookupTable lt_user = new IntLookupTable("user", "uid", map, dbRouter.allPartitionIds);
IntLookupTable lt_tweets = new IntLookupTable("tweets", "uid", lt_user);
IntLookupTable lt_follows = new IntLookupTable("follows", "f1", lt_user);
IntLookupTable lt_followers = new IntLookupTable("followers", "f1", lt_user);
IntLookupTable lt_added = new IntLookupTable("added_tweets", "uid", lt_user);
dbRouter.addLookupTable(lt_user);
dbRouter.addLookupTable(lt_tweets);
dbRouter.addLookupTable(lt_follows);
dbRouter.addLookupTable(lt_followers);
dbRouter.addLookupTable(lt_added);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
// also used for uber hash, but make sure lookup of 1 tweet has userid too
else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(
TwitterRouterType
.REAL_HASH_UID)) { // also used for uber hash, but make sure tweet has userid too
HashPartitionLookup hp_user =
new HashPartitionLookup("user", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_tweets =
new HashPartitionLookup("tweets", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_follows =
new HashPartitionLookup("follows", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_followers =
new HashPartitionLookup("followers", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_added =
new HashPartitionLookup("added_tweets", "uid", dbRouter.allPartitionIds);
dbRouter.addLookupTable(hp_user);
dbRouter.addLookupTable(hp_tweets);
dbRouter.addLookupTable(hp_follows);
dbRouter.addLookupTable(hp_followers);
dbRouter.addLookupTable(hp_added);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.REAL_HASH_TID)) {
HashPartitionLookup hp_user =
new HashPartitionLookup("user", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_tweets =
new HashPartitionLookup("tweets", "id", dbRouter.allPartitionIds);
HashPartitionLookup hp_follows =
new HashPartitionLookup("follows", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_followers =
new HashPartitionLookup("followers", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_added =
new HashPartitionLookup("added_tweets", "uid", dbRouter.allPartitionIds);
dbRouter.addLookupTable(hp_user);
dbRouter.addLookupTable(hp_tweets);
dbRouter.addLookupTable(hp_follows);
dbRouter.addLookupTable(hp_followers);
dbRouter.addLookupTable(hp_added);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.SINGLE_BACKEND)) {
assert dbRouter.allPartitionIds.size() == 1;
int partId = dbRouter.allPartitionIds.iterator().next();
OnePartitionTable o_user = new OnePartitionTable("user", "uid", partId);
OnePartitionTable o_tweets = new OnePartitionTable("tweets", "id", partId);
OnePartitionTable o_follows = new OnePartitionTable("follows", "f1", partId);
OnePartitionTable o_followers = new OnePartitionTable("followers", "f1", partId);
OnePartitionTable o_added = new OnePartitionTable("added_tweets", "uid", partId);
dbRouter.addLookupTable(o_user);
dbRouter.addLookupTable(o_tweets);
dbRouter.addLookupTable(o_follows);
dbRouter.addLookupTable(o_followers);
dbRouter.addLookupTable(o_added);
} else {
assert false : "no other twitter routers supported";
}
return dbRouter;
} else {
for (String tabName : tableToColNames.keySet()) {
try {
LookupTable lt =
RouterLoader.loadLookupTable(
schemaName, tabName, tableToColNames.get(tabName), cnxMap);
DebugUtil.print(lt.toString());
dbRouter.addLookupTable(lt);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
return dbRouter;
}
}
