Google Cloud BigQuery - AI & ML SQL Queries With Real World Logistic Regression Example

[@SiddhantNaik-xf5ig]

Thanks for the detailed video. Why didn't you use the AUTO_SPLIT function here ?

[@aanyadagar]

Hi dad!

[@Gogeeko]

--Below are all Google BigQuery AI/ML - Classification Model SQL Queries I showed in this video
SELECT
*
FROM
`bigquery-public-data.ml_datasets.census_adult_income`
LIMIT
100;
CREATE OR REPLACE VIEW
`tba.input_view` AS
SELECT
age,
workclass,
native_country,
marital_status,
education_num,
occupation,
race,
hours_per_week,
income_bracket,
CASE
WHEN MOD(functional_weight, 10) < 8 THEN 'training'
WHEN MOD(functional_weight, 10) = 8 THEN 'evaluation'
WHEN MOD(functional_weight, 10) = 9 THEN 'prediction'
END AS dataframe
FROM
`bigquery-public-data.ml_datasets.census_adult_income`
select count(*) from tba.input_view;
CREATE OR REPLACE MODEL
`tba.census_model`
OPTIONS
( model_type='LOGISTIC_REG',
auto_class_weights=TRUE,
data_split_method='NO_SPLIT',
input_label_cols=['income_bracket'],
max_iterations=15) AS
SELECT
* EXCEPT(dataframe)
FROM
`tba.input_view`
WHERE
dataframe = 'training';

--Evaluate
SELECT
*
FROM
ML.EVALUATE (MODEL `tba.census_model`,
(
SELECT
*
FROM
`tba.input_view`
WHERE
dataframe = 'evaluation'
)
);
--80% accuracy
--Predict
SELECT
*
FROM
ML.PREDICT (MODEL `tba.census_model`,
(
SELECT
*
FROM
`tba.input_view`
WHERE
dataframe = 'prediction'
)
);