Ingest files from OneDrive

Beta

This feature is in Beta. Workspace admins can enable it from the Previews page by opting into the Lakeflow Connect for OneDrive For Business preview. See Manage Databricks previews.

note

OneDrive ingestion supports use in workspaces with the Configure enhanced security and compliance settings enabled.

You can ingest structured, semi-structured, and unstructured files from a user's Microsoft OneDrive for Business (the personal My files drive) into Delta tables. SQL and Spark APIs support incremental ingestion of OneDrive files using batch and streaming APIs, including Auto Loader, spark.read, and COPY INTO, all with Unity Catalog governance. Use these APIs to create Spark DataFrames, materialized views, and streaming tables directly from files in OneDrive.

OneDrive ingestion reads files from an individual user's personal drive. To ingest from SharePoint sites, team sites, or shared document libraries, see Ingest files from SharePoint instead.

Choose how to ingest from OneDrive

Lakeflow Connect offers two ways to ingest from OneDrive. They both access data in OneDrive, but differ in their level of management.

Option	Description
Managed OneDrive connector	A fully-managed connector for enterprise applications that ingests data into Delta tables and keeps them in sync with the source.
SQL and Spark APIs	Build custom ingestion pipelines with SQL, PySpark, or Lakeflow pipelines using batch and streaming APIs such as read_files, spark.read, COPY INTO, and Auto Loader. Offers the flexibility to perform complex transformations during ingestion, while giving you greater responsibility for managing and maintaining your pipelines.

Option	Description
Managed OneDrive connector	A fully-managed connector for enterprise applications that ingests data into Delta tables and keeps them in sync with the source.
SQL and Spark APIs	Build custom ingestion pipelines with SQL, PySpark, or Lakeflow pipelines using batch and streaming APIs such as read_files, spark.read, COPY INTO, and Auto Loader. Offers the flexibility to perform complex transformations during ingestion, while giving you greater responsibility for managing and maintaining your pipelines.

Key features

The SQL and Spark APIs offer:

• Ingestion of structured, semi-structured, and unstructured files from a user's personal OneDrive
• Granular ingestion: Ingest a whole drive, a folder, or a single file
• Batch and streaming ingestion using spark.read, Auto Loader, and COPY INTO
• Automatic schema inference and evolution for structured and semi-structured formats such as CSV
• Secure credential storage with a Unity Catalog connection
• File selection with pattern matching using pathGlobFilter

Requirements

To ingest files from OneDrive, you must have the following:

• A workspace with Unity Catalog enabled.
• CREATE CONNECTION privileges to create a OneDrive connection, or the appropriate privilege to use an existing one based on your cluster access mode:
  • Dedicated Access Mode: MANAGE CONNECTION.
  • Standard Access Mode: USE CONNECTION.
• Compute that uses Databricks Runtime 19 or above.
• OAuth authentication set up with the Files.Read.All permission scope.

Create the connection

Create a Unity Catalog connection to store your OneDrive credentials. OneDrive ingestion uses OAuth user-to-machine (U2M) authorization against Microsoft Entra ID (Azure AD) and Microsoft Graph. The connection setup process is the same whether you use SQL and Spark APIs or the managed OneDrive connector.

For complete connection setup instructions, including the OAuth U2M: Databricks-managed and OAuth U2M: Custom-managed options, see Create a OneDrive connection.

The connection reads files as the signed-in user, so it can access only the files that user can access in their OneDrive.

Read files from OneDrive

To read files, pass the connection you created using the databricks.connection option and a URL that points to the OneDrive resource you want to access. The URL you provide determines the scope of the ingestion.

The following path types are supported:

Path type	Description
Whole drive	Copy the root URL of your OneDrive from the address bar. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents
Folder	Open the folder in OneDrive and copy the URL from the address bar. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fuser%2FDocuments%2FReports
File	Select the file, click the overflow menu (...), and select Open in browser or Details, then copy the URL. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/Reports/report.csv
Share link	Select the file or folder, click Share or Copy link, and use the resulting link. Databricks recommends setting the share link to never expire. https://mytenant-my.sharepoint.com/:x:/g/personal/user_mytenant_onmicrosoft_com/Eab12...

Path type	Description
Whole drive	Copy the root URL of your OneDrive from the address bar. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents
Folder	Open the folder in OneDrive and copy the URL from the address bar. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fuser%2FDocuments%2FReports
File	Select the file, click the overflow menu (...), and select Open in browser or Details, then copy the URL. https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/Reports/report.csv
Share link	Select the file or folder, click Share or Copy link, and use the resulting link. Databricks recommends setting the share link to never expire. https://mytenant-my.sharepoint.com/:x:/g/personal/user_mytenant_onmicrosoft_com/Eab12...

note

