How to run SQL queries from services when data migrated from SQL server to data lake?

[StanleyTang]

Currently our service provides an API to serve the purchase records. The purchase records are stored in SQL database. To simplify, when users want to get their recent purchase records, they make an API call. The API call will run a SQL query on the DB and return recent records to callers.

Recently the purchase records data are migrated to Data Lake and can access through Azure Databricks. I go through the tutorial of Azure Databricks and can run some SQL queries on a cluster to retrieve those recent purchase records, but that seems more of a batch processing manner.

Now I am migrating our service to use data lake as a data storage. What is a good practice to support the scenario, where users trigger an API call to run a SQL query and get the recent records immediately? Would a Databricks SQL warehouse suitable for this?

[Debayan]

Hi @Stanley Tang​ , There are several rest API resources managed by Databricks. You can refer https://docs.databricks.com/dev-tools/api/latest/index.html.

In this scenario, SQL Warehouses API can be used: https://docs.databricks.com/sql/api/sql-endpoints.html

Please let us know if this helps.

[StanleyTang]

Hi @Debayan Mukherjee​, I investigated the documents. It seems that SQL Warehouses API is used to configure and manage the SQL Warehouse.

However, our demand is we can setup a place has similar behavior like SQL server, where we can run SQL queries and get data immediately through calling API.

For example, I maintain some queries, e.g. QueryRecent10Records. and I would like to get the result for QueryRecent10Records through sending API request from our service.

Please correct me if I misunderstand.

[Debayan]

Hi @Stanley Tang​ , for this we have query API : https://docs.databricks.com/sql/api/queries-dashboards.html

Also, you can go through the variety of APIs databricks has:

https://docs.databricks.com/dev-tools/api/latest/index.html