<jupyter_start><jupyter_text>Turn the following cell back into 'code' to run dependecy installations 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>from dotenv import dotenv_values, load_dotenv
import os
import pathlib

dotenv_dir = os.environ.get('CREDENTIAL_DOTENV_DIR', os.environ.get('PWD', '/opt/app-root/src'))
dotenv_path = pathlib.Path(dotenv_dir) / 'credentials.env'
if os.path.exists(dotenv_path):
    load_dotenv(dotenv_path=dotenv_path,override=True)
import trino
from sqlalchemy.engine import create_engine

sqlstring = 'trino://{user}@{host}:{port}/'.format(
    user = os.environ['TRINO_USER'],
    host = os.environ['TRINO_HOST'],
    port = os.environ['TRINO_PORT']
)
sqlargs = {
    'auth': trino.auth.JWTAuthentication(os.environ['TRINO_PASSWD']),
    'http_scheme': 'https'
}
engine = create_engine(sqlstring, connect_args = sqlargs)
print("connecting with engine " + str(engine))
connection = engine.connect()
import boto3
s3 = boto3.resource(
    service_name="s3",
    endpoint_url=os.environ["S3_DEV_ENDPOINT"],
    aws_access_key_id=os.environ["S3_DEV_ACCESS_KEY"],
    aws_secret_access_key=os.environ["S3_DEV_SECRET_KEY"],
)
import pandas as pd
sql = """
select *
  from osc_datacommons_dev.urgentem.itr_emissions_1_v2
  limit 10
"""
df = pd.read_sql(sql, engine).convert_dtypes()
df
sql = """
select *
  from osc_datacommons_dev.gleif.gleif_isin_lei
  limit 10
"""
df = pd.read_sql(sql, engine).convert_dtypes()
df
sql = """
select *
  from osc_datacommons_dev.gleif.gleif_direct_issuer_ultimate_issuer
  limit 10
"""
df = pd.read_sql(sql, engine).convert_dtypes()
df
big_demo_query = """
select itr.isin,
       itr.company_name,
       glflei.lei,
       glfpar.ultimate_parent_issuer_lei as parent_lei,
       itr.achieved_reduction
  from osc_datacommons_dev.urgentem.itr_emissions_1_v2 as itr
  inner join osc_datacommons_dev.gleif.gleif_isin_lei as glflei
          on itr.isin = glflei.isin
  left join osc_datacommons_dev.gleif.gleif_direct_issuer_ultimate_issuer as glfpar
        on glflei.lei = glfpar.direct_issuer_lei
  where itr.scope = 'S1+S2' and itr.end_year > 2021
"""
df = pd.read_sql(big_demo_query, engine) \
    .convert_dtypes() \
    .astype({'parent_lei': 'string'})
df
schemaname = 'demo'
tablename = os.environ.get('ODSC_DEMO_TBL', 'odsc_isin_reduction_notebook')
sql = f"""
drop table if exists osc_datacommons_dev.{schemaname}.{tablename}
"""
qres = engine.execute(sql)
# trino hive connector isn't managing these for deletion \o/
s3.Bucket(os.environ["S3_DEV_BUCKET"]).objects \
    .filter(Prefix=f'trino/{schemaname}/{tablename}/') \
    .delete()
print(qres.fetchall())
import osc_ingest_trino
columnschema = osc_ingest_trino.create_table_schema_pairs(df)

tabledef = f"""create table if not exists osc_datacommons_dev.{schemaname}.{tablename}(
{columnschema}
) with (
    format = 'parquet',
    external_location = 's3a://ocp-odh-os-demo-s3/trino/{schemaname}/{tablename}/'
)"""
print(tabledef)
qres = engine.execute(tabledef)
print(qres.fetchall())
sql = f"""
insert into osc_datacommons_dev.{schemaname}.{tablename}
(isin, company_name, lei, parent_lei, achieved_reduction)
select * from ({big_demo_query})
"""
qres = engine.execute(sql)
print(qres.fetchall())<jupyter_output>[(7,)]
{'fraction_non_alphanumeric': 0.1273094688221709, 'fraction_numerical': 0.008371824480369515, 'mean_word_length': 5.46455223880597, 'pattern_counts': {'":': 0, '<': 4, '<?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': '628044', 'license': 'FTL', 'n_tokens_mistral': 1338, 'n_tokens_neox': 1319, 'n_words': 236, 'path': 'notebooks/odsc-west-2021-federations.ipynb', 'repo_name': 'os-climate/data-platform-demo', 'size': '3464'}