Relational Databases

Pyramid allows you to connect to relational databases by choosing the required server and database. You can then choose to copy selected tables from the given database, or to directly query the database.

Supported Relational Databases

Pyramid currently supports the following relational databases:

  • ClickHouse
  • DB2
  • DB2 AS400
  • Denodo
  • Exasol
  • Firebird
  • MySQL
  • Netezza
  • Oracle
  • PostgreSQL
  • SAP Hana
  • SAP IQ
  • SQL Server
  • SQL Server Azure
  • Teradata
  • Vertica

 

  • Click here to learn more about connecting to a SAP Hana datasource.

Connect to a Relational Database

To connect to a relational database, add the relevant Source node to the Data Flow. With the Source node selected, go to the Properties panel.

Select the server where the required database is stored. The server drop-down list (red highlight below) exposes the servers of the current source type that have been configured by an Admin. If you don't see the required server in the list, click the refresh button (purple arrow below). Admin users can add servers here by clicking the Add Server button (green arrow).

Once you've selected the server, choose the required database from the second drop-down list (blue highlight):

At this point, you can either enable direct querying of the database, or use the ETL tools to load a new data model into a target.

To enable direct querying, select the checkbox beside 'Direct Query Data Source' (orange highlight above). In this case, there is no need to create a flow diagram; instead you can progress immediately to Data Modeling to define the semantic layer of logic that determines how the database is queried.

If your data set requires manipulation or cleansing, you should opt to build a data flow diagram. To do this, do not enable direct querying.

  • Click here to learn more about direct querying and data ingestion.

Table Selection

Go to the Tables window (image below) to choose which tables to copy into the new data model. Table selection is relevant for both direct querying and data ingestion. Select the tables to copy, and then select 'Add Tables' to assign each table to an individual node, or select 'Add as Multi-Select' to copy all tables to a single node using the multi-select function.

Your selected tables will be added to the data flow using the given function. In this example, the 'Add Tables' function was used to add 4 tables:

Another way of adding tables from the datasource to the data flow is via the Select functions. You can connect 'Table' or 'Tables' nodes to the source node and then input the column(s) for each select operation. Another option is to use the SQL Query node to copy a data set from the source using an SQL or SOQL expression.

  • Click here to learn more about the Select functions.

ClosedExample. Connect to a Relational Database and Add Tables

In this example, the Azure node was used to connect to a database on an Azure server (green arrow below). Five tables were then selected and added using the Table function (blue arrow). The process was documented in the Description window.

ClosedExample. Connect to a Relational Database and Add Multi-Select Table

In this example, the Azure node was used to connect to a database on an Azure server. All tables in the database were then added to the data flow using the multi-select Tables function (blue arrow below). The process was documented in the Description window.

ClosedExample. Direct Query a Relational Database

In this example, the Azure node was used to connect to a database on an Azure server. Direct querying was enabled, and then 2 tables were deselected from the Tables window.

Because direct querying was selected, the 'Add' buttons in the Tables window are disabled, and the Azure node cannot be connected to any other nodes. The next step in this process is Data Modeling.