Java tutorial
/* * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You under the Apache License, Version 2.0 * (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package gobblin.metastore.database; import javax.sql.DataSource; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.AbstractMap; import java.util.Arrays; import java.util.Calendar; import java.util.List; import java.util.Map; import java.util.Set; import java.util.TimeZone; import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.base.Optional; import com.google.common.base.Preconditions; import com.google.common.base.Predicate; import com.google.common.base.Strings; import com.google.common.collect.ImmutableList; import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import com.google.common.collect.Maps; import com.google.common.collect.Sets; import com.linkedin.data.template.GetMode; import com.linkedin.data.template.StringMap; import gobblin.metastore.JobHistoryStore; import gobblin.rest.JobExecutionInfo; import gobblin.rest.JobExecutionQuery; import gobblin.rest.JobStateEnum; import gobblin.rest.LauncherTypeEnum; import gobblin.rest.Metric; import gobblin.rest.MetricArray; import gobblin.rest.MetricTypeEnum; import gobblin.rest.QueryListType; import gobblin.rest.Table; import gobblin.rest.TableTypeEnum; import gobblin.rest.TaskExecutionInfo; import gobblin.rest.TaskExecutionInfoArray; import gobblin.rest.TaskStateEnum; import gobblin.rest.TimeRange; /** * An implementation of {@link JobHistoryStore} backed by MySQL. * * <p> * The DDLs for the MySQL job history store can be found under metastore/src/main/resources. * </p> * * @author Yinan Li */ @SupportedDatabaseVersion(isDefault = false, version = "1.0.1") public class DatabaseJobHistoryStoreV101 implements VersionedDatabaseJobHistoryStore { private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseJobHistoryStoreV101.class); private static final String JOB_EXECUTION_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_job_executions (job_name,job_id,start_time,end_time,duration,state," + "launched_tasks,completed_tasks,launcher_type,tracking_url) VALUES(?,?,?,?,?,?,?,?,?,?)" + " ON DUPLICATE KEY UPDATE start_time=VALUES(start_time),end_time=VALUES(end_time)," + "duration=VALUES(duration),state=VALUES(state),launched_tasks=VALUES(launched_tasks)," + "completed_tasks=VALUES(completed_tasks),launcher_type=VALUES(launcher_type)," + "tracking_url=VALUES(tracking_url)"; private static final String TASK_EXECUTION_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_task_executions (task_id,job_id,start_time,end_time,duration," + "state,failure_exception,low_watermark,high_watermark,table_namespace,table_name,table_type) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE start_time=VALUES(start_time)," + "end_time=VALUES(end_time),duration=VALUES(duration),state=VALUES(state)," + "failure_exception=VALUES(failure_exception),low_watermark=VALUES(low_watermark)," + "high_watermark=VALUES(high_watermark),table_namespace=VALUES(table_namespace)," + "table_name=VALUES(table_name),table_type=VALUES(table_type)"; private static final String JOB_METRIC_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_job_metrics (job_id,metric_group,metric_name," + "metric_type,metric_value) VALUES(?,?,?,?,?) ON DUPLICATE KEY UPDATE " + "metric_value=VALUES(metric_value)"; private static final String TASK_METRIC_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_task_metrics (task_id,metric_group,metric_name," + "metric_type,metric_value) VALUES(?,?,?,?,?) ON DUPLICATE KEY UPDATE " + "metric_value=VALUES(metric_value)"; private static final String JOB_PROPERTY_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_job_properties (job_id,property_key,property_value) VALUES(?,?,?)" + " ON DUPLICATE KEY UPDATE property_value=VALUES(property_value)"; private static final String TASK_PROPERTY_UPSERT_STATEMENT_TEMPLATE = "INSERT INTO gobblin_task_properties (task_id,property_key,property_value) VALUES(?,?,?)" + " ON DUPLICATE KEY UPDATE property_value=VALUES(property_value)"; private static final String LIST_DISTINCT_JOB_EXECUTION_QUERY_TEMPLATE = "SELECT j.job_id FROM gobblin_job_executions j, " + "(SELECT MAX(last_modified_ts) AS most_recent_ts, job_name " + "FROM gobblin_job_executions GROUP BY job_name) max_results " + "WHERE j.job_name = max_results.job_name AND j.last_modified_ts = max_results.most_recent_ts"; private static final String LIST_RECENT_JOB_EXECUTION_QUERY_TEMPLATE = "SELECT job_id FROM gobblin_job_executions"; private static final String JOB_NAME_QUERY_BY_TABLE_STATEMENT_TEMPLATE = "SELECT j.job_name FROM gobblin_job_executions j, gobblin_task_executions t " + "WHERE j.job_id=t.job_id AND %s GROUP BY j.job_name"; private static final String JOB_ID_QUERY_BY_JOB_NAME_STATEMENT_TEMPLATE = "SELECT job_id FROM gobblin_job_executions WHERE job_name=?"; private static final String JOB_EXECUTION_QUERY_BY_JOB_ID_STATEMENT_TEMPLATE = "SELECT * FROM gobblin_job_executions WHERE job_id IN (%s)"; private static final String TASK_EXECUTION_QUERY_STATEMENT_TEMPLATE = "SELECT * FROM gobblin_task_executions WHERE job_id IN (%s)"; private static final String JOB_METRIC_QUERY_STATEMENT_TEMPLATE = "SELECT job_id,metric_group,metric_name,metric_type,metric_value FROM gobblin_job_metrics WHERE job_id IN (%s)"; private static final String TASK_METRIC_QUERY_STATEMENT_TEMPLATE = "SELECT job_id,m.task_id,metric_group,metric_name,metric_type,metric_value FROM gobblin_task_metrics m JOIN gobblin_task_executions t ON t.task_id = m.task_id WHERE job_id IN (%s)"; private static final String JOB_PROPERTY_QUERY_STATEMENT_TEMPLATE = "SELECT job_id,property_key,property_value FROM gobblin_job_properties WHERE job_id IN (%s)"; private static final String TASK_PROPERTY_QUERY_STATEMENT_TEMPLATE = "SELECT job_id,p.task_id,property_key,property_value FROM gobblin_task_properties p JOIN gobblin_task_executions t ON t.task_id = p.task_id WHERE job_id IN (%s)"; private DataSource dataSource; @Override public void init(DataSource dataSource) { this.dataSource = dataSource; } @Override public synchronized void put(JobExecutionInfo jobExecutionInfo) throws IOException { Optional<Connection> connectionOptional = Optional.absent(); try { connectionOptional = Optional.of(getConnection()); Connection connection = connectionOptional.get(); connection.setAutoCommit(false); // Insert or update job execution information upsertJobExecutionInfo(connection, jobExecutionInfo); upsertJobMetrics(connection, jobExecutionInfo); upsertJobProperties(connection, jobExecutionInfo); // Insert or update task execution information if (jobExecutionInfo.hasTaskExecutions()) { upsertTaskExecutionInfos(connection, jobExecutionInfo.getTaskExecutions()); upsertTaskMetrics(connection, jobExecutionInfo.getTaskExecutions()); Optional<StringMap> jobProperties = Optional.absent(); if (jobExecutionInfo.hasJobProperties()) { jobProperties = Optional.of(jobExecutionInfo.getJobProperties()); } upsertTaskProperties(connection, jobProperties, jobExecutionInfo.getTaskExecutions()); } connection.commit(); } catch (SQLException se) { LOGGER.error("Failed to put a new job execution information record", se); if (connectionOptional.isPresent()) { try { connectionOptional.get().rollback(); } catch (SQLException se1) { LOGGER.error("Failed to rollback", se1); } } throw new IOException(se); } finally { if (connectionOptional.isPresent()) { try { connectionOptional.get().close(); } catch (SQLException se) { LOGGER.error("Failed to close connection", se); } } } } @Override public synchronized List<JobExecutionInfo> get(JobExecutionQuery query) throws IOException { Preconditions.checkArgument(query.hasId() && query.hasIdType()); Optional<Connection> connectionOptional = Optional.absent(); try { connectionOptional = Optional.of(getConnection()); Connection connection = connectionOptional.get(); switch (query.getIdType()) { case JOB_ID: return processQueryByIds(connection, query, Filter.MISSING, Lists.newArrayList(query.getId().getString())); case JOB_NAME: return processQueryByJobName(connection, query.getId().getString(), query, Filter.MISSING); case TABLE: return processQueryByTable(connection, query); case LIST_TYPE: return processListQuery(connection, query); default: throw new IOException("Unsupported query ID type: " + query.getIdType().name()); } } catch (SQLException se) { LOGGER.error("Failed to execute query: " + query, se); throw new IOException(se); } finally { if (connectionOptional.isPresent()) { try { connectionOptional.get().close(); } catch (SQLException se) { LOGGER.error("Failed to close connection", se); } } } } @Override public void close() throws IOException { // Nothing to do } private Connection getConnection() throws SQLException { return this.dataSource.getConnection(); } private void upsertJobExecutionInfo(Connection connection, JobExecutionInfo info) throws SQLException { Preconditions.checkArgument(info.hasJobName()); Preconditions.checkArgument(info.hasJobId()); try (PreparedStatement upsertStatement = connection .prepareStatement(JOB_EXECUTION_UPSERT_STATEMENT_TEMPLATE)) { int index = 0; upsertStatement.setString(++index, info.getJobName()); upsertStatement.setString(++index, info.getJobId()); upsertStatement.setTimestamp(++index, info.hasStartTime() ? new Timestamp(info.getStartTime()) : null, getCalendarUTCInstance()); upsertStatement.setTimestamp(++index, info.hasEndTime() ? new Timestamp(info.getEndTime()) : null, getCalendarUTCInstance()); upsertStatement.setLong(++index, info.hasDuration() ? info.getDuration() : -1); upsertStatement.setString(++index, info.hasState() ? info.getState().name() : null); upsertStatement.setInt(++index, info.hasLaunchedTasks() ? info.getLaunchedTasks() : -1); upsertStatement.setInt(++index, info.hasCompletedTasks() ? info.getCompletedTasks() : -1); upsertStatement.setString(++index, info.hasLauncherType() ? info.getLauncherType().name() : null); upsertStatement.setString(++index, info.hasTrackingUrl() ? info.getTrackingUrl() : null); upsertStatement.executeUpdate(); } } private void upsertTaskExecutionInfos(Connection connection, TaskExecutionInfoArray taskExecutions) throws SQLException { Optional<PreparedStatement> upsertStatement = Optional.absent(); int batchSize = 0; for (TaskExecutionInfo taskExecution : taskExecutions) { if (!upsertStatement.isPresent()) { upsertStatement = Optional .of(connection.prepareStatement(TASK_EXECUTION_UPSERT_STATEMENT_TEMPLATE)); } addTaskExecutionInfoToBatch(upsertStatement.get(), taskExecution); if (batchSize++ > 1000) { executeBatches(upsertStatement); upsertStatement = Optional.absent(); batchSize = 0; } } executeBatches(upsertStatement); } private void upsertJobProperties(Connection connection, JobExecutionInfo jobExecutionInfo) throws SQLException { if (jobExecutionInfo.hasJobProperties()) { Optional<PreparedStatement> upsertStatement = Optional.absent(); int batchSize = 0; for (Map.Entry<String, String> property : jobExecutionInfo.getJobProperties().entrySet()) { if (!upsertStatement.isPresent()) { upsertStatement = Optional .of(connection.prepareStatement(JOB_PROPERTY_UPSERT_STATEMENT_TEMPLATE)); } addPropertyToBatch(upsertStatement.get(), property.getKey(), property.getValue(), jobExecutionInfo.getJobId()); if (batchSize++ > 1000) { executeBatches(upsertStatement); upsertStatement = Optional.absent(); batchSize = 0; } } executeBatches(upsertStatement); } } private void upsertTaskProperties(Connection connection, Optional<StringMap> jobProperties, TaskExecutionInfoArray taskExecutions) throws SQLException { Optional<PreparedStatement> upsertStatement = Optional.absent(); int batchSize = 0; for (TaskExecutionInfo taskExecution : taskExecutions) { if (taskExecution.hasTaskProperties()) { for (Map.Entry<String, String> property : taskExecution.getTaskProperties().entrySet()) { if (!jobProperties.isPresent() || !jobProperties.get().containsKey(property.getKey()) || !jobProperties.get().get(property.getKey()).equals(property.getValue())) { if (!upsertStatement.isPresent()) { upsertStatement = Optional .of(connection.prepareStatement(TASK_PROPERTY_UPSERT_STATEMENT_TEMPLATE)); } addPropertyToBatch(upsertStatement.get(), property.getKey(), property.getValue(), taskExecution.getTaskId()); if (batchSize++ > 1000) { executeBatches(upsertStatement); upsertStatement = Optional.absent(); batchSize = 0; } } } } } executeBatches(upsertStatement); } private void addPropertyToBatch(PreparedStatement upsertStatement, String key, String value, String id) throws SQLException { Preconditions.checkArgument(!Strings.isNullOrEmpty(id)); Preconditions.checkArgument(!Strings.isNullOrEmpty(key)); Preconditions.checkArgument(!Strings.isNullOrEmpty(value)); int index = 0; upsertStatement.setString(++index, id); upsertStatement.setString(++index, key); upsertStatement.setString(++index, value); upsertStatement.addBatch(); } private void upsertJobMetrics(Connection connection, JobExecutionInfo jobExecutionInfo) throws SQLException { if (jobExecutionInfo.hasMetrics()) { Optional<PreparedStatement> upsertStatement = Optional.absent(); int batchSize = 0; for (Metric metric : jobExecutionInfo.getMetrics()) { if (!upsertStatement.isPresent()) { upsertStatement = Optional .of(connection.prepareStatement(JOB_METRIC_UPSERT_STATEMENT_TEMPLATE)); } addMetricToBatch(upsertStatement.get(), metric, jobExecutionInfo.getJobId()); if (batchSize++ > 1000) { executeBatches(upsertStatement); upsertStatement = Optional.absent(); batchSize = 0; } } executeBatches(upsertStatement); } } private void upsertTaskMetrics(Connection connection, TaskExecutionInfoArray taskExecutions) throws SQLException { Optional<PreparedStatement> upsertStatement = Optional.absent(); int batchSize = 0; for (TaskExecutionInfo taskExecution : taskExecutions) { if (taskExecution.hasMetrics()) { for (Metric metric : taskExecution.getMetrics()) { if (!upsertStatement.isPresent()) { upsertStatement = Optional .of(connection.prepareStatement(TASK_METRIC_UPSERT_STATEMENT_TEMPLATE)); } addMetricToBatch(upsertStatement.get(), metric, taskExecution.getTaskId()); if (batchSize++ > 1000) { executeBatches(upsertStatement); upsertStatement = Optional.absent(); batchSize = 0; } } } } executeBatches(upsertStatement); } private void executeBatches(Optional<PreparedStatement> upsertStatement) throws SQLException { if (upsertStatement.isPresent()) { try { upsertStatement.get().executeBatch(); } finally { upsertStatement.get().close(); } } } private void addMetricToBatch(PreparedStatement upsertStatement, Metric metric, String id) throws SQLException { Preconditions.checkArgument(!Strings.isNullOrEmpty(id)); Preconditions.checkArgument(metric.hasGroup()); Preconditions.checkArgument(metric.hasName()); Preconditions.checkArgument(metric.hasType()); Preconditions.checkArgument(metric.hasValue()); int index = 0; upsertStatement.setString(++index, id); upsertStatement.setString(++index, metric.getGroup()); upsertStatement.setString(++index, metric.getName()); upsertStatement.setString(++index, metric.getType().name()); upsertStatement.setString(++index, metric.getValue()); upsertStatement.addBatch(); } private void addTaskExecutionInfoToBatch(PreparedStatement upsertStatement, TaskExecutionInfo info) throws SQLException { Preconditions.checkArgument(info.hasTaskId()); Preconditions.checkArgument(info.hasJobId()); int index = 0; upsertStatement.setString(++index, info.getTaskId()); upsertStatement.setString(++index, info.getJobId()); upsertStatement.setTimestamp(++index, info.hasStartTime() ? new Timestamp(info.getStartTime()) : null, getCalendarUTCInstance()); upsertStatement.setTimestamp(++index, info.hasEndTime() ? new Timestamp(info.getEndTime()) : null, getCalendarUTCInstance()); upsertStatement.setLong(++index, info.hasDuration() ? info.getDuration() : -1); upsertStatement.setString(++index, info.hasState() ? info.getState().name() : null); upsertStatement.setString(++index, info.hasFailureException() ? info.getFailureException() : null); upsertStatement.setLong(++index, info.hasLowWatermark() ? info.getLowWatermark() : -1); upsertStatement.setLong(++index, info.hasHighWatermark() ? info.getHighWatermark() : -1); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasNamespace() ? info.getTable().getNamespace() : null); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasName() ? info.getTable().getName() : null); upsertStatement.setString(++index, info.hasTable() && info.getTable().hasType() ? info.getTable().getType().name() : null); upsertStatement.addBatch(); } private List<JobExecutionInfo> processQueryByIds(Connection connection, JobExecutionQuery query, Filter tableFilter, List<String> jobIds) throws SQLException { Map<String, JobExecutionInfo> jobExecutionInfos = getJobExecutionInfos(connection, jobIds); addMetricsToJobExecutions(connection, query, jobExecutionInfos); addPropertiesToJobExecutions(connection, query, jobExecutionInfos); addTasksToJobExecutions(connection, query, tableFilter, jobExecutionInfos); return ImmutableList.copyOf(jobExecutionInfos.values()); } private Map<String, JobExecutionInfo> getJobExecutionInfos(Connection connection, List<String> jobIds) throws SQLException { Map<String, JobExecutionInfo> jobExecutionInfos = Maps.newLinkedHashMap(); if (jobIds != null && jobIds.size() > 0) { String template = String.format(JOB_EXECUTION_QUERY_BY_JOB_ID_STATEMENT_TEMPLATE, getInPredicate(jobIds.size())); int index = 1; try (PreparedStatement jobExecutionQueryStatement = connection.prepareStatement(template)) { for (String jobId : jobIds) { jobExecutionQueryStatement.setString(index++, jobId); } try (ResultSet jobRs = jobExecutionQueryStatement.executeQuery()) { while (jobRs.next()) { JobExecutionInfo jobExecutionInfo = resultSetToJobExecutionInfo(jobRs); jobExecutionInfos.put(jobExecutionInfo.getJobId(), jobExecutionInfo); } } } } return jobExecutionInfos; } private void addMetricsToJobExecutions(Connection connection, JobExecutionQuery query, Map<String, JobExecutionInfo> jobExecutionInfos) throws SQLException { if (query.isIncludeJobMetrics() && jobExecutionInfos.size() > 0) { String template = String.format(JOB_METRIC_QUERY_STATEMENT_TEMPLATE, getInPredicate(jobExecutionInfos.size())); int index = 1; try (PreparedStatement jobMetricQueryStatement = connection.prepareStatement(template)) { for (String jobId : jobExecutionInfos.keySet()) { jobMetricQueryStatement.setString(index++, jobId); } try (ResultSet jobMetricRs = jobMetricQueryStatement.executeQuery()) { while (jobMetricRs.next()) { String jobId = jobMetricRs.getString("job_id"); JobExecutionInfo jobExecutionInfo = jobExecutionInfos.get(jobId); MetricArray metricArray = jobExecutionInfo.getMetrics(GetMode.NULL); if (metricArray == null) { metricArray = new MetricArray(); jobExecutionInfo.setMetrics(metricArray); } metricArray.add(resultSetToMetric(jobMetricRs)); } } } } } private void addPropertiesToJobExecutions(Connection connection, JobExecutionQuery query, Map<String, JobExecutionInfo> jobExecutionInfos) throws SQLException { if (jobExecutionInfos.size() > 0) { Set<String> propertyKeys = null; if (query.hasJobProperties()) { propertyKeys = Sets.newHashSet(Iterables.filter(Arrays.asList(query.getJobProperties().split(",")), new Predicate<String>() { @Override public boolean apply(String input) { return !Strings.isNullOrEmpty(input); } })); } if (propertyKeys == null || propertyKeys.size() > 0) { String template = String.format(JOB_PROPERTY_QUERY_STATEMENT_TEMPLATE, getInPredicate(jobExecutionInfos.size())); if (propertyKeys != null && propertyKeys.size() > 0) { template += String.format(" AND property_key IN (%s)", getInPredicate(propertyKeys.size())); } int index = 1; try (PreparedStatement jobPropertiesQueryStatement = connection.prepareStatement(template)) { for (String jobId : jobExecutionInfos.keySet()) { jobPropertiesQueryStatement.setString(index++, jobId); } if (propertyKeys != null && propertyKeys.size() > 0) { for (String propertyKey : propertyKeys) { jobPropertiesQueryStatement.setString(index++, propertyKey); } } try (ResultSet jobPropertiesRs = jobPropertiesQueryStatement.executeQuery()) { while (jobPropertiesRs.next()) { String jobId = jobPropertiesRs.getString("job_id"); JobExecutionInfo jobExecutionInfo = jobExecutionInfos.get(jobId); StringMap jobProperties = jobExecutionInfo.getJobProperties(GetMode.NULL); if (jobProperties == null) { jobProperties = new StringMap(Maps.<String, String>newHashMap()); jobExecutionInfo.setJobProperties(jobProperties); } Map.Entry<String, String> property = resultSetToProperty(jobPropertiesRs); if (propertyKeys == null || propertyKeys.contains(property.getKey())) { jobProperties.put(property.getKey(), property.getValue()); } } } } } } } private void addTasksToJobExecutions(Connection connection, JobExecutionQuery query, Filter tableFilter, Map<String, JobExecutionInfo> jobExecutionInfos) throws SQLException { Map<String, Map<String, TaskExecutionInfo>> tasksExecutions = getTasksForJobExecutions(connection, query, tableFilter, jobExecutionInfos); addMetricsToTasks(connection, query, tableFilter, tasksExecutions); addPropertiesToTasks(connection, query, tableFilter, tasksExecutions); for (Map.Entry<String, Map<String, TaskExecutionInfo>> taskExecution : tasksExecutions.entrySet()) { JobExecutionInfo jobExecutionInfo = jobExecutionInfos.get(taskExecution.getKey()); TaskExecutionInfoArray taskExecutionInfos = new TaskExecutionInfoArray(); for (TaskExecutionInfo taskExecutionInfo : taskExecution.getValue().values()) { taskExecutionInfos.add(taskExecutionInfo); } jobExecutionInfo.setTaskExecutions(taskExecutionInfos); } } private Map<String, Map<String, TaskExecutionInfo>> getTasksForJobExecutions(Connection connection, JobExecutionQuery query, Filter tableFilter, Map<String, JobExecutionInfo> jobExecutionInfos) throws SQLException { Map<String, Map<String, TaskExecutionInfo>> taskExecutionInfos = Maps.newLinkedHashMap(); if (query.isIncludeTaskExecutions() && jobExecutionInfos.size() > 0) { String template = String.format(TASK_EXECUTION_QUERY_STATEMENT_TEMPLATE, getInPredicate(jobExecutionInfos.size())); if (tableFilter.isPresent()) { template += " AND " + tableFilter; } int index = 1; try (PreparedStatement taskExecutionQueryStatement = connection.prepareStatement(template)) { for (String jobId : jobExecutionInfos.keySet()) { taskExecutionQueryStatement.setString(index++, jobId); } if (tableFilter.isPresent()) { tableFilter.addParameters(taskExecutionQueryStatement, index); } try (ResultSet taskRs = taskExecutionQueryStatement.executeQuery()) { while (taskRs.next()) { TaskExecutionInfo taskExecutionInfo = resultSetToTaskExecutionInfo(taskRs); if (!taskExecutionInfos.containsKey(taskExecutionInfo.getJobId())) { taskExecutionInfos.put(taskExecutionInfo.getJobId(), Maps.<String, TaskExecutionInfo>newLinkedHashMap()); } taskExecutionInfos.get(taskExecutionInfo.getJobId()).put(taskExecutionInfo.getTaskId(), taskExecutionInfo); } } } } return taskExecutionInfos; } private void addMetricsToTasks(Connection connection, JobExecutionQuery query, Filter tableFilter, Map<String, Map<String, TaskExecutionInfo>> taskExecutionInfos) throws SQLException { if (query.isIncludeTaskMetrics() && taskExecutionInfos.size() > 0) { int index = 1; String template = String.format(TASK_METRIC_QUERY_STATEMENT_TEMPLATE, getInPredicate(taskExecutionInfos.size())); if (tableFilter.isPresent()) { template += " AND t." + tableFilter; } try (PreparedStatement taskMetricQueryStatement = connection.prepareStatement(template)) { for (String jobId : taskExecutionInfos.keySet()) { taskMetricQueryStatement.setString(index++, jobId); } if (tableFilter.isPresent()) { tableFilter.addParameters(taskMetricQueryStatement, index); } try (ResultSet taskMetricRs = taskMetricQueryStatement.executeQuery()) { while (taskMetricRs.next()) { String jobId = taskMetricRs.getString("job_id"); String taskId = taskMetricRs.getString("task_id"); TaskExecutionInfo taskExecutionInfo = taskExecutionInfos.get(jobId).get(taskId); MetricArray metricsArray = taskExecutionInfo.getMetrics(GetMode.NULL); if (metricsArray == null) { metricsArray = new MetricArray(); taskExecutionInfo.setMetrics(metricsArray); } metricsArray.add(resultSetToMetric(taskMetricRs)); } } } } } private void addPropertiesToTasks(Connection connection, JobExecutionQuery query, Filter tableFilter, Map<String, Map<String, TaskExecutionInfo>> taskExecutionInfos) throws SQLException { if (taskExecutionInfos.size() > 0) { Set<String> propertyKeys = null; if (query.hasTaskProperties()) { propertyKeys = Sets.newHashSet(Iterables.filter(Arrays.asList(query.getTaskProperties().split(",")), new Predicate<String>() { @Override public boolean apply(String input) { return !Strings.isNullOrEmpty(input); } })); } if (propertyKeys == null || propertyKeys.size() > 0) { String template = String.format(TASK_PROPERTY_QUERY_STATEMENT_TEMPLATE, getInPredicate(taskExecutionInfos.size())); if (propertyKeys != null && propertyKeys.size() > 0) { template += String.format("AND property_key IN (%s)", getInPredicate(propertyKeys.size())); } if (tableFilter.isPresent()) { template += " AND t." + tableFilter; } int index = 1; try (PreparedStatement taskPropertiesQueryStatement = connection.prepareStatement(template)) { for (String jobId : taskExecutionInfos.keySet()) { taskPropertiesQueryStatement.setString(index++, jobId); } if (propertyKeys != null && propertyKeys.size() > 0) { for (String propertyKey : propertyKeys) { taskPropertiesQueryStatement.setString(index++, propertyKey); } } if (tableFilter.isPresent()) { tableFilter.addParameters(taskPropertiesQueryStatement, index); } try (ResultSet taskPropertiesRs = taskPropertiesQueryStatement.executeQuery()) { while (taskPropertiesRs.next()) { String jobId = taskPropertiesRs.getString("job_id"); String taskId = taskPropertiesRs.getString("task_id"); TaskExecutionInfo taskExecutionInfo = taskExecutionInfos.get(jobId).get(taskId); StringMap taskProperties = taskExecutionInfo.getTaskProperties(GetMode.NULL); if (taskProperties == null) { taskProperties = new StringMap(); taskExecutionInfo.setTaskProperties(taskProperties); } Map.Entry<String, String> property = resultSetToProperty(taskPropertiesRs); if (propertyKeys == null || propertyKeys.contains(property.getKey())) { taskProperties.put(property.getKey(), property.getValue()); } } } } } } } private List<JobExecutionInfo> processQueryByJobName(Connection connection, String jobName, JobExecutionQuery query, Filter tableFilter) throws SQLException { Preconditions.checkArgument(!Strings.isNullOrEmpty(jobName)); // Construct the query for job IDs by a given job name Filter filter = Filter.MISSING; String jobIdByNameQuery = JOB_ID_QUERY_BY_JOB_NAME_STATEMENT_TEMPLATE; if (query.hasTimeRange()) { // Add time range filter if applicable try { filter = constructTimeRangeFilter(query.getTimeRange()); if (filter.isPresent()) { jobIdByNameQuery += " AND " + filter; } } catch (ParseException pe) { LOGGER.error("Failed to parse the query time range", pe); throw new SQLException(pe); } } // Add ORDER BY jobIdByNameQuery += " ORDER BY created_ts DESC"; // Query job IDs by the given job name List<String> jobIds = Lists.newArrayList(); try (PreparedStatement queryStatement = connection.prepareStatement(jobIdByNameQuery)) { int limit = query.getLimit(); if (limit > 0) { queryStatement.setMaxRows(limit); } queryStatement.setString(1, jobName); if (filter.isPresent()) { filter.addParameters(queryStatement, 2); } try (ResultSet rs = queryStatement.executeQuery()) { while (rs.next()) { jobIds.add(rs.getString(1)); } } } return processQueryByIds(connection, query, tableFilter, jobIds); } private List<JobExecutionInfo> processQueryByTable(Connection connection, JobExecutionQuery query) throws SQLException { Preconditions.checkArgument(query.getId().isTable()); Filter tableFilter = constructTableFilter(query.getId().getTable()); // Construct the query for job names by table definition String jobNameByTableQuery = String.format(JOB_NAME_QUERY_BY_TABLE_STATEMENT_TEMPLATE, tableFilter.getFilter()); List<JobExecutionInfo> jobExecutionInfos = Lists.newArrayList(); // Query job names by table definition try (PreparedStatement queryStatement = connection.prepareStatement(jobNameByTableQuery)) { if (tableFilter.isPresent()) { tableFilter.addParameters(queryStatement, 1); } try (ResultSet rs = queryStatement.executeQuery()) { while (rs.next()) { jobExecutionInfos .addAll(processQueryByJobName(connection, rs.getString(1), query, tableFilter)); } } return jobExecutionInfos; } } private List<JobExecutionInfo> processListQuery(Connection connection, JobExecutionQuery query) throws SQLException { Preconditions.checkArgument(query.getId().isQueryListType()); Filter timeRangeFilter = Filter.MISSING; QueryListType queryType = query.getId().getQueryListType(); String listJobExecutionsQuery; if (queryType == QueryListType.DISTINCT) { listJobExecutionsQuery = LIST_DISTINCT_JOB_EXECUTION_QUERY_TEMPLATE; if (query.hasTimeRange()) { try { timeRangeFilter = constructTimeRangeFilter(query.getTimeRange()); if (timeRangeFilter.isPresent()) { listJobExecutionsQuery += " AND " + timeRangeFilter; } } catch (ParseException pe) { LOGGER.error("Failed to parse the query time range", pe); throw new SQLException(pe); } } } else { listJobExecutionsQuery = LIST_RECENT_JOB_EXECUTION_QUERY_TEMPLATE; } listJobExecutionsQuery += " ORDER BY last_modified_ts DESC"; try (PreparedStatement queryStatement = connection.prepareStatement(listJobExecutionsQuery)) { int limit = query.getLimit(); if (limit > 0) { queryStatement.setMaxRows(limit); } if (timeRangeFilter.isPresent()) { timeRangeFilter.addParameters(queryStatement, 1); } try (ResultSet rs = queryStatement.executeQuery()) { List<String> jobIds = Lists.newArrayList(); while (rs.next()) { jobIds.add(rs.getString(1)); } return processQueryByIds(connection, query, Filter.MISSING, jobIds); } } } private JobExecutionInfo resultSetToJobExecutionInfo(ResultSet rs) throws SQLException { JobExecutionInfo jobExecutionInfo = new JobExecutionInfo(); jobExecutionInfo.setJobName(rs.getString("job_name")); jobExecutionInfo.setJobId(rs.getString("job_id")); try { Timestamp startTime = rs.getTimestamp("start_time"); if (startTime != null) { jobExecutionInfo.setStartTime(startTime.getTime()); } } catch (SQLException se) { jobExecutionInfo.setStartTime(0); } try { Timestamp endTime = rs.getTimestamp("end_time"); if (endTime != null) { jobExecutionInfo.setEndTime(endTime.getTime()); } } catch (SQLException se) { jobExecutionInfo.setEndTime(0); } jobExecutionInfo.setDuration(rs.getLong("duration")); String state = rs.getString("state"); if (!Strings.isNullOrEmpty(state)) { jobExecutionInfo.setState(JobStateEnum.valueOf(state)); } jobExecutionInfo.setLaunchedTasks(rs.getInt("launched_tasks")); jobExecutionInfo.setCompletedTasks(rs.getInt("completed_tasks")); String launcherType = rs.getString("launcher_type"); if (!Strings.isNullOrEmpty(launcherType)) { jobExecutionInfo.setLauncherType(LauncherTypeEnum.valueOf(launcherType)); } String trackingUrl = rs.getString("tracking_url"); if (!Strings.isNullOrEmpty(trackingUrl)) { jobExecutionInfo.setTrackingUrl(trackingUrl); } return jobExecutionInfo; } private TaskExecutionInfo resultSetToTaskExecutionInfo(ResultSet rs) throws SQLException { TaskExecutionInfo taskExecutionInfo = new TaskExecutionInfo(); taskExecutionInfo.setTaskId(rs.getString("task_id")); taskExecutionInfo.setJobId(rs.getString("job_id")); try { Timestamp startTime = rs.getTimestamp("start_time"); if (startTime != null) { taskExecutionInfo.setStartTime(startTime.getTime()); } } catch (SQLException se) { taskExecutionInfo.setStartTime(0); } try { Timestamp endTime = rs.getTimestamp("end_time"); if (endTime != null) { taskExecutionInfo.setEndTime(endTime.getTime()); } } catch (SQLException se) { taskExecutionInfo.setEndTime(0); } taskExecutionInfo.setDuration(rs.getLong("duration")); String state = rs.getString("state"); if (!Strings.isNullOrEmpty(state)) { taskExecutionInfo.setState(TaskStateEnum.valueOf(state)); } String failureException = rs.getString("failure_exception"); if (!Strings.isNullOrEmpty(failureException)) { taskExecutionInfo.setFailureException(failureException); } taskExecutionInfo.setLowWatermark(rs.getLong("low_watermark")); taskExecutionInfo.setHighWatermark(rs.getLong("high_watermark")); Table table = new Table(); String namespace = rs.getString("table_namespace"); if (!Strings.isNullOrEmpty(namespace)) { table.setNamespace(namespace); } String name = rs.getString("table_name"); if (!Strings.isNullOrEmpty(name)) { table.setName(name); } String type = rs.getString("table_type"); if (!Strings.isNullOrEmpty(type)) { table.setType(TableTypeEnum.valueOf(type)); } taskExecutionInfo.setTable(table); return taskExecutionInfo; } private Metric resultSetToMetric(ResultSet rs) throws SQLException { Metric metric = new Metric(); metric.setGroup(rs.getString("metric_group")); metric.setName(rs.getString("metric_name")); metric.setType(MetricTypeEnum.valueOf(rs.getString("metric_type"))); metric.setValue(rs.getString("metric_value")); return metric; } private AbstractMap.SimpleEntry<String, String> resultSetToProperty(ResultSet rs) throws SQLException { return new AbstractMap.SimpleEntry<>(rs.getString("property_key"), rs.getString("property_value")); } private Filter constructTimeRangeFilter(TimeRange timeRange) throws ParseException { List<String> values = Lists.newArrayList(); StringBuilder sb = new StringBuilder(); if (!timeRange.hasTimeFormat()) { LOGGER.warn("Skipping the time range filter as there is no time format in: " + timeRange); return Filter.MISSING; } DateFormat dateFormat = new SimpleDateFormat(timeRange.getTimeFormat()); boolean hasStartTime = timeRange.hasStartTime(); if (hasStartTime) { sb.append("start_time>?"); values.add(new Timestamp(dateFormat.parse(timeRange.getStartTime()).getTime()).toString()); } if (timeRange.hasEndTime()) { if (hasStartTime) { sb.append(" AND "); } sb.append("end_time<?"); values.add(new Timestamp(dateFormat.parse(timeRange.getEndTime()).getTime()).toString()); } if (sb.length() > 0) { return new Filter(sb.toString(), values); } return Filter.MISSING; } private Filter constructTableFilter(Table table) { List<String> values = Lists.newArrayList(); StringBuilder sb = new StringBuilder(); boolean hasNamespace = table.hasNamespace(); if (hasNamespace) { sb.append("table_namespace=?"); values.add(table.getNamespace()); } boolean hasName = table.hasName(); if (hasName) { if (hasNamespace) { sb.append(" AND "); } sb.append("table_name=?"); values.add(table.getName()); } if (table.hasType()) { if (hasName) { sb.append(" AND "); } sb.append("table_type=?"); values.add(table.getType().name()); } if (sb.length() > 0) { return new Filter(sb.toString(), values); } return Filter.MISSING; } private static Calendar getCalendarUTCInstance() { return Calendar.getInstance(TimeZone.getTimeZone("UTC")); } private static String getInPredicate(int count) { return StringUtils.join(Iterables.limit(Iterables.cycle("?"), count).iterator(), ","); } }