Technical Articles

September 27, 2021 10 minute read

Automatically Add Members to Spaces in SAP Data Warehouse Cloud Using @sap/dwc-cli

This blog post is part of a series of blogs I published about @sap/dwc-cli. Find all blog posts related to this topic in my overview blog post here.

The @sap/dwc-cli Node.js module is available since wave 2021.20. In another blog “New Command-Line Interface for SAP Data Warehouse Cloud – code your way to the cloud!” I describe how to install and use the command-line interface (CLI). With this blog I’m introducing you to one of the most asked use cases for the CLI: automated mass-member assignment to a space in SAP Data Warehouse Cloud. Wow! 🙂

Introduction

This blog is part of a blog post series about the Command-Line Interface (CLI) for SAP Data Warehouse Cloud. Find the full overview of all available blogs in the overview blog post here.

With the @sap/dwc-cli Node module being available, you can easily interact with the space management area in your SAP Data Warehouse Cloud tenant. In this blog, I demonstrate how you can use the @sap/dwc-cli module to assign a bunch of members specified in a `*.json` file to one of your spaces with two commands in only a few seconds instead of many manual clicks in the UI taking minutes or hours.

After you have read the blog you will know how to automatically retrieve passcodes to send commands to your SAP Data Warehouse Cloud tenant, work with the space definition and add members to your space.

Please note that this description only applies to customers of SAP Data Warehouse Cloud that use the SAP-provided default Identity Provider. If you use a custom Identity Provider, eg Azure AD, which comes with a different HTML page for handling user logins, the description below cannot be applied 1:1, but you have to adjust the code to log in and retrieve the passcode accordingly, matching the HTML pages of your custom Identity Provider.

Project Setup

As we will create a Node.js project we have to make sure that Node is installed in our environment.

To check whether Node is installed run

$ node -v
v12.21.0

If you receive a similar output you’re good to go! Otherwise, check out the Node website and the information for setting up Node according to your environment.

Next, we create a new folder and initialize our Node project by running

$ mkdir cli-add-users
$ cd cli-add-users
$ npm init --yes

The –yes option added to the npm init command creates a default package.json file sufficient for our case and should look like this:

{
  "name": "dwc-cli-add-users",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}

Now, the only missing step is to create an empty index.js file next to the package.json file we will fill with some content in the next section. To create the file run if you are using a Unix-like operating system:

$ touch index.js

The folder cli-add-users contains the following files now:

cli-add-users
|- package.json
|- index.js

Tipp: If you want to push your code to a git repository, you also want to create a .gitignore file in the root folder with the following content:

// .gitignore

node_modules

Installing Dependencies

For our project, we require a few Node dependencies like the @sap/dwc-cli module and other modules to read and write files (fs-extra), a headless chromium browser for automated passcode retrieval (puppeteer), and the path module helping us with directories and files paths.

To install the dependencies run

$ npm install @sap/dwc-cli fs-extra puppeteer path

Running this command installs the CLI locally in your project into the node_modules folder. If you instead want to install the CLI globally or might did so already earlier, you can also remove it from the list of dependencies and install it by running

$ npm install -g @sap/dwc-cli

Note that if you want to use the globally installed CLI you have to replace ./node_modules/@sap/dwc-cli/dist/index.js with dwc in the code example explained in this blog post.

Now we are good to continue with the fun part, the implementation!

Creating the Executable Skeleton

When we later run our Node program by calling node index.js we need to write a function that is executed. Add the following content to the index.js file:

// index.js

(async () => {
    console.log('I was called!');
})();

We created an asynchronous function printing a simple statement to the console. When you now run node index.js you will the following:

$ node index.js 
I was called!

Defining the Environment

What we want to implement is a list of tasks where we interact with the @sap/dwc-cli module to

retrieve the definition of an existing space and store it locally
read the `*.json` file containing the additional members to add to the space
update the locally stored space definition file with the additional members
push the updated space definition back to our SAP Data Warehouse Cloud tenant

To carry out the four steps we need to know

where the `*.json` file containing the new members is located
where the space definition shall be stored locally
the URL of the SAP Data Warehouse Cloud tenant
the passcode URL of the SAP Data Warehouse Cloud tenant
which space to update
a business username and password used for reading and updating the space in the SAP Data Warehouse Cloud tenant

Let’s go ahead and declare some variables with the required information (make sure to replace the information wrapped in <…>).

const MEMBERS_FILE = 'members.json';
const SPACE_DEFINITION_FILE = 'space.json';
const DWC_URL = 'https://<prefix>.<region>.hcs.cloud.sap/'; // eg https://mytenant.eu10.hcs.cloud.sap/
const DWC_PASSCODE_URL = 'https://<prefix>.authentication.<region>.hana.ondemand.com/passcode'; // eg https://mytenant.authentication.eu10.hana.ondemand.com/passcode
const SPACE = '<space ID>';
const USERNAME = '<username>'; // eg firstname.lastname@company.com
const PASSWORD = '<password>';

