Import Database Table
Import Database Table is a feature that allows admins and analysts to import data from a database table to another. Admins/analysts can also specify the frequency of import to allow the table to be kept in sync with the source. The destination table then can be used in reports/dashboards like a normal table.
#Step 1: Set up a data source to allow data import
Set up your database to allow write access for a given data source. Please have a look at our docs on how to connect your database to Holistics.
#Step 2: Create a new data import
Click Add new data import in Manage -> Data Imports
#Step 3: Select Source Table
Follow the instructions in the image below to select and preview your source table
#Step 4: Select/Create Destination Table
Select your destination data source and schema name. Then enter your destination table name.
#Step 5: Validate input and destination table structure
Follow the steps below:
1) Choose import mode for this import.
2) Setup destination columns' name and type
7) Schedules for current data import can be modified under Schedules header on the left side panel. Optionally, data import can be executed manually by clicking on 'Execute' link for that import from the list.
8) Once you've completed step 4) to Validate Sync Config and fill data import's name, you can Save it.
#Step 6: View, edit and execute your data import
Once saved, your data import can be found at Data Imports Listing
#Import Data from MongoDB Source
Holistics Data Import support loading data from MongoDB (NoSQL) into your SQL reporting database. Due to the NoSQL nature of MongoDB, here are a few things to take note.
- For the nested object, you can use MongoDB dot notation to navigate to the wanted value.
- When specifying
where_clause, you need to specify a JSON object similar to
find()method of MongoDB.
- Please see this on how to Import Records In A Custom Range
See an example of a transport file based on the MongoDB sample restaurants dataset:
Note: if you're using incremental data import on MongoDB, and your increment column is of type
datetime, please add the following to your transport JSON:
If you want to fetch data in a list of objects to separate column values. Here is a quick example of how to use dot notation.
Took the collection below as an example, column
size is a nested object
- We then click + New Column to add new columns, as you can see, we access child object by using
- Save and execute the Data Import, and Voilà!
In the case that you have an array inside, for example
and you want to get the value of
colour from the list, you could try to +New Column to add new columns and then access child object inside the array by using:
favorites.0.colourin order to get the first object in the array (blue)
favorites.1.colourin order to get the second object in the array (orange)