<jupyter_start><jupyter_text>**INTRODUCTION TO BIGQUERY ML USING PYTHON.**`Developed by: <NAME>`---Github: https://github.com/carlangastrLinkedin profile: https://www.linkedin.com/in/carlangastr/ This tutorial introduces data scientists to BigQuery ML and is based on the official documentation tutorial. BigQuery ML enables users to create and execute machine learning models in BigQuery using SQL queries.In this tutorial, i will use the API REST of Google on Python and one of the bigquery public datasets to make model to predict the sessions in a website, based on the information of Google Analytics.---  **STEP ONE: Setup and create your dataset**Create a BigQuery dataset to store your ML model. Being a platform within BigQuery, datasets can host data and models.Using the right hand parameters, you can easily change the values ​​and run the code, using your credentials and project id.<jupyter_code>#Obviously we need to import our libraries

from google.cloud import bigquery
from google.oauth2 import service_account
from google.cloud.bigquery import magics
import os

import pandas as pd
file_json = 'credentials.json' #@param {type:"string"}
project_id = '' #@param {type:"string"}
credential = service_account.Credentials.from_service_account_file(file_json)
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = file_json

client = bigquery.Client(credentials = credential, project = project_id)

name_of_dataset = '' #@param {type:"string"}

dataset = client.create_dataset(name_of_dataset, exists_ok=True) 
print('dataset created')
#Load magic commands
%load_ext google.cloud.bigquery<jupyter_output><empty_output><jupyter_text>After loading the magic commands on our notebook, we only need to use '%% bigquery' to execute our queries.Keep in mind that this property is valid only in jupyter notebooks.--- **STEP THREE: Create and train your model**Creating the model within BigQuery ML is quite simple, just use the standard syntax and give the model the parameters to be created.BigQuery ML does not offer the possibility of moving the hyperparameters as much, as you could do in Keras or Pytorch. These models are already ordered parts, similar to sklearn,powerful but without much possibility of change.You can see more about the documentation of CREATE MODEL here.<jupyter_code>%%bigquery
CREATE MODEL IF NOT EXISTS `bqml_tutorial.arima_model`
OPTIONS(model_type = 'ARIMA', 
time_series_data_col='sessions', 
time_series_timestamp_col='date',
data_frequency='DAILY'
#holiday_region='US'
) AS

SELECT PARSE_TIMESTAMP("%Y-%m-%d", SUBSTR(CAST(date AS STRING), 0,10)) date, sum(sessions) sessions
FROM `project_id.dataset_name.table_name` -- ¡Replace the data here! 
where date <= '2020-12-31'
AND country = 'chile'
GROUP by 1
ORDER by 1 DESC<jupyter_output><empty_output><jupyter_text>After training our model, we can request its statistics to understand its effectiveness.The results of training, coefficients and evaluation can be saved in other additional tables within our dataset.<jupyter_code>%%bigquery

CREATE OR REPLACE TABLE `bqml_tutorial.arima_results_traing` AS (SELECT
 *
FROM
ML.TRAINING_INFO(MODEL `bqml_tutorial.arima_model`)
);

CREATE OR REPLACE TABLE `bqml_tutorial.arima_results_coeff` AS (
SELECT
  *
FROM
  ML.ARIMA_COEFFICIENTS(MODEL `bqml_tutorial.arima_model`)
);

CREATE OR REPLACE TABLE `bqml_tutorial.arima_results_evaluate` AS (
SELECT
 *
FROM
 ML.EVALUATE(MODEL `bqml_tutorial.arima_model`)
);<jupyter_output><empty_output><jupyter_text>--- **STEP FOUR: Doing predictions**After evaluating the effectiveness of our model, we are ready to make predictions.In this case we can use `ML.FORECAST` to ask BigQuery ML for a prediction.In this case we use `FORCAST` but depending on the model we could use `PREDICT`<jupyter_code>%%bigquery
SELECT 
  *
 FROM ML.FORECAST(MODEL `bqml_tutorial.arima_model`, STRUCT(90 AS horizon, 0.8 AS confidence_level))<jupyter_output><empty_output><jupyter_text>If you are not using a jupyter notebook, you can use the pandas `read.gbq ()` function to run the query and get a result dataframe.Applying this will give you the ability to visualize the results through matplot.<jupyter_code>query_statement = """
WITH actuals AS (
SELECT 
  PARSE_TIMESTAMP("%Y-%m-%d", 
  SUBSTR(CAST(date AS STRING), 0,10)) date, 
  sum(sessions) sessions
FROM `project_id.dataset_name.table_name`
where date > '2020-12-31'
AND country = 'chile'
GROUP BY 1)

SELECT 
 forecast_timestamp,
 SUM(sessions) AS actuals,
 SUM(prediction_interval_lower_bound) AS lower_value,
 SUM(forecast_value) AS middle_value,
 SUM(prediction_interval_upper_bound) AS upper_value
 FROM ML.FORECAST(MODEL `bqml_tutorial.arima_model`, 
	STRUCT(90 AS horizon, 0.9 AS confidence_level)) as ML
 LEFT JOIN actuals AS ac 
 ON forecast_timestamp = ac.date
 GROUP BY 1
"""

dataframe = pd.read_gbq(query)


dataframe.plot(x ='forecast_timestamp’, 
		y=['actuals', 'upper_value', 'middle_value', 'lower_value’], 
		kind = 'line')<jupyter_output><empty_output><jupyter_text>If the only thing you are looking for is to execute a query without having to write it to a dataframe, you can directly use the `QUERY` function from the BigQuery library.You can combine this with native python functions to standardize how you write queries and automate the creation of your models.In the section below I leave you a small example:<jupyter_code>##This is an example

query_create_model = """

CREATE MODEL IF NOT EXISTS `{dataset_name}.{model_name}`
OPTIONS(model_type='{ml_model}') AS
SELECT
  {select_condition}
FROM
  `{project_id}.{dataset_name}.{table_name}`
WHERE
  {where_condition}

"""

query_job = client.query(query_create_model.format(
                                                    dataset_name = 'your',
                                                    model_name = 'parameters',
                                                   ml_model = 'here'))
print(query_job.result())


query_create_model.format()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08372404554588078, 'fraction_numerical': 0.005860683188211655, 'mean_word_length': 5.033333333333333, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '392436', 'license': 'MIT', 'n_tokens_mistral': 1920, 'n_tokens_neox': 1853, 'n_words': 625, 'path': 'Google BigQuery/Introduction_to_bigquery_ML_on_Python_Carlos_Trujillo.ipynb', 'repo_name': 'carlangastr/marketing-science-projects', 'size': '5981'}