You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ambari.apache.org by yu...@apache.org on 2014/11/14 03:19:44 UTC
[02/29] ambari git commit: AMBARI-8269. Merge branch-windows-dev
changes to trunk. (Jayush Luniya via yusaku)
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/ambari-web/app/views/wizard/controls_view.js
----------------------------------------------------------------------
diff --git a/ambari-web/app/views/wizard/controls_view.js b/ambari-web/app/views/wizard/controls_view.js
index b4e70cb..a4cbf3f 100644
--- a/ambari-web/app/views/wizard/controls_view.js
+++ b/ambari-web/app/views/wizard/controls_view.js
@@ -255,7 +255,24 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
connectionUrl.set('value', "jdbc:oracle:thin:@//" + this.get('hostName') + ":1521/" + this.get('databaseName'));
dbClass.set('value', "oracle.jdbc.driver.OracleDriver");
break;
+ case 'Existing MSSQL Server database with integrated authentication':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName') + ";integratedSecurity=true");
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName'));
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
}
+ var isNotExistingMySQLServer = this.get('serviceConfig.value') !== 'Existing MSSQL Server database with integrated authentication';
+ this.get('categoryConfigsAll').findProperty('name', 'javax.jdo.option.ConnectionUserName').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
+ this.get('categoryConfigsAll').findProperty('name', 'javax.jdo.option.ConnectionPassword').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
} else if (this.get('serviceConfig.serviceName') === 'OOZIE') {
switch (this.get('serviceConfig.value')) {
case 'New Derby Database':
@@ -274,7 +291,44 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
connectionUrl.set('value', "jdbc:oracle:thin:@//" + this.get('hostName') + ":1521/" + this.get('databaseName'));
dbClass.set('value', "oracle.jdbc.driver.OracleDriver");
break;
+ case 'Existing MSSQL Server database with integrated authentication':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName') + ";integratedSecurity=true");
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName'));
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
+ }
+ isNotExistingMySQLServer = this.get('serviceConfig.value') !== 'Existing MSSQL Server database with integrated authentication';
+ this.get('categoryConfigsAll').findProperty('name', 'oozie.service.JPAService.jdbc.username').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
+ this.get('categoryConfigsAll').findProperty('name', 'oozie.service.JPAService.jdbc.password').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
+ } else if (this.get('serviceConfig.serviceName') === 'HDFS') {
+ switch (this.get('serviceConfig.value')) {
+ case 'Existing MSSQL Server database with integrated authentication':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName') + ";integratedSecurity=true");
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ connectionUrl.set('value', "jdbc:sqlserver://" + this.get('hostName') + ";databaseName=" + this.get('databaseName'));
+ dbClass.set('value', "com.microsoft.sqlserver.jdbc.SQLServerDriver");
+ break;
}
+ var isNotExistingMySQLServer = this.get('serviceConfig.value') !== 'Existing MSSQL Server database with integrated authentication';
+ this.get('categoryConfigsAll').findProperty('name', 'sink.dblogin').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
+ this.get('categoryConfigsAll').findProperty('name', 'sink.dbpassword').setProperties({
+ isVisible: isNotExistingMySQLServer,
+ isRequired: isNotExistingMySQLServer
+ });
}
connectionUrl.set('defaultValue', connectionUrl.get('value'));
}
@@ -287,6 +341,8 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
switch (this.get('serviceConfig.serviceName')) {
case 'HIVE':
return this.get('categoryConfigsAll').findProperty('name', 'ambari.hive.db.schema.name').get('value');
+ case 'HDFS':
+ return this.get('categoryConfigsAll').findProperty('name', 'sink.db.schema.name').get('value');
case 'OOZIE':
return this.get('categoryConfigsAll').findProperty('name', 'oozie.db.schema.name').get('value');
default:
@@ -314,13 +370,32 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
case 'Existing Oracle Database':
hostname = this.get('categoryConfigsAll').findProperty('name', 'hive_existing_oracle_host');
break;
+ case 'Existing MSSQL Server database with integrated authentication':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'hive_existing_mssql_server_host');
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'hive_existing_mssql_server_2_host');
+ break;
}
if (hostname) {
returnValue = hostname.get('value');
} else {
returnValue = this.get('categoryConfigsAll').findProperty('name', 'hive_hostname').get('value');
}
-
+ } else if (this.get('serviceConfig.serviceName') === 'HDFS') {
+ switch (value) {
+ case 'Existing MSSQL Server database with integrated authentication':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'sink_existing_mssql_server_host');
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'sink_existing_mssql_server_2_host');
+ break;
+ }
+ if (hostname) {
+ returnValue = hostname.get('value');
+ } else {
+ returnValue = this.get('categoryConfigsAll').findProperty('name', 'sink.dbservername').get('value');
+ }
} else if (this.get('serviceConfig.serviceName') === 'OOZIE') {
switch (value) {
case 'New Derby Database':
@@ -335,6 +410,12 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
case 'Existing Oracle Database':
hostname = this.get('categoryConfigsAll').findProperty('name', 'oozie_existing_oracle_host');
break;
+ case 'Existing MSSQL Server database with integrated authentication':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'oozie_existing_mssql_server_host');
+ break;
+ case 'Existing MSSQL Server database with sql auth':
+ hostname = this.get('categoryConfigsAll').findProperty('name', 'oozie_existing_mssql_server_2_host');
+ break;
}
if (hostname) {
returnValue = hostname.get('value');
@@ -348,6 +429,8 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
connectionUrl: function () {
if (this.get('serviceConfig.serviceName') === 'HIVE') {
return this.get('categoryConfigsAll').findProperty('name', 'javax.jdo.option.ConnectionURL');
+ } else if (this.get('serviceConfig.serviceName') === 'HDFS') {
+ return this.get('categoryConfigsAll').findProperty('name', 'sink.jdbc.url');
} else {
return this.get('categoryConfigsAll').findProperty('name', 'oozie.service.JPAService.jdbc.url');
}
@@ -356,6 +439,8 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
dbClass: function () {
if (this.get('serviceConfig.serviceName') === 'HIVE') {
return this.get('categoryConfigsAll').findProperty('name', 'javax.jdo.option.ConnectionDriverName');
+ } else if (this.get('serviceConfig.serviceName') === 'HDFS') {
+ return this.get('categoryConfigsAll').findProperty('name', 'sink.jdbc.driver');
} else {
return this.get('categoryConfigsAll').findProperty('name', 'oozie.service.JPAService.jdbc.driver');
}
@@ -371,11 +456,11 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
**/
handleDBConnectionProperty: function () {
if (!['addServiceController', 'installerController'].contains(App.clusterStatus.wizardControllerName)) return;
- var handledProperties = ['oozie_database', 'hive_database'];
+ var handledProperties = ['oozie_database', 'hive_database', 'sink_database'];
var currentValue = this.get('serviceConfig.value');
- var databases = /MySQL|PostgreSQL|Oracle|Derby/gi;
+ var databases = /MySQL|PostgreSQL|Oracle|Derby|MSSQL/gi;
var currentDB = currentValue.match(databases)[0];
- var databasesTypes = /MySQL|PostgreS|Oracle|Derby/gi;
+ var databasesTypes = /MySQL|PostgreS|Oracle|Derby|MSSQL/gi;
var currentDBType = currentValue.match(databasesTypes)[0];
var existingDatabase = /existing/gi.test(currentValue);
// db connection check button show up if existed db selected
@@ -392,6 +477,7 @@ App.ServiceConfigRadioButtons = Ember.View.extend({
// warning msg under database type radio buttons, to warn the user to setup jdbc driver if existed db selected
var propertyHive = this.get('categoryConfigsAll').findProperty('displayName', 'Hive Database');
var propertyOozie = this.get('categoryConfigsAll').findProperty('displayName', 'Oozie Database');
+ var propertyMetricsSink = this.get('categoryConfigsAll').findProperty('displayName', 'Metrics Sink Database');
var propertyAppendTo2 = propertyHive ? propertyHive : propertyOozie;
if (currentDB && existingDatabase) {
if (handledProperties.contains(this.get('serviceConfig.name'))) {
@@ -775,6 +861,8 @@ App.CheckDBConnectionView = Ember.View.extend({
hostNameProperty: function() {
if (!/wizard/i.test(this.get('controller.name')) && this.get('parentView.service.serviceName') === 'HIVE') {
return this.get('parentView.service.serviceName').toLowerCase() + '_hostname';
+ } else if (!/wizard/i.test(this.get('controller.name')) && this.get('parentView.service.serviceName') === 'HDFS') {
+ return 'sink.dbservername';
}
return '{0}_existing_{1}_host'.format(this.get('parentView.service.serviceName').toLowerCase(), this.get('databaseName').toLowerCase());
}.property('databaseName'),
@@ -785,6 +873,7 @@ App.CheckDBConnectionView = Ember.View.extend({
/** @property {object} requiredProperties - properties that necessary for database connection **/
requiredProperties: function() {
var propertiesMap = {
+ HDFS: ['sink.db.schema.name','sink.dblogin','sink.dbpassword','sink.jdbc.driver','sink.jdbc.url'],
OOZIE: ['oozie.db.schema.name','oozie.service.JPAService.jdbc.username','oozie.service.JPAService.jdbc.password','oozie.service.JPAService.jdbc.driver','oozie.service.JPAService.jdbc.url'],
HIVE: ['ambari.hive.db.schema.name','javax.jdo.option.ConnectionUserName','javax.jdo.option.ConnectionPassword','javax.jdo.option.ConnectionDriverName','javax.jdo.option.ConnectionURL']
};
@@ -793,8 +882,8 @@ App.CheckDBConnectionView = Ember.View.extend({
/** @property {Object} propertiesPattern - check pattern according to type of connection properties **/
propertiesPattern: function() {
return {
- user_name: /username$/ig,
- user_passwd: /password$/ig,
+ user_name: /(username|dblogin)$/ig,
+ user_passwd: /(dbpassword|password)$/ig,
db_connection_url: /jdbc\.url|connectionurl/ig
}
}.property(),
@@ -802,6 +891,7 @@ App.CheckDBConnectionView = Ember.View.extend({
masterHostName: function() {
var serviceMasterMap = {
'OOZIE': 'oozieserver_host',
+ 'HDFS': 'hadoop_host',
'HIVE': 'hivemetastore_host'
};
return this.get('parentView.categoryConfigsAll').findProperty('name', serviceMasterMap[this.get('parentView.service.serviceName')]).get('value');
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/ambari-web/app/views/wizard/step1_view.js
----------------------------------------------------------------------
diff --git a/ambari-web/app/views/wizard/step1_view.js b/ambari-web/app/views/wizard/step1_view.js
index 886543e..d3d0c4b 100644
--- a/ambari-web/app/views/wizard/step1_view.js
+++ b/ambari-web/app/views/wizard/step1_view.js
@@ -21,6 +21,12 @@ App.WizardStep1View = Em.View.extend({
templateName: require('templates/wizard/step1'),
+ /**
+ * Is Repositories Accordion collapsed
+ * @type {bool}
+ */
+ isRLCollapsed: true,
+
didInsertElement: function () {
if (this.get('isRLCollapsed')) {
this.$('.accordion-body').hide();
@@ -114,12 +120,6 @@ App.WizardStep1View = Em.View.extend({
}.property('allRepositories.@each.emptyError', 'isNoOsChecked', 'allRepositories.@each.validation'),
/**
- * Is Repositories Accordion collapsed
- * @type {bool}
- */
- isRLCollapsed: true,
-
- /**
* Checkbox for each stack
* @type {Ember.Checkbox}
*/
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/ambari-web/pom.xml
----------------------------------------------------------------------
diff --git a/ambari-web/pom.xml b/ambari-web/pom.xml
index 65e568d..294e60f 100644
--- a/ambari-web/pom.xml
+++ b/ambari-web/pom.xml
@@ -97,13 +97,13 @@
</goals>
<configuration>
<target name="ambari-web-clean">
- <exec dir="${basedir}" executable="rm">
- <arg value="-rf"/>
+ <exec dir="${basedir}" executable="${executable.rmdir}">
+ <arg value="${args.rm.clean}"/>
<arg value="public"/>
<arg value="node_modules"/>
</exec>
- <exec dir="${basedir}" executable="mkdir">
- <arg value="public"/>
+ <exec dir="${basedir}" executable="${executable.mkdir}">
+ <arg value="${args.mkdir.clean}"/>
</exec>
</target>
</configuration>
@@ -116,26 +116,25 @@
</goals>
<configuration>
<target name="ambari-web-compile">
- <exec dir="${basedir}" executable="npm">
- <env key="PYTHON" value="${project.basedir}/../ambari-common/src/main/unix/ambari-python-wrap" />
- <arg value="install"/>
+ <exec dir="${basedir}" executable="${executable.npm}" failonerror="true">
+ <arg value="${args.npm.install}"/>
</exec>
- <exec dir="${basedir}" executable="brunch" failonerror="true">
- <arg value="build"/>
+ <exec dir="${basedir}" executable="${executable.brunch}" failonerror="true">
+ <arg value="${args.brunch.build}"/>
</exec>
<!-- sets Ambari version to make it accessible from code -->
- <exec dir="${basedir}" executable="sh" failonerror="true">
- <arg value="${basedir}/set-ambari-version.sh"/>
+ <exec dir="${basedir}" executable="${executable.shell}" failonerror="true">
+ <arg value="${basedir}${dirsep}set-ambari-version.${fileextension.shell}"/>
<arg value="${ambariVersion}"/>
</exec>
<!-- sets default stack and version to use for install -->
- <exec dir="${basedir}" executable="sh" failonerror="true">
- <arg value="${basedir}/set-default-stack-version.sh"/>
+ <exec dir="${basedir}" executable="${executable.shell}" failonerror="true">
+ <arg value="${basedir}${dirsep}set-default-stack-version.${fileextension.shell}"/>
<arg value="${defaultStackVersion}"/>
</exec>
<!-- enables experimental features if enableExperimental is set to true -->
- <exec dir="${basedir}" executable="sh" failonerror="true">
- <arg value="${basedir}/toggle-experimental.sh"/>
+ <exec dir="${basedir}" executable="${executable.shell}" failonerror="true">
+ <arg value="${basedir}${dirsep}toggle-experimental.${fileextension.shell}"/>
<arg value="${enableExperimental}"/>
</exec>
</target>
@@ -149,22 +148,21 @@
</goals>
<configuration>
<target name="ambari-web unit tests" unless="${skipTests}">
- <exec dir="${basedir}" executable="npm" failonerror="true">
- <env key="PYTHON" value="${basedir}/../ambari-common/src/main/unix/ambari-python-wrap" />
- <arg value="test"/>
+ <exec dir="${basedir}" executable="${executable.npm}" failonerror="true">
+ <arg value="${args.npm.test}"/>
</exec>
</target>
</configuration>
</execution>
<execution>
- <id>gzip amabri-web content</id>
+ <id>gzip ambari-web content</id>
<phase>prepare-package</phase>
<goals>
<goal>run</goal>
</goals>
<configuration>
<target name="ambari-web-package">
- <exec dir="${basedir}" executable="gzip" failonerror="true">
+ <exec dir="${basedir}" executable="${executable.gzip}" failonerror="true">
<arg value="-f"/>
<arg value="public/javascripts/app.js"/>
<arg value="public/javascripts/vendor.js"/>
@@ -218,4 +216,54 @@
</plugin>
</plugins>
</build>
+ <profiles>
+ <profile>
+ <id>windows</id>
+ <activation>
+ <os>
+ <family>win</family>
+ </os>
+ </activation>
+ <properties>
+ <envClassifier>win</envClassifier>
+ <dirsep>\</dirsep>
+ <executable.brunch>cmd</executable.brunch>
+ <args.brunch.build>/C brunch build</args.brunch.build>
+ <executable.gzip>gzip</executable.gzip>
+ <executable.mkdir>cmd</executable.mkdir>
+ <args.mkdir.clean>/C mkdir public</args.mkdir.clean>
+ <executable.npm>cmd</executable.npm>
+ <args.npm.install>/C npm install</args.npm.install>
+ <args.npm.test>/C npm install</args.npm.test>
+ <executable.rmdir>cmd</executable.rmdir>
+ <args.rm.clean>/C rmdir /S /Q</args.rm.clean>
+ <executable.shell>cmd</executable.shell>
+ <fileextension.shell>cmd</fileextension.shell>
+ </properties>
+ </profile>
+ <profile>
+ <id>linux</id>
+ <activation>
+ <os>
+ <family>unix</family>
+ </os>
+ </activation>
+ <properties>
+ <envClassifier>linux</envClassifier>
+ <dirsep>/</dirsep>
+ <executable.brunch>brunch</executable.brunch>
+ <args.brunch.build>build</args.brunch.build>
+ <executable.gzip>gzip</executable.gzip>
+ <executable.mkdir>mkdir</executable.mkdir>
+ <args.mkdir.clean>public</args.mkdir.clean>
+ <executable.npm>npm</executable.npm>
+ <args.npm.install>install</args.npm.install>
+ <args.npm.test>test</args.npm.test>
+ <executable.rmdir>rm</executable.rmdir>
+ <args.rm.clean>-rf</args.rm.clean>
+ <executable.shell>sh</executable.shell>
+ <fileextension.shell>sh</fileextension.shell>
+ </properties>
+ </profile>
+ </profiles>
</project>
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/ambari-web/test/controllers/main/host/add_controller_test.js
----------------------------------------------------------------------
diff --git a/ambari-web/test/controllers/main/host/add_controller_test.js b/ambari-web/test/controllers/main/host/add_controller_test.js
index 6bd098f..a6c94bc 100644
--- a/ambari-web/test/controllers/main/host/add_controller_test.js
+++ b/ambari-web/test/controllers/main/host/add_controller_test.js
@@ -415,7 +415,7 @@ describe('App.AddHostController', function () {
describe("#getInstallOptions()", function () {
it("", function () {
controller.set('installOptionsTemplate', {'prop': 'installOptionsTemplate'});
- expect(controller.getInstallOptions()).to.be.eql({
+ expect(controller.get('getInstallOptions')).to.be.eql({
prop: 'installOptionsTemplate'
});
});
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/ambari-web/test/views/main/dashboard/widgets_test.js
----------------------------------------------------------------------
diff --git a/ambari-web/test/views/main/dashboard/widgets_test.js b/ambari-web/test/views/main/dashboard/widgets_test.js
index fdd2b34..4fdd6cd 100644
--- a/ambari-web/test/views/main/dashboard/widgets_test.js
+++ b/ambari-web/test/views/main/dashboard/widgets_test.js
@@ -32,6 +32,7 @@ describe('App.MainDashboardWidgetsView', function() {
});
describe('#setInitPrefObject', function() {
+ var host_metrics_widgets_count = 4;
var hdfs_widgets_count = 7;
var mapreduce_widgets_count = 7;
var hbase_widgets_count = 4;
@@ -40,19 +41,35 @@ describe('App.MainDashboardWidgetsView', function() {
var tests = Em.A([
{
models: {
+ host_metrics_model: null,
hdfs_model: null,
mapreduce_model: null,
hbase_model: null,
yarn_model: null
},
e: {
- visibleL: total_widgets_count - hdfs_widgets_count - mapreduce_widgets_count - hbase_widgets_count - yarn_widgets_count - 1,
+ visibleL: total_widgets_count - host_metrics_widgets_count- hdfs_widgets_count - mapreduce_widgets_count - hbase_widgets_count - yarn_widgets_count - 1,
hiddenL: 0
},
m: 'All models are null'
},
{
models: {
+ host_metrics_model: {},
+ hdfs_model: null,
+ mapreduce_model: null,
+ hbase_model: null,
+ yarn_model: null
+ },
+ e: {
+ visibleL: total_widgets_count - hdfs_widgets_count - mapreduce_widgets_count - hbase_widgets_count - yarn_widgets_count - 1,
+ hiddenL: 0
+ },
+ m: 'hdfs_model, mapreduce_model, hbase_model, yarn_model are null'
+ },
+ {
+ models: {
+ host_metrics_model: {},
hdfs_model: {},
mapreduce_model: null,
hbase_model: null,
@@ -66,6 +83,7 @@ describe('App.MainDashboardWidgetsView', function() {
},
{
models: {
+ host_metrics_model: {},
hdfs_model: {},
mapreduce_model: {},
hbase_model: null,
@@ -79,6 +97,7 @@ describe('App.MainDashboardWidgetsView', function() {
},
{
models: {
+ host_metrics_model: {},
hdfs_model: {},
mapreduce_model: {},
hbase_model: {},
@@ -92,6 +111,7 @@ describe('App.MainDashboardWidgetsView', function() {
},
{
models: {
+ host_metrics_model: {},
hdfs_model: {},
mapreduce_model: {},
hbase_model: {},
@@ -106,6 +126,7 @@ describe('App.MainDashboardWidgetsView', function() {
]);
tests.forEach(function(test) {
it(test.m, function() {
+ mainDashboardWidgetsView.set('host_metrics_model', test.models.host_metrics_model);
mainDashboardWidgetsView.set('hdfs_model', test.models.hdfs_model);
mainDashboardWidgetsView.set('mapreduce_model', test.models.mapreduce_model);
mainDashboardWidgetsView.set('hbase_model', test.models.hbase_model);
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.ddl
----------------------------------------------------------------------
diff --git a/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.ddl b/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.ddl
deleted file mode 100644
index 1abf3fa..0000000
--- a/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.ddl
+++ /dev/null
@@ -1,793 +0,0 @@
-/*
-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.
-*/
-
-/*
-Deployment script for HadoopMetrics
-*/
-
-USE [master]
-GO
-
-IF db_id('HadoopMetrics') IS NOT NULL
-BEGIN
- Print N'Dropping [dbo].[HadoopMetrics] database...'
- DROP DATABASE HadoopMetrics
-END
-GO
-
-Print N'Creating [dbo].[HadoopMetrics] database...'
-CREATE DATABASE [HadoopMetrics]
-GO
-
-USE [HadoopMetrics]
-GO
-
-SET QUOTED_IDENTIFIER ON;
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'CompletedJob' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[CompletedJob]...';
- CREATE TABLE [dbo].[CompletedJob] (
- [ClusterNodeID] INT NOT NULL,
- [TagSetID] INT NOT NULL,
- [MapProgressPercent] INT NOT NULL,
- [CleanupProgressPercent] INT NOT NULL,
- [SetupProgressPercent] INT NOT NULL,
- [ReduceProgressPercent] INT NOT NULL,
- [RunState] INT NOT NULL,
- [StartTime] DATETIME NOT NULL,
- [EndTime] DATETIME NOT NULL
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'PK_CompletedJob_ClusterNodeID_TagSetID' AND type_desc = N'CLUSTERED' AND IS_PRIMARY_KEY=N'1')
-BEGIN
- PRINT N'Creating [dbo].[CompletedJob].[PK_CompletedJob_ClusterNodeID_TagSetID]...';
- ALTER TABLE [dbo].[CompletedJob]
- ADD CONSTRAINT [PK_CompletedJob_ClusterNodeID_TagSetID] PRIMARY KEY CLUSTERED ([ClusterNodeID] ASC, [TagSetID] ASC);
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_CompletedJob_EndTime' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[CompletedJob].[IX_CompletedJob_EndTime]...';
- CREATE NONCLUSTERED INDEX [IX_CompletedJob_EndTime]
- ON [dbo].[CompletedJob]([EndTime] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_CompletedJob_TagSetID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[CompletedJob].[IX_CompletedJob_TagSetID]...';
- CREATE NONCLUSTERED INDEX [IX_CompletedJob_TagSetID]
- ON [dbo].[CompletedJob]([TagSetID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Configuration' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[Configuration]...';
- CREATE TABLE [dbo].[Configuration] (
- [RequestedRefreshRate] INT NOT NULL
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'DatabaseVersion' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[DatabaseVersion]...';
- CREATE TABLE [dbo].[DatabaseVersion] (
- [Major] INT NOT NULL,
- [Minor] INT NOT NULL,
- [Build] INT NOT NULL,
- [Revision] INT NOT NULL
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricName' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[MetricName]...';
- CREATE TABLE [dbo].[MetricName] (
- [MetricID] INT IDENTITY (1, 1) NOT NULL,
- [Name] NVARCHAR (256) NOT NULL,
- PRIMARY KEY CLUSTERED ([MetricID] ASC)
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricName_Name' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricName].[IX_MetricName_Name]...';
- CREATE UNIQUE NONCLUSTERED INDEX [IX_MetricName_Name]
- ON [dbo].[MetricName]([Name] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricPair' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[MetricPair]...';
- CREATE TABLE [dbo].[MetricPair] (
- [RecordID] BIGINT NOT NULL,
- [MetricID] INT NOT NULL,
- [MetricValue] NVARCHAR (512) NOT NULL
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'UX_MetricPair_RecordID_MetricID' AND type_desc = N'CLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricPair].[UX_MetricPair_RecordID_MetricID]...';
- CREATE UNIQUE CLUSTERED INDEX [UX_MetricPair_RecordID_MetricID]
- ON [dbo].[MetricPair]([RecordID] ASC, [MetricID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricRecord' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord]...';
- CREATE TABLE [dbo].[MetricRecord] (
- [RecordID] BIGINT IDENTITY (1, 1) NOT NULL,
- [RecordTypeID] INT NOT NULL,
- [NodeID] INT NOT NULL,
- [SourceIP] NVARCHAR (256) NULL,
- [ClusterNodeID] INT NOT NULL,
- [ServiceID] INT NOT NULL,
- [TagSetID] INT NOT NULL,
- [RecordTimestamp] BIGINT NOT NULL,
- [RecordDate] AS DATEADD(second, CONVERT (INT, RecordTimestamp / 1000), CONVERT (DATETIME, '1970-01-01T00:00:00.000', 126)) PERSISTED,
- PRIMARY KEY CLUSTERED ([RecordID] ASC)
- );
-END
-GO
-
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_ClusterNodeID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_ClusterNodeID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_ClusterNodeID]
- ON [dbo].[MetricRecord]([ClusterNodeID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_RecordID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_RecordID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_RecordID]
- ON [dbo].[MetricRecord]([NodeID] ASC, [RecordID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID]
- ON [dbo].[MetricRecord]([NodeID] ASC, [RecordTypeID] ASC, [ClusterNodeID] ASC)
- INCLUDE([RecordDate]) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_TagSetID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_TagSetID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_TagSetID]
- ON [dbo].[MetricRecord]([NodeID] ASC, [TagSetID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordDate' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordDate]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordDate]
- ON [dbo].[MetricRecord]([RecordDate] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID]
- ON [dbo].[MetricRecord]([RecordTimestamp] DESC, [NodeID] ASC, [RecordTypeID] ASC)
- INCLUDE([RecordID]) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTypeID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTypeID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTypeID]
- ON [dbo].[MetricRecord]([RecordTypeID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp]
- ON [dbo].[MetricRecord]([RecordTypeID] ASC, [ClusterNodeID] ASC, [ServiceID] ASC, [TagSetID] ASC, [RecordTimestamp] DESC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_TagSetID' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_TagSetID]...';
- CREATE NONCLUSTERED INDEX [IX_MetricRecord_TagSetID]
- ON [dbo].[MetricRecord]([TagSetID] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[MetricRecord].[UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp]...';
- CREATE UNIQUE NONCLUSTERED INDEX [UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp]
- ON [dbo].[MetricRecord]([RecordTypeID] ASC, [NodeID] ASC, [TagSetID] ASC, [RecordTimestamp] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Service' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[Service]...';
- CREATE TABLE [dbo].[Service] (
- [ServiceID] BIGINT IDENTITY (1, 1) NOT NULL,
- [Name] NVARCHAR (256),
- PRIMARY KEY CLUSTERED ([ServiceID] ASC)
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Node' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[Node]...';
- CREATE TABLE [dbo].[Node] (
- [NodeID] INT IDENTITY (1, 1) NOT NULL,
- [Name] NVARCHAR (256) NOT NULL,
- [LastKnownIP] NVARCHAR (256) NULL,
- [LastNameNodeHeartBeat] DATETIME NULL,
- [LastJobTrackerHeartBeat] DATETIME NULL,
- [LastDataNodeHeartBeat] DATETIME NULL,
- [LastTaskTrackerHeartBeat] DATETIME NULL,
- PRIMARY KEY CLUSTERED ([NodeID] ASC)
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_Node_Name' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[Node].[IX_Node_Name]...';
- CREATE UNIQUE NONCLUSTERED INDEX [IX_Node_Name]
- ON [dbo].[Node]([Name] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'RecordType' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[RecordType]...';
- CREATE TABLE [dbo].[RecordType] (
- [RecordTypeID] INT IDENTITY (1, 1) NOT NULL,
- [Name] NVARCHAR (256) NOT NULL,
- [Context] NVARCHAR (256) NOT NULL,
- PRIMARY KEY CLUSTERED ([RecordTypeID] ASC)
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_RecordType_Context_Name' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[RecordType].[IX_RecordType_Context_Name]...';
- CREATE UNIQUE NONCLUSTERED INDEX [IX_RecordType_Context_Name]
- ON [dbo].[RecordType]([Context] ASC, [Name] ASC) ;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'TagSet' and type_desc = N'USER_TABLE')
-BEGIN
- PRINT N'Creating [dbo].[TagSet]...';
- CREATE TABLE [dbo].[TagSet] (
- [TagSetID] INT IDENTITY (1, 1) NOT NULL,
- [TagPairs] NVARCHAR (512) NOT NULL,
- PRIMARY KEY CLUSTERED ([TagSetID] ASC)
- );
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_TagSet_TagPairs' AND type_desc = N'NONCLUSTERED')
-BEGIN
- PRINT N'Creating [dbo].[TagSet].[IX_TagSet_TagPairs]...';
- CREATE UNIQUE NONCLUSTERED INDEX [IX_TagSet_TagPairs]
- ON [dbo].[TagSet]([TagPairs] ASC) ;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_CompletedJob_TagSet_TagSetID')
-BEGIN
- PRINT N'Creating FK_CompletedJob_TagSet_TagSetID...';
- ALTER TABLE [dbo].[CompletedJob] WITH NOCHECK
- ADD CONSTRAINT [FK_CompletedJob_TagSet_TagSetID] FOREIGN KEY ([TagSetID]) REFERENCES [dbo].[TagSet] ([TagSetID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricPair_MetricName_MetricID')
-BEGIN
- PRINT N'Creating FK_MetricPair_MetricName_MetricID...';
- ALTER TABLE [dbo].[MetricPair] WITH NOCHECK
- ADD CONSTRAINT [FK_MetricPair_MetricName_MetricID] FOREIGN KEY ([MetricID]) REFERENCES [dbo].[MetricName] ([MetricID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricPair_MetricRecord_RecordID')
-BEGIN
- PRINT N'Creating FK_MetricPair_MetricRecord_RecordID...';
- ALTER TABLE [dbo].[MetricPair] WITH NOCHECK
- ADD CONSTRAINT [FK_MetricPair_MetricRecord_RecordID] FOREIGN KEY ([RecordID]) REFERENCES [dbo].[MetricRecord] ([RecordID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_Node_NodeID')
-BEGIN
- PRINT N'Creating FK_MetricRecord_Node_NodeID...';
- ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
- ADD CONSTRAINT [FK_MetricRecord_Node_NodeID] FOREIGN KEY ([NodeID]) REFERENCES [dbo].[Node] ([NodeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_RecordType_RecordTypeID')
-BEGIN
- PRINT N'Creating FK_MetricRecord_RecordType_RecordTypeID...';
- ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
- ADD CONSTRAINT [FK_MetricRecord_RecordType_RecordTypeID] FOREIGN KEY ([RecordTypeID]) REFERENCES [dbo].[RecordType] ([RecordTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_TagSet_TagSetID')
-BEGIN
- PRINT N'Creating FK_MetricRecord_TagSet_TagSetID...';
- ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
- ADD CONSTRAINT [FK_MetricRecord_TagSet_TagSetID] FOREIGN KEY ([TagSetID]) REFERENCES [dbo].[TagSet] ([TagSetID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspInsertMetricValue' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating [dbo].[uspInsertMetricValue]...';
- exec('CREATE PROCEDURE [dbo].[uspInsertMetricValue]
- @recordID bigint,
- @metricName nvarchar(256),
- @metricValue nvarchar(512)
- AS
- BEGIN
- SET NOCOUNT ON;
-
- DECLARE @metricID int;
- DECLARE @err int;
-
- IF @recordID IS NULL OR @metricName IS NULL RETURN;
-
- BEGIN TRANSACTION;
- SELECT @metricID = MetricID FROM MetricName WHERE Name = @metricName;
- IF @metricID IS NULL
- BEGIN
- INSERT INTO MetricName (Name) VALUES (@metricName);
- SELECT @err = @@ERROR, @metricID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- COMMIT TRANSACTION;
-
- INSERT INTO MetricPair (RecordID, MetricID, MetricValue) VALUES (@recordID, @metricID, @metricValue);
- RETURN;
-
- Abort:
- ROLLBACK TRANSACTION;
- RETURN;
- END')
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspUpdateHeartBeats' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating [dbo].[uspUpdateHeartBeats]...';
- exec('CREATE PROCEDURE [dbo].[uspUpdateHeartBeats]
- @NodeID int,
- @SourceIP nvarchar(256),
- @NameNodeLast datetime,
- @JobTrackerLast datetime,
- @DataNodeLast datetime,
- @TaskTrackerLast datetime,
- @LastKnownIP nvarchar(256)
- AS
- BEGIN
- IF @NodeID IS NOT NULL
- BEGIN
- IF @NameNodeLast IS NOT NULL
- BEGIN
- UPDATE Node SET LastNameNodeHeartBeat = @NameNodeLast WHERE NodeID = @NodeID;
- END
- IF @JobTrackerLast IS NOT NULL
- BEGIN
- UPDATE Node SET LastJobTrackerHeartBeat = @JobTrackerLast WHERE NodeID = @NodeID;
- END
- IF @DataNodeLast IS NOT NULL
- BEGIN
- UPDATE Node SET LastDataNodeHeartBeat = @DataNodeLast WHERE NodeID = @NodeID;
- END
- IF @TaskTrackerLast IS NOT NULL
- BEGIN
- UPDATE Node SET LastTaskTrackerHeartBeat = @TaskTrackerLast WHERE NodeID = @NodeID;
- END
- IF @LastKnownIP IS NULL OR @SourceIP <> @LastKnownIP
- BEGIN
- UPDATE Node SET LastKnownIP = @SourceIP WHERE NodeID = @NodeID;
- END
- END
- END')
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspGetMetricRecord' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating [dbo].[uspGetMetricRecord]...';
- exec('CREATE PROCEDURE [dbo].[uspGetMetricRecord]
- @recordTypeContext nvarchar(256),
- @recordTypeName nvarchar(256),
- @nodeName nvarchar(256),
- @sourceIP nvarchar(256),
- @clusterNodeName nvarchar(256),
- @serviceName nvarchar(256),
- @tagPairs nvarchar(512),
- @recordTimestamp bigint,
- @metricRecordID bigint OUTPUT
- AS
- BEGIN
- SET NOCOUNT ON;
-
- DECLARE @recordTypeID int
- DECLARE @nodeID int
- DECLARE @clusterNodeID int
- DECLARE @tagSetID int
- DECLARE @serviceID int
- DECLARE @err int
- DECLARE @recordIDCutoff bigint
-
- BEGIN TRANSACTION;
- SELECT @recordTypeID = RecordTypeID FROM RecordType WHERE Context = @recordTypeContext AND Name = @recordTypeName;
- IF @recordTypeID IS NULL
- BEGIN
- INSERT INTO RecordType (Context, Name) VALUES (@recordTypeContext, @recordTypeName);
- SELECT @err = @@ERROR, @recordTypeID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- COMMIT TRANSACTION;
-
- BEGIN TRANSACTION;
- SELECT @serviceID = serviceID FROM Service WHERE Name = @serviceName;
- IF @serviceID IS NULL
- BEGIN
- INSERT INTO Service (Name) VALUES (@serviceName);
- SELECT @err = @@ERROR, @serviceID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- COMMIT TRANSACTION;
-
- BEGIN TRANSACTION;
- SELECT @nodeID = NodeID FROM Node WHERE Name = @nodeName;
-
- IF @nodeID IS NULL
- BEGIN
-
- /* Start with a node type of uninitialized. HealthNode will determine node type based on metrics delivered over time. */
- INSERT INTO Node (Name, LastKnownIP) VALUES (@nodeName, @sourceIP);
- SELECT @err = @@ERROR, @nodeID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
-
- COMMIT TRANSACTION;
-
- -- Do our best to determine the cluster node ID based on completely flakey input from user which might be an IP address, a non-FQDN,
- -- or an FQDN. Note that worker nodes may have a completely different idea about the name of the namenode (which is the node
- -- which represents the cluster) compared with the namenode itself
-
- BEGIN TRANSACTION;
- IF ((SELECT [dbo].[ufnIsIPAddress](@clusterNodeName)) = 1)
- BEGIN
- SELECT TOP 1 @clusterNodeID = NodeID from Node WHERE LastKnownIP = @clusterNodeName ORDER BY LastNameNodeHeartBeat DESC;
- IF @clusterNodeID IS NULL
- BEGIN
- INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
- SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- END
- ELSE
- IF ((SELECT CHARINDEX(@clusterNodeName, ''.'', 1)) > 0)
- BEGIN
- -- IF this is not an IP address, but there is a dot in the name we assume we are looking at an FQDN
- SELECT @clusterNodeID = NodeID FROM Node WHERE Name = @clusterNodeName;
- IF @clusterNodeID IS NULL
- BEGIN
- INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
- SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- END
- ELSE
- BEGIN
- -- We have got a non-FQDN, but the NameNode might know its FQDN, so be careful! We must prefer the FQDN if we can find one.
- -- Sadly, yes, this could break things if we are monitoring clusters from different domains. This is now by design!
- SELECT TOP 1 @clusterNodeID = NodeID FROM Node WHERE Name LIKE @clusterNodeName + ''.%'' ORDER BY LastNameNodeHeartBeat DESC;
- IF @clusterNodeID IS NULL
- BEGIN
- SELECT @clusterNodeID = NodeID FROM Node WHERE Name = @clusterNodeName;
- if @clusterNodeID IS NULL
- BEGIN
- INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
- SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- END
- END
- COMMIT TRANSACTION;
-
- -- Cleanup older metric records and pairs if necessary
- -- Policy is to keep between 60000 and 90000 metric records and associated metric pairs per node.
- IF (SELECT COUNT(*) FROM MetricRecord WHERE NodeID = @nodeID) > 90000
- BEGIN
- SELECT @recordIDCutoff = MIN(RecordID) FROM MetricRecord WHERE RecordID IN (SELECT TOP 60000 RecordID FROM MetricRecord WHERE NodeID = @nodeID ORDER BY RecordDate DESC);
- IF @recordIDCutoff IS NOT NULL
- BEGIN
- DELETE FROM MetricPair
- FROM MetricPair as mp
- JOIN MetricRecord as mr ON mp.RecordID = mr.RecordID
- WHERE mr.RecordID < @recordIDCutoff AND mr.NodeID = @nodeID;
-
- DELETE FROM MetricRecord
- WHERE RecordID < @recordIDCutoff AND NodeID = @nodeID;
- END;
- END;
-
-
- BEGIN TRANSACTION;
- SELECT @tagSetID = TagSetID FROM TagSet WHERE TagPairs = @tagPairs;
- IF @tagSetID IS NULL
- BEGIN
- INSERT INTO TagSet (TagPairs) VALUES (@tagPairs);
- SELECT @err = @@ERROR, @tagSetID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- COMMIT TRANSACTION;
-
- BEGIN TRANSACTION;
- SELECT @metricRecordID = RecordID FROM MetricRecord WHERE RecordTypeID = @recordTypeID AND NodeID = @nodeID AND ServiceID = @serviceID AND TagSetID = @tagSetID AND RecordTimestamp = @recordTimestamp;
- IF @metricRecordID IS NULL
- BEGIN
- INSERT INTO MetricRecord (RecordTypeID, NodeID, SourceIP, ClusterNodeID, ServiceID, TagSetID, RecordTimestamp) VALUES (@recordTypeID, @nodeID, @sourceIP, @clusterNodeID, @serviceID, @tagSetID, @recordTimestamp);
- SELECT @err = @@ERROR, @metricRecordID = SCOPE_IDENTITY();
- IF @err <> 0 GOTO Abort;
- END
- COMMIT TRANSACTION;
-
- GOTO Success;
-
- Abort:
- ROLLBACK TRANSACTION;
- SET @metricRecordID = NULL;
- RETURN;
-
- Success:
- RETURN;
-
- END')
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufnIsIPAddress' and type_desc = N'SQL_SCALAR_FUNCTION')
-BEGIN
- PRINT N'Creating [dbo].[ufnIsIPAddress]...';
- exec('CREATE FUNCTION [dbo].[ufnIsIPAddress]
- (
- @inputString nvarchar(max)
- )
- RETURNS BIT
- AS
- BEGIN
- DECLARE @currentPos bigint = 1;
- DECLARE @nextPos bigint = 0;
- DECLARE @count int = 0;
-
- if (LEN(@inputString) = 0) RETURN 0;
-
- SELECT @nextPos = CHARINDEX(''.'', @inputString, @currentPos);
-
- WHILE (@nextPos < LEN(@inputString) AND @count < 4)
- BEGIN
- IF (@nextPos = 0) SET @nextPos = LEN(@inputString);
- IF ((SELECT ISNUMERIC(SUBSTRING(@inputString, @currentPos, @nextPos - @currentPos))) = 1)
- BEGIN
- SET @count = @count + 1;
- SET @currentPos = @nextPos
- SELECT @nextPos = CHARINDEX(''.'', @inputString, @currentPos + 1);
- END
- ELSE BREAK;
- END
-
- IF (@count = 4) RETURN 1;
-
- SET @currentPos = 1;
- SET @nextPos = 0;
- SET @count = 0;
-
- WHILE (@currentPos <= LEN(@inputString))
- BEGIN
- IF EXISTS (SELECT 1 WHERE SUBSTRING(@inputString, @currentPos, 1) LIKE ''[0-9A-Fa-f:]'')
- BEGIN
- IF (SUBSTRING(@inputString, @currentPos, 1) = N'':'') SET @count = @count + 1;
- SET @currentPos = @currentPos + 1;
- END
- ELSE RETURN 0;
- END
- IF @count >= 4 return 1;
-
- RETURN 0;
- END')
-END
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'RethrowError' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating Stored Proc: [dbo].[RethrowError]...';
- exec('CREATE PROCEDURE [dbo].[RethrowError]
- AS
- BEGIN
- DECLARE @ErrorMessage NVARCHAR(4000);
- DECLARE @ErrorSeverity INT;
- DECLARE @ErrorState INT;
-
- SELECT
- @ErrorMessage = ERROR_MESSAGE(),
- @ErrorSeverity = ERROR_SEVERITY(),
- @ErrorState = ERROR_STATE();
-
- RAISERROR (@ErrorMessage, -- Message text.
- @ErrorSeverity, -- Severity.
- @ErrorState -- State.
- );
- END
- ')
-END
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspPurgeMetrics' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- -- purge metrics older than @noOfDays
- PRINT N'Creating [dbo].[uspPurgeMetrics]...';
- exec('CREATE PROCEDURE [dbo].[uspPurgeMetrics]
- @noOfDays bigint
- AS
- BEGIN
-
- IF @noOfDays IS NULL OR @noOfDays < 1
- BEGIN
- RAISERROR(''INVALID_ARGUMENT'', 15, 1)
- RETURN
- END;
-
- DECLARE @recordIDCutOff BIGINT
- SELECT @recordIDCutoff = MAX(RecordID) FROM MetricRecord WHERE DateDiff(day, RecordDate, CURRENT_TIMESTAMP) >= @noOfDays
-
- IF @recordIDCutoff IS NOT NULL
- BEGIN
- BEGIN TRY
- BEGIN TRANSACTION
-
- DELETE FROM MetricPair WHERE RecordID <= @recordIDCutoff
-
- DELETE FROM MetricRecord WHERE RecordID <= @recordIDCutoff
-
- IF @@TRANCOUNT > 0
- BEGIN
- COMMIT TRANSACTION
- END
-
- END TRY
- BEGIN CATCH
- IF @@TRANCOUNT > 0
- BEGIN
- ROLLBACK TRANSACTION;
- END
-
- -- get error infromation and raise error
- EXECUTE [dbo].[RethrowError]
- RETURN
-
- END CATCH
- END;
- END');
-END
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufGetMetrics' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating [dbo].[ufGetMetrics]...';
-exec( 'CREATE FUNCTION dbo.ufGetMetrics
- (@startTimeStamp bigint,
- @endTimeStamp bigint,
- @recordTypeContext NVARCHAR(256),
- @recordTypeName NVARCHAR(256),
- @metricName NVARCHAR(256),
- @serviceComponentName NVARCHAR(256),
- @nodeName NVARCHAR(256)
- )
- RETURNS TABLE --(MetricTimeStamp bigint, MetricValue NVARCHAR(512))
- AS
- RETURN
- (
- SELECT s.RecordTimeStamp AS RecordTimeStamp,
- mp.MetricValue AS MetricValue
- FROM MetricPair mp
- INNER JOIN (SELECT mr.RecordID AS RecordID,
- mr.RecordTimeStamp AS RecordTimeStamp
- FROM MetricRecord mr
- INNER JOIN RecordType rt ON (mr.RecordTypeId = rt.RecordTypeId)
- INNER JOIN Node nd ON (mr.NodeID = nd.NodeID)
- INNER JOIN Service sr ON (mr.ServiceID = sr.ServiceID)
- WHERE rt.Context = @recordTypeContext
- AND rt.Name = @recordTypeName
- AND (nd.Name = @nodeName)
- AND (sr.Name = @serviceComponentName)
- AND mr.RecordTimestamp >= @startTimeStamp
- AND mr.RecordTimestamp <= @endTimeStamp
- ) s ON (mp.RecordID = s.RecordID)
- INNER JOIN MetricName mn ON (mp.MetricID = mn.MetricID)
- WHERE (mn.Name = @metricName)
- )'
-)
-END
-
-GO
-
-IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufGetAggregatedServiceMetrics' and type_desc = N'SQL_STORED_PROCEDURE')
-BEGIN
- PRINT N'Creating [dbo].[ufGetAggregatedServiceMetrics]...';
- exec( 'CREATE FUNCTION [dbo].[ufGetAggregatedServiceMetrics]
- (@startTimeStamp bigint,
- @endTimeStamp bigint,
- @recordTypeContext NVARCHAR(256),
- @recordTypeName NVARCHAR(256),
- @metricName NVARCHAR(256),
- @serviceComponentName NVARCHAR(256),
- @period integer
- )
- RETURNS TABLE ----(TimeStampBlock integer, MetricTimeStamp bigint, MetricValue NVARCHAR(512))
- AS
- RETURN
- (
- SELECT FLOOR ((mr.RecordTimeStamp - @startTimeStamp) / @period) TimeStampBlock, MAX(mr.RecordTimeStamp) RecordTimeStamp, SUM(CONVERT(NUMERIC(18,4), MetricValue)) AggMetricValue
- FROM MetricPair mp
- INNER JOIN MetricRecord mr ON (mp.RecordID = mr.RecordID)
- INNER JOIN RecordType rt ON (rt.RecordTypeID = mr.RecordTypeID)
- INNER JOIN MetricName mn ON (mn.MetricID = mp.MetricID)
- INNER JOIN Service sr ON (sr.ServiceID = mr.ServiceID)
- WHERE mr.RecordTimestamp >= @startTimeStamp
- AND mr.RecordTimestamp <= @endTimeStamp
- AND mn.Name = @metricName
- AND rt.Context = @recordTypeContext
- AND rt.Name = @recordTypeName
- AND sr.Name = @serviceComponentName
- GROUP BY FLOOR ((mr.RecordTimeStamp - @startTimeStamp) / @period)
- )'
- )
-END
-GO
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.sql
----------------------------------------------------------------------
diff --git a/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.sql b/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.sql
new file mode 100644
index 0000000..6a88310
--- /dev/null
+++ b/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATE.sql
@@ -0,0 +1,784 @@
+/*
+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.
+*/
+
+/*
+Schema population script for $(METRICSDBNAME)
+
+Use this script in sqlcmd mode, setting the environment variables like this:
+set METRICSDBNAME=HadoopMetrics
+
+sqlcmd -S localhost\SQLEXPRESS -i C:\app\ambari-server-1.3.0-SNAPSHOT\resources\Hadoop-Metrics-SQLServer-CREATE.sql
+*/
+
+USE [$(METRICSDBNAME)]
+GO
+
+SET QUOTED_IDENTIFIER ON;
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'CompletedJob' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[CompletedJob]...';
+ CREATE TABLE [dbo].[CompletedJob] (
+ [ClusterNodeID] INT NOT NULL,
+ [TagSetID] INT NOT NULL,
+ [MapProgressPercent] INT NOT NULL,
+ [CleanupProgressPercent] INT NOT NULL,
+ [SetupProgressPercent] INT NOT NULL,
+ [ReduceProgressPercent] INT NOT NULL,
+ [RunState] INT NOT NULL,
+ [StartTime] DATETIME NOT NULL,
+ [EndTime] DATETIME NOT NULL
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'PK_CompletedJob_ClusterNodeID_TagSetID' AND type_desc = N'CLUSTERED' AND IS_PRIMARY_KEY=N'1')
+BEGIN
+ PRINT N'Creating [dbo].[CompletedJob].[PK_CompletedJob_ClusterNodeID_TagSetID]...';
+ ALTER TABLE [dbo].[CompletedJob]
+ ADD CONSTRAINT [PK_CompletedJob_ClusterNodeID_TagSetID] PRIMARY KEY CLUSTERED ([ClusterNodeID] ASC, [TagSetID] ASC);
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_CompletedJob_EndTime' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[CompletedJob].[IX_CompletedJob_EndTime]...';
+ CREATE NONCLUSTERED INDEX [IX_CompletedJob_EndTime]
+ ON [dbo].[CompletedJob]([EndTime] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_CompletedJob_TagSetID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[CompletedJob].[IX_CompletedJob_TagSetID]...';
+ CREATE NONCLUSTERED INDEX [IX_CompletedJob_TagSetID]
+ ON [dbo].[CompletedJob]([TagSetID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Configuration' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[Configuration]...';
+ CREATE TABLE [dbo].[Configuration] (
+ [RequestedRefreshRate] INT NOT NULL
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'DatabaseVersion' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[DatabaseVersion]...';
+ CREATE TABLE [dbo].[DatabaseVersion] (
+ [Major] INT NOT NULL,
+ [Minor] INT NOT NULL,
+ [Build] INT NOT NULL,
+ [Revision] INT NOT NULL
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricName' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[MetricName]...';
+ CREATE TABLE [dbo].[MetricName] (
+ [MetricID] INT IDENTITY (1, 1) NOT NULL,
+ [Name] NVARCHAR (256) NOT NULL,
+ PRIMARY KEY CLUSTERED ([MetricID] ASC)
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricName_Name' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricName].[IX_MetricName_Name]...';
+ CREATE UNIQUE NONCLUSTERED INDEX [IX_MetricName_Name]
+ ON [dbo].[MetricName]([Name] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricPair' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[MetricPair]...';
+ CREATE TABLE [dbo].[MetricPair] (
+ [RecordID] BIGINT NOT NULL,
+ [MetricID] INT NOT NULL,
+ [MetricValue] NVARCHAR (512) NOT NULL
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'UX_MetricPair_RecordID_MetricID' AND type_desc = N'CLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricPair].[UX_MetricPair_RecordID_MetricID]...';
+ CREATE UNIQUE CLUSTERED INDEX [UX_MetricPair_RecordID_MetricID]
+ ON [dbo].[MetricPair]([RecordID] ASC, [MetricID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'MetricRecord' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord]...';
+ CREATE TABLE [dbo].[MetricRecord] (
+ [RecordID] BIGINT IDENTITY (1, 1) NOT NULL,
+ [RecordTypeID] INT NOT NULL,
+ [NodeID] INT NOT NULL,
+ [SourceIP] NVARCHAR (256) NULL,
+ [ClusterNodeID] INT NOT NULL,
+ [ServiceID] INT NOT NULL,
+ [TagSetID] INT NOT NULL,
+ [RecordTimestamp] BIGINT NOT NULL,
+ [RecordDate] AS DATEADD(second, CONVERT (INT, RecordTimestamp / 1000), CONVERT (DATETIME, '1970-01-01T00:00:00.000', 126)) PERSISTED,
+ PRIMARY KEY CLUSTERED ([RecordID] ASC)
+ );
+END
+GO
+
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_ClusterNodeID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_ClusterNodeID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_ClusterNodeID]
+ ON [dbo].[MetricRecord]([ClusterNodeID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_RecordID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_RecordID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_RecordID]
+ ON [dbo].[MetricRecord]([NodeID] ASC, [RecordID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_RecordTypeID_ClusterNodeID]
+ ON [dbo].[MetricRecord]([NodeID] ASC, [RecordTypeID] ASC, [ClusterNodeID] ASC)
+ INCLUDE([RecordDate]) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_NodeID_TagSetID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_NodeID_TagSetID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_NodeID_TagSetID]
+ ON [dbo].[MetricRecord]([NodeID] ASC, [TagSetID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordDate' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordDate]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordDate]
+ ON [dbo].[MetricRecord]([RecordDate] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTimestamp_NodeID_RecordTypeID]
+ ON [dbo].[MetricRecord]([RecordTimestamp] DESC, [NodeID] ASC, [RecordTypeID] ASC)
+ INCLUDE([RecordID]) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTypeID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTypeID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTypeID]
+ ON [dbo].[MetricRecord]([RecordTypeID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_RecordTypeID_ClusterNodeID_ServiceID_TagSetID_RecordTimestamp]
+ ON [dbo].[MetricRecord]([RecordTypeID] ASC, [ClusterNodeID] ASC, [ServiceID] ASC, [TagSetID] ASC, [RecordTimestamp] DESC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_MetricRecord_TagSetID' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[IX_MetricRecord_TagSetID]...';
+ CREATE NONCLUSTERED INDEX [IX_MetricRecord_TagSetID]
+ ON [dbo].[MetricRecord]([TagSetID] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[MetricRecord].[UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp]...';
+ CREATE UNIQUE NONCLUSTERED INDEX [UX_MetricRecord_RecordTypeID_NodeID_TagSetID_RecordTimestamp]
+ ON [dbo].[MetricRecord]([RecordTypeID] ASC, [NodeID] ASC, [TagSetID] ASC, [RecordTimestamp] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Service' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[Service]...';
+ CREATE TABLE [dbo].[Service] (
+ [ServiceID] BIGINT IDENTITY (1, 1) NOT NULL,
+ [Name] NVARCHAR (256),
+ PRIMARY KEY CLUSTERED ([ServiceID] ASC)
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'Node' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[Node]...';
+ CREATE TABLE [dbo].[Node] (
+ [NodeID] INT IDENTITY (1, 1) NOT NULL,
+ [Name] NVARCHAR (256) NOT NULL,
+ [LastKnownIP] NVARCHAR (256) NULL,
+ [LastNameNodeHeartBeat] DATETIME NULL,
+ [LastJobTrackerHeartBeat] DATETIME NULL,
+ [LastDataNodeHeartBeat] DATETIME NULL,
+ [LastTaskTrackerHeartBeat] DATETIME NULL,
+ PRIMARY KEY CLUSTERED ([NodeID] ASC)
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_Node_Name' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[Node].[IX_Node_Name]...';
+ CREATE UNIQUE NONCLUSTERED INDEX [IX_Node_Name]
+ ON [dbo].[Node]([Name] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'RecordType' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[RecordType]...';
+ CREATE TABLE [dbo].[RecordType] (
+ [RecordTypeID] INT IDENTITY (1, 1) NOT NULL,
+ [Name] NVARCHAR (225) NOT NULL,
+ [Context] NVARCHAR (225) NOT NULL,
+ PRIMARY KEY CLUSTERED ([RecordTypeID] ASC)
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_RecordType_Context_Name' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[RecordType].[IX_RecordType_Context_Name]...';
+ CREATE UNIQUE NONCLUSTERED INDEX [IX_RecordType_Context_Name]
+ ON [dbo].[RecordType]([Context] ASC, [Name] ASC) ;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'TagSet' and type_desc = N'USER_TABLE')
+BEGIN
+ PRINT N'Creating [dbo].[TagSet]...';
+ CREATE TABLE [dbo].[TagSet] (
+ [TagSetID] INT IDENTITY (1, 1) NOT NULL,
+ [TagPairs] NVARCHAR (450) NOT NULL,
+ PRIMARY KEY CLUSTERED ([TagSetID] ASC)
+ );
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.indexes WHERE name = N'IX_TagSet_TagPairs' AND type_desc = N'NONCLUSTERED')
+BEGIN
+ PRINT N'Creating [dbo].[TagSet].[IX_TagSet_TagPairs]...';
+ CREATE UNIQUE NONCLUSTERED INDEX [IX_TagSet_TagPairs]
+ ON [dbo].[TagSet]([TagPairs] ASC) ;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_CompletedJob_TagSet_TagSetID')
+BEGIN
+ PRINT N'Creating FK_CompletedJob_TagSet_TagSetID...';
+ ALTER TABLE [dbo].[CompletedJob] WITH NOCHECK
+ ADD CONSTRAINT [FK_CompletedJob_TagSet_TagSetID] FOREIGN KEY ([TagSetID]) REFERENCES [dbo].[TagSet] ([TagSetID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricPair_MetricName_MetricID')
+BEGIN
+ PRINT N'Creating FK_MetricPair_MetricName_MetricID...';
+ ALTER TABLE [dbo].[MetricPair] WITH NOCHECK
+ ADD CONSTRAINT [FK_MetricPair_MetricName_MetricID] FOREIGN KEY ([MetricID]) REFERENCES [dbo].[MetricName] ([MetricID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricPair_MetricRecord_RecordID')
+BEGIN
+ PRINT N'Creating FK_MetricPair_MetricRecord_RecordID...';
+ ALTER TABLE [dbo].[MetricPair] WITH NOCHECK
+ ADD CONSTRAINT [FK_MetricPair_MetricRecord_RecordID] FOREIGN KEY ([RecordID]) REFERENCES [dbo].[MetricRecord] ([RecordID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_Node_NodeID')
+BEGIN
+ PRINT N'Creating FK_MetricRecord_Node_NodeID...';
+ ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
+ ADD CONSTRAINT [FK_MetricRecord_Node_NodeID] FOREIGN KEY ([NodeID]) REFERENCES [dbo].[Node] ([NodeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_RecordType_RecordTypeID')
+BEGIN
+ PRINT N'Creating FK_MetricRecord_RecordType_RecordTypeID...';
+ ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
+ ADD CONSTRAINT [FK_MetricRecord_RecordType_RecordTypeID] FOREIGN KEY ([RecordTypeID]) REFERENCES [dbo].[RecordType] ([RecordTypeID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = N'FK_MetricRecord_TagSet_TagSetID')
+BEGIN
+ PRINT N'Creating FK_MetricRecord_TagSet_TagSetID...';
+ ALTER TABLE [dbo].[MetricRecord] WITH NOCHECK
+ ADD CONSTRAINT [FK_MetricRecord_TagSet_TagSetID] FOREIGN KEY ([TagSetID]) REFERENCES [dbo].[TagSet] ([TagSetID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspInsertMetricValue' and type_desc = N'SQL_STORED_PROCEDURE')
+BEGIN
+ PRINT N'Creating [dbo].[uspInsertMetricValue]...';
+ exec('CREATE PROCEDURE [dbo].[uspInsertMetricValue]
+ @recordID bigint,
+ @metricName nvarchar(256),
+ @metricValue nvarchar(512)
+ AS
+ BEGIN
+ SET NOCOUNT ON;
+
+ DECLARE @metricID int;
+ DECLARE @err int;
+
+ IF @recordID IS NULL OR @metricName IS NULL RETURN;
+
+ BEGIN TRANSACTION;
+ SELECT @metricID = MetricID FROM MetricName WHERE Name = @metricName;
+ IF @metricID IS NULL
+ BEGIN
+ INSERT INTO MetricName (Name) VALUES (@metricName);
+ SELECT @err = @@ERROR, @metricID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ COMMIT TRANSACTION;
+
+ INSERT INTO MetricPair (RecordID, MetricID, MetricValue) VALUES (@recordID, @metricID, @metricValue);
+ RETURN;
+
+ Abort:
+ ROLLBACK TRANSACTION;
+ RETURN;
+ END')
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspUpdateHeartBeats' and type_desc = N'SQL_STORED_PROCEDURE')
+BEGIN
+ PRINT N'Creating [dbo].[uspUpdateHeartBeats]...';
+ exec('CREATE PROCEDURE [dbo].[uspUpdateHeartBeats]
+ @NodeID int,
+ @SourceIP nvarchar(256),
+ @NameNodeLast datetime,
+ @JobTrackerLast datetime,
+ @DataNodeLast datetime,
+ @TaskTrackerLast datetime,
+ @LastKnownIP nvarchar(256)
+ AS
+ BEGIN
+ IF @NodeID IS NOT NULL
+ BEGIN
+ IF @NameNodeLast IS NOT NULL
+ BEGIN
+ UPDATE Node SET LastNameNodeHeartBeat = @NameNodeLast WHERE NodeID = @NodeID;
+ END
+ IF @JobTrackerLast IS NOT NULL
+ BEGIN
+ UPDATE Node SET LastJobTrackerHeartBeat = @JobTrackerLast WHERE NodeID = @NodeID;
+ END
+ IF @DataNodeLast IS NOT NULL
+ BEGIN
+ UPDATE Node SET LastDataNodeHeartBeat = @DataNodeLast WHERE NodeID = @NodeID;
+ END
+ IF @TaskTrackerLast IS NOT NULL
+ BEGIN
+ UPDATE Node SET LastTaskTrackerHeartBeat = @TaskTrackerLast WHERE NodeID = @NodeID;
+ END
+ IF @LastKnownIP IS NULL OR @SourceIP <> @LastKnownIP
+ BEGIN
+ UPDATE Node SET LastKnownIP = @SourceIP WHERE NodeID = @NodeID;
+ END
+ END
+ END')
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspGetMetricRecord' and type_desc = N'SQL_STORED_PROCEDURE')
+BEGIN
+ PRINT N'Creating [dbo].[uspGetMetricRecord]...';
+ exec('CREATE PROCEDURE [dbo].[uspGetMetricRecord]
+ @recordTypeContext nvarchar(256),
+ @recordTypeName nvarchar(256),
+ @nodeName nvarchar(256),
+ @sourceIP nvarchar(256),
+ @clusterNodeName nvarchar(256),
+ @serviceName nvarchar(256),
+ @tagPairs nvarchar(512),
+ @recordTimestamp bigint,
+ @metricRecordID bigint OUTPUT
+ AS
+ BEGIN
+ SET NOCOUNT ON;
+
+ DECLARE @recordTypeID int
+ DECLARE @nodeID int
+ DECLARE @clusterNodeID int
+ DECLARE @tagSetID int
+ DECLARE @serviceID int
+ DECLARE @err int
+ DECLARE @recordIDCutoff bigint
+
+ BEGIN TRANSACTION;
+ SELECT @recordTypeID = RecordTypeID FROM RecordType WHERE Context = @recordTypeContext AND Name = @recordTypeName;
+ IF @recordTypeID IS NULL
+ BEGIN
+ INSERT INTO RecordType (Context, Name) VALUES (@recordTypeContext, @recordTypeName);
+ SELECT @err = @@ERROR, @recordTypeID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ COMMIT TRANSACTION;
+
+ BEGIN TRANSACTION;
+ SELECT @serviceID = serviceID FROM Service WHERE Name = @serviceName;
+ IF @serviceID IS NULL
+ BEGIN
+ INSERT INTO Service (Name) VALUES (@serviceName);
+ SELECT @err = @@ERROR, @serviceID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ COMMIT TRANSACTION;
+
+ BEGIN TRANSACTION;
+ SELECT @nodeID = NodeID FROM Node WHERE Name = @nodeName;
+
+ IF @nodeID IS NULL
+ BEGIN
+
+ /* Start with a node type of uninitialized. HealthNode will determine node type based on metrics delivered over time. */
+ INSERT INTO Node (Name, LastKnownIP) VALUES (@nodeName, @sourceIP);
+ SELECT @err = @@ERROR, @nodeID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+
+ COMMIT TRANSACTION;
+
+ -- Do our best to determine the cluster node ID based on completely flakey input from user which might be an IP address, a non-FQDN,
+ -- or an FQDN. Note that worker nodes may have a completely different idea about the name of the namenode (which is the node
+ -- which represents the cluster) compared with the namenode itself
+
+ BEGIN TRANSACTION;
+ IF ((SELECT [dbo].[ufnIsIPAddress](@clusterNodeName)) = 1)
+ BEGIN
+ SELECT TOP 1 @clusterNodeID = NodeID from Node WHERE LastKnownIP = @clusterNodeName ORDER BY LastNameNodeHeartBeat DESC;
+ IF @clusterNodeID IS NULL
+ BEGIN
+ INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
+ SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ END
+ ELSE
+ IF ((SELECT CHARINDEX(@clusterNodeName, ''.'', 1)) > 0)
+ BEGIN
+ -- IF this is not an IP address, but there is a dot in the name we assume we are looking at an FQDN
+ SELECT @clusterNodeID = NodeID FROM Node WHERE Name = @clusterNodeName;
+ IF @clusterNodeID IS NULL
+ BEGIN
+ INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
+ SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ END
+ ELSE
+ BEGIN
+ -- We have got a non-FQDN, but the NameNode might know its FQDN, so be careful! We must prefer the FQDN if we can find one.
+ -- Sadly, yes, this could break things if we are monitoring clusters from different domains. This is now by design!
+ SELECT TOP 1 @clusterNodeID = NodeID FROM Node WHERE Name LIKE @clusterNodeName + ''.%'' ORDER BY LastNameNodeHeartBeat DESC;
+ IF @clusterNodeID IS NULL
+ BEGIN
+ SELECT @clusterNodeID = NodeID FROM Node WHERE Name = @clusterNodeName;
+ if @clusterNodeID IS NULL
+ BEGIN
+ INSERT INTO Node (Name, LastKnownIP) VALUES (@clusterNodeName, @sourceIP);
+ SELECT @err = @@ERROR, @clusterNodeID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ END
+ END
+ COMMIT TRANSACTION;
+
+ -- Cleanup older metric records and pairs if necessary
+ -- Policy is to keep between 60000 and 90000 metric records and associated metric pairs per node.
+ IF (SELECT COUNT(*) FROM MetricRecord WHERE NodeID = @nodeID) > 90000
+ BEGIN
+ SELECT @recordIDCutoff = MIN(RecordID) FROM MetricRecord WHERE RecordID IN (SELECT TOP 60000 RecordID FROM MetricRecord WHERE NodeID = @nodeID ORDER BY RecordDate DESC);
+ IF @recordIDCutoff IS NOT NULL
+ BEGIN
+ DELETE FROM MetricPair
+ FROM MetricPair as mp
+ JOIN MetricRecord as mr ON mp.RecordID = mr.RecordID
+ WHERE mr.RecordID < @recordIDCutoff AND mr.NodeID = @nodeID;
+
+ DELETE FROM MetricRecord
+ WHERE RecordID < @recordIDCutoff AND NodeID = @nodeID;
+ END;
+ END;
+
+
+ BEGIN TRANSACTION;
+ SELECT @tagSetID = TagSetID FROM TagSet WHERE TagPairs = @tagPairs;
+ IF @tagSetID IS NULL
+ BEGIN
+ INSERT INTO TagSet (TagPairs) VALUES (@tagPairs);
+ SELECT @err = @@ERROR, @tagSetID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ COMMIT TRANSACTION;
+
+ BEGIN TRANSACTION;
+ SELECT @metricRecordID = RecordID FROM MetricRecord WHERE RecordTypeID = @recordTypeID AND NodeID = @nodeID AND ServiceID = @serviceID AND TagSetID = @tagSetID AND RecordTimestamp = @recordTimestamp;
+ IF @metricRecordID IS NULL
+ BEGIN
+ INSERT INTO MetricRecord (RecordTypeID, NodeID, SourceIP, ClusterNodeID, ServiceID, TagSetID, RecordTimestamp) VALUES (@recordTypeID, @nodeID, @sourceIP, @clusterNodeID, @serviceID, @tagSetID, @recordTimestamp);
+ SELECT @err = @@ERROR, @metricRecordID = SCOPE_IDENTITY();
+ IF @err <> 0 GOTO Abort;
+ END
+ COMMIT TRANSACTION;
+
+ GOTO Success;
+
+ Abort:
+ ROLLBACK TRANSACTION;
+ SET @metricRecordID = NULL;
+ RETURN;
+
+ Success:
+ RETURN;
+
+ END')
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufnIsIPAddress' and type_desc = N'SQL_SCALAR_FUNCTION')
+BEGIN
+ PRINT N'Creating [dbo].[ufnIsIPAddress]...';
+ exec('CREATE FUNCTION [dbo].[ufnIsIPAddress]
+ (
+ @inputString nvarchar(max)
+ )
+ RETURNS BIT
+ AS
+ BEGIN
+ DECLARE @currentPos bigint = 1;
+ DECLARE @nextPos bigint = 0;
+ DECLARE @count int = 0;
+
+ if (LEN(@inputString) = 0) RETURN 0;
+
+ SELECT @nextPos = CHARINDEX(''.'', @inputString, @currentPos);
+
+ WHILE (@nextPos < LEN(@inputString) AND @count < 4)
+ BEGIN
+ IF (@nextPos = 0) SET @nextPos = LEN(@inputString);
+ IF ((SELECT ISNUMERIC(SUBSTRING(@inputString, @currentPos, @nextPos - @currentPos))) = 1)
+ BEGIN
+ SET @count = @count + 1;
+ SET @currentPos = @nextPos
+ SELECT @nextPos = CHARINDEX(''.'', @inputString, @currentPos + 1);
+ END
+ ELSE BREAK;
+ END
+
+ IF (@count = 4) RETURN 1;
+
+ SET @currentPos = 1;
+ SET @nextPos = 0;
+ SET @count = 0;
+
+ WHILE (@currentPos <= LEN(@inputString))
+ BEGIN
+ IF EXISTS (SELECT 1 WHERE SUBSTRING(@inputString, @currentPos, 1) LIKE ''[0-9A-Fa-f:]'')
+ BEGIN
+ IF (SUBSTRING(@inputString, @currentPos, 1) = N'':'') SET @count = @count + 1;
+ SET @currentPos = @currentPos + 1;
+ END
+ ELSE RETURN 0;
+ END
+ IF @count >= 4 return 1;
+
+ RETURN 0;
+ END')
+END
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'RethrowError' and type_desc = N'SQL_STORED_PROCEDURE')
+BEGIN
+ PRINT N'Creating Stored Proc: [dbo].[RethrowError]...';
+ exec('CREATE PROCEDURE [dbo].[RethrowError]
+ AS
+ BEGIN
+ DECLARE @ErrorMessage NVARCHAR(4000);
+ DECLARE @ErrorSeverity INT;
+ DECLARE @ErrorState INT;
+
+ SELECT
+ @ErrorMessage = ERROR_MESSAGE(),
+ @ErrorSeverity = ERROR_SEVERITY(),
+ @ErrorState = ERROR_STATE();
+
+ RAISERROR (@ErrorMessage, -- Message text.
+ @ErrorSeverity, -- Severity.
+ @ErrorState -- State.
+ );
+ END
+ ')
+END
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'uspPurgeMetrics' and type_desc = N'SQL_STORED_PROCEDURE')
+BEGIN
+ -- purge metrics older than @noOfDays
+ PRINT N'Creating [dbo].[uspPurgeMetrics]...';
+ exec('CREATE PROCEDURE [dbo].[uspPurgeMetrics]
+ @noOfDays bigint
+ AS
+ BEGIN
+
+ IF @noOfDays IS NULL OR @noOfDays < 1
+ BEGIN
+ RAISERROR(''INVALID_ARGUMENT'', 15, 1)
+ RETURN
+ END;
+
+ DECLARE @recordIDCutOff BIGINT
+ SELECT @recordIDCutoff = MAX(RecordID) FROM MetricRecord WHERE DateDiff(day, RecordDate, CURRENT_TIMESTAMP) >= @noOfDays
+
+ IF @recordIDCutoff IS NOT NULL
+ BEGIN
+ BEGIN TRY
+ BEGIN TRANSACTION
+
+ DELETE FROM MetricPair WHERE RecordID <= @recordIDCutoff
+
+ DELETE FROM MetricRecord WHERE RecordID <= @recordIDCutoff
+
+ IF @@TRANCOUNT > 0
+ BEGIN
+ COMMIT TRANSACTION
+ END
+
+ END TRY
+ BEGIN CATCH
+ IF @@TRANCOUNT > 0
+ BEGIN
+ ROLLBACK TRANSACTION;
+ END
+
+ -- get error infromation and raise error
+ EXECUTE [dbo].[RethrowError]
+ RETURN
+
+ END CATCH
+ END;
+ END');
+END
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufGetMetrics' and type_desc = N'SQL_TABLE_VALUED_FUNCTION')
+BEGIN
+ PRINT N'Creating [dbo].[ufGetMetrics]...';
+ exec('CREATE FUNCTION dbo.ufGetMetrics
+ (@startTimeStamp bigint,
+ @endTimeStamp bigint,
+ @recordTypeContext NVARCHAR(256),
+ @recordTypeName NVARCHAR(256),
+ @metricName NVARCHAR(256),
+ @serviceComponentName NVARCHAR(256),
+ @nodeName NVARCHAR(256)
+ )
+ RETURNS TABLE --(MetricTimeStamp bigint, MetricValue NVARCHAR(512))
+ AS
+ RETURN
+ (
+ SELECT s.RecordTimeStamp AS RecordTimeStamp,
+ mp.MetricValue AS MetricValue
+ FROM MetricPair mp
+ INNER JOIN (SELECT mr.RecordID AS RecordID,
+ mr.RecordTimeStamp AS RecordTimeStamp
+ FROM MetricRecord mr
+ INNER JOIN RecordType rt ON (mr.RecordTypeId = rt.RecordTypeId)
+ INNER JOIN Node nd ON (mr.NodeID = nd.NodeID)
+ INNER JOIN Service sr ON (mr.ServiceID = sr.ServiceID)
+ WHERE rt.Context = @recordTypeContext
+ AND rt.Name = @recordTypeName
+ AND (nd.Name = @nodeName)
+ AND (sr.Name = @serviceComponentName)
+ AND mr.RecordTimestamp >= @startTimeStamp
+ AND mr.RecordTimestamp <= @endTimeStamp
+ ) s ON (mp.RecordID = s.RecordID)
+ INNER JOIN MetricName mn ON (mp.MetricID = mn.MetricID)
+ WHERE (mn.Name = @metricName)
+ )'
+)
+END
+
+GO
+
+IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = N'ufGetAggregatedServiceMetrics' and type_desc = N'SQL_TABLE_VALUED_FUNCTION')
+BEGIN
+ PRINT N'Creating [dbo].[ufGetAggregatedServiceMetrics]...';
+ exec( 'CREATE FUNCTION [dbo].[ufGetAggregatedServiceMetrics]
+ (@startTimeStamp bigint,
+ @endTimeStamp bigint,
+ @recordTypeContext NVARCHAR(256),
+ @recordTypeName NVARCHAR(256),
+ @metricName NVARCHAR(256),
+ @serviceComponentName NVARCHAR(256),
+ @period integer
+ )
+ RETURNS TABLE ----(TimeStampBlock integer, MetricTimeStamp bigint, MetricValue NVARCHAR(512))
+ AS
+ RETURN
+ (
+ SELECT FLOOR ((mr.RecordTimeStamp - @startTimeStamp) / @period) TimeStampBlock, MAX(mr.RecordTimeStamp) RecordTimeStamp, SUM(CONVERT(NUMERIC(18,4), MetricValue)) AggMetricValue
+ FROM MetricPair mp
+ INNER JOIN MetricRecord mr ON (mp.RecordID = mr.RecordID)
+ INNER JOIN RecordType rt ON (rt.RecordTypeID = mr.RecordTypeID)
+ INNER JOIN MetricName mn ON (mn.MetricID = mp.MetricID)
+ INNER JOIN Service sr ON (sr.ServiceID = mr.ServiceID)
+ WHERE mr.RecordTimestamp >= @startTimeStamp
+ AND mr.RecordTimestamp <= @endTimeStamp
+ AND mn.Name = @metricName
+ AND rt.Context = @recordTypeContext
+ AND rt.Name = @recordTypeName
+ AND sr.Name = @serviceComponentName
+ GROUP BY FLOOR ((mr.RecordTimeStamp - @startTimeStamp) / @period)
+ )'
+ )
+END
+GO
http://git-wip-us.apache.org/repos/asf/ambari/blob/8de3425f/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATELOCAL.sql
----------------------------------------------------------------------
diff --git a/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATELOCAL.sql b/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATELOCAL.sql
new file mode 100644
index 0000000..65218bb
--- /dev/null
+++ b/contrib/ambari-scom/metrics-sink/db/Hadoop-Metrics-SQLServer-CREATELOCAL.sql
@@ -0,0 +1,140 @@
+/*
+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.
+*/
+
+/*
+Deployment script for $(METRICSDBNAME)
+
+Use this script in sqlcmd mode, with a series of environment variables like this:
+set METRICSDBNAME=HadoopMetrics
+set METRICSDBLOGNAME=HadoopMetrics_log
+
+set METRICSDBPATH=C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\HadoopMetrics.mdf
+set METRICSDBLOGPATH=C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\HadoopMetrics_log.ldf
+
+set METRICSDBOWNER=hadoop
+
+sqlcmd -S localhost\SQLEXPRESS -i C:\app\ambari-server-1.3.0-SNAPSHOT\resources\Hadoop-Metrics-SQLServer-CREATELOCAL.sql
+*/
+
+USE [master]
+GO
+
+IF db_id('$(METRICSDBNAME)') IS NOT NULL
+BEGIN
+ Print N'Dropping [dbo].[$(METRICSDBNAME)] database...'
+ ALTER DATABASE [$(METRICSDBNAME)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
+ DROP DATABASE [$(METRICSDBNAME)]
+END
+GO
+
+Print N'Creating [dbo].[$(METRICSDBNAME)] database at $(METRICSDBPATH)...'
+
+/****** Object: Database [$(METRICSDBNAME)] Script Date: 9/11/2014 3:58:17 PM ******/
+CREATE DATABASE [$(METRICSDBNAME)]
+ CONTAINMENT = NONE
+ ON PRIMARY
+ (NAME = N'$(METRICSDBNAME)', FILENAME = N'$(METRICSDBPATH)' , SIZE = 3264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)
+ LOG ON
+ (NAME = N'$(METRICSDBLOGNAME)', FILENAME = N'$(METRICSDBLOGPATH)' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
+GO
+
+ALTER DATABASE [$(METRICSDBNAME)] SET COMPATIBILITY_LEVEL = 120
+GO
+IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
+BEGIN
+ EXEC [$(METRICSDBNAME)].[dbo].[sp_fulltext_database] @action = 'enable'
+END
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ANSI_NULL_DEFAULT OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ANSI_NULLS OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ANSI_PADDING OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ANSI_WARNINGS OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ARITHABORT OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET AUTO_CLOSE ON
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET AUTO_SHRINK OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET AUTO_UPDATE_STATISTICS ON
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET CURSOR_CLOSE_ON_COMMIT OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET CURSOR_DEFAULT GLOBAL
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET CONCAT_NULL_YIELDS_NULL OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET NUMERIC_ROUNDABORT OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET QUOTED_IDENTIFIER OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET RECURSIVE_TRIGGERS OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ENABLE_BROKER
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET DATE_CORRELATION_OPTIMIZATION OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET TRUSTWORTHY OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET ALLOW_SNAPSHOT_ISOLATION OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET PARAMETERIZATION SIMPLE
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET READ_COMMITTED_SNAPSHOT OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET HONOR_BROKER_PRIORITY OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET RECOVERY SIMPLE
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET MULTI_USER
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET PAGE_VERIFY CHECKSUM
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET DB_CHAINING OFF
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET TARGET_RECOVERY_TIME = 0 SECONDS
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET DELAYED_DURABILITY = DISABLED
+GO
+ALTER DATABASE [$(METRICSDBNAME)] SET READ_WRITE
+GO
+
+USE [$(METRICSDBNAME)]
+GO
+
+IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE METRICSDBNAME MODIFY FILEGROUP [PRIMARY] DEFAULT
+GO
+
+ALTER authorization on DATABASE::$(METRICSDBNAME) to [NT AUTHORITY\SYSTEM]
+GO
+
+if exists (select 1 from master.sys.syslogins where name='$(METRICSDBOWNER)')
+BEGIN
+ CREATE USER [$(METRICSDBOWNER)] FOR LOGIN [$(METRICSDBOWNER)]
+ ALTER ROLE [db_owner] ADD MEMBER [$(METRICSDBOWNER)]
+END
+GO
+
+Print N'[dbo].[$(METRICSDBNAME)] database created.'