torsstei/sqlfunction repository overview

⁠Purpose

This docker image can be used to create an OpenWhisk function (for instance using IBM Cloud Functions) that runs an arbitrary ANSI SQL statement on data stored in IBM Cloud Object Storage. Refer to the IBM Cloud SQL Query documentation⁠ to understand the general capabilities of this service.

This image is using the ibmcloudsql⁠ client library for Python to interact with IBM Cloud SQL Query. If you want to understand the mechanics of this implementation or want to make changes you can find the sources for this docker image here⁠.

⁠Usage

⁠Prerequisites

The usage described below requires you to have installed the IBM Cloud CLI utility⁠ (bx) and the IBM Cloud Functions plugin⁠ for it.

The description also assumes that you have already successfully logged on to IBM Cloud using one of the by logon options in your terminal window.

⁠Create the SQL function

• bx wsk action create sqlcloudfunction --timeout 300000 --docker torsstei/sqlfunction
• You can name the function also other than sqlcloudfunction. Note that we set the timeout to the maximum supported 5 minutes for IBM Cloud Functions, which means you can run SQL queries that take up to 5 minutes to execute.
• In case you want to delete the function you can issue bx wsk action delete sqlcloudfunction

⁠Parameters

The function requires you to set the following parameters:

• sql The SQL statement text. Refer to IBM Cloud SQL Query documentation for details and use the IBM Cloud SQL Query web console to author and test your SQL statement interactively.
• api_key - Your IAM API key for your IBM Cloud user or service ID with access to your SQL Query instance and Cloud Object Storage bucket. You can create API Keys in the IBM Cloud Console in Manage->Security->Platform API Keys.
• instance_crn - The instance CRN of your SQL Query instance. You can retrieve it in IBM Cloud console dashboard for your SQL Query instance. Press the Instance CRN button there to copy it to your clipboard.
• target_url - Cloud Object Storage URL for the SQL result target. Format: cos://<endpoint>/<bucket>/[<prefix>].
• client_info (optional) - Tracking information to identify your client application inside IBM Cloud.

Bind parameters before function invocation

It is practical to register the parameter values with your function instance that you created in the previous step. Typically you want to do this for the api_key, instance_crn and target_url. This means that you do not have to pass these parameters with every invocation of your function. To register the parameters issue:

bx wsk action update sqlcloudfunction \
 --param apikey <your API key here> \
 --param sqlquery_instance_crn <your SQL Query instance CRN here> \
 --param target_url cos://<endpoint>/<bucket>/[<prefix>]

.
You can also bind the sql statement text in a static way like this if you want to create one cloud function instance per SQL query that you want to run. But in the following we show you how to submit the SQL query text with the invocation of the function.

⁠Call the function

bx wsk action invoke -br --result sqlcloudfunction \
 --param sql "SELECT * FROM cos://us-geo/sql/employees.parquet STORED AS PARQUET"

.
So we pass the SQL query as a parameter with the invocation. In the above example we are using one of the sample data sets that are available out of the box in SQL Query. The function returns a json document that has four top level keys:

• jobId The unique identifier of a single SQL Query submission.
• result_location The URL where the result of the SQL query has been written in Cloud Object Storage. The URL format is the same as in the target_url parameter.
• result_access_pandas A ready rot use Python code snippet to access the result set as a pandas data frame. Only the users API key needs to be added in.
• result_set_sample The first 10 rows of the result set as a json structure.