<jupyter_start><jupyter_text>Database queries and loading imaging datasetsThis notebook demonstrates some example imagingDB usecases for data that has already been uploaded to the dataset.It will show you how to perform some simple queries in SQLAlchemy, as well as fetching the associated images.It will also compare how long it takes to load the data from local storage vs S3.<jupyter_code>%matplotlib inline

import matplotlib.pyplot as plt
import numpy as np
import importlib
import os
import sys
import time

module_path = os.path.abspath(os.path.join('..'))
print(module_path)
if module_path not in sys.path:
    sys.path.append(module_path)
    
# Import both local and S3 storage
import imaging_db.database.db_operations as db_ops
import imaging_db.filestorage.s3_storage as s3_storage
import imaging_db.filestorage.local_storage as local_storage
import imaging_db.utils.db_utils as db_utils<jupyter_output>/Users/jenny.folkesson/Code/imagingDB<jupyter_text>Load database credentials file by providing the full path to the file. This is the JSON file containing database login information such as host, port, names of database, driver and user, as well as password.<jupyter_code>credentials_filename = "/Users/jenny.folkesson/Code/db_credentials.json"
credentials_str = db_utils.get_connection_str(credentials_filename)<jupyter_output><empty_output><jupyter_text>Now we can run some queries. Let's start with listing the names of existing tables<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    print(db_ops.Base.metadata.tables.keys())<jupyter_output>dict_keys(['data_set', 'file_global', 'frames', 'frames_global'])<jupyter_text>You can see the database schema in the [Readme](https://github.com/czbiohub/imagingDB), and you can see how the tables are defined in SQLAlchemy in imaging_db/database/The main table [DataSet](https://github.com/czbiohub/imagingDB/blob/master/imaging_db/database/dataset.py) contains the unique identifier, a description, and it's also telling us if the file as been sliced into individual 2D frames or uploaded as is (uninspected file).<jupyter_code># Print three datasets with their properties
with db_ops.session_scope(credentials_str) as session:
    datasets = session.query(db_ops.DataSet)
    for d in datasets[2:5]:
        print(d.id)
        print(d.dataset_serial)
        print("Description:", d.description)
        print("Date/time:", d.date_time)
        print("Has this file been split into frames:", d.frames)
        print("Microscope:", d.microscope)
        print("Parent key:", d.parent_id)
        print("--------")<jupyter_output>11
ISP-2018-06-01-00-00-00-0002
Description: New test ome tif
Date/time: 2018-06-01 00:00:00
Has this file been split into frames: True
Microscope: None
Parent key: None
--------
12
ISP-2018-06-01-00-00-00-0003
Description: Same as 002
Date/time: 2018-06-01 00:00:00
Has this file been split into frames: True
Microscope: None
Parent key: 11
--------
13
SMS-2018-07-03-18-35-00-0010
Description: Only 10 frames from each channel at position 0 from larger background corrected dataset
Date/time: 2018-07-03 18:35:00
Has this file been split into frames: True
Microscope: Birefringence
Parent key: None
--------<jupyter_text>Next, let's print some of the fields for the first three files in FileGlobal.[FileGlobal](https://github.com/czbiohub/imagingDB/blob/master/imaging_db/database/file_global.py) is the table which contains files that have been uploaded as is to storage. It has a one to one mapping with DataSet.<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    files = session.query(db_ops.FileGlobal)
    for f in files[:3]:
        # This table has a one to one mapping with Project so we can retrieve ID
        print(f.data_set.dataset_serial)
        print("Folder location in storage directory:", f.storage_dir)
        print(f.metadata_json)
        print("--------")<jupyter_output>ML-2018-11-28-10-57-40-0002
