7. Import a Table from a Source Database

This step-by-step guide loads a table from a source database into the TSA layer.

The example uses a table with information about people in the schema testDB of the SQL Server database TESTING.

Example: Test Table in SQL Server

 1create table testDB.Person
 2(
 3    CustomerId bigint,
 4    FirstName  varchar(100),
 5    LastName   varchar(100),
 6    Salary     decimal(8,2),
 7    Birthday   date,
 8    CreatedOn  datetime not null,
 9    UpdatedOn  datetime,
10    primary key (CustomerId)
11);
12
13insert into testDB.Person (CustomerId, FirstName, LastName, Salary, Birthday, CreatedOn, UpdatedOn)
14values
15(5971250571, 'Jane', 'Doe', 3500.64, '19710525', getdate()-5, null),
16(6755130370, 'Max', 'Mustermann', 3500.65, '19700313', getdate()-3, getdate()-1);

The following steps are necessary in RED:

  1. Create the connection to the source database

  2. Add a BDomain

  3. Add the DBI BDomain settings

  4. Add a CDC group

  5. Add a CDC watcher

  6. Add the BDomain-specific metadata of the watcher

  7. Add a loader

  8. Add the BDomain-specific metadata of the loader

  9. Create the TSA table

Before you start, suspend the DB jobs on the RED server, so that no load starts with incomplete metadata:

Example: Suspend the DB Jobs

redctl suspend

More information about the RED server and its commands can be found in the RED-PSS Guide.

Hint

These steps are the same whether the data is historised as an SCD Type-2 or Type-3 table, as the historisation type is only defined in the PSA layer.

7.1. Create the Connection to the Source Database

The loader reads the source via an Exasol connection. If it does not exist yet, a database administrator creates it, e.g. for SQL Server via JDBC (the JDBC driver must be installed in Exasol):

Example: Create the Connection ‘SQL_SERVER’

1CREATE CONNECTION SQL_SERVER
2    TO 'jdbc:sqlserver://<host>:1433;databaseName=TESTING'
3    USER '<user>' IDENTIFIED BY '<password>';

7.2. Add a BDomain

Make sure the BDomain exists or add it first: Add a BDomain.

Check that the BDomain ‘TESTING’ exists in REDADM.RED_BDOMAINS.

7.3. Add the DBI BDomain Settings

Add the DBI-specific BDomain settings: Add DBI BDomain Settings.

Check the settings in DBIADM.DBI_BDOMAIN_SETTINGS.

7.4. Add a CDC Group

Add the CDC group ‘tst_cdc_grp’: Add a CDC Group.

Check the CDC group in DBIADM.DBI_CDC_GROUPS.

7.5. Add a CDC Watcher

Add a watcher that triggers the load once a day: Add a CDC Watcher.

Check the settings in DBIADM.DBI_CDCG.

7.6. Add the BDomain-Specific Metadata of the Watcher

Add BDomain-Specific Metadata to a CDC Watcher.

Check the settings in DBIADM.DBI_CDCG_BD.

7.7. Add a Loader

Add the loader ‘TST_PERS_LDR’: Add a DBI Loader.

Check the settings in DBIADM.DBI_LOADER.

7.8. Add the BDomain-Specific Metadata of the Loader

Add BDomain-Specific Metadata to a Loader.

Check the settings in DBIADM.DBI_LOADER_BD for the loader ‘TST_PERS_LDR’ and the BDomain ‘TESTING’.

7.9. Create the TSA Table

Create the TSA table with API_DBIADM.CREATE_TSA_TABLES. Unlike a CFI loader, the columns do not have to be defined, as the loader reads the structure from the source database.

API_DBIADM.REFRESH updates the column lists from which the DBI connector builds its import statements, so that the new TSA table can be loaded.

The TSA table is named <BDOMAIN>_<LDR_NAME>, i.e. DBITSA.TESTING_TST_PERS_LDR. The names of all TSA tables are listed in DBIADM.DBI_LOADER_DETAILS.

Tip

You can first define the metadata of all loaders and then create all missing TSA tables at once by calling API_DBIADM.CREATE_TSA_TABLES with NULL for both parameters.

If the structure of the source table changes later, apply the changes to the TSA table with API_DBIADM.REFRESH_LOADER.

7.10. Resume the DB Jobs

The load starts as soon as the DB jobs are resumed and the watcher detects a change:

Example: Resume the DB Jobs

redctl resume

The loads are logged in DBIADM.DBI_CDC_LOG_DETAILS. To load the data into the PSA layer, continue with Load DBI Data into the PSA.