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:
Create the connection to the source database
Add a BDomain
Add the DBI BDomain settings
Add a CDC group
Add a CDC watcher
Add the BDomain-specific metadata of the watcher
Add a loader
Add the BDomain-specific metadata of the loader
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.