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.

General#

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

Add new data import

Step 3: Select Source Table#

Follow the instructions in the image below to select and preview your source table

Setup Source

Step 4: Select/Create Destination Table#

Select your destination data source and schema name. Then enter your destination table name.

Setup Destination

Step 5: Validate input and destination table structure#

Follow the steps below:

Validate input and destination table structure

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

New Data Import

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:

{
  "from_ds_id": "31-mongo-source",
  "from_table_name": "restaurants",
  "dest_ds_id": "11-pg-reporting-db",
  "dest_table_name": "public.restaurants",
  "columns": [
    {
      "column_name": "restaurant_id",
      "data_type": "int"
    },
    {
      "column_name": "name",
      "data_type": "varchar"
    },
    {
      "column_name": "borough",
      "data_type": "varchar"
    },
    {
      "column_name": "zipcode",
      "data_type": "varchar",
      "source_expression": "address.zipcode"
    }
  ],
  "default_mode": "incremental",
  "increment_column": "restaurant_id",
  "where_clause": {
    "borough": { "$ne": "Brooklyn" }
  }
}

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: "increment_column_type": "datetime"

Use case#

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 size.h, size.w and size.uom

- Save and execute the Data Import, and Voilà!

In the case that you have an array inside, for example

{

  Name:"abc",

  email:"[email protected]",

 favorites:[{colour:"blue",place:"bangalore"},{colour:"orange",place:"bangalore"}]

}

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.colour in order to get the first object in the array (blue)
or favorites.1.colour in order to get the second object in the array (orange)