Folder location in storage directory: raw_files/ML-2018-11-28-10-57-40-0002
{'file_origin': '/Volumes/dav/LibraryDatabase/Plate1_FACS/A1_ATL2_B_FACS.png'}
--------
ML-2018-11-28-10-57-47-0002
Folder location in storage directory: raw_files/ML-2018-11-28-10-57-47-0002
{'file_origin': '/Volumes/dav/LibraryDatabase/Plate1_FACS/A1_ATL2_B_FACS.png'}
--------
ML-2018-11-28-10-57-53-0002
Folder location in storage directory: raw_files/ML-2018-11-28-10-57-53-0002
{'file_origin': '/Volumes/dav/LibraryDatabase/Plate1_FACS/A1_ATL2_B_FACS.png'}
--------<jupyter_text>[FramesGlobal](https://github.com/czbiohub/imagingDB/blob/master/imaging_db/database/frames_global.py) contains global information for a particular file that has been split into individual frames and their global metadata. It has a one to one mapping with DataSet. Here are the three entries corresponding to the DataSet entries above.<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    frames = session.query(db_ops.FramesGlobal)
    for f in frames[2:5]:
        print("Number of frames:", f.nbr_frames)
        print("Image width:", f.im_width)
        print("Image height:", f.im_height)
        print("z depth in stack:", f.nbr_slices)
        print("Number of channels:", f.nbr_channels)
        print("Number of time points:", f.nbr_timepoints)
        print("Number of positions (FOVs):", f.nbr_positions)
        print("Number of color channels:", f.im_colors)
        print("Bit depth:", f.bit_depth)
        print("Directory in storage (local and S3):", f.storage_dir)
        # This table has a one to one mapping with Project so we can retrieve ID
        print("Unique ID:", f.data_set.dataset_serial)
        # Not printing metadata, but it's a queryable json where all global information is stored
        print("--------")<jupyter_output>Number of frames: 60
Image width: 1081
Image height: 1081
z depth in stack: 10
Number of channels: 6
Number of time points: 1
Number of positions (FOVs): 1
Number of color channels: 1
Bit depth: <class 'numpy.uint16'>
Directory in storage (local and S3): raw_frames/SMS-2018-07-03-18-35-00-0010
Unique ID: SMS-2018-07-03-18-35-00-0010
--------
Number of frames: 31590
Image width: 2048
Image height: 2048
z depth in stack: 26
Number of channels: 9
Number of time points: 1
Number of positions (FOVs): 135
Number of color channels: 1
Bit depth: uint16
Directory in storage (local and S3): raw_frames/SMS-2018-11-01-19-06-00-0001
Unique ID: SMS-2018-11-01-19-06-00-0001
--------
Number of frames: 72
Image width: 1024
Image height: 1024
z depth in stack: 36
Number of channels: 2
Number of time points: 1
Number of positions (FOVs): 1
Number of color channels: 1
Bit depth: uint16
Directory in storage (local and S3): raw_frames/ML-2018-11-06-09-49-00-0001
Unique ID: ML-2018-11-06-09-49-00-0001
------[...]<jupyter_text>Last but not least is [Frames](https://github.com/czbiohub/imagingDB/blob/master/imaging_db/database/frames.py), which is the table that contains specific information for each of the 2D frames summarized in FramesGlobal.This table has a many to one mapping with FramesGlobal.<jupyter_code># print first 3 frames
with db_ops.session_scope(credentials_str) as session:
    frame_info = session.query(db_ops.Frames)
    for i in frame_info[0:3]:
        print(i.id)
        print("Channel index:", i.channel_idx)
        print("Slice (z) index:", i.slice_idx)
        print("Time index:", i.time_idx)
        print("Position index:", i.pos_idx)
        print("Channel name:", i.channel_name)
        print("File name:", i.file_name)
        # Since Frames has a many to one mapping to FramesGlobal, we can easily retrieve info from there
        print("Total number of frames in dataset:", i.frames_global.nbr_frames)
        # FramesGlobal in turn has a one to one mapping to DataSet, so from a Frame we can also access DataSet info:
        print("Project ID:", i.frames_global.data_set.dataset_serial)
        print("Sha256:", i.sha256)
        print("------------------------------------")<jupyter_output>818
Channel index: 0
Slice (z) index: 1
Time index: 0
Position index: 1
Channel name: DAPI
File name: im_c000_z001_t000_p001.png
Total number of frames in dataset: 39
Project ID: ISP-2018-06-08-15-45-00-0001
Sha256: 951d91c5121e63b36f13b53d4e727df0bfa2d1fcb42caaf077ae526b3376828d
------------------------------------
820
Channel index: 0
Slice (z) index: 3
Time index: 0
Position index: 1
Channel name: DAPI
File name: im_c000_z003_t000_p001.png
Total number of frames in dataset: 39
Project ID: ISP-2018-06-08-15-45-00-0001
Sha256: deb5ee52e38300fed4986f2396465ba862abbaccab6862e58edbd06a3a18e313
------------------------------------
822
Channel index: 0
Slice (z) index: 5
Time index: 0
Position index: 1
Channel name: DAPI
File name: im_c000_z005_t000_p001.png
Total number of frames in dataset: 39
Project ID: ISP-2018-06-08-15-45-00-0001
Sha256: f9f776e82e16c5997f6b8aa071c5afce0a8a6d2c7257eefa13df6c871f8bca21
------------------------------------<jupyter_text>Some other examples of queries:Find all data sets which have been split into frames vs kept as unopened files.<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    datasets = session.query(db_ops.DataSet).filter(db_ops.DataSet.frames == True).all()
    print("Nbr datasets split into frames:", len(datasets))
    datasets = session.query(db_ops.DataSet).filter(db_ops.DataSet.frames == False).all()
    print("Nbr datasets kept as files:", len(datasets))<jupyter_output>Nbr datasets split into frames: 8949
Nbr datasets kept as files: 8709<jupyter_text>Get all unique channel names for data sets split into frames. If channel name is not accessible during the metadata extraction process it will be listed as None.<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    for value in session.query(db_ops.Frames.channel_name).distinct():
        print(value[0])<jupyter_output>None
488
568
C
Orientation_y
Scattering
405
Transmission
Orientation
A
Cy5
DAPI
Orientation_x
G
Retardance
Cy3
T
FITC<jupyter_text>Now let's get into joint queries. A typical usecase would be to retrieve frames from a known dataset ID.In this example we only want to retrieve the images from a specific channel named FITC.We can get this information by joining tables Frames, FramesGlobal, and DataSet.Let's start off with just getting the first frame from a query.<jupyter_code>dataset_identifier = "ISP-2018-06-08-15-45-00-0001"
channel_name = "FITC"

file_name = None
with db_ops.session_scope(credentials_str) as session:
    first_frame = session.query(db_ops.Frames) \
        .join(db_ops.FramesGlobal) \
        .join(db_ops.DataSet) \
        .filter(db_ops.DataSet.dataset_serial == dataset_identifier) \
        .filter(db_ops.Frames.channel_name == channel_name) \
        .first()

    storage_dir = first_frame.frames_global.storage_dir
    print(storage_dir)
    file_name = first_frame.file_name
    print(file_name)<jupyter_output>raw_frames/ISP-2018-06-08-15-45-00-0001
im_c001_z000_t000_p001.png<jupyter_text>Let's fetch it from S3 storage and plot it.<jupyter_code>data_loader = s3_storage.S3Storage(storage_dir=storage_dir)

t0 = time.time()
im = data_loader.get_im(file_name)
print("Load time: {:.2f} seconds".format(time.time() - t0))
print("Im", im.dtype, im.shape)
plt.imshow(im)
plt.rcParams['figure.figsize'] = [10, 10]
plt.axis('off')
plt.show()<jupyter_output>Load time: 0.51 seconds
Im uint16 (2048, 2048)<jupyter_text>Load again but from local storage this time. Make sure you've mounted your local storage, and if it's not mounted to the default location you need to specify mount location.<jupyter_code>data_loader = local_storage.LocalStorage(storage_dir=storage_dir)

t0 = time.time()
im = data_loader.get_im(file_name)
print("Load time: {:.2f} seconds".format(time.time() - t0))
print("Im", im.dtype, im.shape)<jupyter_output>Load time: 0.81 seconds
Im uint16 (2048, 2048)<jupyter_text>Here we do the same query but instead of fetching just the first result, we fetch all images from the FITC channel.<jupyter_code>file_names = []
with db_ops.session_scope(credentials_str) as session:
    all_frames = session.query(db_ops.Frames) \
        .join(db_ops.FramesGlobal) \
        .join(db_ops.DataSet) \
        .filter(db_ops.DataSet.dataset_serial == dataset_identifier) \
        .filter(db_ops.Frames.channel_name == channel_name) \
        .order_by(db_ops.Frames.file_name) \
        .all()

    bit_depth = all_frames[0].frames_global.bit_depth
    print("Bit depth:", bit_depth)
    for f in all_frames:
        file_names.append(f.file_name)
        print(f.file_name)<jupyter_output>Bit depth: uint16
im_c001_z000_t000_p001.png
im_c001_z001_t000_p001.png
im_c001_z002_t000_p001.png
im_c001_z003_t000_p001.png
im_c001_z004_t000_p001.png
im_c001_z005_t000_p001.png
im_c001_z006_t000_p001.png
im_c001_z007_t000_p001.png
im_c001_z008_t000_p001.png
im_c001_z009_t000_p001.png
im_c001_z010_t000_p001.png
im_c001_z011_t000_p001.png
im_c001_z012_t000_p001.png<jupyter_text>Now we can fetch that data from storage. Plot the first image again.<jupyter_code>data_loader = local_storage.LocalStorage(storage_dir=storage_dir)
t0 = time.time()
im_stack = data_loader.get_stack(file_names)
print("Load time: {:.2f} seconds".format(time.time() - t0))

print("Stack shape: {} and type: {}".format(im_stack.shape, im_stack.dtype))<jupyter_output>Load time: 0.82 seconds
Stack shape: (2048, 2048, 13) and type: uint16<jupyter_text>You can also use the class [DatabaseOperations](https://github.com/czbiohub/imagingDB/blob/master/imaging_db/database/db_operations.py) to retrieve metadata from a dataset, which you can then use to fetch whatever images you want to load.<jupyter_code>db_inst = db_ops.DatabaseOperations(
    dataset_serial="ISP-2018-06-01-00-00-00-0002",
)
with db_ops.session_scope(credentials_str) as session:
    global_meta, frames_meta = db_inst.get_frames_meta(
        session=session,
        channels=1,
    )
    print("All global information about dataset:", global_meta)
# Print local metadata for one frame
print(frames_meta.iloc[0])
# Get image stack from metadata
data_loader = local_storage.LocalStorage(storage_dir=global_meta["storage_dir"])
t0 = time.time()
im_stack, dim_order = data_loader.get_stack_from_meta(global_meta, frames_meta)
print("Time to load stack: {:.2f}".format(time.time() - t0))
print("Dimensions and order:", dim_order)
print(im_stack.shape)
# When displaying too much output in a Jupyter Notebook you can run into a "IOPub data rate exceeded."
# You can uncomment this cell if you've increased you NotebookApp.iopub_data_rate_limit to significantly higher
# than default settings.

# import matplotlib.animation as animation
# from IPython.display import HTML


# # Plot a flythrough of the first channel
# fig = plt.figure()
# plt.rcParams['figure.figsize'] = [10, 10]
# plt.axis('off')

# frames = []
# for i in range(im_stack.shape[2]):
#     frame = plt.imshow(im_stack[:, :, i], animated=True)
#     frames.append([frame])

# anim = animation.ArtistAnimation(fig, frames)

# # Call function to display the animation
# HTML(anim.to_html5_video())<jupyter_output><empty_output><jupyter_text>Here's another example fetching a small subset of data from a larger dataset. Find a dataset with kidney in description.<jupyter_code>with db_ops.session_scope(credentials_str) as session:
    datasets = db_ops.get_datasets(
        session=session,
        search_dict={'description': 'kidney'}
    )
    print("Found {} datasets.".format(len(datasets)))
    dataset_serial = datasets[0].dataset_serial
    print("ID:", dataset_serial)
    print("Full description:", datasets[0].description)
db_inst = db_ops.DatabaseOperations(
    dataset_serial=dataset_serial,
)
# Fetch a few z slices from two of the FOVs and channels
with db_ops.session_scope(credentials_str) as session:
    global_meta, frames_meta = db_inst.get_frames_meta(
        session=session,
        channels=['568', 'Transmission'],
        positions=[50, 60],
        slices=[11, 12, 13, 14, 15],
    )
    
# See how much data is in the dataset
print('Total nbr frames: {}'.format(global_meta['nbr_frames']))
print('Nbr FOVs: {}'.format(global_meta['nbr_positions']))
print('Nbr slices per FOV: {}'.format(global_meta['nbr_slices']))
print('Nbr channels: {}'.format(global_meta['nbr_channels']))
print('Nbr timepoints: {}'.format(global_meta['nbr_timepoints']))
# Load the images
data_loader = local_storage.LocalStorage(storage_dir=global_meta["storage_dir"])
t0 = time.time()
im_stack, dim_order = data_loader.get_stack_from_meta(global_meta, frames_meta)
print("Time to load stack: {:.2f}".format(time.time() - t0))
print("Dimensions and order: {} of stack with shape: {}".format(dim_order, im_stack.shape))
# Plot the center slice of each channel side by side
plt.subplot(121); plt.imshow(im_stack[:, :, 3, 0, 0])
plt.axis('off')
plt.subplot(122); plt.imshow(im_stack[:, :, 3, 1, 0])
plt.rcParams['figure.figsize'] = [10, 15]
plt.axis('off')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11528527823432731, 'fraction_numerical': 0.05899272129607889, 'mean_word_length': 4.43444976076555, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '376359', 'license': 'MIT', 'n_tokens_mistral': 6281, 'n_tokens_neox': 5577, 'n_words': 1560, 'path': 'notebooks/database_queries.ipynb', 'repo_name': 'czbiohub/imagingDB', 'size': '17036'}