SQL Database for Cloud data

Objectives

• Provision a SQL database
• Create the database schema (table) and load data
• Deploy a pre-built containerized app to Code Engine
• Connect the app and database service (share credentials)
• Monitor, Secure, Backup & Recovery of cloud databases

Before you begin

This tutorial requires:

• IBM Cloud CLI with the Code Engine plugin,
• git to clone the source code repository.

To avoid the installation of these tools you can use the Cloud Shell from the IBM Cloud console.

You will find instructions to download and install these tools for your operating environment in the Getting started with tutorials guide.

1. Clone the Github repository for this tutorial and change into its directory. In a terminal, execute the following lines:

   git clone https://github.com/IBM-Cloud/cloud-sql-database.git
   cd cloud-sql-database
   

2. Go to GeoNames and download and extract the file cities1000.zip. It holds information about cities with a population of more than 1000. You are going to use it as data set.

Provision the SQL Database

Start by creating an instance of the Db2 Warehouse on Cloud service.

1. Visit the IBM Cloud® console. Click on Catalog in the top navigation bar.
2. Click on Databases on the left pane and select Db2 Warehouse.
3. Pick the Flex One plan and change the suggested service name to sqldatabase (you will use that name later on). Pick a resource group and a location for the deployment of the database.
4. Click on Create. The provisioning starts.
5. In the Resource List, locate the new instance under Databases and wait for it to be available (sometimes you may need to refresh the page). Click on the entry for your Db2 Warehouse on Cloud service.
6. Click on Open Console to launch the database console.

Create a table

You need a table to hold the sample data. Create it using the console.

1. In the console for Db2 Warehouse on Cloud click on the upper left menu icon, then Run SQL in the navigation bar.
2. Click on the + symbol (Add a new script) next to the Untitled - 1 tab.
3. Click on From file and select the file cityschema.txt from the GitHub repository that was previously cloned to your local directory and open it.
4. Click on Run all to execute the statement. It should show a success message.

Load data

Now that the table "cities" has been created, you are going to load data into it. This can be done in different ways, for example from your local machine or from cloud object storage (COS) or Amazon S3 interface. For this tutorial, you are going to upload data from your machine. During that process, you adapt the table structure and data format to fully match the file content.

1. In the console for Db2 Warehouse on Cloud click on the upper left menu icon, then Data in the navigation bar.

2. As Source keep the selection on My Computer.

3. Under File selection, click on Drag a file here or browse files to locate and pick the file "cities1000.txt" you downloaded in the first section of this guide.

4. Click Next to get to the Target overview with a Schema selection. Choose the schema BLUADMIN, then the table CITIES. Click on Next again.

   Because the table is empty it does not make a difference to either append to or overwrite existing data.

5. Now customize how the data from the file "cities1000.txt" is interpreted during the load process. First, disable Header in first row because the file contains data only.

6. Next, type in 0x09 as separator. It means that values within the file are delimited by tab(ulator).

7. Last, pick "YYYY-MM-DD" as date format. Now, everything should look similar to what is shown in this screen capture.

   

8. Click Next and you are offered to review the load settings. Agree and click Begin Load to start loading the data into the CITIES table. The progress is displayed. Once the data is uploaded it should only take few seconds until the load is finished and some statistics are presented.

9. Click on View Table to browse the data. You may scroll down or click on column names to change the sort order.

Verify Loaded Data Using SQL

The data has been loaded into the relational database. There were no errors, but you should run some quick tests anyway. Use the built-in SQL editor to type in and execute some SQL statements.

1. In the left navigation click on Run SQL to get back to the SQL editor. Click on the + symbol (Add new script) and Create new to create a new editor tab.

   Instead of the built-in SQL editor you can use cloud-based and traditional SQL tools on your desktop or server machine with Db2 Warehouse on Cloud. The connection information can be found in the Administration menu in the left navigation.

2. In the editor type or copy in the following query:

   select count(*) from cities;
   

   Select the text of the query, then, in dropdown next to Run All, choose Run selected. In the section with results, the same number of rows as reported by the load process should be shown.

3. In the "SQL Editor" enter the following statement on a new line:

   select countrycode, count(name) from cities
   group by countrycode
   order by 2 desc;
   

   Mark the text of the above statement and click the Run selected button. Only this statement is executed, returning some by country statistics in the results section.

4. Finally, run the following statement similarly to retrieve details about San Francisco in California:

   select * from cities
   where name='San Francisco'
   and countrycode='US';
   

Deploy the application code

Change back to the terminal. Now you are going to deploy the application code, using a pre-built container image. You can modify the application code and build the container image on your own. See the instructions in the GitHub repository for details.

1. If you are not logged in, use ibmcloud login or ibmcloud login --sso to log in interactively. Set the region and resource group to where the database has been provisioned. Replace RESOURCE_GROUP and REGION accordingly.

   ibmcloud target -g RESOURCE_GROUP -r REGION
   

2. Create a new Code Engine project named sqldatabase:

   ibmcloud ce project create --name sqldatabase
   

   Select the new project as the active one:

   ibmcloud ce project select --name sqldatabase
   

3. Then, deploy the app naming it worldcities.

   ibmcloud ce app create --name worldcities --image icr.io/solution-tutorials/tutorial-cloud-sql-database:latest --min-scale 1
   

4. Last, create a service binding between the existing Db2 Warehouse on Cloud database and the app:

   ibmcloud ce application bind --name worldcities --service-instance sqldatabase
   

   Once the binding is created, a new app revision is started.

5. Now you can check the app details for its status and to retrieve its URL:

   ibmcloud ce app get --name worldcities 
   

   In the output, look for the line starting with URL. The shown URL should have a pattern like https://worldcities.unique-subdomain.region.codeengine.appdomain.cloud. Click on the link to access the app. Another option to retrieve app details is to visit the Code Engine console.

Security, Backup & Recovery, Monitoring

The Db2 Warehouse on Cloud is a managed service. IBM takes care of securing the environment, daily backups and system monitoring. When you are using one of the enterprise plans there are several options to manage access and to configure enhanced data encryption.

In addition to the traditional administration options the Db2 Warehouse on Cloud service also offers a REST API for monitoring, user management, utilities, load, storage access and more.

Test the App

The app to display city information based on the loaded data set is reduced to a minimum. It offers a search form to specify a city name - names are case sensitive - and few preconfigured cities. They are translated to either /search?name=cityname (search form) or /city/cityname (directly specified cities). Both requests are served from the same lines of code in the background. The cityname is passed as value to a prepared SQL statement using a parameter marker for security reasons. The rows are fetched from the database and passed to an HTML template for rendering.

Cleanup

To clean up resources used by the tutorial, follow these steps:

1. Visit the IBM Cloud® Resource List.
2. In the Code Engine section locate the project sqldatabase. Click on the three dots and select Delete to delete the project and its app.
3. Locate the database sqldatabase under Databases. Again, click on the three dots and select Delete to delete the database.

Depending on the resource it might not be deleted immediately, but retained (by default for 7 days). You can reclaim the resource by deleting it permanently or restore it within the retention period. See this document on how to use resource reclamation.

Expand the tutorial

Want to extend this app? Here are some ideas:

1. Offer a wildcard search on the alternate names.
2. Search for cities of a specific country and within a certain population values only.
3. Change the page layout by replacing the CSS styles and extending the templates.
4. Allow form-based creation of new city information or allow updates to existing data, for example population.

Related Content

• Documentation: API Description for the ibm_db Python driver
• Db2 Data Management Console
• Db2 on Cloud