Preparing your Redshift warehouse for deployments
Your warehouse-specific sample code can be found in the "Settings" tab. Click your warehouse name in the left menu.
The code will look like this:
create role modelbit_role;
grant create or replace function to role modelbit_role;
grant create or replace external function to role modelbit_role;
grant role modelbit_role to demo_user;
Once you've run this sample code, download the CloudFormation template from that page and upload it to AWS CloudFormation. This will give you permissions to call models in Lambda functions in Modelbit's AWS account.
Once run, click the "Outputs" tab, and copy the
ModelbitRedshiftRoleArn back into Modelbit. This is how Modelbit knows
that your AWS account is the one calling the functions.
Efficiently querying Modelbit models from Redshift
Redshift will efficiently query Modelbit in batch inside
select statements. E.g. this statement is very efficient:
select public.ext_sampleModel_latest(myTable.myInput1, myTable.myInput2)
However, Redshift is inefficient when Modelbit models are called inside
update statements. This statement is not efficient:
set myPredictions = public.ext_sampleModel_latest(myTable.myInput1, myTable.myInput2);
Instead, fetch the predictions first in batch, and then assign them into your table, like so:
create temp table predictions_tbl (row_id int, predictions int);
insert into predictions_tbl (row_id, predictions)
select row_id, public.ext_sampleModel_latest(myInput1, myInput2) as predictions
set predictions = predictions_tbl.predictions
join myTable t on predictions_tbl.row_id = t.row_id;