2. Prerequisites¶
Before you can start with this tutorial, you need a functioning RED environment and an MS-SQL server from which we will import data.
Make sure that you have uploaded a proper JDBC driver for MSSQL to your Exasol cluster. (see https://docs.exasol.com/db/latest/administration/on-premise/manage_drivers/add_jdbc_driver.htm?Highlight=jdbc upload)
Copy the sample CSV data to a file named EMP_person_20250901_20250902144500.csv on the RED-server into the /tmp directory. The file contains data from division EAST.
2.1. Prepare the SQL-server¶
Make sure that a databse ‘red_demo’ and schema ‘emp’ already exist.
In the database ‘red_demo’ in the schema ‘emp’ create an employee table of division WEST:
Each time a row is changed the column UpdatedOn is set to the current time.
Rows can be logically deleted by setting the Deleted flag to true.
We assume that rows will never be physically deleted and, therefore, we it would be safe to configure an incremental load.
1USE red_demo;
2
3create table emp.Person
4(
5 EmpId bigint,
6 FirstName varchar(100),
7 LastName varchar(100),
8 Salary decimal (8,2),
9 Birthday date,
10 Department varchar(10),
11 CreatedOn datetime not null,
12 UpdatedOn datetime,
13 Deleted bit,
14 primary key (EmpId)
15);
Now create some entries:
1USE red_demo;
2
3insert into emp.Person (EmpId, FirstName, LastName, Salary, Birthday, Department, CreatedOn, UpdatedOn, Deleted)
4values
5('001', 'William', 'Cooper', 3500.64, '19720707', 'HR', convert(date, '2025-08-25'), null, null),
6('002', 'Cynthia', 'Wilkes', 4500.55, '19651010', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null),
7('003', 'Robert', 'Stanton', 6400.00, '19680726', 'IT', convert(date, '2025-08-30'), convert(date, '2025-09-01'), null);
Finally create a read only user RED_RO:
1CREATE LOGIN red_ro
2WITH PASSWORD = 'Sx123456';
3
4USE red_demo;
5CREATE USER red_ro FOR LOGIN red_ro;
6GRANT SELECT ON emp.person TO red_ro;
2.2. Prepare the Connection in Exasol¶
As SYS user create a connection in Exasol to the SQL-server.
1create connection RED_DEMO_MSSQL to 'jdbc:sqlserver://<ip_address>:<port>;trustServerCertificate=true'
2user 'RED_RO' identified by 'Sx123456'
3
4grant connection RED_DEMO_MSSQL to R_OWNER_DBITSA;
5grant access on connection RED_DEMO_MSSQL to R_OWNER_DBITSA;
Finally test the connection:
1import from jdbc at RED_DEMO_MSSQL statement 'select 1';
2.3. Create an Appilication & Security Manager Account in Exasol¶
As SYS user create a user ‘king’ and grant him the roles R_APPMGR and R_SECMGR. These two roles make this user an application and security manager.
1execute script API_SCURTY.CREATE_USER (
2 'ADAM' -- p_user_name
3 ,'HU' -- p_user_type
4 ,'sx123' -- p_authentication
5 ,true -- p_private_sbx
6 ,null -- p_quota
7 ,'AppMgr. & SecMgr.' -- p_comment
8 ,'<your mail address>' -- p_email
9);
10
11grant R_APPMGR, R_SECMGR to ADAM;
If the RED framework has been configured properly you should receive a mail which tells you how to change the deault password. If for whatever reason you have not received such a mail, use the following sequnce to reset the password:
1alter user ADAM identified by "sx123" replace "Firstpw123+";
Note
‘Firstpw123+’ is the RED installation default password for HUs. You should change it by setting the parameter sec_hu_def_passwd in SCURTY.ADM_PARAMS.
Finally, check the user settings in SCURTY.REP_ALL_USERS.
2.4. Configure BDomains in RED¶
As application manager ‘ADAM’ add the two BDomains for divions EAST and WEST.
1execute script API_REDADM.ADD_BDOMAIN(
2 'DIV_EAST' -- p_bdomain
3 ,'file loads from div. EAST' -- p_description
4);
5
6execute script API_REDADM.ADD_BDOMAIN(
7 'DIV_WEST' -- p_bdomain
8 ,'DB loads from div. WEST' -- p_description
9);
Check the table REDADM.RED_BDOMAINS for an entry with this name in the field ‘BDOMAIN’.
2.5. Logon and Suspend the Framework¶
Logon to the RED-server and switch to the redsys user:
sudo -iu redsys
Assuming that you have prepared an environment named ‘DEV1’ swich to it and suspend the framework:
[redsys@red01 ~]$ dev1
----------------------------------------------------------
current environment: DEV1
exit with CTRL-D
----------------------------------------------------------
DEV1 [redsys@red01 dev1]$ redctl restart -S
INFO MainProcess[2025-08-30 16:00:41]: stopping server
INFO MainProcess[2025-08-30 16:00:41]: server stopped
INFO MainProcess[2025-08-30 16:00:42]: CTL-version: 1.2.16
INFO MainProcess[2025-08-30 16:00:42]: DB Name: red_dev1
INFO MainProcess[2025-08-30 16:00:42]: Exasol version: 8.31.0
INFO MainProcess[2025-08-30 16:00:42]: BVersion: 1.0.0 (2024-06-04 10:17:57 - OPEN)
INFO MainProcess[2025-08-30 16:00:42]: TVersion: 3.10.0 (2025-07-30 09:17:13 - OPEN)
INFO MainProcess[2025-08-30 16:00:42]: starting server (DB jobs SUSPENDED)
DEV1 [redsys@red01 dev1]$