JMeter Result Analysis using Datasette
Tags: performance, testing, tutorial, database
Published: 2021-02-01 15:28:32

<!-- wp:paragraph -->
<p>Analyzing the results is one of the critical tasks for any performance engineers. Many of us are comfortable using the spreadsheet program if the data set is small. But if you have large data sets, it is very difficult to use spreadsheet programs. In that case, you can use the visualization app such as Grafana, or APM tools like Dynatrace or New Relic. In this blog post, I am going to show you, how you can export and publish your JMeter results to cloud, analyze and share it in a couple of steps. We are going to leverage Datasette tool to export and publish the JMeter results to Vercel.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2 id="h-what-is-datasette">What is Datasette?</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p><em>Datasette is a tool for exploring and publishing data. It helps people take data of any shape or size and publish that as an interactive, explorable website and accompanying API.</em></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><em>Datasette is aimed at data journalists, museum curators, archivists, local governments and anyone else who has data that they wish to share with the world.</em></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here <a href="https://datasette.io/examples">https://datasette.io/examples</a> you can explore few example data sets.</p>
<!-- /wp:paragraph -->

<!-- wp:generateblocks/button-container {"uniqueId":"481a49c4","alignment":"center","isDynamic":true} -->
<!-- wp:generateblocks/button {"uniqueId":"da0aa7aa","hasUrl":true,"backgroundColor":"#0366d6","textColor":"#ffffff","backgroundColorHover":"#222222","textColorHover":"#ffffff","paddingTop":"15","paddingRight":"20","paddingBottom":"15","paddingLeft":"20","gradient":true} -->
<a class="gb-button gb-button-da0aa7aa gb-button-text" href="https://github.com/simonw/datasette">Datasette GitHub Repo</a>
<!-- /wp:generateblocks/button -->
<!-- /wp:generateblocks/button-container -->

<!-- wp:heading -->
<h2 id="h-prerequisites">Prerequisites&nbsp;</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Following are the prerequisites required to export and publish the JMeter test results to Vercel via Datasette.</p>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><li><a href="https://docs.microsoft.com/en-us/windows/wsl/install-win10" target="_blank" rel="noreferrer noopener nofollow">Enable Windows WSL</a></li><li><a href="https://vercel.com" target="_blank" rel="noreferrer noopener nofollow">Vercel</a> account which is free to create</li><li><a href="https://vercel.com/download" target="_blank" rel="noreferrer noopener nofollow">Vercel CLI</a></li><li>Python and PIP</li><li>Datasette and its utilities</li><li><a href="https://qainsights.com/jmeter-integration-with-elastic/" target="_blank" rel="noreferrer noopener">JMeter </a>test results</li></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>If you are using Windows 10, you will not be able to publish the results to Vercel. I have already raised <a href="https://github.com/simonw/datasette/issues/1196" target="_blank" rel="noreferrer noopener">an issue</a> in Datasette repo.</p>
<!-- /wp:paragraph -->

<!-- wp:buttons {"align":"center"} -->
<div class="wp-block-buttons aligncenter"><!-- wp:button {"gradient":"midnight"} -->
<div class="wp-block-button"><a class="wp-block-button__link has-midnight-gradient-background has-background" href="https://github.com/QAInsights/Datasette-JMeter" target="_blank" rel="noreferrer noopener">JMeter Datasette Repo</a></div>
<!-- /wp:button --></div>
<!-- /wp:buttons -->

<!-- wp:heading -->
<h2 id="h-let-s-get-started">Let's get started</h2>
<!-- /wp:heading -->

