ETL in the cloud using SQL Query and Db2

From COS to Db2 using SQL

The SQL Query service on IBM Cloud allows to process data stored on Cloud Object Storage (COS) by writing SQL queries. So far, results were either shown in the console and / or written back to files on COS. Thus, I was happy to notice a new feature: Query results can now be written back to Db2 tables (on cloud). From my tests both Db2 on Cloud and Db2 Warehouse on Cloud are supported.

Control your database encryption keys for Db2 on Cloud

Db2 master key managed by IBM Cloud Key Protect

Since Db2 10.5 I have blogged a couple of times about the native database encryption built into Db2. Today, I want to show you how easy it is to take control of the database encryption keys for Db2 on Cloud. All Db2 database on IBM Cloud are encrypted by default, but with a system encryption key. You can increase data security even further by using your own encryption key (BYOK - bring your own key). Want to know how? Read on...

New tutorial discuss how to enhance cloud app security

Recently, I wrote a new tutorial as part of the IBM Cloud solution tutorials. Have you already developed and deployed an application on IBM Cloud? Then, may be, you followed the introductory tutorial on how to apply end-to-end security to an application. Following "security by design," you are now starting to look into the design of a new application, or perhaps you need to adapt an older application to new security requirements. If that is the case, the new tutorial on how to enhance the security of your deployed application is exactly right for you.

An existing solution is extended for enhanced security

Isolate resources

One of the fundamental principles of cloud computing is the sharing of resources. This could be the sharing of a machine—applications from many users running on the same computer—or just sharing the data center and parts of the infrastructure.
In the new tutorial, you'll learn how you can isolate runtime environments, network traffic, and stored data to increase application security. Some options include the use of dedicated resources or virtual private clouds.

Hyper-protect your data

Almost all services on IBM Cloud that store data use encryption to protect the data against unauthorized access. When using database services or object storage, by default, the encryption key is system-generated. You can increase data protection by controlling the encryption keys. IBM Key Protect and Hyper Protect Crypto Services help you provision encrypted keys for storage services as well as apps.

In the new tutorial, you learn how to control and even bring your own encryption keys. You also find out about the LinuxONE-based Hyper Protect services on IBM Cloud. They provide an extra layer of protection and the highest level of isolation.

Evaluate and monitor app security

Events related to IBM Cloud account activities—such as logging in or provisioning a service—are logged to Activity Tracker with LogDNA. In the tutorial, you learn how to enhance your app to send security or audit messages and integrate them across the stack. Use security advisors and set up notifications to stay ahead and informed.

Get started with the tutorial

The tutorial on how to enhance security of your deployed application is part of the IBM Cloud solution tutorials. It helps you learn about enhanced data encryption options, isolate your application runtime for extended security, and use activity logs and security advisors to evaluate app security.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

IBM Cloud: Some fun with Python and Cloud Foundry Logging

IBM Cloud: Turn Log Data into Donut

Last month, after receiving user questions, I blogged about how to decipher Cloud Foundry log entries. Today, I want to point you to a small Cloud Foundry Python app I wrote. It helps to better understand Python and Cloud Foundry logging. You can also use it to test the IBM Cloud Log Analysis service which provides an easy-to-use interface to logs generated by applications running in the IBM Cloud. In the premium plans, external log events can also be fed into the service for consolidated storage and analysis.

As usual, the code for my app is available on Github: https://github.com/data-henrik/application-log-analysis/. Once deployed to IBM Cloud, the app can be used to send messages on a chosen log level back to the server. The server-side log level, i.e., the threshold for processed log messages can also be set. The app produces diagnostic output on "stdout" and "stderr". The two are treated differently by Cloud Foundry. Here is a screenshot of the logging app:

Test app for Cloud Foundry logging

The produced log entries can also be used to try out the IBM Cloud Log Analysis service. Diagnostic logs are automatically forwarded to the Log Search of that service. The messages are fed into Elasticsearch and can be analyzed using Kibana. I wrote some search queries (one shown below) and then built visualizations like the shown "Donut" based on those queries. I will write more about that in a future blog post.

Search Query for Elasticsearch / IBM Cloud Log Analysis

An official tutorial using that app and Log Analysis is available in the IBM Cloud docs.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

App Security Scanner: My Cloud Database App is Secure

Cloud Application Security

Over the past years I have written a couple cloud-based apps. Most of them have a database backened. I know - to a good degree - how to secure the database system. I have some background in secure software engineering. And I trust the cloud providers like IBM to secure the platform and runtime environment. Today, I wanted to get feedback on the overall web app security and tested the Application Security on Cloud service offered in the IBM Cloud Platform. Here is what I did and the results.

