Learn how to Construct Information Purposes on the Databricks Lakehouse With the SQL Connector for Python –

We’re excited to announce Basic Availability of the Databricks SQL Connector for Python. This follows the latest Basic Availability of Databricks SQL on Amazon Internet Companies and Azure. Python builders can now construct knowledge purposes on the lakehouse, benefiting from record-setting efficiency for analytics on all their knowledge.

The native Python connector provides easy set up and a Python DB API 2.0 appropriate interface that makes it simple to question knowledge. It additionally robotically converts between Databricks SQL and Python knowledge varieties, eradicating the necessity for boilerplate code.

On this weblog submit, we’ll run by some examples of connecting to Databricks and operating queries in opposition to a pattern dataset.

Easy set up from PyPI

With this native Python connector, there’s no have to obtain and set up ODBC/JDBC drivers. Set up is thru pip, which implies you possibly can embody this connector in your utility and use it for CI/CD as nicely:

pip set up databricks-sql-connector

Set up requires Python 3.7+

Question tables and views

The connector works with SQL endpoints in addition to All Objective Clusters. On this instance, we present you ways to connect with and run a question on a SQL endpoint. To determine a connection, we import the connector and go in connection and authentication info. You may authenticate utilizing a Databricks private entry token (PAT) or a Microsoft Azure lively listing (AAD) token.

The next instance retrieves a listing of journeys from the NYC taxi pattern dataset and prints the journey distance to the console. cursor.description accommodates metadata in regards to the end result set within the DB-API 2.0 format . cursor.fetchall() fetches all of the remaining rows as a Python checklist.

 
from databricks import sql

# The with syntax will handle closing your cursors and connections
with sql.join(server_hostname="", http_path="",
access_token="") as conn:
  with conn.cursor() as cursor:
    cursor.execute(“SELECT * FROM samples.nyctaxi.journeys WHERE trip_distance < 
%(distance)s LIMIT 2”, {"distance": 10})

    # The outline is within the format (col_name, col_type, …) as per DB-API 2.0
    print(f”Description: {cursor.description}”)
    print(“Outcomes:”)
    for row in cursor.fetchall():
      print(row.trip_distance)

Output (edited for brevity):

5


Description: [('tpep_pickup_datetime', 'timestamp', …), ('tpep_dropoff_datetime', 'timestamp', …), ('trip_distance', 'double', …), …]

Outcomes:
5.35
6.5
5.8
9.0
11.3
…

Word: when utilizing parameterized queries, it is best to rigorously sanitize your enter to forestall SQL injection assaults.

Insert knowledge into tables

The connector additionally helps you to run INSERT statements, which is beneficial for inserting small quantities of knowledge (e.g. hundreds of rows) generated by your Python app into tables:

cursor.execute("CREATE TABLE IF NOT EXISTS squares (x int, x_squared int)")

squares = [(i, i * i) for i in range(100)]
values = ",".be part of([f"({x}, {y})" for (x, y) in squares])
cursor.execute(f"INSERT INTO squares VALUES {values}")

cursor.execute("SELECT * FROM squares")
print(cursor.fetchmany(3))

Output:

[Row(x=0, x_squared=0), Row(x=1, x_squared=1), Row(x=2, x_squared=4)]

To bulk load massive quantities of knowledge (e.g. thousands and thousands of rows), we suggest first importing the information to cloud storage after which executing the COPY INTO command.

Question metadata about tables and views

In addition to executing SQL queries, the connector makes it simple to see metadata about your catalogs, databases, tables and columns. The next instance will retrieve metadata details about columns from a pattern desk:

cursor.columns(schema_name="default", table_name="squares")

for row in cursor.fetchall():
  print(row.COLUMN_NAME)

Output (edited for brevity):

x
x_squared

A vivid future for Python app builders on the lakehouse

We want to thank the contributors to Dropbox’s PyHive connector, which offered the premise for early variations of the Databricks SQL Connector for Python. Within the coming months, we plan to open-source the Databricks SQL Connector for Python and start welcoming contributions from the group.

We’re enthusiastic about what our prospects will construct with the Databricks SQL connector for Python. In upcoming releases, we’re wanting ahead to including help for extra authentication schemes, multi-catalog metadata and SQLAlchemy. Please check out the connector, and provides us suggestions. We might love to listen to from you on what you want to us to help.