This tutorial will help you select and configure an SQL client for accessing the database schemas used by Semarchy xDM.

Learning outcomes

Learning track

This tutorial is the first unit within the Data Publishing & Consumption track, which is composed of SQL-based and REST-based tutorials.

The current unit is a prerequisite for the next SQL-based units of the Data Publishing & Consumption 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 beginning this unit, you must have set up Semarchy xDM and completed the Customer B2C demo tutorial. If you have not yet, follow the Quick Install and Demo Applications tracks from the main Tutorials menu.

GO TO TUTORIALS

Otherwise, enjoy this tutorial!

If you have completed the Install Semarchy xDM on-premises tutorial, then you already have successfully installed a client and used it to run the schema creation 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 section 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 Data Publishing & Consumption tutorial track.

DOWNLOAD DBEAVER

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

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

If you have installed xDM on a local machine, 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 section describes how to configure inbound rules on AWS and Azure. The activation of the SSH tunnel is described in the sections dedicated to pgAdmin and DBeaver configuration.

Configure the inbound rules on AWS

In the case of AWS, connecting remotely requires adding two 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. Click on Security Group ID to retrieve the security group for your instance in the list.
  2. On the main page of your security group, click Edit inbound rules.

  1. Click Add rule to add the first rule.

  1. Enter the following parameters:

Skip ahead to ⓸ Connect to PostgreSQL with pgAdmin or ⓻ Connect to any database with 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. On the button bar, click the Virtual Machines button.

  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 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.

Congratulations!

You have successfully enabled remote connections to PostgreSQL.

Next, you will connect to the database using pgAdmin. If you want to connect using DBeaver instead, go to ⑦ Connect to any database with DBeaver.

In this section, you will add two 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 (i.e., semarchy_repository).

  1. Click Connection and enter the following connection parameters:

  1. (Optional) 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. Right-click on Servers and select Create > Server.
  2. Name the connection after the schema name (i.e., semarchy_customer_b2c_mdm).

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

  1. (Optional) 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.

Congratulations!

Now that you have successfully established connections to PostgreSQL schemas, jump to ⑧ Browse the database objects.

In this section, you will add two 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.
  2. 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.

Congratulations!

Now that you have successfully established connections to Oracle schemas, jump to ⑧ Browse the database objects.

In this section, you will add two 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, and 3 to add the second connection using the following parameters:

Congratulations!

Now that you have successfully established connections to SQL Server schemas, jump to ⓼ Browse the database objects.

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

In this section, you will add two 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 the following connection parameters:

  1. (Optional) 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 whether 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, ...).
  2. Name the connection after the schema name (i.e., semarchy_repository).
  3. Click on Finish.

  1. Click the OK button to save the connection.

Configure the connection for the data location

Repeat the same operations to add a connection for semarchy_customer_b2c_mdm:

  1. In the Connection Settings window, access the Main tab and enter the following 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. Name the connection after the schema name (i.e., semarchy_customer_b2c_mdm).
  2. Click OK to finalize the creation.

Congratulations!

Now that you have successfully established connections to the database schemas, you are ready to have a look at the database objects.
  1. Open the semarchy_repository connection and go to databases > postgres > schemas in the left navigation panel.
  2. Browse the semarchy_repository schema and unfold the Tables element.
    This schema is composed of technical tables which contain xDM metadata.

  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.
    Below are the tables that contain business data for each entity (Nickname, Person, Person_Product, Product, etc.).

  1. Observe that table names include:
  1. Observe that the column structure differs across the SD/SA/MD/GD/... tables.

Well done! In this first unit of the Data Publishing & Consumption track, you have learned how to configure an SQL client for Semarchy xDM. You are now ready to query and load data using the SQL API.

Learning recap

Next steps

In the next unit of the Data Publishing & Consumption track, Query data via the SQL API, 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-matching and basic entities.

To explore other resources, return to the Tutorials menu.

GO TO TUTORIALS

Thank you for completing this tutorial.