This tutorial teaches you how to select and configure a SQL client in order to access the database schemas used by Semarchy xDM.

What you'll learn

Before you start

This tutorial is the first unit within the Integration track, which is composed of SQL-based and REST-based tutorials.

The current unit is a prerequisite for the SQL-based units of the Integration track:

The sample customer data in this tutorial is modeled after the customers of a bicycle retailer that sells high-end bikes and cycling accessories.

Before following this tutorial, you must have set up Semarchy xDM and completed the Customer B2C tutorial. If you have not yet, follow the Quick Install and Demo Application tutorials from the main Tutorials menu.

GO TO TUTORIALS

The estimated duration of this unit is about 15 minutes for an on-premises database, and 30 minutes if your database is hosted on AWS or Azure.

If you have completed the Quick Install on-premises tutorial, then you already have successfully installed a client and used it to run the create schema statements:

In that case, you can use this client to connect to the xDM repository and data location schemas - simply follow the instructions in the step associated with this client.

If you have not installed any SQL client yet, you can install one of those or use DBeaver, a free universal client. Download DBeaver Community Edition, which is sufficient to follow the Integration tutorials track.

DOWNLOAD DBEAVER

Now that you have selected a SQL client, you are ready to connect to the database.

If you have installed xDM using the the AWS or Azure Quickstart VM:

If you have installed xDM on premises, connect to the database using the default or universal client:

Amazon Web Services (AWS) and Microsoft Azure apply default security practices to lock down access to your virtual machine and the PostgreSQL database that is installed on it.

However, to access the PostgreSQL database and perform tasks such as querying and loading data via the SQL API, you need to:

This step describes how to configure inbound rules on AWS and Azure. The activation of the SSH tunnel is described in the steps dedicated to pgAdmin and DBeaver configuration.

Configure the inbound rules on AWS

In the case of AWS, connecting remotely requires to add 2 inbound rules to your AWS security group to allow SSH and PostgreSQL access:

  1. Navigate to the Amazon EC2 Console.
  2. In the navigation panel, under Network & Security, choose Security Groups.

  1. Retrieve the security group for your instance in the list and open it (left-click on Security Group ID).
  2. On the main page of your Security Group, click Edit inbound rules.

  1. Click Add rule to add a first rule.

  1. Enter the following parameters:

Skip ahead to step 3. Connect to PostgreSQL with pgAdmin or 7. Connect to any database using DBeaver, depending on the SQL client you are using.

Configure the inbound rules on Azure

In the case of Azure, connection through the SSH port (22) is enabled by default. In this section you will check that SSH connections have not been restricted, and enable them if needed only.

  1. Navigate to the Azure Portal.
  2. Click the Virtual Machines button on the button bar.

  1. Click on the name of your virtual machine:

  1. Click Networking in the left menu to access network settings:

  1. The list of existing inbound port rules is displayed. Check if connection using the port 22 (SSH) is enabled for any source, or at least your IP.
  2. If no rule exists for Port 22, click the Add inbound port rule button to add a new rule for the SSH port and enter the following parameters:

  1. Click the Add button to finalize the creation of your security rule.

In the next step, you will connect to the database using pgAdmin. If you want to connect using DBeaver instead, go to step 7. Connect to any database using DBeaver.

In this step, you will add 2 connections in pgAdmin to access the repository and data location schemas for PostgreSQL.

Configure the connection for the Semarchy Repository

Adding a connection for semarchy_repository lets you access xDM metadata.

  1. Right-click on Servers and select Create>Server

  1. Name the connection after the schema name semarchy_repository

  1. Click Connection to enter Connection parameters:

  1. If your database is hosted on an Azure or AWS server, you need to connect using an SSH tunnel: navigate to the SSH Tunnel tab and enter the following parameters:

  1. Click Save to finalize the creation of your new connection.

Configure the connection for the Data Location

Repeat the same operations to add a connection for semarchy_customer_b2c_mdm. This lets you access the data location where business data is stored.

  1. Name the connection after the schema name semarchy_customer_b2c_mdm :

  1. Enter the parameters for connection on the Connection tab:

  1. If your database is hosted on an Azure or AWS server: navigate to the SSH Tunnel tab and enter the following parameters:

  1. Click Save to finalize the creation of this second connection.

Now that you have created connections to PostgreSQL schemas, jump to step 8. Browse the database objects.

In this step you will add 2 connections in SQL Developer to access the repository and data location schemas for Oracle, in addition to the SYSTEM connection.

  1. Add a connection for SYSTEM. This gives you DBA access to the Database.
    Enter the following parameters:

  1. Add a connection for SEMARCHY_REPOSITORY.
    Enter the following parameters:

This lets you access xDM metadata.

  1. Add a connection for SEMARCHY_CUSTOMER_B2C_MDM.

This lets you access the data location where business data is stored.

Now that you have created connections to Oracle schemas, jump to step 8. Browse the database objects.

In this step, you will add 2 connections in SQL Server Management Studio to access the repository and data location schemas.

  1. In Object Explorer, click Connect and then Database Engine.

  1. Enter the following parameters:

  1. Click Connect to test the connection.
  2. Repeat operations 1-2-3 to add the second connection using the following parameters:

Now that you have created connections to SQL Server schemas, jump to step 8. Browse the database objects.

DBeaver allows you to connect to any database (PostgreSQL, Oracle, SQL Server, other). The following screenshots are showing PostgreSQL but can be easily adapted to other databases.

In this step you will add 2 connections:

Configure the connection for the Semarchy Repository

  1. In the Database Navigator tree view, right-click on the blank area and select Create>Connection

  1. Select your database type (in the screenshot below: PostgreSQL) and click Next.

  1. Enter connection parameters:

  1. If your database is hosted on an Azure or AWS server, you need to connect using an SSH tunnel: navigate to the SSH tab and enter the following parameters:

  1. Click Test Connection and check the connection is working. If not, check the parameters again (host, port, username and password).

  1. Back to the connection parameters page, click "Connection details (name, type, ...)" and name the connection after the schema name semarchy_repository, then click on Finish.

Configure the connection for the Data Location

Repeat the same operations to add a connection for semarchy_customer_b2c_mdm:

  1. Access the Connection tab and enter the parameters for connection:

  1. If your database is hosted on an Azure or AWS server, you need to connect using an SSH tunnel: navigate to the SSH tab and enter the following parameters:

  1. Click Test Connection and check the connection is working. If not, check the parameters again (host, port, username and password).

  1. Name the connection after the schema name semarchy_customer_b2c_mdm.
  2. Click OK to finalize the creation.

Now that you have created connections to the database schemas, you are ready to have a look at the database objects.

Now that you are connected to the database, take time to look through the schemas.

  1. Navigate into the semarchy_repository schema

  1. Under the customer_b2c_mdm connection, the customer_b2c_mdm schema contains all tables of the data location used by the Customer B2C demo application. These are the tables that contain business data for each entity (Nickname, Person, Person_Product, Product...) :

Observe that table names include:

  1. See that column structure differs across the sd/sa/md/gd/... tables.

In this first unit of the Integration tutorial track, you learned how to configure a SQL client for Semarchy xDM. You are now ready to query and load data using the SQL API.

What we've covered


What's next?

In the next part of the Integration tutorial track, you will learn how to query data in xDM via the SQL API. You will learn basic and advanced queries as well as integration concepts, such as the difference between fuzzy and basic entities.

Thanks for going through this tutorial.