/** * Initializes the map-part of the job with the appropriate input settings. * * @param job The map-reduce job * @param inputClass the class object implementing DBWritable, which is the Java object holding * tuple fields. * @param inputQuery the input query to select fields. Example : "SELECT f1, f2, f3 FROM Mytable * ORDER BY f1" * @param inputCountQuery the input query that returns the number of records in the table. Example * : "SELECT COUNT(f1) FROM Mytable" * @see #setInput(Job, Class, String, String, String, String...) */ public static void setInput( Job job, Class<? extends DBWritable> inputClass, String inputQuery, String inputCountQuery) { job.setInputFormatClass(DBInputFormat.class); DBConfiguration dbConf = new DBConfiguration(job.getConfiguration()); dbConf.setInputClass(inputClass); dbConf.setInputQuery(inputQuery); dbConf.setInputCountQuery(inputCountQuery); }
private static DBConfiguration setOutput(JobConf job, String tableName) { job.setOutputFormat(DBOutputFormat.class); job.setReduceSpeculativeExecution(false); DBConfiguration dbConf = new DBConfiguration(job); dbConf.setOutputTableName(tableName); return dbConf; }
/** * Initializes the reduce-part of the job with the appropriate output settings * * @param job The job * @param tableName The table to insert data into * @param fieldNames The field names in the table. */ public static void setOutput(JobConf job, String tableName, String... fieldNames) { if (fieldNames.length > 0 && fieldNames[0] != null) { DBConfiguration dbConf = setOutput(job, tableName); dbConf.setOutputFieldNames(fieldNames); } else { if (fieldNames.length > 0) setOutput(job, tableName, fieldNames.length); else throw new IllegalArgumentException("Field names must be greater than 0"); } }
/** * Returns the query for getting the total number of rows, subclasses can override this for custom * behaviour. */ protected String getCountQuery() { if (dbConf.getInputCountQuery() != null) { return dbConf.getInputCountQuery(); } StringBuilder query = new StringBuilder(); query.append("SELECT COUNT(*) FROM " + tableName); if (conditions != null && conditions.length() > 0) query.append(" WHERE " + conditions); return query.toString(); }
/** {@inheritDoc} */ public void setConf(Configuration conf) { dbConf = new DBConfiguration(conf); try { this.connection = dbConf.getConnection(); this.connection.setAutoCommit(false); connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); DatabaseMetaData dbMeta = connection.getMetaData(); this.dbProductName = dbMeta.getDatabaseProductName().toUpperCase(); } catch (Exception ex) { throw new RuntimeException(ex); } tableName = dbConf.getInputTableName(); fieldNames = dbConf.getInputFieldNames(); conditions = dbConf.getInputConditions(); }
/** * Initializes the map-part of the job with the appropriate input settings. * * @param job The map-reduce job * @param inputClass the class object implementing DBWritable, which is the Java object holding * tuple fields. * @param tableName The table to read data from * @param conditions The condition which to select data with, eg. '(updated > 20070101 AND length * > 0)' * @param orderBy the fieldNames in the orderBy clause. * @param fieldNames The field names in the table * @see #setInput(Job, Class, String, String) */ public static void setInput( Job job, Class<? extends DBWritable> inputClass, String tableName, String conditions, String orderBy, String... fieldNames) { job.setInputFormatClass(DBInputFormat.class); DBConfiguration dbConf = new DBConfiguration(job.getConfiguration()); dbConf.setInputClass(inputClass); dbConf.setInputTableName(tableName); dbConf.setInputFieldNames(fieldNames); dbConf.setInputConditions(conditions); dbConf.setInputOrderBy(orderBy); }
public static void runJob(String mysqlJar, String output) throws Exception { Configuration conf = new Configuration(); JobHelper.addJarForJob(conf, mysqlJar); DBConfiguration.configureDB( conf, "com.mysql.jdbc.Driver", "jdbc:mysql://localhost/sqoop_test" + "?user=hip_sqoop_user&password=password"); Job job = new Job(conf); job.setJarByClass(DBImportExportMapReduce.class); job.setMapperClass(Map.class); job.setReducerClass(Reduce.class); job.setInputFormatClass(DBInputFormat.class); job.setOutputFormatClass(DBOutputFormat.class); job.setMapOutputKeyClass(StockRecord.class); job.setMapOutputValueClass(NullWritable.class); job.setOutputKeyClass(StockRecord.class); job.setOutputValueClass(NullWritable.class); job.getConfiguration().setInt("mapred.map.tasks", 4); job.setNumReduceTasks(4); DBInputFormat.setInput( job, StockRecord.class, "select * from stocks", "SELECT COUNT(id) FROM stocks"); DBOutputFormat.setOutput(job, "stocks_export", StockRecord.fields); Path outputPath = new Path(output); FileOutputFormat.setOutputPath(job, outputPath); outputPath.getFileSystem(conf).delete(outputPath, true); job.waitForCompletion(true); }
protected RecordReader<LongWritable, T> createDBRecordReader( DBInputSplit split, Configuration conf) throws IOException { @SuppressWarnings("unchecked") Class<T> inputClass = (Class<T>) (dbConf.getInputClass()); try { // use database product name to determine appropriate record reader. if (dbProductName.startsWith("ORACLE")) { // use Oracle-specific db reader. return new OracleDBRecordReader<T>( split, inputClass, conf, connection, getDBConf(), conditions, fieldNames, tableName); } else if (dbProductName.startsWith("MYSQL")) { // use MySQL-specific db reader. return new MySQLDBRecordReader<T>( split, inputClass, conf, connection, getDBConf(), conditions, fieldNames, tableName); } else { // Generic reader. return new DBRecordReader<T>( split, inputClass, conf, connection, getDBConf(), conditions, fieldNames, tableName); } } catch (SQLException ex) { throw new IOException(ex.getMessage()); } }
public Configuration getConf() { return dbConf.getConf(); }
static { URL_KEY = DBConfiguration.PREFERENCES_URL_KEY; DEFAULTS = DBConfiguration.getDefaults(); }
/** * Initializes the reduce-part of the job with the appropriate output settings * * @param job The job * @param tableName The table to insert data into * @param fieldCount the number of fields in the table. */ public static void setOutput(JobConf job, String tableName, int fieldCount) { DBConfiguration dbConf = setOutput(job, tableName); dbConf.setOutputFieldCount(fieldCount); }
private static void initTable( final Connection connection, final DatabaseTable table, final DBConfiguration dbConfiguration) throws DatabaseException { try { checkIfTableExists(connection, table); LOGGER.trace("table " + table + " appears to exist"); } catch (SQLException e) { // assume error was due to table missing; { final StringBuilder sqlString = new StringBuilder(); sqlString.append("CREATE table ").append(table.toString()).append(" (").append("\n"); sqlString .append(" " + KEY_COLUMN + " ") .append(dbConfiguration.getColumnTypeKey()) .append("(") .append(KEY_COLUMN_LENGTH) .append(") NOT NULL PRIMARY KEY,") .append("\n"); sqlString .append(" " + VALUE_COLUMN + " ") .append(dbConfiguration.getColumnTypeValue()) .append(" "); sqlString.append("\n"); sqlString.append(")").append("\n"); LOGGER.trace( "attempting to execute the following sql statement:\n " + sqlString.toString()); Statement statement = null; try { statement = connection.createStatement(); statement.execute(sqlString.toString()); LOGGER.debug("created table " + table.toString()); } catch (SQLException ex) { LOGGER.error("error creating new table " + table.toString() + ": " + ex.getMessage()); } finally { close(statement); } } { final String indexName = table.toString() + "_IDX"; final StringBuilder sqlString = new StringBuilder(); sqlString.append("CREATE index ").append(indexName); sqlString.append(" ON ").append(table.toString()); sqlString.append(" (").append(KEY_COLUMN).append(")"); Statement statement = null; LOGGER.trace( "attempting to execute the following sql statement:\n " + sqlString.toString()); try { statement = connection.createStatement(); statement.execute(sqlString.toString()); LOGGER.debug("created index " + indexName); } catch (SQLException ex) { LOGGER.error("error creating new index " + indexName + ": " + ex.getMessage()); } finally { close(statement); } } } }
private Connection openDB(final DBConfiguration dbConfiguration) throws DatabaseException { final String connectionURL = dbConfiguration.getConnectionString(); final String jdbcClassName = dbConfiguration.getDriverClassname(); try { final byte[] jdbcDriverBytes = dbConfiguration.getJdbcDriver(); if (jdbcDriverBytes != null) { LOGGER.debug("loading JDBC database driver stored in configuration"); final JarClassLoader jarClassLoader = new JarClassLoader(); jarClassLoader.add(new ByteArrayInputStream(jdbcDriverBytes)); final JclObjectFactory jclObjectFactory = JclObjectFactory.getInstance(); // Create object of loaded class driver = (Driver) jclObjectFactory.create(jarClassLoader, jdbcClassName); LOGGER.debug( "successfully loaded JDBC database driver '" + jdbcClassName + "' from application configuration"); } } catch (Throwable e) { final String errorMsg = "error registering JDBC database driver stored in configuration: " + e.getMessage(); final ErrorInformation errorInformation = new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg); LOGGER.error(errorMsg, e); throw new DatabaseException(errorInformation); } if (driver == null) { try { LOGGER.debug("loading JDBC database driver from classpath: " + jdbcClassName); driver = (Driver) Class.forName(jdbcClassName).newInstance(); LOGGER.debug("successfully loaded JDBC database driver from classpath: " + jdbcClassName); } catch (Throwable e) { final String errorMsg = e.getClass().getName() + " error loading JDBC database driver from classpath: " + e.getMessage(); final ErrorInformation errorInformation = new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg); throw new DatabaseException(errorInformation); } } try { LOGGER.debug("opening connection to database " + connectionURL); final Properties connectionProperties = new Properties(); if (dbConfiguration.getUsername() != null && !dbConfiguration.getUsername().isEmpty()) { connectionProperties.setProperty("user", dbConfiguration.getUsername()); } if (dbConfiguration.getPassword() != null) { connectionProperties.setProperty( "password", dbConfiguration.getPassword().getStringValue()); } final Connection connection = driver.connect(connectionURL, connectionProperties); final Map<PwmAboutProperty, String> debugProps = getConnectionDebugProperties(connection); ; LOGGER.debug( "successfully opened connection to database " + connectionURL + ", properties: " + JsonUtil.serializeMap(debugProps)); connection.setAutoCommit(true); return connection; } catch (Throwable e) { final String errorMsg = "error connecting to database: " + Helper.readHostileExceptionMessage(e); final ErrorInformation errorInformation = new ErrorInformation(PwmError.ERROR_DB_UNAVAILABLE, errorMsg); if (e instanceof IOException) { LOGGER.error(errorInformation); } else { LOGGER.error(errorMsg, e); } throw new DatabaseException(errorInformation); } }