Use SPSS and batch deployment with DB2 to predict customer churn.ipynb

Kernel: Python 3

Use SPSS and batch deployment with DB2 to predict customer churn with ibm-watsonx-ai

This notebook contains steps to deploy a sample SPSS stream and start batch scoring new data.

Some familiarity with bash is helpful. This notebook uses Python 3.11.

You will use a data set, Telco Customer Churn, which details anonymous customer data from a telecommunication company. Use the details of this data set to predict customer churn. This is critical to business, as it's easier to retain existing customers than acquire new ones.

Learning goals

The learning goals of this notebook are:

• Loading a CSV file into Db2 on Cloud

• Working with the watsonx.ai Runtime instance

• Batch deployment of an SPSS model

• Scoring data using deployed model and a Db2 connection

Contents

This notebook contains the following parts:

1. Setup

2. Model upload

3. Create db2 connection

4. Web service creation

5. Scoring

6. Clean up

7. Summary and next steps

1. Set up the environment

Before you use the sample code in this notebook:

• Create a watsonx.ai Runtime Service instance (a free plan is offered and information about how to create the instance can be found here).

Install and import the ibm-watsonx-ai and dependecies

Note: ibm-watsonx-ai documentation can be found here.

!pip install wget
!pip install -U ibm-watsonx-ai | tail -n 1

Connection to watsonx.ai Runtime

Authenticate the watsonx.ai Runtime service on IBM Cloud. You need to provide platform api_key and instance location.

You can use IBM Cloud CLI to retrieve platform API Key and instance location.

API Key can be generated in the following way:

ibmcloud login
ibmcloud iam api-key-create API_KEY_NAME

In result, get the value of api_key from the output.

Location of your watsonx.ai Runtime instance can be retrieved in the following way:

ibmcloud login --apikey API_KEY -a https://cloud.ibm.com
ibmcloud resource service-instance INSTANCE_NAME

In result, get the value of location from the output.

Tip: Your Cloud API key can be generated by going to the Users section of the Cloud console. From that page, click your name, scroll down to the API Keys section, and click Create an IBM Cloud API key. Give your key a name and click Create, then copy the created key and paste it below. You can also get a service specific url by going to the Endpoint URLs section of the watsonx.ai Runtime docs. You can check your instance location in your watsonx.ai Runtime Service instance details.

You can also get service specific apikey by going to the Service IDs section of the Cloud Console. From that page, click Create, then copy the created key and paste it below.

Action: Enter your api_key and location in the following cell.

api_key = 'PASTE YOUR PLATFORM API KEY HERE'
location = 'PASTE YOUR INSTANCE LOCATION HERE'

from ibm_watsonx_ai import Credentials

credentials = Credentials(
    api_key=api_key,
    url='https://' + location + '.ml.cloud.ibm.com'
)

from ibm_watsonx_ai import APIClient

client = APIClient(credentials)

Working with spaces

First, create a space that will be used for your work. If you do not have a space, you can use Deployment Spaces Dashboard to create one.

• Click New Deployment Space

• Create an empty space

• Select Cloud Object Storage

• Select watsonx.ai Runtime instance and press Create

• Copy space_id and paste it below

Tip: You can also use SDK to prepare the space for your work. More information can be found here.

Action: Assign space ID below

space_id = 'PASTE YOUR SPACE ID HERE'

You can use list method to print all existing spaces.

client.spaces.list(limit=10)

To be able to interact with all resources available in watsonx.ai Runtime, you need to set space which you will be using.

client.set.default_space(space_id)

'SUCCESS'

2. Upload model

In this section you will learn how to upload the model to the Cloud.

Action: Download sample SPSS model from git project using wget.

import os
from wget import download

sample_dir = 'spss_sample_model'
if not os.path.isdir(sample_dir):
    os.mkdir(sample_dir)

filename=os.path.join(sample_dir, 'db2-customer-satisfaction-prediction.str')
if not os.path.isfile(filename):
    filename = download('https://github.com/IBM/watson-machine-learning-samples/raw/master/cloud/models/spss/db2_customer_satisfaction/model/db2-customer-satisfaction-prediction.str',\
                             out=sample_dir)
print(filename)

Store SPSS sample model in your watsonx.ai Runtime instance.

client.software_specifications.list()

sw_spec_id = client.software_specifications.get_id_by_name("spss-modeler_18.2")

model_meta_props = {
    client.repository.ModelMetaNames.NAME: "SPSS customer satisfaction model",
    client.repository.ModelMetaNames.TYPE: "spss-modeler_18.2",
    client.repository.ModelMetaNames.SOFTWARE_SPEC_ID: sw_spec_id
}

