SNOR🪀L

snorql a.k.a. SQL-Monitoring is an open-source, free-to-use project developed at udaan aimed at diagnosing and resolving common database-related problems using SQL metrics.
Explore the docs »

View Demo · Report Bug · Request Feature

Table of Contents

1. About The Project
   • Built With
2. Usage
   • How to integrate snorql in your project
   • Enable snorql-extensions in your project
   • Build your own custom metrics using snorql
3. Kotlin Documentation
   • snorql-framework KDocs
   • snorql-extension KDocs
4. Roadmap
5. Contributing
6. License
7. Contact
8. Acknowledgments

About The Project

SNORQL comes with two modules

• snorql-framework
• snorql-extensions

What is snorql-framework?

This is a basic framework of the underlying tool used to build metrics. It is extensible in nature and contains all the models, interfaces and classes that can be used to build your own metric.

What is snorql-extensions?

This is built using snorql-framework and acts as a repository of SQL metrics that can be consumed in your project layer. It exposes useful SQL metrics that can be integrated & used with your application

Current extensions include:

• Blocked Queries
• Long-running Queries
• Active Queries
  ... to view the complete list please click here

You can also add your own metrics by following the instructions below under Build your own custom metrics using snorql.

See issues with new-metric label for details on newer extension planned in the roadmap.

(back to top)

Built With

• Kotlin
• Maven

(back to top)

Usage

How to integrate snorql in your project

Add this library as a dependency:

    <dependency>
        <groupId>com.udaan.snorql</groupId>
        <artifactId>snorql-framework</artifactId>
        <version>${versions.snorql-framework}</version>
    </dependency>

Add this these properties to your parent pom:

    <properties>
        <versions.snorql-framework>[1.0,2.0)</versions.snorql-framework>
    </properties>

Implement Connection Interface

@Singleton
class <ExampleSQLConnection> @Inject constructor(private val jdbi3Factory: Jdbi3Factory) : Connection {
    override fun <T> run(databaseName:String, query: String,mapClass: Class<T>,params: Map<String, *>): List<T> {
        // using a db instance, execute the query, Bind to <T>
    }

    override fun storeData(databaseName:String, tableName: String, columns: List<String>, rows: List<List<Any>>) {
        // using a db instance, persist the data to database
    }
}

You can use any SQL database driver. Below is an example using JDBI Driver.

class SQlServerConnection constructor(private val jdbi3Factory: Jdbi3Factory) : Connection {
    override fun <T> run(databaseName:String, query: String, mapClass: Class<T>,params: Map<String, *>): List<T> {
        return if(params.isNotEmpty()){
            jdbi3Factory.getInstance(databaseName).withHandle<List<T>, Exception> { handle ->
                handle.createQuery(query).bindMap(params).mapTo(mapClass).toList()
            }
        } else{
            jdbi3Factory.getInstance(databaseName).withHandle<List<T>, Exception> { handle ->
                handle.createQuery(query).mapTo(mapClass).toList() as List<T>
            }
        }
    }

    override fun storeData(databaseName:String, tableName: String,columns: List<String>, rows: List<List<Any>>) {
        val columnStr = columns.joinToString(", ")
        return jdbi3Factory.getInstance(databaseName).withHandle<Unit, Exception> { handle ->
            handle.prepareBatch("INSERT INTO $tableName ($columnStr) VALUES (<values>) ")
                .bindList("values", rows)
                .execute()
        }
    }
}

Generate the metric response

Make a call to <SqlMetricManager.getMetric()> with appropriate input to get the result.

Below is an example to consume the metric as an API.

/
 * Fetches list of active-queries metric for a [activeQueryMetricInput]
 */
@POST
@Path("activeQueries")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
fun getActiveQueryMetric(
    @RequestBody activeQueryInput: ActiveQueryInput,
    @Suspended asyncResponse: AsyncResponse
) = asyncResponse.with {
    val response = SqlMetricManager.getMetric<ActiveQueryInput, ActiveQueryResult, IMetricRecommendation>(
        SQLMetricTypes.ACTIVE_QUERIES.metricId, metricInput
  }
}

Enable 'snorql-extensions' in your project (optional)

Pre-requisites: How to integrate snorql in your project

