Creating a Pipeline

You can perform either of the following:

• Import a Pipeline SQL or
• Create a New pipeline

NOTE There are two types of pipelines, namely: Visual Pipeline and SQL Pipeline. For your convenience, whenever the generic keyword “Pipeline” is used in the document, it specifically refers to Visual Pipeline.

NOTE The SQL Pipeline feature is available for Execution type Snowflake, BigQuery and Databricks SQL.

Importing a Pipeline SQL

For details on importing a pipeline SQL, see Importing SQL.

Creating a New Pipeline

Prerequisite Ensure a domain is available. For details on creating a domain, see Managing Domains.

Following are the steps to add a new pipeline to the domain:

• Click the Domains menu and click the required domain from the list. You can also search for the required domain.
• Click the Pipelines icon.
• In the Pipelines page, click the New Pipeline button.

• In the New Pipeline page, select Create new pipeline in the drop-down list.

NOTE To create a duplicate of an existing pipeline, select the pipeline from the Duplicate Existing Pipeline drop-down list and enable the checkbox to retain properties such as target table name, target schema name, target HDFS location, analytics model name, analytics model HDFS location, MapR-DB table path in import.

• Enter the following details:

• For Spark with Unity catalog enabled environment, below are the additional mandatory fields:

NOTE Infoworks will not create a staging catalog and staging schema if it does not exist. Only target catalog and target schema will be created when not present.

• For Domains with Snowflake environment, enter the following details:

• For Domains with BigQuery environment, enter the following details:

• For Domains with Databricks SQL execution environment, enter the following details:

NOTE Pipeline created in Databricks SQL are compatible with Spark Execution Type and can be changed later in the pipeline settings page.

• Click Save. The new pipeline will be added to the list of pipelines.

Limitation

• The H2O machine learning library is not supported in interactive mode.

NOTE If you are creating a pipeline for the first time, it will be a Visual Pipeline by default. To create the SQL Pipeline, refer to Creating a New Pipeline Version.

Using Spark

Currently, v2.0 and higher versions of Spark are supported. Spark as execution engine uses the Hive metastore / unity catalog to store metadata of tables. All the nodes supported by Hive and Impala are supported by the Spark engine.

NOTE Unity Catalog is only supported for unity catalog enabled data environments.

For Spark with Unity catalog enabled environment, below are the additional mandatory fields:

Limitations of Spark

• Parquet causes issues with the decimal datatype. This affects the pipeline targets that include the decimal datatype. You are recommended to cast any decimal datatype to double when using in a pipeline target.
• The number of tasks for the reduce phase can be tuned using the sql.shuffle.partitions setting. This setting controls the number of files and can be tuned per pipeline using the dt_batch_sparkapp_settings configuration in the Advanced Configuration option.
• Column names with spaces are not supported in Spark v2.2 but supported in v2.0. For example, column name as ID Number is not supported in Spark v2.2.

Best Practices

For best practices, see General Guidelines on Data Pipelines.