Quickstart example with Dockerized Teiid

Version 1

    docker.jpg

    Starting with version 8.10, Teiid team has released a Docker image for Teiid runtime. You can download the Teiid Docker image from Docker Hub. In this article, I will take you through the same Quick Start example that we have as introduction to Teiid, where integration between a File and Relational databases is shown. However in this example, I will be using Docker based Teiid and Docker based MariaDB database to run the same example.  Please go through the Quick Start Example first and understand it, as here I will be not explaining about the example, but  I will show you how to run the example using Docker image.


    You can use the techniques learned in this example to bring in additional sources or replace the sources to be integrated with Teiid. If you already have a existing database or any other NoSQL source already setup on a remote server, there is no reason for you to make that into a docker image before you can use that with Teiid. Note that using single Docker image for specific purpose is very simple, what is complicated is designing the interactions between the multiple Docker container images to accomplish a certain complete usecase. That process is also called as container orchestration. There are few tools that can help with container orchestration, but I wanted to keep this example easy and simple. So, I opted not to use any of those tools.  It is also my assumption in writing this article that, you are familiar with Teiid and also how Docker works. If are new to Teiid, then I suggest starting at http://teiid.org going through some documents and examples, and if you are new to Docker start at Docker  see how it works. So. let's begin with our example.


    If you do not have Docker installed on your machine, we need to first install Docker in your machine. Depending upon your machine type see instructions to install Docker first before we can proceed. I am using Fedora 21 machine for the below scripts, please note that depending upon your OS type they may need to be modified slightly to make them work. (Yes, I am talking about Windows)


    The recipe for this example is at my GitHub repo at docker-quickstarts/data-federation  along with data and VDB files required for the example.

     

    git clone https://github.com/rareddy/docker-quickstarts.git
    cd docker-quickstarts/data-federation
    
    
    
    
    

     

    teiid.png

    We have downloaded the data files required for this example, let's start with downloading the JDBC driver for MarilaDB, if you already have the driver, skip the following step and copy the driver file to location specified in the script.

     

    # get JDBC URL for the MariaDB
    wget -O data/mariadb/mariadb-java-client-1.1.8.jar https://downloads.mariadb.org/interstitial/client-java-1.1.8/mariadb-java-client-1.1.8.jar
    
    
    
    
    

     

    Download the Teiid Docker image, from DockerHub

     

    sudo docker pull teiid/teiid:8.10.0.Final

     

    For configuration management purposes, I would like to design this example such that, I can easily edit the standalone-teiid.xml file or any other configuration file from JBoss EAP or Teiid. For that I need to run the Teiid container image once, and externalize (copy) the configuration directory.  I want to run the JBoss EAP in "standalone" mode, so I need to externalize the "<jboss-eap>/standalone" directory from docker container to my local machine, and then configure the Teiid docker container to use this externalized directory. For that run the following commands.

     

    # start the Teiid docker image
    sudo docker run -d --name=teiid810 teiid/teiid:8.10.0.Final
    
    # copy the standalone directory to local machine
    
    sudo docker cp teiid810:/opt/jboss/teiid-8.10.0.Final/standalone teiid810
    
    
    # kill the Teiid image
    
    sudo docker kill teiid810
    
    
    # remove the image
    
    sudo docker rm teiid810
    
    
    
    
    

     

    We have all the necessary files for dockerized Teiid. I do not have a MariaDB available locally, so I will be getting a docker image for that. if you already have a existing database/image, you can skip all the steps involving the MariaDB

     

    sudo docker pull mariadb:latest
    
    
    
    
    

     

    Start the MariaDB database and I am going to name the docker container as "accounts", as it represents the "Accounts" database from quick start example. Give different user and password information if you choose to do so.

     

    sudo docker create --privileged=true -t -i \
        -v `pwd`/data/mariadb:/opt/jboss/data-federation/data/mariadb \
        -e MYSQL_ROOT_PASSWORD=mypass \
        -e MYSQL_USER=user \
        -e MYSQL_PASSWORD=mypass \
        -e MYSQL_DATABASE=accounts \
        --name=accounts mariadb
    
    # start the database
    
    sudo docker start accounts
    
    
    
    
    

     

    We have started the MariaDB database, but the schema for the Accounts database is not been created so far in the above MariaDB docker container, before we can use this database, we need to create the schema and fill in the tables with data. The Github data we downloaded at the beginning of the article contains necessary files for this purpose.

     

    sudo docker exec -ti accounts bash

     

    The above command will open shell prompt, where execute the following statement to create the schema for "Accounts" database.

     

    mysql -u user -p accounts < /opt/jboss/data-federation/data/mariadb/accounts.sql
    
    # supply password as "mypass", my efforts to include on the cmd did not work.
    # exit from shell
    exit
    
    
    
    
    

     

    Now we got MariaDB instance, we need to create a Teiid instance, that is aware of the MariaDB instance by linking it and also uses the externalized configuration directory created before. The reason for linking the containers is, it makes the configuration of the system simple as you do not have to figure out the mapped ports etc between the container images. When you configure MariaDB 3306 port in Teiid docker images and they both are "linked",  then Teiid container knows how to connect to MariaDB container. If the containers are not linked that will not be the case, and you would have to configure for the actual host and port information.

     

    #create docker container for Teiid
    sudo docker create --privileged=true -t -i -P \
        -v `pwd`/teiid810/standalone:/opt/jboss/teiid-8.10.0.Final/standalone \
        -v `pwd`:/opt/jboss/data-federation \
        --name=teiid810 \
        --link accounts:accounts \
        teiid/teiid:8.10.0.Final
    
    #start the Teiid container
    
    sudo docker start teiid810
    
    
    
    
    

     

    We have created Teiid docker container and MaraiDB container and successfully linked them together, and started both of them. You can view the running containers by using the command


     

    sudo docker ps 

     

     

    which will show results like

     

     

    CONTAINER ID        IMAGE                      COMMAND                CREATED             STATUS              PORTS                                                                                                  NAMES
    8548a3280bc6        teiid/teiid:8.10.0.Final   "/bin/sh -c '$JBOSS_   49 minutes ago      Up 49 minutes       0.0.0.0:32770->8080/tcp, 0.0.0.0:32771->9990/tcp, 0.0.0.0:32768->31000/tcp, 0.0.0.0:32769->35432/tcp   teiid810            
    b5add16d7650        mariadb:latest             "/docker-entrypoint.   50 minutes ago      Up 50 minutes       3306/tcp                                                                                               accounts
    


    Before you can connect to Teiid instance and issue SQL queries against it, we need to first create "admin" user and "application" user, just like in the traditional Teiid installations. For that execute the below commands, and follow the directions given to you by the script. For the "application" user give "odata, rest" as groups.


    #add management user
    sudo docker exec -ti teiid810 /opt/jboss/teiid-8.10.0.Final/bin/add-user.sh
    
    #add application user(choose "odata,rest" as groups)
    sudo docker exec -ti teiid810 /opt/jboss/teiid-8.10.0.Final/bin/add-user.sh
    
    
    
    
    

     

    The environment is now set up, we need to create the data sources in the Teiid runtime to access the MariaDB and also deploy a VDB for our quick start example. I have conveniently created a CLI file that can be run to create necessary configuration changes, otherwise you can also manually edit the standalone-teiid.xml file for it. Note that if you do edit the standalone-teiid.xml file, you need to stop the Teiid container and restart it for configuration changes to take affect. Or you can even open a shell prompt to the Jboss-cli.sh and execute commands. Take look at the below setup.cli file kind of commands I have put together for this task to get a feel for what you are doing.

     

    # lets create data sources and deploy the VDB
    sudo docker exec -ti teiid810 /opt/jboss/teiid-8.10.0.Final/bin/jboss-cli.sh --file=/opt/jboss/data-federation/setup.cli
    
    
    
    
    

     

    We are done setting up the environment and deploying the example vdb. Typically Teiid JDBC port is exposed 31000, however since we are running in the container environment, you can map the port to 31000 on machine where the Teiid docker image running, or you can configure  to any other port or let the docker assign the forwarded port to local machine. I choose the later option, so I need to figure out the forwarded port, which I can find out by running

     

    # List all the ports
    sudo docker port teiid810
    
    
    
    
    

     

    this will show out put like

     

    31000/tcp -> 0.0.0.0:32768
    35432/tcp -> 0.0.0.0:32769
    8080/tcp -> 0.0.0.0:32770
    9990/tcp -> 0.0.0.0:32771
    


    Now using the SquirreL or any other JDBC client application connect to the VDB name "Portfolio" with URL like "jdbc:teiid:Portfolio@mm://0.0.0.0:32768" with Teiid JDBC Driver and issue queries like (NOTE: your port may be different than what listed here)

     

    SELECT ACCOUNT_ID,PORTFOLIO_VALUE FROM "Stocks"."PersonalHoldings"

     

    you will see result

     

    19980002537.5
    199800035,715.3
    1998000420,222.5
    199800055,095.19
    19990007913.75
    1999000814,048.4
    1999000910,196.4
    20000015425.25
    200000195,304
    200000205,471.25
    2000002115,683
    200100229,776.25
    200100273,189.29
    200200342,382.66
    200200352,992
    200200366,845.58


    You can query Teiid container using JDBC/ODBC or OData. If you have custom web-services  even those can be executed. 


    If you want use this newly created Teiid container for continuous testing and want to save this container as an image so that you can use this later, you can create a image by doing a command

     

    sudo docker commit -m "my first example" teiid810 teiid:maria_db_test

     

    that will create a new image with all the changes you made in this article. The next time you want to use the same example, you simply need to do

     

    sudo docker run -d --name=maria_db_test  teiid:maria_db_test


    and start issuing the queries. Don't forget you need to get the right ports for it again, as every time you restart the port mapping will be different unless you configure to use specific ports.


    That's it, enjoy. Let me know if you find any improvements in the scripts or want to contribute to other examples. I am already finding using the Docker images for data sources extremely useful as many times I have to test different versions of the same data source. Having a image readily available without going though lot of configuration is really helpful and fast.

     

    Ramesh..

    http://teiid.org