The database source block allows you to import database data into your Omniscope workflow and reports.
A typical use case would be to import all the data from a single table. If the table is very large it can be beneficial to filter the data before processing further, reducing the number of records and decreasing the amount of time required to execute your workflow.
This article outlines two approaches to filtering that are supported by the database source block.
Scenario setup - populating the database via Omniscope Database OUTPUT block
First we download a CSV file containing Fifa 2019 football player attributes from Kaggle. The dataset contains fields such as player name, age, nationality, value, club etc.
We then used a File source block to import the data and a Database output block to export the data to our database.
Scenario objective
We want to import English Premier League player data from the database. At the moment the data contains worldwide player data. If we examine the table structure there is no "League" or "Country" field, so instead we must filter the data by club name. There are twenty Premier league clubs.
First approach - Table query filter list
In the first approach we will add a series of filters in the database source block options.
We add a database source block to the Omniscope workflow, enter our connection details and click "Connect" to establish a connection. The default query type is "Table". We select the table containing the data we exported in the setup phase.
We can add one or more filter rules by clicking the "+" button. Each filter requires us to enter a field, condition and value. We must therefore add a filter for each Premier League club individually; 20 filters in total.
Before executing we need to change the filter condition to match on ANY of the filters.
We can now execute the block to obtain the filtered data.
Second approach - Custom SQL query input parameter
In the second approach we will use an input dataset to filter the data.
We create a CSV file containing the names of all the premier league clubs and import the data into the workflow using a File source block.
Now we connect the file source block to our database source block.
This time we select the "Custom SQL" query type. If you are unfamiliar with SQL there are many online tutorials available, for example:
The statement allows us to use a field as a parameter by simply inserting [[FIELD_NAME]]. When the statement is executed Omniscope will convert this to a comma separated list of all unique values in that field.
We enter the following custom SQL statement.
We can now execute the block to obtain the filtered data.
This approach is particularly useful if your filter values are likely to change over time. We can use any block as an input, so we can generate the data using an external process or use a connector, such as Twitter or Reddit. Custom SQL input parameters are not currently supported in live query mode.
Now we can create a cool report to share with our friends...
Introduction
The database source block allows you to import database data into your Omniscope workflow and reports.
A typical use case would be to import all the data from a single table. If the table is very large it can be beneficial to filter the data before processing further, reducing the number of records and decreasing the amount of time required to execute your workflow.
This article outlines two approaches to filtering that are supported by the database source block.
Scenario setup - populating the database via Omniscope Database OUTPUT block
In this scenario we are using a MySQL database.
https://www.mysql.com/
First we download a CSV file containing Fifa 2019 football player attributes from Kaggle. The dataset contains fields such as player name, age, nationality, value, club etc.
https://www.kaggle.com/karangadiya/fifa19
We then used a File source block to import the data and a Database output block to export the data to our database.
Scenario objective
We want to import English Premier League player data from the database. At the moment the data contains worldwide player data. If we examine the table structure there is no "League" or "Country" field, so instead we must filter the data by club name. There are twenty Premier league clubs.
First approach - Table query filter list
In the first approach we will add a series of filters in the database source block options.
We add a database source block to the Omniscope workflow, enter our connection details and click "Connect" to establish a connection. The default query type is "Table". We select the table containing the data we exported in the setup phase.
We can add one or more filter rules by clicking the "+" button. Each filter requires us to enter a field, condition and value. We must therefore add a filter for each Premier League club individually; 20 filters in total.
Before executing we need to change the filter condition to match on ANY of the filters.
We can now execute the block to obtain the filtered data.
Second approach - Custom SQL query input parameter
In the second approach we will use an input dataset to filter the data.
We create a CSV file containing the names of all the premier league clubs and import the data into the workflow using a File source block.
Now we connect the file source block to our database source block.
This time we select the "Custom SQL" query type. If you are unfamiliar with SQL there are many online tutorials available, for example:
https://www.w3schools.com/sql/
The statement allows us to use a field as a parameter by simply inserting [[FIELD_NAME]]. When the statement is executed Omniscope will convert this to a comma separated list of all unique values in that field.
We enter the following custom SQL statement.
We can now execute the block to obtain the filtered data.
This approach is particularly useful if your filter values are likely to change over time. We can use any block as an input, so we can generate the data using an external process or use a connector, such as Twitter or Reddit. Custom SQL input parameters are not currently supported in live query mode.
Now we can create a cool report to share with our friends...
0 Votes
0 Comments
Login or Sign up to post a comment