<!-- wp:heading {"level":3} -->
<h3 id="h-installing-datasette">Installing Datasette</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Launch Windows Terminal and open Ubuntu and enter the below command to install Datasette.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>pip install datasette</code></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8179,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-39.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-39-1024x537.png" alt="JMeter Result Analysis using Datasette - Installing Datasette" class="wp-image-8179"/></a><figcaption>JMeter Result Analysis using Datasette - Installing Datasette</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>This will install Datasette in WSL. To verify, issue the command <code>datasette --version</code>.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 id="h-installing-vercel-cli">Installing Vercel CLI</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Follow the <a href="https://vercel.com/download" target="_blank" rel="noreferrer noopener nofollow">instructions</a> mentioned here to install Vercel CLI.</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":3} -->
<h3 id="h-installing-csvs-to-sqlite">Installing CSVS to SQLite</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Output of JMeter result is CSV. Datasette uses SQLite to store the data. Before we publish the results to Vercel, we need to convert CSV into SQLite. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To install this utility, issue the below command in WSL.</p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>pip install csvs-to-sqlite</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p>There is a plugin called <strong>vega </strong>to visualize the JMeter results, but it can be installed later.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2 id="h-converting-csv-to-sqlite">Converting CSV to SQLite</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Now, it is time to convert the JMeter results to SQLite database. Copy the CSV file to the current directory.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8181,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-41.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-41.png" alt="Run1.csv" class="wp-image-8181"/></a><figcaption>Run1.csv</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>To convert CSV to SQLite, issue the below command.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>csvs-to-sqlite Run1.csv JMeterResults.db</code></p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":8182,"sizeSlug":"large","linkDestination":"media"} -->
<figure class="wp-block-image size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-42.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-42-1024x170.png" alt="CSV to SQLite" class="wp-image-8182"/></a><figcaption>CSV to SQLite</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Now we have got the SQLite database which is ready to publish to Vercel.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8183,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-43.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-43.png" alt="SQLite DB" class="wp-image-8183"/></a><figcaption>SQLite DB</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Before we publish it to Vercel, let us publish it to localhost.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2 id="h-publishing-results">Publishing Results</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>To begin publishing to localhost, issue the below command. This will open the Datasette application in the available port.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>datasette serve JMeterResults.db -p 0</code></p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8185,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-45.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-45-1024x117.png" alt="Serving Local" class="wp-image-8185"/></a><figcaption>Serving Local</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Launch the localhost app where you can see the JMeter Results and uploaded Run1.csv file.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8186,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-46.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-46.png" alt="Datasette Home Page" class="wp-image-8186"/></a><figcaption>Datasette Home Page</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Now you can slice and dice the data to analyze the results. It is extremely fast and load time is in milliseconds.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>But this data set doesn't have <code>vega</code>. Let us install <code>vega</code> plugin to visualize the charts.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Issue the below command to install <code>vega</code>.</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">datasette install datasette-vega</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>Now publish the database again and navigate to the app. You could see the chart for the JMeter results.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8187,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-47.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-47.png" alt="JMeter Datasette" class="wp-image-8187"/></a><figcaption>JMeter Datasette</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:heading -->
<h2 id="h-publish-to-vercel">Publish to Vercel</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Now let us take our game to the next level i.e. to the cloud. Open the Windows Ubuntu Terminal and issue the below command to login into your Vercel account.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Issue the below command to login into Vercel.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>vercel login</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>This will prompt you to enter the email ID as shown below.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":8191,"sizeSlug":"large","linkDestination":"media"} -->
<figure class="wp-block-image size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-50.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-50-1024x119.png" alt="Vercel Login" class="wp-image-8191"/></a><figcaption>Vercel Login</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Check your inbox and verify the login. After successful verification, you will get the below message.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":8192,"sizeSlug":"large","linkDestination":"media"} -->
<figure class="wp-block-image size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-51.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-51-1024x155.png" alt="Vercel Successful Login" class="wp-image-8192"/></a><figcaption>Vercel Successful Login</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Before publishing, we need to install Datasette plugin for Vercel using the below command.</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">pip install datasette-publish-vercel</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>Now, we are ready to publish the data to Vercel. To publish the database to Vercel, issue the below command.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>datasette publish vercel JMeterResults.db --project jmeter --install datasette-vega</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>After successful deployment, you will see the link for your project. Here is the link which we published JMeter results to Vercel <a href="https://jmeter.vercel.app/" target="_blank" rel="noreferrer noopener">https://jmeter.vercel.app/</a>.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":8193,"sizeSlug":"large","linkDestination":"media"} -->
<figure class="wp-block-image size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-52.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-52-1024x186.png" alt="Publishing Datasette" class="wp-image-8193"/></a><figcaption>Publishing Datasette</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>After deployment, you can slice and dice the data as shown. Datasette will work only for read queries. You can filter, export, visualize, and publish your data to the supported cloud environment in few steps.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8196,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-54.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-54.png" alt="Exploring Datasette" class="wp-image-8196"/></a><figcaption>Exploring Datasette</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Example filtering in Datasette.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8197,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-55.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-55.png" alt="Filter and more" class="wp-image-8197"/></a><figcaption>Filter and more</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Just below the above options, you can see the raw data with filter, sort, and facet options.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":8199,"sizeSlug":"large","linkDestination":"media"} -->
<div class="wp-block-image"><figure class="aligncenter size-large"><a href="https://qainsights.com/wp-content/uploads/2021/01/image-56.png"><img src="https://qainsights.com/wp-content/uploads/2021/01/image-56.png" alt="Filter, Sort and Facet" class="wp-image-8199"/></a><figcaption>Filter, Sort and Facet</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:heading -->
<h2 id="h-conclusion">Conclusion</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>Datasette helps in exploring and publishing test results to local or cloud. It will help you to define a new perspective to view the test results to detect the anomalies. It is light-weight and fast. Due to its CLI nature, you can easily integrate in the CI/CD pipeline to deploy the dataset to your stakeholders. Datasette works with any performance testing tool. All you need is your raw data in CSV format or SQLite format.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Are you ready to explore Datasette? Please share your inputs in the comments.</p>
<!-- /wp:paragraph -->