(async () => {
    console.log('I was called!');
})();

Retrieving the Passcode

For each command we execute through @sap/dwc-cli we have to provide a unique, non-reusable passcode we have to retrieve from the URL specified with DWC_PASSCODE_URL first. We use puppeteer for this, which is a headless chromium able to run in the background and can be controlled programmatically.

When we run our program we create a browser instance in headless mode and navigate to the passcode URL, enter the username and password and wait for the Temporary Authentication Code page to show up. Let’s replace the console.log statement with some meaningful code.

Note: I’m omitting code we’re not touching in the individual steps for readability. The full content of index.js is added again at the end of this post. Also, the example grows incrementally which results in partially duplicated code, for example, to retrieve the passcode multiple times during the process. The final version shown at the end of this blog post contains an optimized version of the example we create during this post.

const puppeteer = require("puppeteer");

const MEMBERS_FILE = 'members.json';
[...]
const PASSWORD = '<password>';

(async () => {
    const browser = await puppeteer.launch();
    const page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }

    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000});   
    const passcode = await page.$eval('h2', el => el.textContent);
    
    console.log('passcode', passcode);

    await browser.close();
})();

The implementation starts a new Browser instance, navigates to the passcode URL, checks whether the user is logged in already and if not, logs the user in, waits for the Temporary Authentication Code page to appear, retrieves the passcode, logs it to the console, and closes the Browser instance again.

When you now call node index.js you will find a new passcode logged to the console each time you execute the program:

$ node index.js 
passcode qjm4Zlrza2

If you want to see what’s happening for testing your implementation, you can change await puppeteer.launch() to await puppeteer.launch({ headless: false }) and watch the browser do its magic. 😉

Finally, let’s move the part where we retrieve the passcode from the page to a separate function because we need to call it a few times while reading and updating the space:

[...]
const PASSWORD = '<password>';

let page;

const getPasscode = async () => {
    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000}); 
    await page.reload();  
    return await page.$eval('h2', el => el.textContent);
}

(async () => {
    const browser = await puppeteer.launch({ headless: false });
    page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }

    const passcode = await getPasscode();
    
    console.log('passcode', passcode);

    await browser.close();
})();

Reading the Space Definition

To read or update a space we call the locally installed CLI as follows using a separate function. The path.join() call points to the locally installed module.

const puppeteer = require("puppeteer");
const path = require('path');
const exec = require("child_process").exec;

    [...]
    return await page.$eval('h2', el => el.textContent);
}

const execCommand = async (command) => new Promise(async (res, rej) => {
    const passcode = await getPasscode();
    const cmd = `node ${path.join(process.cwd(), 'node_modules', '@sap', 'dwc-cli', 'index.js')} ${command} -H ${DWC_URL} -p ${passcode}`;
    exec(cmd, (error, stdout, stderr) => {
        if (error) {
            rej({ error, stdout, stderr });
        }
        res({ error, stdout, stderr });
    });
});

(async () => {
    const browser = await puppeteer.launch({ headless: false });
    [...]

Please note: With version 2022.2.0 the call to the main.js file changed. Previously, it looked like this (the dist segment got removed and main.js was renamed to index.js with 2022.2.0): const cmd = `node ${path.join(process.cwd(), ‘node_modules’, ‘@sap’, ‘dwc-cli’, ‘dist’, ‘main.js’)} ${command} -H ${DWC_URL} -p ${passcode}`;

The function execCommand expects a command, for example, spaces read -S <Space ID> –output <path>, and always attaches the global mandatory parameters like -H, –host, and -p, –passcode.

Every time you call execCommand it retrieves a new passcode, thus we can remove the const passcode = await getPasscode() call from the main function.

In order to read our space, we then call the function like this in our main function:

    [...]
        await page.click('#logOnFormSubmit');
    }

    await execCommand(`spaces read -S ${SPACE} -o ${SPACE_DEFINITION_FILE}`)

    await browser.close();
})();

Set SPACE to the actual space ID to read and run the program. When finished, you will find a space.json file next to the index.js file containing the space definition.

{
  "<Space ID>": {
    "spaceDefinition": {
      "version": "1.0.4",
      [...]
      "members": [],
      [...]
    }
  }
}

Adding New Members

To add new members to the space we have to

create a file members.json containing the list of members we want to add to the space,
read and parse the file containing the list of members using fs-extra.readFile() and JSON.parse(),
merge the list with the list of already assigned members as defined in the space definition file we retrieved from the tenant,
store the updated members list in the local file using fs-extra.writeFile() and
push the updated space definition back to the tenant.

We create the members.json file using the same structure for the list of members used in the space definition file, an array of objects with two properties name containing the user ID and type set to the constant value user.

