How to Create Import Jobs to Import Datameer dap Database Data

Goal

Create an import job that will import data from a Datameer dap database.

Learn

As an example, suppose that an administrator wants to report on the current state of executions of all workbooks over the last 28 days, it is necessary to gather this kind of information via an external MySQL query from the Datameer MySQL database. As an administrator, I want to have a generic framework to run reports against a SQL database. 

How can I do this directly with Datameer? 

First create a new folder where you will store all of the artifacts.

Setup a connection in Datameer from "Type MySQL" with "Connection Details" to your "Database Host" (where the Datameer database is laying). By default "Database Name", "User Name" and "Password" are "dap".

Next create an "Import Job", which uses the just created connection "ToLocalMySQLInstance". You need to do this for each table you are pulling. There will be three of them, "dap_file", "dap_job_configuration" and "dap_job_execution".

 

Configure how often you like to run the report. In this example it is configured on a daily base. 

After the "Import Job"s are created and ran the first time, you "Add data to new workbook" by right clicking on each them. They will be stored with the same names "dap_file", "dap_job_configuration" and "dap_job_execution" after.

The workbooks need to be configured "Trigger"d "Data driven", so they will run every day after new data from the tables where pulled. 

After you have done this for all three tables, you create an other workbook where you "Add Data" from "dap_file", "dap_job_configuration" and "dap_job_execution".

This workbook will be saved as "current_state_executions".

Within "current_state_executions" you create a joined sheet with two "Inner Join"s on "dap_job_configuration/dap_file__id" - "dap_file/id" and "dap_job_configuration/id" - "dap_job_execution/dap_job_configuration__id" and choose the included columns as you need.

Create a duplicate from that joined sheet with the for you necessary columns and call it "CurrentStateExecutuions". Now you can setup e.g. filter or formulas to fit your needs.