In this section, you will find information about how to manage and configure the United Manufacturing Hub cluster, from customizing the cluster to access the different services.
This is the multi-page printable view of this section. Click here to print.
Administration
- 1: Access the Database
- 2: Access Services From Within the Cluster
- 3: Access Services Outside the Cluster
- 4: Access Kafka Outside the Cluster
- 5: Expose Grafana to the Internet
- 6: Install Custom Drivers in NodeRed
- 7: Execute Kafka Shell Scripts
- 8: Reduce database size
- 9: Delete Assets from the Database
- 10: Change the Language in Factoryinsight
- 11: Explore Cached Data
- 12: Optimize Time Consuming Queries
- 13: Optimize Database Datatypes
1 - Access the Database
There are multiple ways to access the database. If you want to just visualize data, then using Grafana or a database client is the easiest way. If you need to also perform SQL commands, then using a database client or the CLI are the best options.
Generally, using a database client gives you the most flexibility, since you can both visualize the data and manipulate the database. However, it requires you to install a database client on your machine.
Using the CLI gives you more control over the database, but it requires you to have a good understanding of SQL.
Grafana, on the other hand, is for visualizing data. It is a good option if you just want to see the data in a dashboard and don’t need to manupulate it.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Get the database credentials
If you are not using the CLI, you need to know the database credentials. You can find them in the timescale-post-init-pw Secret. By default, the username is factoryinsight and the password is changeme.
...
ALTER USER factoryinsight WITH PASSWORD 'changeme';
...
Access the database using a database client
There are many database clients that you can use to access the database. Here’s a list of some of the most popular database clients:
Name | Free or Paid | Platforms |
---|---|---|
pgAdmin | Free | Windows, macOS, Linux |
DataGrip | Paid | Windows, macOS, Linux |
DBeaver | Both | Windows, macOS, Linux |
For the sake of this tutorial, pgAdmin will be used as an example, but other clients have similar functionality. Refer to the specific client documentation for more information.
Forward the database port to your local machine
- From the Pods section in UMHLens / OpenLens, find the united-manufacturing-hub-timescaledb-0 Pod.
- In the Pod Details window, click the Forward button next to the postgresql:5432/TCP port.
- Enter a port number, such as 5432, and click Start. You can disable the Open in browser option if you don’t want to open the port in your browser.
Using pgAdmin
You can use pgAdmin to access the database. To do so, you need to install the pgAdmin client on your machine. For more information, see the pgAdmin documentation.
Once you have installed the client, you can add a new server from the main window.
In the General tab, give the server a meaningful name. In the Connection tab, enter the database credentials:
- The Host name/address is localhost.
- The Port is the port you forwarded.
- The Maintenance database is postgres.
- The Username and Password are the ones you found in the Secret.
Click Save to save the server.
You can now connect to the database by double-clicking the server.
Use the side menu to navigate through the server. The tables are listed under the Schemas > public > Tables section of the factoryinsight database.
Refer to the pgAdmin documentation for more information on how to use the client to perform database operations.
Access the database using the command line interface
You can access the database from the command line using the psql
command
directly from the united-manufacturing-hub-timescaledb-0 Pod.
You will not need credentials to access the database from the Pod’s CLI.
Open a shell in the database Pod
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Perform SQL commands
Once you have a shell in the database, you can perform SQL commands.
For example, to create an index on the processValueTable:
CREATE INDEX ON processvaluetable (valuename);
When you are done, exit the postgres shell:
exit
Access the database using Grafana
You can use Grafana to visualize data from the database.
Add PostgreSQL as a data source
Open the Grafana dashboard in your browser.
From the Configuration (gear) icon, select Data Sources.
Click Add data source and select PostgreSQL.
Configure the connection to the database:
- The Host is united-manufacturing-hub.united-manufacturing-hub.svc.cluster.local:5432.
- The Database is factoryinsight.
- The User and Password are the ones you found in the Secret.
- Set TLS/SSL Mode to require.
- Enable TimescaleDB.
Everything else can be left as the default.
Click Save & Test to save the data source.
Now click on Explore to start querying the database.
You can also create dashboards using the newly created data source.
What’s next
- See a list of SQL commands
- See how to Delete Assets from the Database
- See how to Reduce the Database Size
- See how to Backup and Restore the Database
- See how to Expose Grafana to the Internet
2 - Access Services From Within the Cluster
All the services deployed in the cluster are visible to each other. That makes it easy to connect them together.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Connect to a service from another service
To connect to a service from another service, you can use the service name as the host name.
To get a list of available services and related ports you can open UMHLens / OpenLens and go to Network > Services.
All of them are available from within the cluster. The ones of type LoadBalancer are also available from outside the cluster using the node IP.
Example
The most common use case is to connect to the MQTT Broker from Node-RED.
To do that, when you create the MQTT node, you can use the service name united-manufacturing-hub-mqtt as the host name and one the ports listed in the Ports column.
The MQTT service name has changed since version 0.9.10. If you are using an older
version, use united-manufacturing-hub-vernemq
instead of
united-manufacturing-hub-mqtt
.
What’s next
3 - Access Services Outside the Cluster
Some of the microservices in the United Manufacturing Hub are exposed outside the cluster with a LoadBalancer service. A LoadBalancer is a service that exposes a set of Pods on the same network as the cluster, but not necessarily to the entire internet. The LoadBalancer service provides a single IP address that can be used to access the Pods.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Accessing the services
The LoadBalancer service provides a single IP address that can be used to access the Pods. To find the IP address, open UMHLens / OpenLens and navigate to Network > Services. The IP address is listed in the External IP column.
To access the services, use the IP address and the port number of the service,
e.g. http://192.168.1.100:8080
.
If you installed the United Manufacturing Hub on your local machine, either
using the Management Console or the command line, the services are accessible
at localhost:<port-number>
.
Services with LoadBalancer by default
The following services are exposed outside the cluster with a LoadBalancer service by default:
- Database at port 5432
- Kafka Console at port 8090
- Kafka Broker at port 9094
- Grafana at port 8080
- MQTT Broker at port 1883
- Node-RED at port 1880
- OPCUA Simulator at port 46010
To access Node-RED, you need to use the /node-red
path, e.g.
http://192.168.1.100:1880/node-red
.
Services without a LoadBalancer
Some of the microservices in the United Manufacturing Hub are exposed via a ClusterIP service. That means that they are only accessible from within the cluster itself. To access them from outside the cluster, you need to create a LoadBalancer service.
Create a LoadBalancer service
If you are looking to expose th Kafka broker, follow the instructions in the Access Kafka outside the cluster page.
For any other microservice, follow these steps to enable the LoadBalancer service:
Open UMHLens / OpenLens and navigate to Network > Services.
Select the service and click the Edit button.
Scroll down to the
status.loadBalancer
section and change it to the following:status: loadBalancer: ingress: - ip: <external-ip>
Replace
<external-ip>
with the external IP address of the node.Scroll to the
spec.type
section and change the value from ClusterIP to LoadBalancer.Click Save to apply the changes.
If you installed the United Manufacturing Hub on your local machine, either using the Management Console or the command line, you also need to map the port exposed by the k3d cluster to a port on your local machine. To do that, run the following command:
k3d cluster edit united-manufacturing-hub --port-add "<local-port>:<cluster-port>@server:0"
Replace <local-port>
with a free port number on your local machine, and
<cluster-port>
with the port number of the service.
Port forwarding in UMHLens / OpenLens
If you don’t want to create a LoadBalancer service, effectively exposing the microservice to anyone that has access to the host IP address, you can use UMHLens / OpenLens to forward the port to your local machine.
- Open UMHLens / OpenLens and navigate to Network > Services.
- Select the service that you want to access.
- Scroll down to the Connection section and click the Forward… button.
- From the dialog, you can choose a port on your local machine to forward the cluster port from, or you can leave it empty to use a random port.
- Click Forward to apply the changes.
- If you left the checkbox Open in browser checked, then the service will open in your default browser.
You can see and manage the forwarded ports of your cluster in the Network > Port Forwarding section.
Security considerations
MQTT broker
There are some security considerations to keep in mind when exposing the MQTT broker.
By default, the MQTT broker is configured to allow anonymous connections. This means that anyone can connect to the broker without providing any credentials. This is not recommended for production environments.
To secure the MQTT broker, you can configure it to require authentication. For that, you can either enable RBAC or set up HiveMQ PKI (recommended for production environments).
If you are using a version of the United Manufacturing Hub older than 0.9.10, then you need to change the ACL configuration to allow your MQTT client to connect to the broker.
Troubleshooting
LoadBalancer service stuck in Pending state
If the LoadBalancer service is stuck in the Pending state, it probably means
that the host port is already in use. To fix this, edit the service and change
the section spec.ports.port
to a different port number.
What’s next
4 - Access Kafka Outside the Cluster
By default the Kafka broker is only available from within the cluster, therefore you cannot access it from external applications.
You can enable external access from the Kafka configuration.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Enable external access from Kafka configuration
From UMHLens / OpenLens, go to Helm > Releases.
Click on the Upgrade button.
Search for the
kafka
section and edit the following values:... kafka: ... externalAccess: autoDiscovery: enabled: true ... enabled: true ... rbac: create: true ...
Click Upgrade.
To verify that the LoadBalancer service is created, go to Network > Services and search for united-manufacturing-hub-kafka-external.
Now you can connect to Kafka from external applications using the node IP and the port 9094.
What’s next
5 - Expose Grafana to the Internet
This page describes how to expose Grafana to the Internet so that you can access it from outside the Kubernetes cluster.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Enable the ingress
To expose Grafana to the Internet, you need to enable the ingress.
- Open UMHLens / OpenLens and go to the Helm > Releases page.
- Click the Upgrade button and search for Grafana.
- Scroll down to the
ingress
section - Set the
enabled
field totrue
. - Add you domain name to the
hosts
field. - Click Upgrade to apply the changes.
Remember to add a DNS record for your domain name that points to the external IP address of the Kubernetes host. You can find the external IP address of the Kubernetes host on the Nodes page in UMHLens / OpenLens.
What’s next
- See how to Access Factoryinsight Outside the Cluster
6 - Install Custom Drivers in NodeRed
NodeRed is running on Alpine Linux as non-root user. This means that you can’t
install packages with apk
. This tutorial shows you how to install packages
with proper security measures.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Change the security context
From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.
Click the Edit button to open the StatefulSet’s configuration.
Press
Ctrl+F
and search forsecurityContext
.Set the values of the
runAsUser
field to 0, offsGroup
to 0, and ofrunAsNonRoot
to false.... securityContext: runAsUser: 0 restartPolicy: Always terminationGracePeriodSeconds: 30 dnsPolicy: ClusterFirst securityContext: runAsUser: 0 runAsNonRoot: false fsGroup: 0 ...
Click Save.
Install the packages
From the Pods section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Install the packages with
apk
:apk add <package>
For example, to install
unixodbc
:apk add unixodbc
You can find the list of available packages here.
Exit the shell.
Revert the security context
For security reasons, you should revert the security context after you install the packages.
From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.
Click the Edit button to open the StatefulSet’s configuration.
Set the values of the
runAsUser
field to 1000, offsGroup
to 1000, and ofrunAsNonRoot
to true.... securityContext: runAsUser: 1000 restartPolicy: Always terminationGracePeriodSeconds: 30 dnsPolicy: ClusterFirst securityContext: runAsUser: 1000 runAsNonRoot: true fsGroup: 1000 ...
Click Save.
What’s next
7 - Execute Kafka Shell Scripts
When working with Kafka, you may need to execute shell scripts to perform administrative tasks. This page describes how to execute Kafka shell scripts.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Open a shell in the Kafka container
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-kafka-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Navigate to the Kafka bin directory:
cd /opt/bitnami/kafka/bin
Execute any Kafka shell scripts. For example, to list all topics:
./kafka-topics.sh --list --zookeeper zookeeper:2181
To exit the shell:
exit
What’s next
8 - Reduce database size
Over time, time-series data can consume a large amount of disk space. To reduce the amount of disk space used by time-series data, there are three options:
- Enable data compression. This reduces the required disk space by applying mathematical compression to the data. This compression is lossless, so the data is not changed in any way. However, it will take more time to compress and decompress the data. For more information, see how TimescaleDB compression works.
- Enable data retention. This deletes old data that is no longer needed, by setting policies that automatically delete data older than a specified time. This can be beneficial for managing the size of the database, as well as adhering to data retention regulations. However, by definition, data loss will occur. For more information, see how TimescaleDB data retention works.
- Downsampling. This is a method of reducing the amount of data stored by aggregating data points over a period of time. For example, you can aggregate data points over a 30-minute period, instead of storing each data point. If exact data is not required, downsampling can be useful to reduce database size. However, data may be less accurate.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Open the database shell
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Enable data compression
To enable data compression, you need to execute the following SQL command from the database shell:
SELECT add_retention_policy('processvaluetable', INTERVAL '7 days');
This command will set a retention policy on the processvaluetable
table, which
will delete data older than 7 days.
Enable data retention
To enable data retention, you need to execute the following SQL command from the database shell:
SELECT add_compression_policy('processvaluetable', INTERVAL '7 days');
This command will set a compression policy on the processvaluetable
table,
which will compress data older than 7 days.
What’s next
9 - Delete Assets from the Database
This is useful if you have created assets by mistake, or to delete the ones that are no longer needed.
This task deletes data from the database. Make sure you have a backup of the database before you proceed.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Also, make sure to backup the database before you proceed. For more information, see Backing Up and Restoring the Database.
Open the database shell
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Choose the assets to delete
You have multiple choices to delete assets, like deleting a single asset, or deleting all assets in a location, or deleting all assets with a specific name.
To do so, you can customize the SQL command using different filters. Specifically, a combination of the following filters:
assetname
location
customer
To filter an SQL command, you can use the WHERE
clause. For example, using all
of the filters:
WHERE assetname = <my-asset> AND location = <my-location> AND customer = <my-customer>;
You can use any combination of the filters, even just one of them.
Here are some examples:
Delete all assets with the same name from any location and any customer:
WHERE assetname = '<asset-name>'
Delete all assets in a specific location:
WHERE location = '<location-name>'
Delete all assets with the same name in a specific location:
WHERE assetname = '<asset-name>' AND location = '<location-name>'
Delete all assets with the same name in a specific location for a single customer:
WHERE assetname = 'my-asset' AND location = 'my-location' AND customer = 'customer'
Delete the assets
Once you know the filters you want to use, you can use the following SQL commands to delete assets:
BEGIN;
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM counttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM ordertable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM processvaluestringtable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM processvaluetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM producttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM shifttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM statetable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM assettable WHERE id IN (SELECT id FROM assets_to_be_deleted);
COMMIT;
Optionally, you can add the following code before the last WITH
statement if
you used the track&trace feature:
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM producttagtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM producttagstringtable WHERE product_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>), uniqueproducts_to_be_deleted AS (SELECT uniqueproductid FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted))
DELETE FROM productinheritancetable WHERE parent_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted) OR child_uid IN (SELECT uniqueproductid FROM uniqueproducts_to_be_deleted);
WITH assets_to_be_deleted AS (SELECT id FROM assettable <filter>)
DELETE FROM uniqueproducttable WHERE asset_id IN (SELECT id FROM assets_to_be_deleted);
What’s next
10 - Change the Language in Factoryinsight
You can change the language in Factoryinsight if you want to localize the returned text, like stop codes, to a different language.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Access the database shell
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Change the language
Execute the following command to change the language:
INSERT INTO configurationtable (customer, languagecode) VALUES ('factoryinsight', <code>) ON CONFLICT(customer) DO UPDATE SET languagecode=<code>;
where <code>
is the language code. For example, to change the language to
German, use 0
.
Supported languages
Factoryinsight supports the following languages:
Language | Code |
---|---|
German | 0 |
English | 1 |
Turkish | 2 |
What’s next
11 - Explore Cached Data
When working with the United Manufacturing Hub, you might want to visualize information about the cached data. This page shows how you can access the cache and explore the data.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Open a shell in the cache Pod
Open UMHLens / OpenLens and navigate to the Config > Secrets page.
Get the cache password from the Secret redis-secret.
From the Pods section click on united-manufacturing-hub-redis-master-0 to open the details page.
If you have multiple cache Pods, you can select any of them.Click the Pod Shell button to open a shell in the container.
Enter the shell:
redis-cli -a <cache-password>
Now you can execute any command. For example, to get the number of keys in the cache, run:
KEYS *
Or, to get the cache size, run:
DBSIZE
For more information about Redis commands, see the Redis documentation.
What’s next
12 - Optimize Time Consuming Queries
When you have a large database, it is possible that some queries take a long time to execute. This especially shows when you are using Grafana and the dropdown menu in the datasource takes a long time to load or does not load at all.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
Your United Manufacturing Hub must be at or later than version 0.9.4. To check the United Manufacturing Hub version, open UMHLens / OpenLens and go to Helm > Releases. The version is listed in the Version column.Open a shell in the database container
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Create an index
Indexes are used to speed up queries. Run this query to create an index on the
processvaluetable
table:
CREATE INDEX ON processvaluetable(valuename, asset_id) WITH (timescaledb.transaction_per_chunk);
Rollback factoryinsight
If you have already created an index, you can rollback the factoryinsight deployment to version 0.9.4. This way it will use a less optimized but faster query, significantly reducing the execution time.
- From the Deployments section in UMHLens / OpenLens, click on united-manufacturing-hub-factoryinsight-deployment to open the details page.
Click the Edit button to open the deployment’s configuration.
- Scroll down to the
spec.containers
section and change theimage
value to unitedmanufacturinghub/factoryinsight:0.9.4. - Click Save.
What’s next
13 - Optimize Database Datatypes
In version 0.9.5 and prior, some tables in the database were created with the
varchar
data type. This data type is not optimal for storing large amounts of
data. In version 0.9.6, the data type of some columns was changed from varchar
to text
. This migration optimizes the database, by changing the data type of
some columns from varchar to text.
Before you begin
You need to have a UMH cluster. If you do not already have a cluster, you can create one by using the Management Console.
From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
psql
Connect to the database:
\c factoryinsight
Alter the tables
Execute the following SQL statements:
ALTER TABLE assettable ALTER COLUMN assetid TYPE text;
ALTER TABLE assettable ALTER COLUMN location TYPE text;
ALTER TABLE assettable ALTER COLUMN customer TYPE text;
ALTER TABLE producttable ALTER COLUMN product_name TYPE text;
ALTER TABLE ordertable ALTER COLUMN order_name TYPE text;
ALTER TABLE configurationtable ALTER COLUMN customer TYPE text;
ALTER TABLE componenttable ALTER COLUMN componentname TYPE text;
Then confirm the changes by using the following SQL statements:
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'assettable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'producttable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'ordertable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'configurationtable';
SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.columns WHERE TABLE_NAME = 'componenttable';