<jupyter_start><jupyter_text>Raw Records and Tables====================While the primary way of querying performantly through Sina is to use the query API, Sina also stores and returns raw JSON suitable for manipulation. This notebook demonstrates using the raw form of Records to display data in tables. One table is created for each Record type and a final one for the relationships between Experiments and Observations. The example is coupled to the NOAA database, in terms of data organization plus column and scalar names, but the underlying principles can be applied to any Sina-assembled Record.First, we create a factory that's aware of our database.<jupyter_code>import IPython.display as ipyd
import sina
import sina.utils
import tabulate

database = sina.utils.get_example_path('noaa/data.sqlite')
print("Using database {}".format(database))
ds = sina.connect(database)


def display_table(table_data):
    """Display a table assuming the first row contains column headings"""
    ipyd.display(ipyd.HTML(tabulate.tabulate(table_data, tablefmt='html')))


print("The data access object factory has been created.  Proceed to the next cell.")<jupyter_output><empty_output><jupyter_text>We then create a RecordDAO to extract every Record with the type "exp" (experiment). This pulls back the raw form, a JSON object that can be loaded for further analysis. In this case, we extract the first file associated with each "exp" Record and display it in a table.<jupyter_code>print("Loading experiment data from the database.  This may take a while.")

# Extract experiment record(s) from the database
all_experiments = ds.records.find_with_type("exp")

# Build a list of table entries, one experiment per row
table_data = [('Experiment Id', 'Data Source')]

# Save off the experiment ids for the relationship queries
exp_ids = []

for exp in all_experiments:
    exp_id = exp['id']
    exp_ids.append(exp_id)

    exp_first_file = list(exp['files'].keys())[0]
    table_data.append([exp_id, exp_first_file])

# Display the data in an HTML table
display_table(table_data)

print("Ready to proceed to the next cell.")<jupyter_output><empty_output><jupyter_text>Again, we extract every Record of a specific type, this one "obs" (observation). This time, we iterate through them, building up a list of selected scalars per experiment, which we will display as a table. We also again display a file associated with this Record.<jupyter_code>print("Loading the observation data from the database.")

# Extract the observation records from the database
all_observations = ds.records.find_with_type("obs")

# Build a list of table entries, one observation per table row
# .. Hard-coding units in the heading instead of extracting from data
table_data = [('Observation Id', 'Depth (m)', 'Pressure (decibars)', 'Temp (C)',
               'Oxygen (micromol/kg)', 'O2 (micromol/kg)', 'O2 QC', 'pH', 'pH QC',
               'Observation Data')]

scalars_of_interest = ['depth', 'press', 'temp', 'ctd_oxy', 'o2', 'o2_qc', 'ph', 'ph_qc']

# Each observation gets a row in the table
for obs in all_observations:
    table_row = [''] * len(table_data[0])
    table_row[0] = obs['id']

    # For each scalar in the observation, if we're interested in it, load it in the right position
    for index, scalar in enumerate(scalars_of_interest):
        table_row[index + 1] = obs['data'][scalar]['value']

    # Row is complete, add to the table
    table_row.append(list(obs['files'].keys())[0])
    table_data.append(table_row)

# Display the data in an HTML table
display_table(table_data)

print("Ready to proceed to the next cell.")<jupyter_output><empty_output><jupyter_text>The process for the third and final type of Record is similar to that of the second. We find and display scalars of interest for every Record of type "qc".<jupyter_code>print("Loading the quality control data from the database.")

# Extract the quality control records from the database
all_qc = ds.records.find_with_type("qc")

# Build a list of table entries, one quality control entry per row
table_data = [['Quality Control Value', 'Description']]

for qc in all_qc:
    table_data.append([qc['id'], qc['data']['desc']['value']])

# Display the data in an HTML table
display_table(table_data)

print("Ready to proceed to the next cell.")<jupyter_output><empty_output><jupyter_text>Finally, we build a table illustrating the heirarchy of this data; one experiment that encompasses many observations. While Relationships don't have a "raw" form like Records do, they're still very simple Python objects, each having a subject, predicate, and object (see Sina's documentation for more detail). For every experiment we discovered previously, we'll have Sina return all relationships in which that experiment was the subject.<jupyter_code>print("Loading relationships from the database.")

# Build a list of table entries, one quality control entry per row
table_data = [['Experiment Id', 'Relationship', 'Observation Id']]

for exp_id in exp_ids:
    all_relationships = ds.relationships.find(subject_id=exp_id)
    for rel in all_relationships:
        table_data.append([exp_id, rel.predicate, rel.object_id])

# Display the data in an HTML table
display_table(table_data)

print("Done.")<jupyter_output><empty_output><jupyter_text>Releasing Resources-------------------------Don't forget to release resources when you're all done!<jupyter_code>ds.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09834862385321101, 'fraction_numerical': 0.0016513761467889909, 'mean_word_length': 5.049944506104328, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '174251', 'license': 'MIT', 'n_tokens_mistral': 1565, 'n_tokens_neox': 1513, 'n_words': 634, 'path': 'examples/noaa/noaa_raw_records_and_tables.ipynb', 'repo_name': 'LLNL/Sina', 'size': '5450'}