Loading data

Relational Modeling uses load queries to load data into tables. This applies in standard integration tables and in custom staging tables. You use a load query to transfer data from a source system directly to an integration table, or to staging tables. You can write queries to transfer data from one staging table to another.

In a load query, you specify a SELECT or SELECT AS query to load data from a source table. If the columns match, the result set of the query is loaded into the table.

Note: Data loads are fastest if the target table has no primary key, thus removing the requirement to check that each record is unique. For more methods to accelerate loads, see the Optimizing Load Performance section.

If the source table has columns that are not in the target table, you can add those columns while running the load query.

Caution: Load queries are canceled after 2 hours. Do not develop load queries, mappings, or scripts that take longer than 2 hours to run. You can specify a time-out for queries, up to a maximum of 2 hours, in the properties of the Modeling Service.

A banner is displayed while scripts and load queries are running. In the banner, you can cancel scripts that you are running, or which another user is running.

Note: 

When a load query is run on a table through an API request, concurrent requests that target the same table are ignored. Only the first query is run. In the user interface, if a load query is being run, the Load button is disabled for another query processing on the same table.

Optimizing load performance

Use these techniques to reduce load times for load queries and scripts:

  • Load changed data: Use incremental loads to transfer records modified since the last import. Incremental loading is usually the biggest gain for recurring loads. See Incremental data loads.
  • Avoid unnecessary primary keys: Tables without a primary key, or with ExistingRowsHandling set to Abort, use a faster INSERT execution mechanism. Add a primary key when uniqueness must be enforced.
    Note: Without a primary key, each load appends rows instead of updating existing ones. Repeated loads can accumulate duplicate data and grow the Staging database. Clear the table before reloading, or use a primary key with Update, to avoid duplicates. See Creating load queries.
  • Load what you require: Select required columns and filter rows in the query. Use Load Data instead of Load Columns and Data unless you are adding columns. See Running a load query manually.
  • Parameterize queries: Custom settings and parameters become SQL parameters, enabling query-plan reuse. See Using custom settings.
  • Run independent loads in parallel: Load queries and scripts can run simultaneously through the Relational Modeling dashboard, API Gateway or ION API, or Application Engine. See Methods of running load queries.
  • Stay within the time-out: Loads are canceled after 2 hours. Split large data sets into smaller operations to stay within the limit. See Scripts.