CLAIRE Copilot for Data Integration > Using CLAIRE Copilot with Data Ingestion and Replication > Database Ingestion and Replication examples of creating a task
  

Database Ingestion and Replication examples of creating a task

Let's look at some examples of configuring a database ingestion and replication task in CLAIRE Copilot

Example: Create a database ingestion task for a combined load from SQL Server to PostgreSQL

You want to replicate data from a SQL Server database to a PostgreSQL database that you use as the primary data store for analytics.
First you need to perform a point-in-time initial load of data to the target and then switch to replicating DML changes in near real time continually to keep the target up to date. To do this, let's create a database ingestion and replication combined initial and incremental load task in CLAIRE Copilot
CLAIRE Copilot picks the connections from your context scope, asks for any schemas and source tables that you didn't include in the prompt, and creates the task.
  1. 1Open CLAIRE Copilot.
  2. 2In the context scope bar, make sure the active context scope includes your SQL Server and PostgreSQL connections. If it doesn't, change or edit the context scope. For more information, see Manage the context scope.
  3. 3In the Ask CLAIRE box, type the following prompt Create an ingestion combined load task with a SQL Server source and a PostgreSQL target. Then click Submit.
    4. CLAIRE Copilot selects the PostgreSQL_CDIR target connection and the SQLSERVER_CDIR source connection. In this example, the context scope contains only one connection of each type, so Copilot selects them for you.
  4. 4Review the runtime environment message.
    5. In this example, Copilot reports that Working_QA_Agent has been selected as your runtime environment, and it does not ask you to choose one.
    This step is skipped if you previously set a default runtime environment for your use.
  5. 5At the request for the PostgreSQL target schema, type the name of the schema, or type @ to see a list of available target schemas. In this example, Copilot found two target schemas, public and test. Type 2.
    6. Copilot confirms that test has been selected as your target schema.
  6. 6At the request for the source schema, click a schema on the card that appears, or type the name of the schema. Alternatively, type @ to see a list of available source schemas.
    7. In this example, Copilot found 22 source schemas. Type dbo.
    Copilot confirms that dbo has been selected as your source schema.
  7. 7At the request for the source tables or objects, type Select all to select all objects in the source schema. To select a subset, enter include or exclude rules with object names or wildcard patterns such as Include prod*, in the order you want Copilot to apply them.
  8. 8
    9. CLAIRE Copilot confirms that it applied the selection rules.
  9. 9The Database Ingestion Task Specification appears. Review it to confirm your settings, including the Combined load type.
  10. 10At the question about generating the task, click Yes and then click Submit.
    11. CLAIRE Copilot generates and saves the task, and displays its name, a link to it, and the project that contains it. Click the link to open the task in the task configuration wizard.
  11. 11Verify the task definition.
    12. You can edit field values and set additional fields on the Task Details, Transform, and Let's Go pages. Toggle on Show Advanced Options to see all fields. However, do not change the source and target connections.
    If you edit the task, save it. Otherwise, the task is ready to deploy.
  12. 12Deploy the task to create a job instance.
    13. You can now run the job from My Jobs or another monitoring interface.

Example: Create a database ingestion task for a combined load from Oracle to PostgreSQL

You want to load data from a Oracle database to a PostgreSQL database that you use as the primary data store for analytics. You also want to keep the databases in sync to ensure up-to-date information for real-time analytics.
To do this, let's create a database ingestion and replication combined initial and incremental load task in CLAIRE Copilot. We've already gathered the source and target connection names and schema names so we'll include that information in the first prompt to streamline the process.
  1. 1Open CLAIRE Copilot.
  2. 2In the Ask CLAIRE box, type the prompt Create an ingestion task that performs an initial data load and then captures ongoing changes from an Oracle source to a PostgreSQL target. Use the source AIN_Oracle connection and DBMI schema. Use the target pgsql connection and test schema. Then click the Submit icon.
    3. A summary of task information is displayed:
  4. 3At the prompt for selecting source tables, type "select all" for all tables in the source schema or enter table names or name masks that include the * and ? wildcards. In this example, let's include table name masks that use the * wildcard to represent one or more characters.
    5. A summary of the task definition appears for your review:
  5. 4Review the summary to verify your settings:
    6. Summary of info entered for the task at the first prompt and during the conversation.
  6. 5To create the task asset, click Yes and click the Submit icon.
    7. CLAIRE Copilot generates the task and opens it in the task configuration wizard.
  7. 6Verify the task definition.
    8. You can edit field values and set additional fields on the Task Details, Transform, and Let's Go pages. Toggle on Show Advanced Options to see all fields. However, do not change the source and target connections.
    For example, on the Let's Go page, enter a custom name and description for the task and your project location so that you can find the task again easily.
  8. 7Save the task.
  9. 8Deploy the task to create a job.
    10. You can now run the job from My Jobs or another monitoring interface.