model_details = client.repository.store_model(filename, model_meta_props)

Note: You can see that model is successfully stored in watsonx.ai Runtime Service.

client.repository.list_models()

3. Create a Db2 connection

You can use commands below to create a db2 connection and required data assets to perform batch scoring.

Create tables in Db2 on Cloud

• Download the inputScore.csv and inputScore2.csv file from the GitHub repository

• Click the Open the console to get started with Db2 on Cloud icon.

• Select the Load Data and Desktop load type.

• Drag and drop the previously downloaded file and click Next.

• Set table name to CUSTOMER and proceed with creating.

Create a connection

schema_name = 'PUT YOUR SCHEMA NAME HERE'

db_name = 'db2'
input_table_1 = 'CUSTOMER'
input_table_2 = 'CUSTOMER_2'
output_table = 'OUTPUT'

db_credentials = {
      "db": "***",
      "host": "***",
      "https_url": "***",
      "password": "***",
      "port": "***",
      "username": "***"
}

db2_data_source_type_id = client.connections.get_datasource_type_id_by_name(db_name)

db2_conn_meta_props= {
    client.connections.ConfigurationMetaNames.NAME: "conn_db2",
    client.connections.ConfigurationMetaNames.DATASOURCE_TYPE: db2_data_source_type_id,
    client.connections.ConfigurationMetaNames.DESCRIPTION: "Connection using DB2",
    client.connections.ConfigurationMetaNames.PROPERTIES: {
        "database": db_credentials["db"],
        "port": db_credentials["port"],          
        "host": db_credentials["host"],
        "password": db_credentials["password"],
        "username": db_credentials["username"]
    }
}

db2_conn_details = client.connections.create(meta_props=db2_conn_meta_props)

Creating connections...
SUCCESS

db2_conn_id = client.connections.get_id(db2_conn_details)

Create input connection data asset

db2_asset_meta_props={
            client.data_assets.ConfigurationMetaNames.NAME: "INPUT_TABLE_1",
            client.data_assets.ConfigurationMetaNames.CONNECTION_ID: db2_conn_id,
            client.data_assets.ConfigurationMetaNames.DESCRIPTION: "db2 table",
            client.data_assets.ConfigurationMetaNames.DATA_CONTENT_NAME: input_table_1 }

db2_conn_input_asset_details = client.data_assets.store(db2_asset_meta_props)  
input_data_1_href = client.data_assets.get_href(db2_conn_input_asset_details)

Creating data asset...
SUCCESS

db2_asset_meta_props={
            client.data_assets.ConfigurationMetaNames.NAME: "INPUT_TABLE_2",
            client.data_assets.ConfigurationMetaNames.CONNECTION_ID: db2_conn_id,
            client.data_assets.ConfigurationMetaNames.DESCRIPTION: "db2 table",
            client.data_assets.ConfigurationMetaNames.DATA_CONTENT_NAME: input_table_2 }

db2_conn_input_asset_details = client.data_assets.store(db2_asset_meta_props)  
input_data_2_href = client.data_assets.get_href(db2_conn_input_asset_details)

Creating data asset...
SUCCESS

Create output connection data assets

db2_asset_meta_props={
            client.data_assets.ConfigurationMetaNames.NAME: "OUTPUT_TABLE",
            client.data_assets.ConfigurationMetaNames.CONNECTION_ID: db2_conn_id,
            client.data_assets.ConfigurationMetaNames.DESCRIPTION: "db2 table",
            client.data_assets.ConfigurationMetaNames.DATA_CONTENT_NAME: output_table }

db2_conn_output_asset_details = client.data_assets.store(db2_asset_meta_props)  
output_data_href = client.data_assets.get_href(db2_conn_output_asset_details)

Creating data asset...
SUCCESS

4. Create batch deployment

Use bellow to create batch deployment for stored model.

model_id = client.repository.get_model_id(model_details)

deployment = client.deployments.create(
    artifact_id=model_id,
    meta_props={
        client.deployments.ConfigurationMetaNames.NAME: "SPSS BATCH customer satisfaction",
        client.deployments.ConfigurationMetaNames.BATCH: {},
        client.deployments.ConfigurationMetaNames.HARDWARE_SPEC: {
            "name": "S",
            "num_nodes": 1
        }
    }
)

#######################################################################################

Synchronous deployment creation for uid: 'b5f984cb-9b46-4884-aa9b-1541efab1faf' started