OneDrive URLs use the personal -my host (for example, mytenant-my.sharepoint.com), which is distinct from the SharePoint host (mytenant.sharepoint.com). Share links to SharePoint sites are not accepted. To ingest from SharePoint, see Ingest files from SharePoint instead.

The URL must resolve to your personal drive, a folder or file within it, or a share link. A bare host URL (for example, https://mytenant-my.sharepoint.com/) or a path that does not point to your drive (for example, https://mytenant-my.sharepoint.com/shared) is not supported.

Examples

There are a few ways to read files from OneDrive using SQL and Spark APIs.

Stream OneDrive files using Auto Loader

Auto Loader provides the most efficient way to incrementally ingest files from OneDrive. It automatically detects new files and processes them as they arrive. It can also ingest structured and semi-structured files such as CSV and JSON with automatic schema inference and evolution. For details about Auto Loader usage, see Common data loading patterns.

Python

# Incrementally ingest new PDF files
df = (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "binaryFile")
    .option("databricks.connection", "my_onedrive_conn")
    .option("cloudFiles.schemaLocation", <path to a schema location>)
    .option("pathGlobFilter", "*.pdf")
    .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents")
)

# Incrementally ingest CSV files with automatic schema inference and evolution
df = (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "csv")
    .option("databricks.connection", "my_onedrive_conn")
    .option("cloudFiles.schemaLocation", <path to a schema location>)
    .option("pathGlobFilter", "*.csv")
    .option("cloudFiles.inferColumnTypes", True)
    .option("header", True)
    .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs")
)

Read OneDrive files using Spark batch read

The following example shows how to ingest OneDrive files in Python using the spark.read function. Set the recursiveFileLookup option to true to read files from nested folders in your OneDrive.

Python

# Read unstructured data as binary files
df = (spark.read
        .format("binaryFile")
        .option("databricks.connection", "my_onedrive_conn")
        .option("recursiveFileLookup", True)
        .option("pathGlobFilter", "*.pdf") # optional. Example: only ingest PDFs
        .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents"))

# Read a batch of CSV files, infer the schema, and load the data into a DataFrame
df = (spark.read
        .format("csv")
        .option("databricks.connection", "my_onedrive_conn")
        .option("pathGlobFilter", "*.csv")
        .option("recursiveFileLookup", True)
        .option("inferSchema", True)
        .option("header", True)
        .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs"))

Read OneDrive files using Spark SQL

The following example shows how to ingest OneDrive files in SQL using the read_files table-valued function. For details about read_files usage, see read_files table-valued function.

SQL

-- Read pdf files
CREATE TABLE my_table AS
SELECT * FROM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents",
  `databricks.connection` => "my_onedrive_conn",
  format => "binaryFile",
  pathGlobFilter => "*.pdf", -- optional. Example: only ingest PDFs
  schemaEvolutionMode => "none"
);

-- Read CSV files with automatic schema inference
CREATE TABLE my_csv_table AS
SELECT * FROM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs",
  `databricks.connection` => "my_onedrive_conn",
  format => "csv",
  header => true,
  schemaEvolutionMode => "none"
);

Ingest OneDrive files in Lakeflow pipelines

note

Ingesting OneDrive files with SQL and Spark APIs requires Databricks Runtime 19 or above. To use it, set "CHANNEL" = "PREVIEW" in your pipeline settings. For more information on previews, see Pipeline properties reference.

The following examples show how to read OneDrive files using Auto Loader in Lakeflow pipelines.

Python

Python

from pyspark import pipelines as dp

# Incrementally ingest new PDF files
@dp.table
def onedrive_pdf_table():
  return (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "binaryFile")
    .option("databricks.connection", "my_onedrive_conn")
    .option("pathGlobFilter", "*.pdf")
    .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents")
  )

# Incrementally ingest CSV files with automatic schema inference and evolution
@dp.table
def onedrive_csv_table():
  return (spark.readStream.format("cloudFiles")
      .option("cloudFiles.format", "csv")
      .option("databricks.connection", "my_onedrive_conn")
      .option("pathGlobFilter", "*.csv")
      .option("cloudFiles.inferColumnTypes", True)
      .option("header", True)
      .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs")
  )

SQL

SQL

-- Incrementally ingest new PDF files
CREATE OR REFRESH STREAMING TABLE onedrive_pdf_table
AS SELECT * FROM STREAM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents",
  format => "binaryFile",
  `databricks.connection` => "my_onedrive_conn",
  pathGlobFilter => "*.pdf");

-- Incrementally ingest CSV files with automatic schema inference and evolution
CREATE OR REFRESH STREAMING TABLE onedrive_csv_table
AS SELECT * FROM STREAM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs",
  format => "csv",
  `databricks.connection` => "my_onedrive_conn",
  pathGlobFilter => "*.csv",
  header => "true");

Incremental ingestion with COPY INTO

COPY INTO provides idempotent incremental loading of files into a Delta table. For details about COPY INTO usage, see Common data loading patterns using COPY INTO.

