--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' ) );
Thanks for the detailed video. Why didn't you use the AUTO_SPLIT function here ?
Hi dad!
--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'
)
);