You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ambari.apache.org by ds...@apache.org on 2013/10/31 19:12:15 UTC
git commit: AMBARI-3642. Create a DDL that keeps the latest 1000 jobs
in the ambari RCA DB (for Oracle and Postgres) (dsen)
Updated Branches:
refs/heads/trunk 2b29a425e -> 18b14d784
AMBARI-3642. Create a DDL that keeps the latest 1000 jobs in the ambari RCA DB (for Oracle and Postgres) (dsen)
Project: http://git-wip-us.apache.org/repos/asf/incubator-ambari/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-ambari/commit/18b14d78
Tree: http://git-wip-us.apache.org/repos/asf/incubator-ambari/tree/18b14d78
Diff: http://git-wip-us.apache.org/repos/asf/incubator-ambari/diff/18b14d78
Branch: refs/heads/trunk
Commit: 18b14d784c78e33e299b30b40b95426bc4d06e7a
Parents: 2b29a42
Author: Dmitry Sen <ds...@hortonworks.com>
Authored: Thu Oct 31 20:12:07 2013 +0200
Committer: Dmitry Sen <ds...@hortonworks.com>
Committed: Thu Oct 31 20:12:07 2013 +0200
----------------------------------------------------------------------
.../main/resources/Ambari-DDL-Oracle-CREATE.sql | 10 +--
.../resources/Ambari-DDL-Postgres-CREATE.sql | 10 +--
.../Ambari-DDL-Postgres-REMOTE-CREATE.sql | 12 +--
.../resources/AmbariRCA-DML-Oracle-CLEANUP.sql | 31 ++++++++
.../AmbariRCA-DML-Postgres-CLEANUP.sql | 29 ++++++++
.../ddl/AmbariRCA-DDL-Oracle-UPGRADE.sql | 77 ++++++++++++++++++++
.../ddl/AmbariRCA-DDL-Postgres-UPGRADE.sql | 37 ++++++++++
7 files changed, 190 insertions(+), 16 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
index 843daff..c8d0065 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql
@@ -108,7 +108,7 @@ CREATE TABLE workflow (
inputBytes INTEGER, outputBytes INTEGER,
duration INTEGER,
PRIMARY KEY (workflowId),
- FOREIGN KEY (parentWorkflowId) REFERENCES workflow(workflowId)
+ FOREIGN KEY (parentWorkflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
CREATE TABLE job (
@@ -122,7 +122,7 @@ CREATE TABLE job (
mapCounters VARCHAR2(4000), reduceCounters VARCHAR2(4000), jobCounters VARCHAR2(4000),
inputBytes INTEGER, outputBytes INTEGER,
PRIMARY KEY(jobId),
- FOREIGN KEY(workflowId) REFERENCES workflow(workflowId)
+ FOREIGN KEY(workflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
CREATE TABLE task (
@@ -130,7 +130,7 @@ CREATE TABLE task (
startTime INTEGER, finishTime INTEGER, status VARCHAR2(4000), error CLOB, counters VARCHAR2(4000),
failedAttempt VARCHAR2(4000),
PRIMARY KEY(taskId),
- FOREIGN KEY(jobId) REFERENCES job(jobId)
+ FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE
);
CREATE TABLE taskAttempt (
@@ -141,8 +141,8 @@ CREATE TABLE taskAttempt (
status VARCHAR2(4000), error CLOB, counters VARCHAR2(4000),
inputBytes INTEGER, outputBytes INTEGER,
PRIMARY KEY(taskAttemptId),
- FOREIGN KEY(jobId) REFERENCES job(jobId),
- FOREIGN KEY(taskId) REFERENCES task(taskId)
+ FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE,
+ FOREIGN KEY(taskId) REFERENCES task(taskId) ON DELETE CASCADE
);
CREATE TABLE hdfsEvent (
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
index 925e618..c0dc843 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql
@@ -204,7 +204,7 @@ CREATE TABLE workflow (
inputBytes BIGINT, outputBytes BIGINT,
duration BIGINT,
PRIMARY KEY (workflowId),
- FOREIGN KEY (parentWorkflowId) REFERENCES workflow (workflowId)
+ FOREIGN KEY (parentWorkflowId) REFERENCES workflow (workflowId) ON DELETE CASCADE
);
GRANT ALL PRIVILEGES ON TABLE workflow TO "mapred";
@@ -219,7 +219,7 @@ CREATE TABLE job (
mapCounters TEXT, reduceCounters TEXT, jobCounters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
PRIMARY KEY (jobId),
- FOREIGN KEY (workflowId) REFERENCES workflow (workflowId)
+ FOREIGN KEY (workflowId) REFERENCES workflow (workflowId) ON DELETE CASCADE
);
GRANT ALL PRIVILEGES ON TABLE job TO "mapred";
@@ -228,7 +228,7 @@ CREATE TABLE task (
startTime BIGINT, finishTime BIGINT, status TEXT, error TEXT, counters TEXT,
failedAttempt TEXT,
PRIMARY KEY (taskId),
- FOREIGN KEY (jobId) REFERENCES job (jobId)
+ FOREIGN KEY (jobId) REFERENCES job (jobId) ON DELETE CASCADE
);
GRANT ALL PRIVILEGES ON TABLE task TO "mapred";
@@ -240,8 +240,8 @@ CREATE TABLE taskAttempt (
status TEXT, error TEXT, counters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
PRIMARY KEY (taskAttemptId),
- FOREIGN KEY (jobId) REFERENCES job (jobId),
- FOREIGN KEY (taskId) REFERENCES task (taskId)
+ FOREIGN KEY (jobId) REFERENCES job (jobId) ON DELETE CASCADE,
+ FOREIGN KEY (taskId) REFERENCES task (taskId) ON DELETE CASCADE
);
GRANT ALL PRIVILEGES ON TABLE taskAttempt TO "mapred";
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql b/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql
index 00eddc8..0b749a3 100644
--- a/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql
+++ b/ambari-server/src/main/resources/Ambari-DDL-Postgres-REMOTE-CREATE.sql
@@ -120,7 +120,7 @@ CREATE TABLE workflow (
inputBytes BIGINT, outputBytes BIGINT,
duration BIGINT,
PRIMARY KEY (workflowId),
- FOREIGN KEY (parentWorkflowId) REFERENCES workflow(workflowId)
+ FOREIGN KEY (parentWorkflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
CREATE TABLE job (
@@ -134,7 +134,7 @@ CREATE TABLE job (
mapCounters TEXT, reduceCounters TEXT, jobCounters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
PRIMARY KEY(jobId),
- FOREIGN KEY(workflowId) REFERENCES workflow(workflowId)
+ FOREIGN KEY(workflowId) REFERENCES workflow(workflowId) ON DELETE CASCADE
);
CREATE TABLE task (
@@ -142,7 +142,7 @@ CREATE TABLE task (
startTime BIGINT, finishTime BIGINT, status TEXT, error TEXT, counters TEXT,
failedAttempt TEXT,
PRIMARY KEY(taskId),
- FOREIGN KEY(jobId) REFERENCES job(jobId)
+ FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE
);
CREATE TABLE taskAttempt (
@@ -153,8 +153,8 @@ CREATE TABLE taskAttempt (
status TEXT, error TEXT, counters TEXT,
inputBytes BIGINT, outputBytes BIGINT,
PRIMARY KEY(taskAttemptId),
- FOREIGN KEY(jobId) REFERENCES job(jobId),
- FOREIGN KEY(taskId) REFERENCES task(taskId)
+ FOREIGN KEY(jobId) REFERENCES job(jobId) ON DELETE CASCADE,
+ FOREIGN KEY(taskId) REFERENCES task(taskId) ON DELETE CASCADE
);
CREATE TABLE hdfsEvent (
@@ -183,4 +183,4 @@ CREATE TABLE clusterEvent (
service TEXT, status TEXT,
error TEXT, data TEXT ,
host TEXT, rack TEXT
-);
\ No newline at end of file
+);
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/AmbariRCA-DML-Oracle-CLEANUP.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/AmbariRCA-DML-Oracle-CLEANUP.sql b/ambari-server/src/main/resources/AmbariRCA-DML-Oracle-CLEANUP.sql
new file mode 100644
index 0000000..1f0f3c2
--- /dev/null
+++ b/ambari-server/src/main/resources/AmbariRCA-DML-Oracle-CLEANUP.sql
@@ -0,0 +1,31 @@
+--
+-- 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.
+--
+-- Cleanup info about old MR jobs. Keep &1 latest MR job info's
+-- &1 - how many latest MR job infos to keep
+
+DELETE FROM workflow
+WHERE workflowid IN (
+ SELECT workflowid
+ FROM (
+ SELECT workflowid, row_number() OVER (ORDER BY lastupdatetime ) AS rnum, count(1) OVER() AS tnum
+ FROM workflow
+ )
+ WHERE rnum <= tnum - &1
+);
+
+COMMIT;
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/AmbariRCA-DML-Postgres-CLEANUP.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/AmbariRCA-DML-Postgres-CLEANUP.sql b/ambari-server/src/main/resources/AmbariRCA-DML-Postgres-CLEANUP.sql
new file mode 100644
index 0000000..3f07096
--- /dev/null
+++ b/ambari-server/src/main/resources/AmbariRCA-DML-Postgres-CLEANUP.sql
@@ -0,0 +1,29 @@
+--
+-- 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.
+--
+
+-- RCA cleanup script
+-- :keep_latest - how many latest MR jobs to keep in RCA
+
+DELETE FROM workflow
+WHERE workflowid NOT IN (
+ SELECT workflowid
+ FROM workflow
+ ORDER BY lastupdatetime
+ DESC
+ LIMIT :keep_latest
+);
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Oracle-UPGRADE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Oracle-UPGRADE.sql b/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Oracle-UPGRADE.sql
new file mode 100644
index 0000000..f402768
--- /dev/null
+++ b/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Oracle-UPGRADE.sql
@@ -0,0 +1,77 @@
+--
+-- 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.
+--
+
+
+
+-- Removing current foreign key constraints.
+DECLARE
+ l_statement VARCHAR2(32676);
+BEGIN
+ SELECT 'ALTER TABLE WORKFLOW DROP CONSTRAINT ' || constraint_name
+ INTO l_statement
+ FROM user_cons_columns
+ WHERE table_name = 'WORKFLOW' AND column_name = 'PARENTWORKFLOWID';
+ EXECUTE IMMEDIATE l_statement;
+
+ SELECT 'ALTER TABLE JOB DROP CONSTRAINT ' || constraint_name
+ INTO l_statement
+ FROM user_cons_columns
+ WHERE table_name = 'JOB' AND column_name = 'WORKFLOWID';
+ EXECUTE IMMEDIATE l_statement;
+
+ SELECT 'ALTER TABLE TASK DROP CONSTRAINT ' || constraint_name
+ INTO l_statement
+ FROM user_cons_columns
+ WHERE table_name = 'TASK' AND column_name = 'JOBID';
+ EXECUTE IMMEDIATE l_statement;
+
+ SELECT 'ALTER TABLE TASKATTEMPT DROP CONSTRAINT ' || constraint_name
+ INTO l_statement
+ FROM user_cons_columns
+ WHERE table_name = 'TASKATTEMPT' AND column_name = 'TASKID';
+ EXECUTE IMMEDIATE l_statement;
+
+ SELECT 'ALTER TABLE TASKATTEMPT DROP CONSTRAINT ' || constraint_name
+ INTO l_statement
+ FROM user_cons_columns
+ WHERE table_name = 'TASKATTEMPT' AND column_name = 'JOBID';
+ EXECUTE IMMEDIATE l_statement;
+
+END;
+/
+
+COMMIT;
+
+--Adding ON DELETE CASCADE foreign key constraints for convenient RCA cleanup
+ALTER TABLE workflow
+ADD FOREIGN KEY(parentworkflowid) REFERENCES workflow(workflowid) ON DELETE CASCADE ;
+
+ALTER TABLE job
+ADD FOREIGN KEY (workflowId) REFERENCES workflow (workflowId) ON DELETE CASCADE;
+
+ALTER TABLE task
+ADD FOREIGN KEY ( jobid ) REFERENCES job ( jobid ) ON DELETE CASCADE;
+
+ALTER TABLE taskAttempt
+ADD FOREIGN KEY (taskid) REFERENCES task(taskid) ON DELETE CASCADE;
+
+ALTER TABLE taskAttempt
+ADD FOREIGN KEY ( jobid ) REFERENCES job ( jobid ) ON DELETE CASCADE;
+
+
+COMMIT;
http://git-wip-us.apache.org/repos/asf/incubator-ambari/blob/18b14d78/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Postgres-UPGRADE.sql
----------------------------------------------------------------------
diff --git a/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Postgres-UPGRADE.sql b/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Postgres-UPGRADE.sql
new file mode 100644
index 0000000..d9a7a75
--- /dev/null
+++ b/ambari-server/src/main/resources/upgrade/ddl/AmbariRCA-DDL-Postgres-UPGRADE.sql
@@ -0,0 +1,37 @@
+--
+-- 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.
+--
+
+
+--Adding ON DELETE CASCADE constrain for convenient RCA cleanup
+ALTER TABLE workflow
+DROP CONSTRAINT workflow_parentworkflowid_fkey,
+ADD FOREIGN KEY(parentworkflowid) REFERENCES workflow(workflowid) ON DELETE CASCADE ;
+
+ALTER TABLE job
+DROP CONSTRAINT job_workflowid_fkey,
+ADD FOREIGN KEY (workflowId) REFERENCES workflow (workflowId) ON DELETE CASCADE;
+
+ALTER TABLE task
+DROP CONSTRAINT task_jobid_fkey,
+ADD FOREIGN KEY ( jobid ) REFERENCES job ( jobid ) ON DELETE CASCADE;
+
+ALTER TABLE taskAttempt
+DROP CONSTRAINT taskattempt_taskid_fkey,
+DROP CONSTRAINT taskattempt_jobid_fkey,
+ADD FOREIGN KEY (taskid) REFERENCES task(taskid) ON DELETE CASCADE,
+ADD FOREIGN KEY ( jobid ) REFERENCES job ( jobid ) ON DELETE CASCADE;