<jupyter_start><jupyter_text>Setup Clever data<jupyter_code>storage_account = 'steduanalytics__update_this'
use_test_env = True
if use_test_env:
    stage1 = 'abfss://test-env@' + storage_account + '.dfs.core.windows.net/stage1'
    stage2 = 'abfss://test-env@' + storage_account + '.dfs.core.windows.net/stage2'
    stage3 = 'abfss://test-env@' + storage_account + '.dfs.core.windows.net/stage3'
else:
    stage1 = 'abfss://stage1@' + storage_account + '.dfs.core.windows.net'
    stage2 = 'abfss://stage2@' + storage_account + '.dfs.core.windows.net'
    stage3 = 'abfss://stage3@' + storage_account + '.dfs.core.windows.net'
# Process resource usage
df = spark.read.csv(stage1 + '/clever', header='true', inferSchema='true')
df = df.withColumn('sis_id',df.sis_id.cast('string'))
df.write.format('parquet').mode('overwrite').save(stage2 + '/clever/resource_usage_students')

# Anonymize data and load into stage3
from pyspark.sql.functions import sha2, lit
df = spark.read.format('parquet').load(stage2 + '/clever/resource_usage_students')
df = df.withColumn('sis_id', sha2(df.sis_id, 256)).withColumn('clever_user_id',lit('*')).withColumn('clever_school_id',lit('*'))
df.write.format('parquet').mode('overwrite').save(stage3 + '/clever/resource_usage_students')

# Create sql on-demand db for Clever data
def create_spark_db(db_name, source_path):
    spark.sql('CREATE DATABASE IF NOT EXISTS ' + db_name)
    spark.sql("create table if not exists " + db_name + ".resource_usage_students using PARQUET location '" + source_path + "/resource_usage_students'")

db_prefix = 'test_' if use_test_env else ''
create_spark_db(db_prefix + 's2_clever', stage2 + '/clever')
create_spark_db(db_prefix + 's3_clever', stage3 + '/clever')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1632536973833902, 'fraction_numerical': 0.01478953356086462, 'mean_word_length': 5.662878787878788, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '637187', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 648, 'n_tokens_neox': 639, 'n_words': 106, 'path': 'modules/Clever/notebooks/Clever_setup_and_update.ipynb', 'repo_name': 'DanielWahlgren/OpenEduAnalytics', 'size': '1758'}