Bluemix: Simple cron-like service for my Python code

Schedule background tasks in Bluemix

This morning I had some time to fiddle around with Bluemix and Python. I wanted to test out creating a cron-like service, i.e., something that runs in the background and kicks off tasks with given intervals or at a given time. For Python there exists a package "schedule" that is easy to set up and use. So how do you create a background service in Bluemix?

The trick is in the file manifest.yml and the Cloudfoundry documentation has all the needed details (Bluemix is built on this open standard). The attribute "no-route" is set to true, indicating that this is not a Web application and we don't need a subdomain name. In addition the attribute "command" is set to invoke the Python interpreter with my script as parameter. Basically, this starts my background task:

 applications:  
 - name: hltimer  
  memory: 256M  
  instances: 1  
  no-route: true  
  command: python mytimer.py  
  path: .  

The cron-like service script is pretty simple. It uses the "schedule" package to set up recurring jobs. I tested it with the Twilio API to send me SMS at given timestamps. You can also use it to scrape webpages in given intervals, kick off feed aggregators, read out sensors and update databases like Cloudant or DB2, and more. See this Github repository for the full source.

 import schedule  
 import time 

 def job():  
 #put the task to execute here  

 def anotherJob():  
 #another task can be defined here  

 schedule.every(10).minutes.do(job)  
 schedule.every().day.at("10:30").do(anotherJob)  

while True:  
   schedule.run_pending()  
   time.sleep(1)  

Node-RED: Simple "phoney" JSON entries in Cloudant

Node-RED Starter
on IBM Bluemix

Want to automatically store data about who called you on which phone number? Last Friday tried exactly that on IBM Bluemix and was amazed how simple it is, no real programming involved. All I needed was the Node-RED starter boilerplate (icon is on the right) on Bluemix and a new API of my mobile service provider.

The Node-RED boilerplate automatically creates a Node.js runtime environment on Bluemix and installs the Node-RED tool into it. In addition, a Cloudant JSON database is created. Once everything is deployed I opened the Node-RED tool in a Web browser. It offers a basic set of different input and output methods, processing nodes, and the ability to connect them in a flow graph. One of the input nodes is a listener for http requests. They help to react to Web service requests. I placed such http input node on the work sheet and labeled it "phone" (see screenshot).

Node-RED tool on Bluemix

How did I obtain information about callers and the numbers they called? Well, I tapped my friends at a security agency. I used a new free service offering at one of my phone service providers. It is called "sipgate.io" and allows to configure an URL/Web address that is accessed whenever someone calls one of the account's phone numbers (you could have multiple phone numbers). An http POST request is sent to the configured URL and the caller's phone number and the called number are included as payload. In my Node-RED application the "phone" node would answer to this request.

What I needed now was the data processing flow of the Web services request. On Friday I already tweeted the entire flow:

Easy. Cool. #NodeRED used to store #caller info coming from #sipgate API into #Cloudant on #Bluemix pic.twitter.com/MFkqFAdp5D
— Henrik Loeser (@data_henrik) October 10, 2014

On the left we see "phone" node as http input. Connected to it is the "ok" node which sends an http response back, telling the phone company's Web services that we received the information. The other connected node is a "json" processor which translates the payload (who called which number) into a meaningful JSON object. That object is then moved on to the "calls" node, a Cloudant output node. All we needed was to select the Cloudant service on Bluemix and to configure the database name.

Cloudant Output Node, Node-RED on Bluemix

The magic itself was pressing the "Deploy" button on top of my Node-RED worksheet. It created the Node.js code for my Bluemix app. I was a little bit nervous about testing the app because I didn't code anything, just clicking. For my test I took the phone in my home office and dialed the number of my mobile phone number. The result? A new, nice and shiny, "phoney" JSON document in Cloudant:

"Phoney" record in Cloudant

Almost too easy. Great stuff, but unfortunately no code to share... :) (You can find the flow on Github)

A word on design and Star Wars

During the vacation my wife and I enjoyed watching two Star Wars movies. First off, renaming or reversioning them from 1 and 3 to episode IV and episode VI is so IBM with renaming, rebranding, and confusing customers (take that, marketing!).

What got me thinking most during the movies was the design and "user interface". In order to interact with a powerful droid like R2-D2 they needed a translator like C-3PO to turn beeps back into a human language (or something understandable). This looked like many of today's products where the service guy is needed too often to turn cryptic (or non-existing) messages into something customers can make sense of.

Anyway, I am looking forward to the next DVD night...