#######################################################################################


ready.


------------------------------------------------------------------------------------------------
Successfully finished deployment creation, deployment_uid='7ca37c54-19ab-4632-a191-625b3da3c898'
------------------------------------------------------------------------------------------------

5. Scoring

You can create batch job using below methods.

5.1 Scoring using data_asset pointing to the DB2.

job_payload_ref = {
    client.deployments.ScoringMetaNames.INPUT_DATA_REFERENCES: [
        {
            "id": "conn_db2",
            "name": "input_data_1_href",
            "type": "data_asset",
            "connection": {},
            "location": {
                "href": input_data_1_href
            }
        },
        {
            "id": "conn_db2",
            "name": "input_data_2_href",
            "type": "data_asset",
            "connection": {},
            "location": {
                "href": input_data_2_href
            }
        }
    ],
    client.deployments.ScoringMetaNames.OUTPUT_DATA_REFERENCE: {
            "type": "data_asset",
            "connection": {},
            "location": {
                "href": output_data_href
            }
    }
}

deployment_id = client.deployments.get_id(deployment)
job = client.deployments.create_job(deployment_id, meta_props=job_payload_ref)

You can retrive job ID.

job_id = client.deployments.get_job_id(job)

Monitor job execution

Here you can check the status of your batch scoring. When batch job is completed the results will be written to a Db2 table.

import time

elapsed_time = 0
while client.deployments.get_job_status(job_id).get('state') != 'completed' and elapsed_time < 300:
    print(f" Current state: {client.deployments.get_job_status(job_id).get('state')}")
    elapsed_time += 10
    time.sleep(10)
if client.deployments.get_job_status(job_id).get('state') == 'completed':
    print(f" Current state: {client.deployments.get_job_status(job_id).get('state')}")
    job_details_do = client.deployments.get_job_details(job_id)
    print(job_details_do)
else:
    print("Job hasn't completed successfully in 5 minutes.")

 Current state: queued
 Current state: running
 Current state: completed
{'entity': {'deployment': {'id': 'ac0dfe9a-6c98-4eeb-967b-120f9234b0e5'}, 'platform_job': {'job_id': 'bfcf37c5-d190-49bd-abca-fb22116323f5', 'run_id': '361b151b-c5f6-48dc-ae1d-776642c9a692'}, 'scoring': {'input_data_references': [{'connection': {}, 'id': 'conn_db2', 'location': {'href': '/v2/assets/04f74392-0b89-4ca6-baa1-157e4295bd8b?space_id=680a7515-620c-461f-9c6f-1f4c535bfc47'}, 'type': 'data_asset'}, {'connection': {}, 'id': 'conn_db2', 'location': {'href': '/v2/assets/d1e1c2eb-d0eb-430f-8707-f948c0b37ba2?space_id=680a7515-620c-461f-9c6f-1f4c535bfc47'}, 'type': 'data_asset'}], 'output_data_reference': {'connection': {}, 'location': {'href': '/v2/assets/8009a976-1218-42b7-81fc-39b77ec225cd?space_id=680a7515-620c-461f-9c6f-1f4c535bfc47'}, 'type': 'data_asset'}, 'status': {'completed_at': '2021-05-13T08:26:06.058Z', 'running_at': '2021-05-13T08:25:58.434Z', 'state': 'completed'}}}, 'metadata': {'created_at': '2021-05-13T08:25:47.071Z', 'id': 'a5d0e0b3-62c5-41f4-879b-41702e16b185', 'modified_at': '2021-05-13T08:26:06.191Z', 'name': 'name_d68b05fd-7866-4a48-af56-235eb3f03809', 'space_id': '680a7515-620c-461f-9c6f-1f4c535bfc47'}}

5.2 Scoring using connection_asset poiniting to the DB2

job_payload_ref = {
    client.deployments.ScoringMetaNames.INPUT_DATA_REFERENCES: [
        {
            "id": "conn_db2",
            "name": "input_table_1",
            "type": "connection_asset",
            "connection": {
                "id": db2_conn_id
            },
            "location": {
                "schema_name": schema_name,
                "file_name": input_table_1
            }
        },
        {
            "id": "conn_db2",
            "name": "input_table_2",
            "type": "connection_asset",
            "connection": {
                "id": db2_conn_id
            },
            "location": {
                "schema_name": schema_name,
                "file_name": input_table_2
            }
        }
    ],
    client.deployments.ScoringMetaNames.OUTPUT_DATA_REFERENCE: {
            "id": "conn_db2",
            "name": "output_table",
            "type": "connection_asset",
            "connection": {
                "id": db2_conn_id
            },
            "location": {
                "schema_name": schema_name,
                "file_name": output_table
            }
    }
}

