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 <[email protected]> Authored: Thu Oct 31 20:12:07 2013 +0200 Committer: Dmitry Sen <[email protected]> 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;
