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.csvin the directory/tmpon the RED server. The file contains data from division EAST.
2.1. Prepare the SQL-server¶
Make sure that the database ‘red_demo’ and the schema ‘emp’ 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 are never physically deleted; therefore, an incremental load is safe.
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 Application and Security Manager Account in Exasol¶
As SYS user create the user ‘ADAM’ and grant the roles R_APPMGR and R_SECMGR, which make this user Application Manager and Security Manager. With p_authentication NULL the user gets the default password, which must be changed at the first login.
1execute script API_SCURTY.CREATE_USER (
2 'ADAM' -- p_user_name
3 ,'HU' -- p_user_type
4 ,null -- p_authentication (default password)
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 receive a mail which tells you how to change the default password. Otherwise log in as ADAM with the default password and change it:
1alter user ADAM identified by "<new password>" replace "Firstpw123+";
Note
‘Firstpw123+’ is the default password for human users after the installation. Change it by setting the parameter sec_hu_def_passwd with API_SCURTY.ADM_SET_PARAM.
Finally, check the user settings in SCURTY.REP_ALL_USERS.
2.4. Open a Business Version¶
Changes to the PSA metadata (chapter 4) require an open business version. Check the current business version in REDSYS.ADM_CURRENT_BVERSION. If its status is CLOSED, open a new one as application manager:
1execute script API_REDADM.OPEN_BV(
2 '1.0.1' -- p_version
3 ,'RED quickstart' -- p_desc
4);
2.5. Configure BDomains in RED¶
As application manager ‘ADAM’ add the two BDomains for the divisions 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 result in REDADM.RED_BDOMAINS.
2.6. 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’, switch to it and suspend the DB jobs:
[redsys@red01 ~]$ dev1
----------------------------------------------------------
current environment: DEV1
exit with CTRL-D
----------------------------------------------------------
DEV1 [redsys@red01 dev1]$ redctl suspend
DEV1 [redsys@red01 dev1]$