<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Create BigQuery stored procedures
This notebook is the second of two notebooks that guide you through completing the prerequisites for running the Real-time Item-to-item Recommendation with BigQuery ML Matrix Factorization and ScaNN solution.
Use this notebook to create the following stored procedures that are needed by the solution
Step1:  Import libraries
Step2:  Configure GCP environment settings
Update the following variables to reflect the values for your GCP environment
Step3:  Authenticate your GCP account
This is required if you run the notebook in Colab. If you use an AI Platform notebook, you should already be authenticated.
Step4:  Create the stored procedure dependencies
Step5:  Create the stored procedures
Run the scripts that create the BigQuery stored procedures.
Step6:  List the stored procedures
<ASSISTANT_TASK:>
Python Code:
!pip install -q -U google-cloud-bigquery pyarrow
Explanation: Create BigQuery stored procedures
This notebook is the second of two notebooks that guide you through completing the prerequisites for running the Real-time Item-to-item Recommendation with BigQuery ML Matrix Factorization and ScaNN solution.
Use this notebook to create the following stored procedures that are needed by the solution:
sp_ComputePMI - Computes pointwise mutual information (PMI) from item co-occurence data. This data is used by a matrix factorization model to learn item embeddings.
sp_TrainItemMatchingModel - Creates the item_embedding_model matrix factorization model. This model learns item embeddings based on the PMI data computed by sp_ComputePMI. 
sp_ExractEmbeddings - Extracts the item embedding values from the item_embedding_model model, aggregates these values to produce a single embedding vector for each item, and stores these vectors in the item_embeddings table. The vector data is later exported to Cloud Storage to be used for item embedding lookup.
Before starting this notebook, you must run the 00_prep_bq_and_datastore notebook to complete the first part of the prerequisites.
After completing this notebook, you can run the solution either step-by-step or with a TFX pipeline:
To start running the solution step-by-step, run the 01_train_bqml_mf_pmi notebook to create item embeddings.
To run the solution by using a TFX pipeline, run the tfx01_interactive notebook to create the pipeline.
Setup
Install the required Python packages, configure the environment variables, and authenticate your GCP account.
End of explanation
import os
from google.cloud import bigquery
Explanation: Import libraries
End of explanation
PROJECT_ID = 'yourProject' # Change to your project.
BUCKET = 'yourBucketName' # Change to the bucket you created.
SQL_SCRIPTS_DIR = 'sql_scripts'
BQ_DATASET_NAME = 'recommendations'
!gcloud config set project $PROJECT_ID
Explanation: Configure GCP environment settings
Update the following variables to reflect the values for your GCP environment:
PROJECT_ID: The ID of the Google Cloud project you are using to implement this solution.
BUCKET: The name of the Cloud Storage bucket you created to use with this solution. The BUCKET value should be just the bucket name, so myBucket rather than gs://myBucket.
End of explanation
try:
  from google.colab import auth
  auth.authenticate_user()
  print("Colab user is authenticated.")
except: pass
Explanation: Authenticate your GCP account
This is required if you run the notebook in Colab. If you use an AI Platform notebook, you should already be authenticated.
End of explanation
%%bigquery --project $PROJECT_ID
CREATE TABLE IF NOT EXISTS recommendations.item_cooc
AS SELECT 0 AS item1_Id, 0 AS item2_Id, 0 AS cooc, 0 AS pmi;
%%bigquery --project $PROJECT_ID
CREATE MODEL IF NOT EXISTS recommendations.item_matching_model
OPTIONS(
    MODEL_TYPE='matrix_factorization', 
    USER_COL='item1_Id', 
    ITEM_COL='item2_Id',
    RATING_COL='score'
)
AS
SELECT 0 AS item1_Id, 0 AS item2_Id, 0 AS score;
Explanation: Create the stored procedure dependencies
End of explanation
client = bigquery.Client(project=PROJECT_ID)
sql_scripts = dict()
for script_file in [file for file in os.listdir(SQL_SCRIPTS_DIR) if '.sql' in file]:
  script_file_path = os.path.join(SQL_SCRIPTS_DIR, script_file)
  sql_script = open(script_file_path, 'r').read()
  sql_script = sql_script.replace('@DATASET_NAME', BQ_DATASET_NAME)
  sql_scripts[script_file] = sql_script
for script_file in sql_scripts:
  print(f'Executing {script_file} script...')
  query = sql_scripts[script_file]
  query_job = client.query(query)
  result = query_job.result()
print('Done.')
Explanation: Create the stored procedures
Run the scripts that create the BigQuery stored procedures.
End of explanation
query = f'SELECT * FROM {BQ_DATASET_NAME}.INFORMATION_SCHEMA.ROUTINES;'
query_job = client.query(query)
query_job.result().to_dataframe()
Explanation: List the stored procedures
End of explanation
<END_TASK>