deployment_id = client.deployments.get_id(deployment)
job = client.deployments.create_job(deployment_id, meta_props=job_payload_ref)

Retrive job ID.

job_id = client.deployments.get_job_id(job)

Monitor job execution

import time

elapsed_time = 0
while client.deployments.get_job_status(job_id).get('state') != 'completed' and elapsed_time < 300:
    print(f" Current state: {client.deployments.get_job_status(job_id).get('state')}")
    elapsed_time += 10
    time.sleep(10)
if client.deployments.get_job_status(job_id).get('state') == 'completed':
    print(f" Current state: {client.deployments.get_job_status(job_id).get('state')}")
    job_details_do = client.deployments.get_job_details(job_id)
    print(job_details_do)
else:
    print("Job hasn't completed successfully in 5 minutes.")

 Current state: queued
 Current state: running
 Current state: completed
{'entity': {'deployment': {'id': 'ac0dfe9a-6c98-4eeb-967b-120f9234b0e5'}, 'platform_job': {'job_id': 'bfcf37c5-d190-49bd-abca-fb22116323f5', 'run_id': '4d83ddcb-88ca-45ca-8209-446698936b8d'}, 'scoring': {'input_data_references': [{'connection': {'id': 'ae2a374b-85fd-4823-b6f5-95f11662324f'}, 'id': 'conn_db2', 'location': {'file_name': 'CUSTOMER', 'schema_name': 'PUT YOUR SCHEMA NAME HERE'}, 'type': 'connection_asset'}, {'connection': {'id': 'ae2a374b-85fd-4823-b6f5-95f11662324f'}, 'id': 'conn_db2', 'location': {'file_name': 'CUSTOMER_2', 'schema_name': 'PUT YOUR SCHEMA NAME HERE'}, 'type': 'connection_asset'}], 'output_data_reference': {'connection': {'id': 'ae2a374b-85fd-4823-b6f5-95f11662324f'}, 'id': 'conn_db2', 'location': {'file_name': 'OUTPUT', 'schema_name': 'PUT YOUR SCHEMA NAME HERE'}, 'type': 'connection_asset'}, 'status': {'completed_at': '2021-05-13T08:26:43.422Z', 'running_at': '2021-05-13T08:26:35.279Z', 'state': 'completed'}}}, 'metadata': {'created_at': '2021-05-13T08:26:23.663Z', 'id': '77973aa5-fbd0-41b8-9012-a4e2016a57dc', 'modified_at': '2021-05-13T08:26:43.622Z', 'name': 'name_3dc38b68-0227-4422-b7eb-b9bdd3f72d95', 'space_id': '680a7515-620c-461f-9c6f-1f4c535bfc47'}}

Preview scored data

In this subsection you will load scored data.

Tip: To install requests execute the following command: !pip install requests

import requests

host = db_credentials["https_url"] + "/dbapi/v3"

url = host + "/auth/tokens"
token = requests.post(url, json={
                             "userid": db_credentials["username"],
                             "password": db_credentials["password"]}).json()['token']

Get stored output using Db2 REST API

auth_header = {
    "Authorization": f"Bearer {token}"
}

sql_command = {
    "commands": "SELECT * FROM OUTPUT",
    "limit": 100,
    "separator": ",",
    "stop_on_error": "yes"
}

url = host + "/sql_jobs"
jobid = requests.post(url, headers=auth_header, json=sql_command).json()['id']

resp = requests.get(f"{url}/{jobid}", headers=auth_header)

results = resp.json()["results"][0]
columns = results["columns"]
rows = results["rows"]

Preview output using pandas DateFrame

Tip: To install pandas execute following command: !pip install pandas

import pandas as pd

pd.DataFrame(data=rows, columns=columns)

6. Clean up

If you want to clean up all created assets:

• experiments

• trainings

• pipelines

• model definitions

• models

• functions

• deployments

see the steps in this sample notebook.

7. Summary and next steps

You successfully completed this notebook! You learned how to use watsonx.ai Runtime for SPSS model deployment and scoring. Check out our Online Documentation for more samples, tutorials, documentation, how-tos, and blog posts.

Author

Jan Sołtysik Intern at watsonx.ai.

Copyright © 2020-2025 IBM. This notebook and its source code are released under the terms of the MIT License.