You've been wanting to see what IBM's cloud data warehouse dashDB is all about, but how can you load in your own data to explore? If you are currently using IBM PureData System for Analytics (formerly Netezza) this blog post is for you. You can make some of your data available in dashDB in only few basic steps:

  • Evaluate compatibility of your source database definitions and scripts with dashDB and run the generated DDL to create the required database artifacts in dashDB.
  • Export the source data to csv files.
  • Load the data files into dashDB.

Take a minute (actually 2 and 22 secconds, but who's counting?) to watch this video that outlines the process:

That looks easy, doesn't it? Let's give it a try.

Ready

Before you begin, complete the following steps to prepare your environment and set up the data warehouse on Bluemix:

  1. Login to Bluemix (or sign up for a free trial).
  2. Provision a dashDB service instance.

    In the dashDB service page, keep the default settings; they should work just fine. If you already have an instance of dashDB that you'd like to use, go right ahead.

  3. Launch dashDB.

    The dashDB web console opens.

  4. Download and install the free Data Studio and the Database Conversion Workbench plugin on a local machine in your environment.

    In the web console, select Connect > Download Tools > Database conversion software. Watch how.

  5. Collect the dashDB connection information. (You'll need it to connect the Database Conversion Workbench and your applications to dashDB.)

    In the dashDB web console, select Connect > Connection Information and note the connection settings for access with and without SSL.

  6. If you are planning to transfer large amounts of data, download the moveToCloud script.

    In the web console, select Load > Load from Cloud > Move to cloud to access the script and supporting documentation.

Set

With your prerequsite tasks complete, you can prepare the data warehouse and extract the data from PureData for Analytics.

  1. Evaluate compatibility of the source DDL and scripts with dashDB and run the generated DDL to create the required database artifacts in dashDB.

  2. Unload the source data to csv files.

    For large data files, we suggest loading them into dashDB from cloud storage, like Softlayer's Swift Object Storage or Amazon S3. You can use the previously mentioned movetoCloud script to upload the exported data to cloud storage, as shown in this video.

Go!

Load the data, in one of 2 ways, depending on your data size and location:

That's it! You've moved data from PureData for Analytics into dashDB and can now run the desired analytics on your cloud data warehouse.

1 comment on"From PureData System for Analytics to a dashDB Cloud Data Warehouse"

  1. frank pantaleo November 14, 2017

    Patrick I’m using the lift CLI (which is free). This is script I came up with to move tables and works in conjunction with a nz_get_table_names

    #!/bin/sh
    source setupEnv.sh
    #
    stable_name=$1
    sdb=xxxx
    suser=source_user
    spassword=source_password
    shost=localhost
    sfile=’some_directory’
    sfile=”${sfile}${stable_name}.csv”
    thost=cloud_server
    tpassword=cloud_password
    tuser=cloud_user
    tschema=cloud_schema
    ttable_name=${stable_name:3}
    tfile=”${stable_name}.csv”
    #
    rm -f ${sfile}
    lift extract –source-table ${stable_name} –source-database ${sdb} –source-host ${shost} –source-user ${suser} –source-password ${spassword} –source-database-port 5480 –file ${sfile} –source-schema ${suser}
    echo $?
    #
    #
    lift put –replace –file ${sfile} –target-user ${tuser} –target-password ${tpassword} –target-host ${thost}
    echo $?
    #
    lift load –filename ${tfile} –target-schema ${tschema} –target-table ${ttable_name} –file-origin extract-pda –target-user ${tuser} –target-password ${tpassword} –target-host ${thost}
    echo $?
    rm -f ${sfile}

Join The Discussion

Your email address will not be published. Required fields are marked *