Databricks SQL

Databricks SQL framework, easy to learn, fast to code, ready for production.

Installation

$ pip install databricks-sql

Configuration

fromdatabricks_sql.clientimportConfigurationCONFIGURATION=Configuration.instance(
access_token="",
command_directory="",
http_path="",
server_hostname="",
)

Usage

Databricks SQL usage description:

Delete

Delete with where

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.delete("catalog.schema.table")
.where("id", "994238a4-8c18-436a-8c06-29ec89c4c056")
.execute()
)

Delete with where condition

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.delete("catalog.schema.table")
.where("description", "%Databricks%", operator="LIKE")
.execute()
)

Execute

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.execute(
""" CREATE TABLE IF NOT EXISTS catalog.schema.table ( id STRING NOT NULL, name STRING NOT NULL, description STRING, CONSTRAINT table_primary_key PRIMARY KEY(id) ) USING DELTA """,
parameters=None,
skip_load=True,
)
)

Insert

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.insert("catalog.schema.table")
.set("id", "994238a4-8c18-436a-8c06-29ec89c4c056")
.set("name", "Name")
.set("description", "Description")
.execute()
)

Paging

Paging with where condition

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.select("catalog.schema.table")
.fields("id", "name", "description")
.where("name", "%Databricks%", operator="LIKE")
.order_by("id")
.paging(0, 10)
)

Paging without where condition

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.select("catalog.schema.table")
.paging(0, 10)
)

Select

Fetch all

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.select("catalog.schema.table")
.execute()
.fetch_all()
)

Fetch many

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.select("catalog.schema.table")
.execute()
.fetch_many(1)
)

Fetch one

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.select("catalog.schema.table")
.execute()
.fetch_one()
)

Select by file

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.execute("command.sql", {"id": "994238a4-8c18-436a-8c06-29ec89c4c056"})
.fetch_one()
)

Select by command

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.execute("SELECT id, name, description FROM catalog.schema.table WHERE id = %(id)s", {"id": "994238a4-8c18-436a-8c06-29ec89c4c056"})
.fetch_one()
)

Update

Update with where

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.update("catalog.schema.table")
.set("name", "New Name")
.set("description", "New Description")
.where("id", "994238a4-8c18-436a-8c06-29ec89c4c056")
.execute()
)

Update with where all

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.update("catalog.schema.table")
.set("name", "New Name")
.set("description", "New Description")
.where_all({"id": "994238a4-8c18-436a-8c06-29ec89c4c056", "name": "Name", "description": "Description"})
.execute()
)

Using mustache

SQL

select
id,
name,
description
fromcatalog.schema.table
where1=1
{{#id}}and id = %(id)s
{{/id}}
{{#name}}and name like %(name)s
{{/name}}

Select with filters

fromdatabricks_sql.clientimportDatabasewithDatabase() asconnection:
(
connection
.execute("command.sql", parameters={"id": "994238a4-8c18-436a-8c06-29ec89c4c056", "name": "Name"})
.fetch_one()
)

License

This project is licensed under the terms of the Apache License 2.0.