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:

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

1. From the Pods section in UMHLens / OpenLens, find the united-manufacturing-hub-timescaledb-0 Pod.
2. In the Pod Details window, click the Forward button next to the postgresql:5432/TCP port.
3. 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.

1. Once you have installed the client, you can add a new server from the main window.

   

   

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

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

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

4. Connect to the database:

   \c factoryinsight
   

Perform SQL commands

Once you have a shell in the database, you can perform SQL commands.

1. For example, to create an index on the processValueTable:

   CREATE INDEX ON processvaluetable (valuename);
   

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

1. Open the Grafana dashboard in your browser.

2. From the Configuration (gear) icon, select Data Sources.

3. Click Add data source and select PostgreSQL.

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

   

   

5. Click Save & Test to save the data source.

6. Now click on Explore to start querying the database.

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

• See Access the MQTT Broker Outside the Cluster
• See Access Factoryinsight Outside the Cluster
• See Access Kafka Outside the Cluster

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:

1. Open UMHLens / OpenLens and navigate to Network > Services.

2. Select the service and click the Edit button.

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

4. Scroll to the spec.type section and change the value from ClusterIP to LoadBalancer.

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

1. Open UMHLens / OpenLens and navigate to Network > Services.
2. Select the service that you want to access.
3. Scroll down to the Connection section and click the Forward… button.
4. 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.
5. Click Forward to apply the changes.
6. 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.

Port forwarding can be unstable, especially if the connection to the cluster is slow. If you are experiencing issues, try to create a LoadBalancer service instead.

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

1. From UMHLens / OpenLens, go to Helm > Releases.

2. Click on the Upgrade button.

3. Search for the kafka section and edit the following values:

   ...
   kafka:
   ...
     externalAccess:
       autoDiscovery:
         enabled: true
       ...
       enabled: true
     ...
     rbac:
       create: true
   ...
   

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

• See Access the MQTT Broker Outside the Cluster
• See Access Factoryinsight Outside the Cluster
• See Access Services from Within the Cluster

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.

1. Open UMHLens / OpenLens and go to the Helm > Releases page.
2. Click the Upgrade button and search for Grafana.
3. Scroll down to the ingress section
4. Set the enabled field to true.
5. Add you domain name to the hosts field.
6. 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

1. From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.

2. Click the Edit button to open the StatefulSet’s configuration.

   

   

3. Press Ctrl+F and search for securityContext.

4. Set the values of the runAsUser field to 0, of fsGroup to 0, and of runAsNonRoot to false.

   ...
          securityContext:
            runAsUser: 0
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      securityContext:
        runAsUser: 0
        runAsNonRoot: false
        fsGroup: 0
   ...
   

5. Click Save.

Install the packages

1. From the Pods section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

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

4. Exit the shell.

Revert the security context

For security reasons, you should revert the security context after you install the packages.

1. From the StatefulSets section in UMHLens / OpenLens, click on united-manufacturing-hub-nodered to open the details page.

2. Click the Edit button to open the StatefulSet’s configuration.

   

   

3. Set the values of the runAsUser field to 1000, of fsGroup to 1000, and of runAsNonRoot to true.

   ...
          securityContext:
            runAsUser: 1000
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      securityContext:
        runAsUser: 1000
        runAsNonRoot: true
        fsGroup: 1000
   ...
   

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

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-kafka-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Navigate to the Kafka bin directory:

   cd /opt/bitnami/kafka/bin
   

4. Execute any Kafka shell scripts. For example, to list all topics:

   ./kafka-topics.sh --list --zookeeper zookeeper:2181
   

5. To exit the shell:

   exit
   

What’s next

• See Access Kafka outside the cluster.

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

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

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

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

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

• See Backing Up and Restoring the Database

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

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

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

What’s next

• See Access the Database

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

1. Open UMHLens / OpenLens and navigate to the Config > Secrets page.

2. Get the cache password from the Secret redis-secret.

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

4. Click the Pod Shell button to open a shell in the container.

   

   

5. Enter the shell:

   redis-cli -a <cache-password>
   

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

• Access the database

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.

Open a shell in the database container

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

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

1. From the Deployments section in UMHLens / OpenLens, click on united-manufacturing-hub-factoryinsight-deployment to open the details page.

2. Click the Edit button to open the deployment’s configuration.

   

   

3. Scroll down to the spec.containers section and change the image value to unitedmanufacturinghub/factoryinsight:0.9.4.
4. Click Save.

What’s next

• See Access the Database
• See Reduce Database Size

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.

1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

2. Click the Pod Shell button to open a shell in the container.

   

   

3. Enter the postgres shell:

   psql
   

4. 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';