Tutorial SharePoint/jdbc

Speed Up SharePoint Reads in Azure Databricks: Measure, Then Trim

Time a baseline SharePoint read in an Azure Databricks notebook, then scope the site, filter, trim columns, split joins and land a copy, re-measuring each time.

Jerod Johnson
Director, Technology Evangelism
Updated

A notebook that reads a SharePoint list in seconds against a test site can drag on the real one, where lists are long, columns are many and everything is filtered after the fact. Before you change anything, find out where the time goes. This guide assumes you already have the CData JDBC Driver for SharePoint installed on the cluster and a notebook that loads a list. If you don't, the installation notes near the end cover the Azure side.

Time one plain read first

Spark reads lazily, so the clock only means something if you force the read. A count() does that. Run this against the list you care about and write the number down.

import time
driver = "cdata.jdbc.sharepoint.SharePointDriver"
url = "jdbc:sharepoint:RTK=5246...;User=myuseraccount;Password=mypassword;Auth Scheme=NTLM;URL=http://sharepointserver/mysite;SharePointEdition=SharePointOnPremise;"
start = time.time()
remote_table = (spark.read.format("jdbc")
    .option("driver", driver)
    .option("url", url)
    .option("dbtable", "MyCustomList")
    .load())
print(remote_table.count(), "rows in", round(time.time() - start, 1), "seconds")

The connection string above is the on-premises form. For SharePoint Online, drop the NTLM scheme and set SharePointEdition=SHAREPOINTONLINE with your User and Password. Run the cell twice and keep the second time, so a cold cluster doesn't skew the baseline.

Notebook output template showing the count and elapsed seconds line that forms the baseline, with N and X as placeholders for your own numbers.
Write down your own baseline before changing anything. Illustration with placeholder values.

Know which side does the work

The driver sends the SQL operations SharePoint can handle, such as filters and aggregations, to SharePoint itself. Anything it can't push down, which is often SQL functions and JOINs, is processed by the driver's own SQL engine after the rows arrive. That split explains most slow reads. A read that fetches a whole list and filters it afterward moves every row across the network before it throws most of them away.

The changes below all follow from that: give SharePoint less to look at, and give the driver less to do.

Diagram of a Databricks notebook sending SQL to the CData JDBC driver, which pushes filters down to the SharePoint list and handles the rest itself.
Filters run in SharePoint; functions and JOINs run in the driver. Illustration.

Change one thing at a time

Take them in this order and re-run the timing cell after each. Start with the scope of the connection.

The URL property can point at the base site or at a sub-site, and it determines which lists and other entities the connection exposes. If your list lives in a sub-site, connect to that sub-site instead of the root.

Next, move the filter into the read. Passing a query through dbtable as a subquery lets the driver see the WHERE clause instead of leaving it to Spark afterward.

query = "(SELECT Name, Revenue FROM MyCustomList WHERE Location = 'Chapel Hill') AS src"
remote_table = (spark.read.format("jdbc")
    .option("driver", driver)
    .option("url", url)
    .option("dbtable", query)
    .load())

That one statement also covers the third change, naming only the columns you need. A list with many fields, including hidden and lookup columns the driver can expose, costs more to fetch than two named columns.

Fourth, keep JOINs out of the driver. If you need a list alongside another entity such as Users, load each one with its own filter and column list, then join the two dataframes in Spark. Whether that beats a driver-side join depends on your data, so time both.

Five-step checklist for trimming a SharePoint read: sub-site, subquery filter, named columns, split joins and a saved table.
Apply the changes in this order and re-time after each one. Illustration.

Land it once

Most notebooks read the same list many times a day. Writing the filtered result to a table means later queries hit Databricks storage instead of SharePoint.

remote_table.createOrReplaceTempView("sp_revenue")
remote_table.write.format("parquet").saveAsTable("sharepoint_revenue")

The temp view is only visible in the notebook that created it, which is why the saved table is the version other people can query. The cost is staleness: the copy is as old as its last write, so schedule the refresh at whatever interval the report can tolerate. If many notebooks depend on the same lists, CData Sync can keep a replicated copy up to date instead of each notebook doing its own read.

What to compare after each change

Change What to check
Connect to the sub-site Row count and time for the same list
Filter in the subquery Time, and that the row count matches the old filtered result
Name only needed columns Time, and that downstream code still finds its columns
Split the join Time against the driver-side join on the same filters
Saved table Time for the second and later reads

Installing the driver on the cluster

Upload the JAR to a blob container, then add the Spark option spark.hadoop.fs.azure.account.key.<storage account>.blob.core.windows.net to the cluster with the storage account key as its value. Install the library from the cluster's Libraries tab using the ADLS option and the abfss:// URL of the JAR. The older DBFS method is deprecated, although no end-of-life has been announced. You also need to set the RTK property in the JDBC URL unless you are using a beta driver; the licensing file in the installation explains how.

To run these timings on your own lists, download the CData JDBC Driver for SharePoint.

Ready to get started?

Download a free trial of the SharePoint Driver to get started:

 Download Now

Learn more:

SharePoint Icon SharePoint JDBC Driver

Provides Java developers with the power to easily connect their Web, Desktop, and Mobile applications to data in SharePoint Server Lists, Contacts, Calendar, Links, Tasks, and more!