SQL

CREATE TABLE IF NOT EXISTS onedrive_pdf_table;
CREATE TABLE IF NOT EXISTS onedrive_csv_table;

-- Incrementally ingest new PDF files
COPY INTO onedrive_pdf_table
  FROM "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents"
  FILEFORMAT = BINARYFILE
  PATTERN = '*.pdf'
  FORMAT_OPTIONS ('databricks.connection' = 'my_onedrive_conn')
  COPY_OPTIONS ('mergeSchema' = 'true');

-- Incrementally ingest CSV files with automatic schema inference and evolution
COPY INTO onedrive_csv_table
  FROM "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents/IoT_Logs"
  FILEFORMAT = CSV
  PATTERN = '*.csv'
  FORMAT_OPTIONS ('databricks.connection' = 'my_onedrive_conn', 'header' = 'true', 'inferSchema' = 'true')
  COPY_OPTIONS ('mergeSchema' = 'true');

Efficient incremental ingestion with change tracking

By default, Auto Loader discovers new files in OneDrive by listing the drive on each update. For large drives, you can instead track changes using the Microsoft Graph change feed, which returns only the files that were added or modified since the previous update rather than re-listing the whole drive. To enable it, set the cloudFiles.readChangeFeed option to true.

Python

df = (spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "binaryFile")
    .option("cloudFiles.readChangeFeed", "true")
    .option("databricks.connection", "my_onedrive_conn")
    .option("cloudFiles.schemaLocation", <path to a schema location>)
    .load("https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents")
)

When using cloudFiles.readChangeFeed, note the following:

• The binaryFile format is required. Change tracking is not available for other formats.
• The load path must resolve to a drive or a directory within a drive. A bare personal site or tenant root is not supported.
• You cannot provide an explicit schema. The output schema is determined automatically by the file format.

Parse unstructured files

When ingesting unstructured files from OneDrive (such as PDFs, Word documents, or PowerPoint files) using SQL and Spark APIs with binaryFile format, the file contents are stored as raw binary data. To prepare these files for AI workloads such as retrieval-augmented generation (RAG), search, classification, or document understanding, you can parse the binary content into structured, queryable output using ai_parse_document.

The following example shows how to parse unstructured documents stored in a bronze Delta table named documents, adding a new column with parsed content:

SQL

CREATE TABLE documents AS
SELECT * FROM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents",
  `databricks.connection` => "my_onedrive_conn",
  format => "binaryFile",
  pathGlobFilter => "*.{pdf,docx}",
  schemaEvolutionMode => "none"
);
SELECT *, ai_parse_document(content, map('version', '2.0')) AS parsed_content
FROM documents;

The parsed_content column contains extracted text, tables, layout information, and metadata that can be directly used for downstream AI pipelines.

Incremental parsing with Lakeflow pipelines

You can also use ai_parse_document within Lakeflow pipelines to enable incremental parsing.

For example, you can define a bronze streaming table that ingests the raw binary files, then a second streaming table that lands the parsed content in a new column:

SQL

-- Bronze: incrementally ingest the raw documents as binary
CREATE OR REFRESH STREAMING TABLE onedrive_documents
AS SELECT * FROM STREAM read_files(
  "https://mytenant-my.sharepoint.com/personal/user_mytenant_onmicrosoft_com/Documents",
  `databricks.connection` => "my_onedrive_conn",
  format => "binaryFile",
  pathGlobFilter => "*.{pdf,docx}");

-- Silver: land the parsed content in a new column
CREATE OR REFRESH STREAMING TABLE onedrive_documents_parsed
AS SELECT
  *,
  ai_parse_document(content, map('version', '2.0')) AS parsed_content
FROM STREAM onedrive_documents;

Because both tables are streaming tables, newly ingested OneDrive files are parsed automatically whenever the pipeline updates, and the parsed output stays in sync with the incoming data.

See ai_parse_document function for supported formats and advanced options, including the version option that pins the output schema.

Limitations

The SQL and Spark APIs have the following limitations.

• OneDrive ingestion reads from a single user's personal OneDrive (My files). SharePoint sites, team sites, and shared document libraries are not supported. See Ingest files from SharePoint for those.
• OneDrive Lists and Site Pages are not supported. Only the files stored in a user's OneDrive drive are ingested.
• You can use the pathGlobFilter option to filter files by name. Folder path-based filtering is not supported.
• Writing back to OneDrive is not supported. Ingestion is read-only.
• Auto Loader cleanSource (deleting or archiving files at the source after ingestion) is not supported.

Additional resources

• Learn about Auto Loader for advanced streaming ingestion patterns
• Explore COPY INTO for idempotent incremental loads
• Review ai_parse_document for parsing unstructured files
• Learn about ingesting from SharePoint
• Set up job scheduling to automate your ingestion workflows