How to connect databricks delta tables from Django application

Question

I am new to Django framework, in my project I have requirement that I have to connect to databricks delta tables and perform CRUD operation on it. If we can connect it. I request you to list down the steps please.Thanks.

Answer 1

what you tried using databricks-sql-connector was correct, But you have confusion about the database details in settings.py, there you can’t add databricks cluster database. As per documentation following are the supporting

Database you can use in Django. (https://docs.djangoproject.com/en/4.1/ref/databases/)

1. PostgreSQL

2. MariaDB

3. MySQL

4. Oracle

5. SQLite

So here the idea is get data from delta tables using databricks-sql-connector and handle those data

According to your application.

Below are the steps to follow,

Step 1: Install python package in your Django python environment

https://pypi.org/project/databricks-sql-connector/

pip install databricks-sql-connector

Step 2: Configurations in setting.py Leave the default database as it is.

Step 3: Run python migration.

python .\manage.py makemigrations

Step 4: Create a delta table in databricks cluster

%sql
CREATE TABLE products (
product_name string,
price int)
USING DELTA;

Above is the code for creating table. Here you can see column names are similar to model fields created in models.py.

Step 5: Insert data into the table

insert into products values ('TV',60000)
insert into products values (Iphone,50000)
insert into products values (Macbook,150000)

Step 6:

Get the host, http_path and access token from databricks cluster.

You can find your host and http path under Cluster > Configuration > Advanced Options > JDBC/ODBC

For access token, you need to create one. Go to the drop down on right of databricks menu > User settings > Generate new token .

Step 7:

Know create a view to get the above data. Here is an example for read operation

from django.shortcuts import render
from databricks import sql
def get(request):
connection = sql.connect(
server_hostname= your_host,
http_path= your_http_path,
access_token= your_access_token
cursor = connection.cursor()
cursor.execute('SELECT * FROM `products`')
result = cursor.fetchall()
tmpB=[]
for row in result:
dictRow = row.asDict()
tmpB.append(dictRow)
cursor.close()
connection.close()
return render(request,'details.html',{product:tmpB})

After getting data you can just render it in your template or you can create Model object. From Step 8 shows how to store retrieved data in the Django default database sqlite .

Code in details.html

{% block content %}
<h1>Book List</h1>
<ul>
{% for prd in product %}
<li>{{ prd.product_name }} : Price = {{ prd.price}}</a></li>
{% endfor %}
</ul>
{% endblock %}

Result: Here are the results displayed in the page.

Step 8:

Here the step for storing the retrieved data in default database Create model in model.py

Code in models.py

from django.db import models
# Create your models here.
class Product(models.Model):
product_name = models.CharField(max_length=200)
price = models.IntegerField(default=0)

Step 9: Know again run the python migration command

python .\manage.py makemigrations

This will create a migration files with name strating 0001.py as shown in image.

Then run sql migrate command with providing the name as below, this command creates required tables in default database.

python .\manage.py sqlmigrate myapp 0001

Step 10: Know get the data and create model object using it. Code in views.py

from django.shortcuts import render
from .models import Product
from databricks import sql
def get(request):
connection = sql.connect(
server_hostname=host,
http_path=http_path,
access_token=access_token)
cursor = connection.cursor()
cursor.execute('SELECT * FROM `products`')
result = cursor.fetchall()
for row in result:
dictRow = row.asDict()
dataTosave =  Product(product_name = dictRow['product_name'],price = dictRow['price'])
dataTosave.save()
cursor.close()
connection.close()
productDT = Product.objects.all()
return render(request,'details.html',{product: productDT })

But make sure you use appropriate column names in delta tables and model fields while creating objects.

Similarly, you can execute CRUD operations. Below documentation will help you for it.

https://learn.microsoft.com/en-us/azure/databricks/dev-tools/python-sql-connector