Apache Drill for DB Connection in Postman

Author : Geetu Sadana

Image Credits : https://en.wikipedia.org/wiki/Apache_Drill

Looking into building up an end to end solution for API testing, Postman being a rest client not a DB client was a big challenge. So we need a Middleware to communicate from Postman to DB directly. So this is how it works -

RESTFUL layer <> Middle layer <> Database

Extensive R&D was performed to establish DB connection via postman as not much information was available on the internet.

This can be resolved by installing the Apache Drill framework. It is an open source SQL query engine for Big Data exploration.

Pre-Requisites:

• A JAVA_HOME environment variable that points to the JDK installation
• A PATH environment variable includes pointer to bin directory of JDK installation

Install Drill on Windows:

• Download the latest version of Apache Drill.
• Move the apache-drill-<version>.tar.gz file to a directory where you want to install Drill.
• Unzip the TAR.GZ file

Start Drill on Windows:

• Open Command Prompt.
• Open the apache-drill- folder.
• Go to the bin directory.
• Type command on the command line: drill-embedded.bat
• Drill will start on your system

Exiting the Drill Shell:

To exit the Drill shell, type ‘!quit’ command on prompt

Accessing the Web UI:

To access the Drill Web UI, enter the URL for your Drill configuration

http://localhost:8047

Storage Plugin:

• Start the Web UI
• Go to Storage tab and Click on Create plugin

• In Configuration, set the required properties using JSON format as shown in the following example. Change the properties to match your environment. Each configuration registered with Drill must have a distinct name.

{

“type”: “jdbc”,

“driver”: “com.mysql.cj.jdbc.Driver”,

“url”: “jdbc:mysql://IP of your DB environment”,

“username”: “username”,

“password”: “password”,

“enabled”: true

}

• Click Create.
• You can now perform the sql query on Drill command prompt OR Web UI in query section. Query should be in below format

select * from myplugin.schema_name.table_name

Use Postman to Run SQL Queries on Drill Data Source:

• Make sure that your Drill service is running within command prompt
• In Postman, set type to POST, enter the request URL as http://<drillbit-hostname>:8047/query.json.
• Select the Headers tab and then enter Content-Type as the key and application/json as the value.
• Select application/json from Body tab while raw radio button enabled.
• In Body box, enter your query to get response in JSON format from Drill data source as below, Postman will display the query result.

{

“queryType”: “SQL”,

“query”: “select * from ` select * from myplugin.schema_name.table_name;`”

}

Other blogs in this series -

Advanced Features of Postman :

Postman is a powerful tool used to test web services. It offers a simple user-friendly interface. List of various advanced features of postman

• Collection runner
• Data Driven Testing in collection
• Global Variables
• Assertions

To learn more about these features, refer here.

Newman :

Newman is a command line Collection Runner for Postman. It allows you to run and test a Postman Collection directly from the command line. To read more about Newman refer here.

Codeless API Automation and Monitoring System using Postman :

This blog describes the technique to automate APIs using Postman instead of Rest Assured and describes the advantages of using Postman over Rest Assured. To read more about this, refer here.