package com.media6.hive2rdbms.job; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.conf.Configured; import org.apache.hadoop.mapred.JobConf; import org.apache.hadoop.util.GenericOptionsParser; import org.apache.hadoop.util.Tool; import org.apache.hadoop.util.ToolRunner; import com.media6.hive2rdbms.util.HiveUtil; public class ShowCreateTable extends Configured implements Tool { public static String DB_NAME="db.name"; public static String TABLE_NAME="table.name"; @Override public int run(String[] args) throws Exception { Configuration config = getConf(); JobConf conf = new JobConf(config,ShowCreateTable.class); GenericOptionsParser parser = new GenericOptionsParser(conf, args); for (String arg: args){ if (arg.contains("=")){ String vname = arg.substring(0,arg.indexOf('=')); String vval = arg.substring(arg.indexOf('=')+1); conf.set( vname,vval.replace("\"", "") ); } } HiveUtil hu = new HiveUtil(); if (conf.get(TABLE_NAME)==null ){ System.err.println("Required props: -D "+TABLE_NAME); System.err.println("Optinal props: -D "+DB_NAME ); return 1; } System.out.println( hu.showCreateTable(conf.get(DB_NAME, "default"), conf.get(TABLE_NAME)) ); return 0; } public static void main(String[] args) throws Exception { int ret = ToolRunner.run(new Configuration(), new ShowCreateTable(), args); System.exit(ret); } } package com.media6.hive2rdbms.util; import java.io.File; import java.io.PrintStream; import java.io.UnsupportedEncodingException; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.hive.conf.HiveConf; import org.apache.hadoop.hive.metastore.HiveMetaStoreClient; import org.apache.hadoop.hive.metastore.api.FieldSchema; import org.apache.hadoop.hive.metastore.api.MetaException; import org.apache.hadoop.hive.metastore.api.NoSuchObjectException; import org.apache.hadoop.hive.metastore.api.Schema; import org.apache.hadoop.hive.metastore.api.SerDeInfo; import org.apache.hadoop.hive.metastore.api.StorageDescriptor; import org.apache.hadoop.hive.ql.Driver; import org.apache.hadoop.hive.ql.exec.Utilities; import org.apache.hadoop.hive.ql.processors.CommandProcessor; import org.apache.hadoop.hive.ql.processors.CommandProcessorFactory; import org.apache.hadoop.hive.ql.session.SessionState; import org.apache.hadoop.hive.shims.ShimLoader; import org.apache.hadoop.util.ReflectionUtils; import org.apache.thrift.TException; import org.apache.hadoop.hive.cli.CliDriver; import org.apache.hadoop.hive.cli.CliSessionState; import org.apache.hadoop.hive.cli.OptionsProcessor; import com.media6.hive2rdbms.common.Hive2RdbmsConf; public class HiveUtil { public String hiveToMysqlType(String type){ if (type.equalsIgnoreCase("string")){ return " VARCHAR(255) "; } return type; } private String mysqlToHiveType(int type) { if (type == java.sql.Types.SMALLINT) { return "INT"; } if (type == java.sql.Types.TINYINT) { return "INT"; } if (type == java.sql.Types.INTEGER) { return "INT"; } if (type == java.sql.Types.BIGINT) { return "INT"; } if (type == java.sql.Types.DECIMAL) { return "DOUBLE"; } if (type == java.sql.Types.DOUBLE) { return "DOUBLE"; } if (type == java.sql.Types.FLOAT) { return "DOUBLE"; } if (type == java.sql.Types.VARCHAR) { return "STRING"; } if (type == java.sql.Types.CHAR) { return "STRING"; } if (type == java.sql.Types.CLOB) { return "STRING"; } throw new UnsupportedOperationException( "do not know what to do with sql.type " + type); } public List resultSetFieldList(String cmd, Configuration h2conf){ int ret=0; SessionState.initHiveLog4j(); SessionState ss = new SessionState(new HiveConf(SessionState.class)); HiveConf conf = ss.getConf(); SessionState.start(ss); if (h2conf.get(Hive2RdbmsConf.DISTRIBUTED_CACHE_FILES) != null){ //conf.set("hive.aux.jars.path","file:///opt/hive-0.6.0/auxlib/hivelib-1.0.30.jar"); conf.set("hive.aux.jars.path",h2conf.get(Hive2RdbmsConf.DISTRIBUTED_CACHE_FILES)); } String cmd_trimmed = cmd.trim(); String[] tokens = cmd_trimmed.split("\\s+"); String cmd_1 = cmd_trimmed.substring(tokens[0].length()).trim(); CommandProcessor proc = null; try { proc = CommandProcessorFactory.get(tokens[0], (HiveConf) conf ); } catch (NoSuchMethodError e){ try { Class c = Class.forName("org.apache.hadoop.hive.ql.processors.CommandProcessorFactory"); Method m = c.getMethod("get", new Class [] { String.class } ); proc =(CommandProcessor) m.invoke(null, new Object[]{ tokens[0]}); } catch (Exception e1) { e1.printStackTrace(); } } if (proc instanceof Driver) { Driver driver = (Driver) proc; int res = driver.compile(cmd); Schema sch = driver.getSchema(); List fields = sch.getFieldSchemas(); return fields; } else { ret = proc.run(cmd_1).getResponseCode(); } return null; } public String generateSimpleHiveCreateTableString( String tableName,String fields, String driver, String connectionUrl ){ StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE " + tableName); sb.append(" ( "); try { try { Class.forName(driver); } catch (ClassNotFoundException e) { throw new RuntimeException("can not find driver " + driver, e); } Connection conn = DriverManager.getConnection(connectionUrl); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT "+fields+" FROM "+tableName+" limit 1"); ResultSetMetaData rsmd = rs.getMetaData(); List columnNamesAndTypes = new ArrayList(); for (int i = 0; i < rsmd.getColumnCount(); i++) { columnNamesAndTypes.add(rsmd.getColumnName(i + 1) + " " + mysqlToHiveType(rsmd.getColumnType(i + 1))); } sb.append(HiveUtil.join(columnNamesAndTypes, " , ")); sb.append(" ) "); conn.close(); } catch (SQLException ex) { throw new RuntimeException("SQL Exception ", ex); } return sb.toString(); } public String genateHiveCreateTableString(String tableName, String query, String driver, String connectionUrl) { StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE " + tableName); sb.append(" ( "); try { try { Class.forName(driver); } catch (ClassNotFoundException e) { throw new RuntimeException("can not find driver " + driver, e); } Connection conn = DriverManager.getConnection(connectionUrl); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); List columnNamesAndTypes = new ArrayList(); for (int i = 0; i < rsmd.getColumnCount(); i++) { columnNamesAndTypes.add(rsmd.getColumnName(i + 1) + " " + mysqlToHiveType(rsmd.getColumnType(i + 1))); } sb.append(HiveUtil.join(columnNamesAndTypes, " , ")); sb.append(" ) "); conn.close(); } catch (SQLException ex) { throw new RuntimeException("SQL Exception ", ex); } return sb.toString(); } public static String join(List s, String delimiter) { int capacity = 0; int delimLength = delimiter.length(); Iterator iter = s.iterator(); if (iter.hasNext()) { capacity += iter.next().length() + delimLength; } StringBuilder buffer = new StringBuilder(capacity); iter = s.iterator(); if (iter.hasNext()) { buffer.append(iter.next()); while (iter.hasNext()) { buffer.append(delimiter); buffer.append(iter.next()); } } return buffer.toString(); } public int doHiveCommand(String cmd, Configuration h2conf ){ int ret=0; SessionState.initHiveLog4j(); SessionState ss = new SessionState(new HiveConf(SessionState.class)); HiveConf conf = ss.getConf(); SessionState.start(ss); if (h2conf.get(Hive2RdbmsConf.DISTRIBUTED_CACHE_FILES) != null){ //conf.set("hive.aux.jars.path","file:///opt/hive-0.6.0/auxlib/hivelib-1.0.30.jar"); conf.set("hive.aux.jars.path",h2conf.get(Hive2RdbmsConf.DISTRIBUTED_CACHE_FILES)); } String cmd_trimmed = cmd.trim(); String[] tokens = cmd_trimmed.split("\\s+"); String cmd_1 = cmd_trimmed.substring(tokens[0].length()).trim(); //this is changed in trunk CommandProcessor proc = null; try { proc = CommandProcessorFactory.get(tokens[0], (HiveConf) conf ); } catch (NoSuchMethodError e){ try { Class c = Class.forName("org.apache.hadoop.hive.ql.processors.CommandProcessorFactory"); Method m = c.getMethod("get", new Class [] { String.class } ); proc =(CommandProcessor) m.invoke(null, new Object[]{ tokens[0]}); } catch (Exception e1) { //many things could go wrong here e1.printStackTrace(); } } if (proc instanceof Driver) { ret = proc.run(cmd).getResponseCode(); } else { ret = proc.run(cmd_1).getResponseCode(); } return ret; } public String getLocationForTable(String db, String table) { HiveConf hiveConf = new HiveConf(SessionState.class); HiveMetaStoreClient client = null; try { client = new HiveMetaStoreClient(hiveConf); } catch (MetaException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } org.apache.hadoop.hive.metastore.api.Table t = null; try { t = client.getTable(db, table); } catch (MetaException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } catch (TException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } catch (NoSuchObjectException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } StorageDescriptor sd = t.getSd(); return sd.getLocation(); } public String showCreateTable(String db, String table){ HiveConf hiveConf = new HiveConf(SessionState.class); HiveMetaStoreClient client = null; try { client = new HiveMetaStoreClient(hiveConf); } catch (MetaException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } org.apache.hadoop.hive.metastore.api.Table t = null; try { t = client.getTable(db, table); } catch (MetaException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } catch (TException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } catch (NoSuchObjectException e) { throw new RuntimeException("getting location for " + db + " " + table, e); } StorageDescriptor sd = t.getSd(); StringBuilder results = new StringBuilder(); results.append("CREATE " ); if (t.getTableType().equalsIgnoreCase("EXTERNAL_TABLE")){ results.append(" EXTERNAL "); } results.append(" TABLE "+t.getTableName() +" ( \n"); //columns first List fsc = sd.getCols(); List columns = new ArrayList(); for (FieldSchema col: fsc){ columns.add( " " +col.getName()+" "+col.getType()+" " ); } results.append( HiveUtil.join(columns, ",\n")); results.append(" ) \n"); //partition columns List partKeys = t.getPartitionKeys(); if (partKeys.size() > 0 ) { results.append(" PARTITIONED BY ( "); List partCols = new ArrayList(); for (FieldSchema field: partKeys){ partCols.add( field.getName() + " " + field.getType() ); // dont forget comments } results.append(HiveUtil.join(partCols, ",\n")); results.append(" ) \n"); } //serde info SerDeInfo ser = sd.getSerdeInfo(); results.append( " ROW FORMAT SERDE '"+ser.getSerializationLib()+"' \n" ); if (ser.getParametersSize() > 0){ results.append( "WITH SERDEPROPERTIES ("); List scolumns = new ArrayList(); for (Map.Entry entry: ser.getParameters().entrySet() ){ if (entry.getKey().equals("field.delim")) { scolumns.add( " '"+entry.getKey()+"'="+charToEscapedOctal(entry.getValue().charAt(0)) ); } else if (entry.getKey().equals("line.delim")) { scolumns.add( " '"+entry.getKey()+"'="+charToEscapedOctal(entry.getValue().charAt(0)) ); } else if (entry.getKey().equals("serialization.format")) { scolumns.add( " '"+entry.getKey()+"'="+charToEscapedOctal(entry.getValue().charAt(0)) ); } else { scolumns.add( " '"+entry.getKey()+"'="+entry.getValue() ); } } results.append( HiveUtil.join(scolumns, ",\n")); results.append(" ) "); } results.append( " STORED AS INPUTFORMAT '"+sd.getInputFormat()+"' \n"); results.append( " OUTPUTFORMAT '"+sd.getOutputFormat()+"' \n" ); results.append( " LOCATION '"+sd.getLocation()+"' \n" ); return results.toString(); } public String charToEscapedOctal(char c){ return "'\\"+ StringUtils.leftPad( Integer.toOctalString( c),3,'0') +"'"; } } hive --service jar /opt/hive2rdbms/hive2rdbms-1.0-SNAPSHOT.jar com.media6.hive2rdbms.job.ShowCreateTable -D db.name=default -D table.name=my_table 2>/dev/null