<jupyter_start><jupyter_text>Objective : Return aggregated result with an ML prediction using BQ ML<jupyter_code>###
## helpful resource corner
##

# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions
import os
import json

from google.cloud import bigquery
from google.cloud import storage

from botocore.exceptions import ClientError
home = os.path.expanduser('~')
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]=f"{home}/.config/gcloud/alpine-beacon-336222-c6a1c087e3ef.json"
client = bigquery.Client()
project = client.project
dataset_id = "{}.music".format(project)
dataset = bigquery.Dataset(dataset_id)
print(dataset_id)
tables = client.list_tables(dataset_id)  # Make an API request.

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
#    if table.table_id == "slices": 
        fqn = "{}.{}.{}".format(table.project, table.dataset_id, table.table_id)
        print(fqn)
qry = f"SELECT INFO FROM {fqn}"
query_job = client.query(qry)
results = query_job.result()
for row in results:
    print(row)
# this is kinda cringe but it works and I don't have chatty sql swimming around
with open("sql/dml/count_artists.sql", "r") as the_file:
    the_qRy = ""
    while True:
        line = the_file.readline()
        if line == "":
            break
        else:
            the_qRy = the_qRy + " " + line
query_job3 = client.query(the_qRy)
results3 = query_job3.result()
df = results3.to_dataframe()
df.rename(columns={"f0_":"count"}, inplace = True)
#idx = 0
#for result in results3:
#    idx += 1
#    print(result)
#    if idx > 5:
#        break
df.head(10)
df.tail(10)
with open("sql/models/psn_prdn_model.sql", "r") as the_file:
    model_qRy = ""
    while True:
        line = the_file.readline()
        if line == "":
            break
        else:
            model_qRy = model_qRy + " " + line

print(model_qRy)
%%time
model_query_job = client.query(model_qRy)
model_results = model_query_job.result()<jupyter_output>CPU times: user 32.8 ms, sys: 8.19 ms, total: 41 ms
Wall time: 1min 12s
{'fraction_non_alphanumeric': 0.13022351797862003, 'fraction_numerical': 0.01749271137026239, 'mean_word_length': 3.6795454545454547, 'pattern_counts': {'":': 4, '<': 4, '<?xml version=': 0, '>': 5, 'https://': 1, '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': '613548', 'license': 'MIT', 'n_tokens_mistral': 756, 'n_tokens_neox': 727, 'n_words': 172, 'path': 'predicate.ipynb', 'repo_name': 'kay-be/music_makes_the_ppl', 'size': '2058'}