<jupyter_start><jupyter_text>Run these in a cell if you need to install onto your nb env```%%capture pipoutput%pip install boto3 python-dotenv%pip install trino sqlalchemy sqlalchemy-trino%pip install pandas pyarrow fastparquet%pip install anytree%pip install osc-ingest-tools```<jupyter_code>import osc_ingest_trino as osc
osc.load_credentials_dotenv()
bucket = osc.attach_s3_bucket('S3_DEV')
engine_dev = osc.attach_trino_engine('TRINO_OSCU1')
engine_quant = osc.attach_trino_engine('TRINO_OSCU2')
engine_user = osc.attach_trino_engine('TRINO_OSCU3')
import os
print(os.environ['TRINO_OSCU1_USER'])
print(os.environ['TRINO_OSCU2_USER'])
print(os.environ['TRINO_OSCU3_USER'])
import pandas as pd
data = [['company1',1.1], ['company2', 2.2], ['company3', 3.3]]
dfbe = pd.DataFrame(data, columns = ['company_name','dev1'])
dfbe = dfbe.convert_dtypes()
print(dfbe.info(verbose=True))
dfbe
ingest_catalog = 'osc_datacommons_dev'
ingest_schema = 'demo'
ingest_table = 'demo_dv_backend'
osc.drop_unmanaged_table(ingest_catalog, ingest_schema, ingest_table, engine_dev, bucket, verbose=True)
osc.ingest_unmanaged_parquet(dfbe, ingest_schema, ingest_table, bucket,
                             verbose=True)
sql = osc.unmanaged_parquet_tabledef(dfbe, ingest_catalog, ingest_schema, ingest_table, bucket,
                                    verbose = True)
qres = engine_dev.execute(sql)
print(qres.fetchall())
sql = f"""
select * from {ingest_catalog}.{ingest_schema}.{ingest_table}
"""
dfuf = pd.read_sql(sql, engine_dev)
dfuf
nrows = dfuf.shape[0]
npublic = max(1, int(nrows * 0.1))
neval = max(1, int(nrows * 0.1))
nlicensed = nrows - npublic - neval
assert(nlicensed > 0)
accesscol = ['public']*npublic
accesscol.extend(['demo_dv_licensed']*nlicensed)
accesscol.extend(['demo_dv_eval']*neval)
accesscol
import math
dfuf['quant1'] = dfuf['dev1'].map(lambda x: 2 * x)
dfuf['user1'] = dfuf['quant1'].map(lambda x: x * x)
dfuf['user2'] = dfuf['quant1'].map(lambda x: math.sqrt(x))
dfuf['access'] = accesscol
dfuf = dfuf.convert_dtypes()
dfuf
ingest_table = 'demo_dv_userfacing'
osc.drop_unmanaged_table(ingest_catalog, ingest_schema, ingest_table, engine_dev, bucket, verbose=True)
osc.ingest_unmanaged_parquet(dfuf, ingest_schema, ingest_table, bucket,
                             verbose=True)
sql = osc.unmanaged_parquet_tabledef(dfuf, ingest_catalog, ingest_schema, ingest_table, bucket,
                                    verbose = True)
qres = engine_dev.execute(sql)
print(qres.fetchall())
userfacing_table = f'{ingest_catalog}.{ingest_schema}.{ingest_table}'
sql = f"""
select * from {userfacing_table}
"""
df = pd.read_sql(sql, engine_dev)
df
sql = f"""
select access, company_name, quant1, user1, user2 from {userfacing_table}
"""
df = pd.read_sql(sql, engine_quant)
df
try:
    sql = f"""
        select dev1 from {userfacing_table}
    """
    df = pd.read_sql(sql, engine_quant)
except Exception as e:
    print(e)
sql = f"""
select access, company_name, user1, user2 from {userfacing_table}
"""
df = pd.read_sql(sql, engine_user)
df
try:
    sql = f"""
        select dev1 from {userfacing_table}
    """
    df = pd.read_sql(sql, engine_user)
except Exception as e:
    print(e)
try:
    sql = f"""
        select  quant1 from {userfacing_table}
    """
    df = pd.read_sql(sql, engine_user)
except Exception as e:
    print(e)
# going to start doing table drop at the end, which
# should increase odds of other being able to run demos
osc.drop_unmanaged_table(ingest_catalog, ingest_schema, 'demo_dv_backend', engine_dev, bucket, verbose=True)
osc.drop_unmanaged_table(ingest_catalog, ingest_schema, 'demo_dv_userfacing', engine_dev, bucket, verbose=True)<jupyter_output>[{'ResponseMetadata': {'RequestId': 'XSGG7D9FTZD8JG4W', 'HostId': 'Sfa1NrrYiNVTQcdQ9MWUWA6h9K43qGqZgO+41qaVA83HAEnyQBIDC0o18YRdv/YBSH9Jdtik8ss=', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amz-id-2': 'Sfa1NrrYiNVTQcdQ9MWUWA6h9K43qGqZgO+41qaVA83HAEnyQBIDC0o18YRdv/YBSH9Jdtik8ss=', 'x-amz-request-id': 'XSGG7D9FTZD8JG4W', 'date': 'Wed, 08 Dec 2021 23:24:02 GMT', 'content-type': 'application/xml', 'transfer-encoding': 'chunked', 'server': 'AmazonS3', 'connection': 'close'}, 'RetryAttempts': 0}, 'Deleted': [{'Key': 'trino/demo/demo_dv_backend/a42dd7191cfd4714aa9c1e867c1cf036.parquet'}]}]
[{'ResponseMetadata': {'RequestId': 'XSGQ0CR5342G8WZG', 'HostId': '1h3Qq2gEGkbNBoqlrUrb7SgnVBmlCXCehCN1qM9RD07/yJoFo6Upu7SOVb2SnhaY4zh+dHtUrUE=', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amz-id-2': '<KEY> 'x-amz-request-id': 'XSGQ0CR5342G8WZG', 'date': 'Wed, 08 Dec 2021 23:24:02 GMT', 'content-type': 'application/xml', 'transfer-encodin[...]
{'fraction_non_alphanumeric': 0.13997833152762731, 'fraction_numerical': 0.03401950162513543, 'mean_word_length': 5.778267254038179, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 6, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '148139', 'license': 'FTL', 'n_tokens_mistral': 1883, 'n_tokens_neox': 1785, 'n_words': 336, 'path': 'notebooks/trino-data-vault-demo.ipynb', 'repo_name': 'os-climate/data-platform-demo', 'size': '4688'}