Azure machine Learning Workspace Notebooks - write sqlite files

Hi @SRILAKSHMI C Thanks for the answer.

1. I executed the below; it created a folder, sqlite3 and sqlite3-journal file but with the cell "hanging" which does not end; why is that? "/home/azureuser/cloudfiles/code/Users/lc03/chroma-db2"
2. And... you said "/home/azureuser/cloudfiles/..." not working but then why "/home/azureuser/cloudfiles/code/Users/lc03/chroma-db2" is different and should work?
3. /home/azureuser/chroma-db2 and /tmp/chroma-db2 worked for me and why do you say they do not work?

Thank you!

You said!

Directories where SQLite will NOT work

These paths are FUSE-mounted → SQLite file-locking fails:

./chroma_db

~/chroma_db

/home/azureuser/cloudfiles/... (certain internal subfolders)

/mnt/batch/tasks/...

/home/azureuser root

/tmp

Hi Daun (Daniel) Lee,

Thanks for the follow-up let me clarify the behavior.

1. Why the cell hangs even though the folder and SQLite files were created

SQLite can create the folder and the DB file, but the final step (obtaining an exclusive file lock) fails on FUSE-mounted folders. AML Notebooks will keep waiting for that lock indefinitely, which is why the cell doesn’t complete. So creation succeeds, but SQLite locking does not.

2. Difference between “cloudfiles” and “cloudfiles/code/Users/<username>”

Not all folders under cloudfiles behave the same.

• Certain directories directly under /home/azureuser/cloudfiles/ are fully FUSE-mounted → SQLite locks fail.

The path /home/azureuser/cloudfiles/code/Users/<username>/ is the user workspace area and behaves differently. It allows normal file access and locking, which is why it’s the recommended location.

That is why one subpath under cloudfiles may work while others do not.

3. Why /home/azureuser/ and /tmp/ worked

These are local (non-FUSE) filesystem paths, so SQLite locking works normally.

However:

/home/azureuser/ → not visible in the AML Notebook Explorer

/tmp/ → gets wiped when compute stops

Neither is suitable for persistent storage

So they technically work for locking but do not meet your requirements for visibility and persistence.

For SQLite/Chroma and Notebook UI visibility and persistence:

/home/azureuser/cloudfiles/code/Users/<your-username>/chroma_db

This is the only location that satisfies all three conditions consistently within AML Notebooks.

I Hope this helps. Do let me know if you have any further queries.

Thank you!

Hi @SRILAKSHMI C Thank you for your answer. I think it shouldn't hang basically, so the following cells can be executed; something that hangs can't be an answer. Would you suggest a way not hanging and meeting the requirements? It's very cumbersome to recover from hanging; refreshing browser, switching kernel, etc. Thank you!

Hi Daun (Daniel) Lee,

Thanks for the clarification and you’re absolutely right that the cell should not hang.

Even if the filesystem blocks SQLite locking, Chroma should ideally return an error rather than waiting forever. Since this behaviour is coming from a combination of SQLite + Chroma + the AML FUSE filesystem, the best path is to avoid the locking issue entirely.

Here is the recommended way to prevent hanging while still meeting your requirements (SQLite works + path is visible + persistent + stable).

Why hanging happens

When Chroma initializes SQLite, it tries to obtain an exclusive write lock. On FUSE-mounted paths, the lock request never returns → Chroma waits forever → the cell “hangs”.

So the solution is to avoid the initial write lock happening on FUSE.

The Reliable Fix (No Hang + Visible + Persistent)

1. Write the DB to a safe local path first (lock works, no hang)

import os

local_dir = "/mnt/azureml/cr/chroma_temp"
os.makedirs(local_dir, exist_ok=True)

db = Chroma.from_documents(docs, emb, persist_directory=local_dir)

• This location never hangs, because it is local SSD.
• SQLite locking works 100% of the time.

2. Copy it into your workspace-visible folder

import shutil

target_dir = "/home/azureuser/cloudfiles/code/Users/lc03/chroma-db2"
os.makedirs(target_dir, exist_ok=True)

shutil.copytree(local_dir, target_dir, dirs_exist_ok=True)

SQLite DB is now in your preferred visible folder, No hanging, Persists across compute restarts, Fully visible in AML Notebooks

This two-step pattern is the officially recommended workaround for SQLite, HuggingFace datasets, LangChain vectorstores, and any workload that requires locking.

Why this works

• Local SSD (/mnt/azureml/cr/) supports real filesystem locking → Chroma finishes immediately.
• Once created, SQLite content is just files → copying them to cloudfiles does not require locking.
• You get the best of both:
  • No hanging
  • Visible in Notebook Explorer
  • Persistent

Re-open the DB from the target folder

After copying:

db = Chroma(
    embedding_function=emb,
    persist_directory=target_dir
)

This works because reading does not require exclusive locks.

Use this workflow:

Create & write SQLite DB in local SSD (no hang)

Copy to cloudfiles (visible + persistent)

Re-open from there (read operations OK)

This is the most stable and scalable solution within AML Notebook filesystem constraints.

Thank you!

Amazing solution and workaround :)