This tutorial shows how to provision a SQL (relational) database service, create a table and load a larger data set, city informationy into the database. Thereafter, we deploy a web app "worldcities" to make use of that data and show how to access the cloud database. The app is written in Python using the Flask framework.
This tutorial is part of IBM Cloud tutorials and discussed as SQL Database for Cloud Data.
To get this SQL database-backed app up and running only few steps and about 10 minutes are needed. We will provsion the database service, create a table, load some data, then push the app to the IBM Cloud.
- Start by selecting the Db2 Warehouse on Cloud service in the Data & Analytics section of the catalog.
- Pick the Entry plan. Change the suggested service name to "sqldatabase" (we will use that name later on). Pick a region (data center) for the deployment of the database and make sure that the correct organization and space are selected.
- Click on Create. After a short moment you should get a success notification. You can click it away or wait until you are taken to the dashboard.
We create the SQL database service in the form of Db2 Warehouse on Cloud.
- In the IBM Cloud dashboard locate the entry for the Db2 Warehouse on Cloud service. Click on it and you will be taken to the service dashboard. From here you can get to the documentation ("IBM Knowledge Center") under "Learn" or to the console (Web UI) for Db2 Warehouse on Cloud. Click on Open. The console is now loaded.
- If it is the first time using the console, you are offered to take a tour. Take it. Remember how you can easily take the tour again. It is explained during the tour.
- Click Explore in the navigation bar. It takes you to a list of existing schemas in the database. Locate the schema beginning with "DASH". Click on it.
- Now we are creating the new table. Use the "+ New Table" for it. It brings up a form for the table name and its columns. Put in "cities" as table name. Copy the column definitions from the file cityschema.txt and paste them into box for the columns and data types.
- Click on Create to define the new table.
Now that the table "cities" has been created, we are going to load data into it.
- Download and extract the file cities1000.zip from GeoNames. It holds information about cities with a population of more than 1000. We are going to use it as data set.
- In the top navigation of the Db2 console click on Load. This brings up the load dialog where you have a choice of loading data from your local machine, from cloud object storage (COS) with Swift interface (IBM Cloud / Softlayer) or from Amazon S3. You can also utilize the Lift migration service to transfer data from existing data sources. And if that's not enough you could send in disk drives to quickly upload large amounts of data. For our case, uploading from the local machine will do.
- In the "File selection" either use "browse files" to locate and pick the file "cities1000.txt" (see above) or drag it into that landing area. Click Next to get to the schema overview. Choose the schema starting with "DASH" again, then the table "CITIES". Because the table is empty it does not make a difference to either append to or overwrite existing data. Click on Next again.
- The dialog shown then is used to 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. Next, type in "0x09" as separator. It means that values within the file are delimited by tab(ulator). Last, pick "YYYY-MM-DD" as date format.
- Click Next and you are offered to review the load settings. If you agree, 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.
The ready-to-run code for the database app is located in this Github repository. Clone or download the repository, then push it to the IBM Cloud. You need to be logged in to the region, org and space to which the database has been provisioned.
cf push your-app-name
The file manifest.yml tells the IBM Cloud to bind the app and the database service named "sqldatabase" together. No further configuration is needed. Once the push process is finished you should be able to access the app. Enjoy.
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 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.
Want to extend this app? Here are some ideas:
- Offer a wildcard search on the alternate names.
- Search for cities of a specific country and within a certain population values only.
- Change the page layout by replacing the CSS styles and extending the templates.
- Allow form-based creation of new city information or allow updates to existing data, e.g. population.
- Documentation: IBM Knowledge Center for Db2 Warehouse on Cloud
- Frequently asked questions about IBM Db2 on Cloud and IBM Db2 Warehouse on Cloud answering questions related to managed service, data backup, data encryption and security, and much more.
- Free Db2 Developer Community Edition for developers
- Documentation: API Description of ibm_db Python driver
- IBM Data Server Manager