Add this library as a dependency:

    <dependency>
        <groupId>com.udaan.snorql</groupId>
        <artifactId>snorql-extensions</artifactId>
        <version>${versions.snorql-extensions}</version>
    </dependency>

Add this these properties to your parent pom:

    <properties>
        <versions.snorql-extensions>[1.0,2.0)</versions.snorql-extensions>
    </properties>

Initialize SQLCommonMetrics

Call registerSQLMetrics() during module bootstrap of the application

/
 * RegisterSQLMetrics in App Startup
*/
fun registerSQLMetrics() { 
 // Initialises all the metric provided in snorql-extensions
    SQLCommonMetrics.initialize()
}

Build your own custom metrics using snorql

Let's see how we can build your own custom metric using snorql-framework in just 8 simple steps

Step1. Add a new json object for your metric to sql-monitoring-conf.json

"<metricName>": {
    "queries": {
      "main": "<metricQuery>"
    },
    "supportsHistorical": <boolean>,
    "supportsRealTime": <boolean>,
    "isParameterized": <boolean>
  }

Step2. Create a new enum member for your Metric in the Enum Class

enum class <MetricEnum>(private val metricId:String):IMtericId {
    <METRIC_NAME>("<metricName>");
    override fun getId(): String {
       TODO("Implement this")
    }
}

Step3. Create a MetricDTOClass

data class <MetricDTO> (
     TODO("Add members for your metric")
)

Step4. Create new MetricInputClass

data class <MetricInput>(
      TODO("Override <MetricInput> members")
      TODO("Add your members")
) : MetricInput()

Step5. Create a MetricResultClass

data class <MetricResult>(val queryList: List<MetricDTO>) : IMetricResult()

Step6. Create a MetricRecommendationClass(optional: Only if your metric supports recommendation)

data class <MetricRecommendation>(val queryList: List<MetricDTO>) : IMetricRecommendation()

Now that we have created our model classes, we can use them to implement our metric

Step7. Create a MetricClass

class <Metric>: IMetric<T, R, IMetricRecommendation>{
    override fun getMetricResult(metricInput: MetricInputClass,metricConfig: MetricResultClass): MetricResultClass {
        // Business logic to fetch metric query, execute and format it according to <MetricDTO> 
        TODO("Implement this")
    }
    
    override fun getMetricResponseMetadata(metricInput: MetricInputClass, metricOutput: MetricOutput<MetricResultClass, IMetricRecommendation>): Map<String, Any>? {
        // Business logic to add your metadata (ex: Metric Input Object, Metric Query, etc..)
        TODO("Implement this")
    }

    override fun saveMetricResult(metricInput: MetricInput, result: IMetricResult) {
        // Logic to persist the [result] data object to your data store
        TODO("Implement this")
    }	
}

Step8. Finally, Register your Metric to the SqlMetricManager

SqlMetricManager
    .addMetric(<MetricCategoryEnums>.<METRIC>.getId(), <MetricEnumName>())

Note: Add the above code(Step8) during the start of your application.

Here's an example to create your first custom metric

(back to top)

Kotlin Documentation

snorql-framework KDocs

Please find the detailed KDoc for snorql-framework here.

snorql-extensions KDocs

Please find the detailed KDoc for snorql-extensions here.

(back to top)

Roadmap

• [] Snorql Persistence
  • Storage/Querying of historical metrics
  • [] Auto purge older metrics
• [] Snorql Alerting

See the open issues for a full list of proposed features (and known issues).

(back to top)

Contributing

Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.

If you have a suggestion that would make this better, please fork the repo and create a pull request. You can also simply open an issue with the tag "enhancement". Don't forget to give the project a star! Thanks again!

1. Fork the Project
2. Create your Feature Branch (git checkout -b feature/AmazingFeature)
3. Commit your Changes (git commit -m 'Add some AmazingFeature')
4. Push to the Branch (git push origin feature/AmazingFeature)
5. Open a Pull Request

(back to top)

License

Distributed under the Apache License. See LICENSE for more information.

(back to top)

Contact

udaan.com - @udaandotcom

Project Link: https://github.com/udaan-com/snorql

Report Bug · Request Feature

(back to top)

Acknowledgments

• Lakshmi Ramakrishnan
• Anish Tiwari
• Prakul Jain
• Prince Bansal
• Lokesh Devnani
• Kaushik Mukherjee

(back to top)