[
    {
        "name": "<User ID 1>",
        "type": "user"
    },
    {
        "name": "<User ID 2>",
        "type": "user"
    },
    {
        "name": "<User ID 3>",
        "type": "user"
    },
    [...]
]

Add as many objects to the array as members you want to add to the space and use the correct user IDs for the name property.

Reading and parsing the members.json file and space.json file can be achieved as follows:

[...]
const exec = require("child_process").exec;
const fs = require('fs-extra');
[...]

  await execCommand(`spaces read -S ${SPACE} -o ${SPACE_DEFINITION_FILE}`);

  const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
  const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

  await browser.close();
})();

Merging the list of additional members with the list of existing members and writing it back to disk can be done like this:

  [...]
  const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
  const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

  spaceDefinition[SPACE].spaceDefinition.members = spaceDefinition[SPACE].spaceDefinition.members.concat(additionalMembers);

  await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

  await browser.close();
})();

Finally, we need to update the space using the modified local file. To do so, we execute the spaces create command and provide the modified space.json file. Note that we retrieve a new passcode automatically when calling execCommand().

    [...]
    await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

    await execCommand(`spaces create -f ${SPACE_DEFINITION_FILE}`);

    await browser.close();
})();

Conclusion

Et voilà, there you go! The space was updated successfully and the new members have been correctly assigned. You see, assigning many members to a space in SAP Data Warehouse Cloud is not so complicated when using the @sap/dwc-cli CLI. Of course, the greatest benefit comes with automating the creation of the list of members to be assigned which we hard coded in the members.json file. If you replace the respective lines of code with an automated process to, for example, convert an excel sheet into the right JSON format and merge this with the list of existing members assigned to your space, that’s then where the beauty lies! To close, here’s the full code example for your reference:

const puppeteer = require("puppeteer");
const path = require('path');
const exec = require("child_process").exec;
const fs = require('fs-extra');

const MEMBERS_FILE = 'members.json';
const SPACE_DEFINITION_FILE = 'space.json';
const DWC_URL = 'https://<prefix>.<region>.hcs.cloud.sap/'; // eg https://mytenant.eu10.hcs.cloud.sap/
const DWC_PASSCODE_URL = 'https://<prefix>.authentication.<region>.hana.ondemand.com/passcode'; // eg https://mytenant.authentication.eu10.hana.ondemand.com/passcode
const SPACE = '<space ID>';
const USERNAME = '<username>'; // eg firstname.lastname@company.com
const PASSWORD = '<password>';

let page;

const getPasscode = async () => {
    await page.waitForSelector('div.island > h1 + h2', {visible: true, timeout: 5000}); 
    await page.reload();  
    return await page.$eval('h2', el => el.textContent);
}

const execCommand = async (command) => new Promise(async (res, rej) => {
    const passcode = await getPasscode();
    const cmd = `node ${path.join(process.cwd(), 'node_modules', '@sap', 'dwc-cli', 'index.js')} ${command} -H ${DWC_URL} -p ${passcode} -V`;
    exec(cmd, (error, stdout, stderr) => {
        if (error) {
            rej({ error, stdout, stderr });
        }
        res({ error, stdout, stderr });
    });
});

(async () => {
    const browser = await puppeteer.launch({ headless: true });
    page = await browser.newPage();
    await page.goto(DWC_PASSCODE_URL);

    await page.waitForSelector('#logOnForm', {visible: true, timeout: 5000}); 
    if (await page.$('#logOnForm') !== null) {
        await page.type('#j_username', USERNAME);
        await page.type('#j_password', PASSWORD);
        await page.click('#logOnFormSubmit');
    }
    
    await execCommand(`spaces read -S ${SPACE} -o ${SPACE_DEFINITION_FILE}`);

    const spaceDefinition = JSON.parse(await fs.readFile(SPACE_DEFINITION_FILE, 'utf-8'));
    const additionalMembers = JSON.parse(await fs.readFile(MEMBERS_FILE, 'utf-8'));

    spaceDefinition[SPACE].spaceDefinition.members = spaceDefinition[SPACE].spaceDefinition.members.concat(additionalMembers);

    await fs.writeFile(SPACE_DEFINITION_FILE, JSON.stringify(spaceDefinition, null, 2), 'utf-8');

    await execCommand(`spaces create -f ${SPACE_DEFINITION_FILE}`);

    await browser.close();
})();

Please note: With version 2022.2.0 the call to the main.js file changed. Previously, it looked like this (the dist segment got removed and main.js was renamed to index.js with 2022.2.0): const cmd = `node ${path.join(process.cwd(), ‘node_modules’, ‘@sap’, ‘dwc-cli’, ‘dist’, ‘main.js’)} ${command} -H ${DWC_URL} -p ${passcode}`;